How can I have this code run faster?

Lopez, Ahiezer 236

I have this code that is executed only when I open a form. I press a button that runs a macro and that macro simply opens the form. Once this form is opened, the code in the "On Load" event is triggered, this code can be seen below. This code calls on two different functions each used to calculate the deadlines using a table that has the company holidays.

The code all in all takes about 7 minutes to open, and thats with the form only having 13 records in it. How can I make this code execute faster?

By the way, the Access database is linked to an SQL server so the tables come from an SQL server.

The code was attached as a text file because the format of the code was getting messed up in the question.

User's image

Code.txt

2 answers

AlphonseG 201 Reputation points

2024-07-11T18:57:16.68+00:00

You are opening an entire SQL Server table (or whatever), walking through each row then for each, opening multiple separate recordsets, then updating each record.

This is a classic definition of SLOW CRAWL.

Don't see why all of these updates should be necessary every time the form is open.
First, I would question the database design.
Next, all of whatever processing is necessary should be moved to the server and done in a bulk/batch process. NOT row by row.

You will need to provide much more detail.
Please sign in to rate this answer.

1 person found this answer helpful.
Lopez, Ahiezer 236 Reputation points

2024-07-11T20:33:18.5633333+00:00

I agree I dont think I need to update each record every time the form opens.

The reason why im doing this is because this form gets new records every week. These new records come with some empty fields and we populate those fields. Those fields being populated are the ones you see being updated by the code. These updated fields are updated based on holidays, workdays, and type of record (meaning does it need a deadline). Furthermore these updated fields are calculated based on a date that the new record already comes with. Im sorry if im not explaining all that well but that's why I didnt do this in bulk.

AlphonseG 201 Reputation points

2024-07-11T21:03:54.8233333+00:00

You could probably get some improvement by limiting the number of records that need updating by using some criteria. However, it is the process itself that is flawed. The best way you are going to get a radical improvement is to convert to a batch process executed in SQL Server and called via a passthrough query from Access. If schema and indexing is good, I would guess that you would get your 7 minutes down to seconds.

If you aren't fluent enough in T-SQL, then take the time to learn. If you are responsible for an app using SQL Server back end, I guarantee you won't be sorry. You will find so many ways to make improvements. I've been working with SQL Server back ends for about 25 years and still get excited with it's capabilities.

Barring that, copy tblHolidays (one time at start of process) into an array (search for GetRows) and do your lookups in the array. That will save all of those additional round trips to the server for each row to be updated.
The next best would be to copy tblHolidays to a local temp table (one time at start of process) and do your lookups from that.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Karl Donaubauer 1,891 Reputation points MVP

2024-07-11T11:34:10.2533333+00:00

Hi,

You don't show us the VBA code to see if there's anything that might last 7 minutes.

Probably more important would be to know your Access version and build number (see File - Account) because there's a recent/current bug that causes performance problems.

Servus
Karl

Access News
Access Forever
Access DevCon
Access-Entwickler-Konferenz AEK
Please sign in to rate this answer.
Lopez, Ahiezer 236 Reputation points

2024-07-11T16:54:18.1833333+00:00

Sorry I didnt know that. I thought perhaps my code is inefficient.

This is my version.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How can I have this code run faster?

2 answers

Your answer