I am sure I have read that there is something you can do to get a table into “memory” if you use it often, so reducing access time every time you call it, but after 45 minutes of trawling through all my notes, crib sheets and posts I just cannot find it!
The problem I have is a query on an access table takes 29 seconds to return results the first time I run it and 2 seconds the subsequent times (presumably because the link to the table in access is already established). The query is in a front end database and the data in a back end.
When I port the table to SQL Server 2000 (with all the relevant indexes, etc) and run the same query it consistently takes 7 seconds (a great improvement on the 29 seconds, but not so good against the 2!). I link to the table using ODBC. Creating an SQL view to replace the query is, unfortunately, not an option. I am looking to improve this response time somehow and wondered if loading the link in somehow would improve things.
All advice welcomed!
