Performance on linked tables

[Stanley]

I notice that I can open a table in my back end database rather quickly. When
I open the same table from another application that has a link to that table,
I have a noticable delay (2 seconds say).
I also tried running a query directed on my backup with good performance.
When I ran the same query from my front end with links to the tables in the
query, my performance was degraded.
Any ideas.
Thanks,
Stanley

 

[Bryan in Bakersfield]

There are at least two issues that may be affecting your performance.

Whenever one database accesses another (like the front end doing something
in the back end), the front end database attempts to delete the .ldb locking
file for the back end and create a new one. If there is no one else in the
database the delay is negligible. When someone else is already in the back
end, it takes longer. See
http://www.granite.ab.ca/access/performanceldblocking.htm for more
information. If your running more than one query, set up a form that opens a
table in the back end and stays open. This will keep the connection alive
during your process so the front end won't be logging in and out of the back
end between each query.

There is also an issue with tables created in Access where the subdatasheets
property is set to [Auto]. If there are no subdatasheets the property should
be set to [None]. See
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q261000 for more info
and some VB code to change the properties automatically. This should be done
in both the front end and the back end - they claim it only needs to be done
in the back end but I have noticed performance improvement by running it in
the front end too.

HTH
Bryan

 

[Jerry Whittle]

One trick is to open a form on the FE based on a junk table in the BE. Do
this on startup and hide the form. The idea is that having a persistent
connection between the FE and BE speeds things up.

You also might want to turn off your virus checker for a few quick tests. If
you notice an improvement, see if you can exclude the scanning for mdb or md?
files.

Turning off Name AutoCorrect seems to help.

Actually instead of typing up all the above, I should have just mentions
Tony Toews' excellent web site on performance issues:
http://www.granite.ab.ca/access/performancefaq.htm