Access queries become slow after SQL Update via ODBC

[snkelsey]

I have an Access 2010 database originally created by our customer that is separated into a file for tables and a file for forms/queries. The table file is in a shared folder on a server running Windows Server 2012 64bit and the forms file is on each users desktop.

When a user runs a particular query from their desktop it takes almost 2 minutes the first time, but only 3 seconds each time after that (the customer originally created the database and this performance is acceptable).

There is now a different system that interacts with the Access table file via ODBC (system DSN using 64bit drivers on the Windows server). When this new system sends a SELECT to Access everything works fine and the Access query still takes 2 minutes or 3 seconds to run.

However, after the new system sends an UPDATE via ODBC the Access query takes almost 4 minutes, regardless of if it is run for the first time or not. This happens even if the SQL updates a table unrelated to the Access query.

I have noticed that when the ODBC statement runs it creates an entry in the laccdb file but this entry does not disappear after the statement finishes. Even if I delete the entry from the file it gets reconstituted when running the Access query. Closing and reopening Access will clear the laccdb file and the query will return to the 2 minute/3 second behavior.



I don't know much about Access but it seems that after performing an UPDATE via ODBC the ODBC user is persisted in Access and somehow slows the query. This particular query uses fields from a form (I have read that this can slow performance) and in fact removing the fields from the query can speed it up, but I'd like to know why it slows down after ODBC updates.

Can anyone shed some light on my problem?

- Stephen

[mbenton]

ODBC tables can slow things down at times...I usually set a parameter in the query that limits the data. When there's not as much data the query works faster.