Linked table is slow

[Jerry]

I have an Access 2000 database file with several tables
that are in a normalized, relational structure. It runs
with acceptable speed. Recently, I created a second
database file that is identical to the original except it
uses linked tables to the original file and a query
filter on the core table to limit the number of records
for users in the linked database (about half the
records). Both databases are in the same folder on the C:
drive of my computer during development. The speed in the
linked database is unacceptably slow compared with the
original. Eventually, these files need to be moved to
different folders on a server, which will no doubt slow
performance even more. Is there a way to improve the
performance of the linked database?

TIA Jerry

 

[Arvin Meyer]

Make sure that the filter is on a well designed indexed field. Indexes often
improve query speed by a factor of 10 (or more).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Jerry]

Thanks for the reply Arvin. The filter is on a lookup
field and I already use the index long integer for the
filter.
Let me be more specific (I'm figuring this out while I go
along), the linked database has a startup form using the
filtered query on the linked core table. When I open the
linked file, it opens the database window in a normal
time frame but the startup form takes 30 - 40 seconds to
load. Once the form is loaded, records advance normally.
If I try to view the form in design view, it takes 30-40
seconds for design view to load. Same when returning to
form view. This form pops open immediately in the
original file. Any ideas?

 

[Albert D. Kallal]

You can eliminate that delay..and restore most performance if you force
(keep) a persistent connection to the back end database AT ALL times.

So, in your startup code...simply open any old table to a reocrdset, and
keep it open (you can also just open a form, and minimize it..and make it
invisible also if you wish).

That above trick should get your performance back.

You can read about the above trick..and a bunch of other things to check
here:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Jerry]

Albert

Thanks! That's a great FAQ page you sent.

Jerry