How can I have this code run faster?

Question

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.

Code.txt

Answer 1

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.

Answer 2

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