Forms slow to open

[MICHELE]

Some of the forms in my database take up to 5 minutes to open. I do have subforms in some of them (not all of them). Is there anything I can do to speed them up? Once we're in, they move pretty quickly, just opening is so slow.

 

[Dgavilanes]

Is your database connected to a network?
When was the last time you compress the database?
Does your computer have enough memory?
Dennis

 

[MICHELE]

Only the tables are connected to the network. Users run the application from their C drives. I compact and repair every day.
Could memory be a factor on everyone's computer?
Any other ideas?

 

[Dgavilanes]

We experience the same problem from time to time, and I know is the network.
To proof a point, if you copy the tables from your network drive and placed them locally C:drive then link your DB to the local tables you will see Db move faster.Or
just open a local file.
I read before that is not a good idea to compact the Db daily, I do it only when I make changes or add forms etc, otherwise once a month.
You be the judge

 

[MICHELE]

I like the idea of not compacting everyday, but I'm curious why doing it everyday is not good.

 

[Pat Hartman]

When you compact the database, Jet needs to "compile", "optimize", and save an access plan for each query before it is run for the first time. So although it is good to compact the tables on a regular basis so that Access will update its internal table statisitcs that are used for optimizing queries, you probably don't need to do it every day.

Two more things can make forms slow to load:
1. Many combo or list boxes (each requires its own query to run) or other "complex" form controls.
2. Form based on a table or a query without criteria. This forces Access to retrieve the entire contents of the table that the form is looking at thus causing unnecessary network traffic.

 

[PMrider]

go to TOOLS/OPTIONS then go to the GENERAL tab and uncheck all of the NAME AUTOCORRECT. This will speed up your forms opening up.

 

[MICHELE]

Unchecking autocorrect really made a big difference.
Thanks!

 

[Jonathan Kok]

What's the purpose of Name AutoCorrect? What's going to change if I disable it? (I'm having the same problem with forms taking forever to load)

 

[Keith Nichols]

uncheck all of the NAME AUTOCORRECT.

Great tip. Thanks.

Is there a way to check the status of the Name Autocorrect features when the DB loads and, if checked, uncheck them? I suppose, to be polite, they should be reset to whatever they were when the DB loaded.

This is already sounding complicated.

Maybe the best solution is just to inform users that they can change these settings to speed things up.

 

[KB280882]

Thanks for the tip, unchecking Autocorrect worked great.

 

[missinglinq]

What's the purpose of Name AutoCorrect? What's going to change if I disable it? (I'm having the same problem with forms taking forever to load)

Name AutoCorrect checks for object name changes, and if, for example, you go into the Table Design and change the name of a field, Access will change the name of that field anywhere (well, almost anywhere) that it appears in the entire database.

Obviously this only has value during the development stage of the database (and some question even that) but cannot possibly have value after the db is deployed for use!

In addition to slowing an app down, as talked about here, it has also been indicted as a cause of chronic, repeated corruption.

 

[dbDamo]

Another tip I was given was to create a form based on a small, simple table and open this form in hidden mode in an autoexec macro. This form then stays open in the background ensuring connection with the back end is constant.

This, along with the unchecking of Name Autocorrect, has made my DB much faster...

 

[itsmebdye]

I have a similar problem however only appears to be persistent in Access 2010. I have a database that I originally developed in Access 2003, only certain forms in the database seam to be taking a long time to open (5 minutes or more) and this only occurs when opening the database in Access 2010. I have tried turning off Name Autocorrect and it did not help. Is there any sort of debugging I can do to determine what is hanging up the form? Any other helpful suggestions would be much appreciated.

 

[EMonk]

To throw another issue into the mix, we have an Access front-end that connects to several SQL databases via ODBC. One of our databases was running remarkably slow - 40+ seconds to open a form with 2x subforms, all connected to tables in the one database.

The database in question is an MS SQL Express server running on one of our web servers. The ODBC server address was similar to 10.0.0.10\EXPRESS,2333 which is outside our normal 192.168.8.* network. Server ping times were good (all <1ms) and network speed high enough that data throughput wasn't a concern.

After much mucking about with network settings, ODBC options and so on we finally tracked the issue to ODBC requiring a DNS response for the server even though it was connecting by IP address. I fixed our DNS servers to return correct responses for 10.0.0.10 and the form load time dropped down to ~2 seconds.

Moral of the story: if you have an ODBC connection that specifies the server by IP address, make sure that your DNS is configured to return a hostname for it... or if you don't have a full DNS server, add the server to your hosts file.

 

[liamfitz]

I don't have 'NAME AUTOCORRECT' under General in Tools/Options ?