Just to clarify it's not actually my database and I didn't choose access, I'm just helping a pany out by developing some of their already implemented access database.

Anyway, on some of their forms the forms open and run extremely slowly for no apparent reason. There is one form which takes a long long time to open on everyone's puter but runs fine when it's there and there's another form that runs fine on most people's puters and is practically unusable on a few.

The forms have a few subforms on them and no VBA script running in the background that might cause an endless loop and I am stumped for ideas

I have turned auto namecorrect off and it came up on one of them saying "The recordset cannot be edited" because of some grouping on one of the text boxes but even when I worked my way around that it still ran just as slow

Just to clarify it's not actually my database and I didn't choose access, I'm just helping a pany out by developing some of their already implemented access database.

Anyway, on some of their forms the forms open and run extremely slowly for no apparent reason. There is one form which takes a long long time to open on everyone's puter but runs fine when it's there and there's another form that runs fine on most people's puters and is practically unusable on a few.

The forms have a few subforms on them and no VBA script running in the background that might cause an endless loop and I am stumped for ideas

I have turned auto namecorrect off and it came up on one of them saying "The recordset cannot be edited" because of some grouping on one of the text boxes but even when I worked my way around that it still ran just as slow

• javascript
• database
• performance
• ms-access

• 2 As is stands, this question is impossible to answer. To begin with, we need to know what database it is, it's ballpark size, and what the slow queries look like. – user180326 Commented Sep 15, 2010 at 10:16
• Just to clarify, you question isn't very detailed. Anyway, its not really enough information to answer it properly. – aarona Commented Sep 16, 2010 at 5:13
• 2 To the folks who want to close this question. Why? Because the question is vague? So what? – Tony Toews Commented Sep 16, 2010 at 5:49

3 Answers 3

Tony Toews's Access Performance FAQ is the best place to start, in my opinion.

That said, the problems you describe sound like they fall into two classes:

A. slow opening forms.

B. slow performing forms.

There are two mon causes for A:

1. creation of the LDB file/contention for it with existing users. This problem is usually resolved with some form of the solution from Tony's LDB Locking article. You can tell if this is the cause of the problem if opening the first form is slow, and opening successive forms are not slow if you leave the initial one open. FWIW, I don't use that method, but use a persistent database variable that acplishes the same thing (not the most recent time I've posted the code on SO, but perhaps the one with the best context is here: MS Access: Is there a significant overhead when using CurrentDB as opposed to DBEngine(0)(0)?).

2. out-of-date metadata in linked tables. This can happen if, for instance, you work on the front end on your test server, move it to the production environment and update the connect strings to point to the production back end. Updating the connect string doesn't refresh all the metadata stored in the linked table definitions, and there's no way to actually fully refresh them. So, you have to delete and recreate the linked tables in the production environment. The symptom of this one is that in the test environment forms open immediately or in only a second or two, and in the production environment take a minute or more to open. After opening, they generally work just fine. FWIW, I haven't really seen this problem except in the earliest days of Access 2000 when it was a significant and terrible problem that almost cost me a job (my first A2000 project).

Slow-performing forms is more plicated to fix, but the cause is usually pretty unplicated: the forms are loading too much data at once. Forms with lots of subforms (usually on a tab control) and lots of large bo boxes are the usual culprit. The solution is to not load the subforms/bo boxes until they are actually displayed. In a tab control that means loading the subform for each tab in the tab control's OnChange event. For bo boxes, you'd load them when they are displayed, or if they have too many records in them (over 1000, I'd say), don't load a rowsource until after the user has typed 1 or 2 characters (using the OnChange event of the bo box).

The problem with that is that you're trading one major slowdown (loading all that stuff when the form is first opened) for a number of much smaller slowdowns (loading each subform/rowsource as its needed). It's a trade-off and you have to decide where you want your pain.

There are lots of other things to do, and one thing to examine early on in troubleshooting performance problems is the Recordsource of each main form. Left joins can get really expensive performance-wise and it's a good idea to eliminate any of them that aren't absolutely required. I just recently vastly speeded up a form that had a left join to the parent table from the child table. It was impossible for the child to exist without a parentID in the PK field in the child table, so that left join was pletely unnecessary. Removing it really speeded up navigation from record to record.

Identify the recordsource from the form properties (table/query/sql statement) and run this directly on the affected machine/s. That will help to narrow down the problem as either form or recordsource. You may for example find that one of the forms is referencing a particularly slow query (large tables, multiple joins, dlookups etc) in which case you would need to focus on optimising this.

You might like to read: http://office.microsoft./en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx