Access 2007 Linked table Performance

Question

I have an Access 2007 application (previously in Access 2003) that is running into some performance issues when the linked database tables are on a network drive. In 2003 the application worked perfectly fine. Now in 2007 the speed of data retrieval using sql and a recordset is degraded pretty poorly. The exact area that i am encountering the issue is on:

DIM rs AS NEW ADODB.RECORDSET
rs.Open tsSql, CurrentProject.AccessConnection, iKeyset, iLock

The iKeyset is set to 0 and iLock is set to 3

the rs.Open command is taking from 4 - 5 seconds which is an issue due to the fact that on some of my forms this can happen multiple times on load.

any thoughts?

EDIT: not to mention that I believe that opening and saving forms in design view appears to be slower than normal under these circumstances.

Answer 1

There is a hotfix Description of the Access 2007 hotfix package (Access.msp): August 26, 2008 that mention performance issues however this is very likely included in Access 2007 SP2. Click the Office button >> Access options (lower right hand corner) >> Resources tab and see what the section titled about Microsoft Office Access 2007 shows. Mine states 12.0.6535.5005).

Answer 2

Depending on the SQL statement involved (i.e., your variable 'tsSQL' above) , your recordset should open instantly whether ADO or DAO.

Ideas that come to my mind: is your connection persistent? This seems to cause more of a delay in 2007 than in 2003. Maybe it's dropping and re-establishing the connection each time. With forms this happens when your recordsource is blank and set in code, so one is advised to always have a recordsource such as "tblBlankTable" (a small one-record table just to keep the connection alive all the time in a form that is always open). The 4 to 5 second delay is about what you get when this is not in place. Try making sure you have a form connected to your DB all the time when running your rs.open and see if that works. Possibly you might need a form with an ADO connection open in your case. Lots written out there on Access forums about this.

Is it faster when you are the only user versus when others have the DB with a lock file open? That is the tell-tale sign.

Another idea is whether you indexed everything correctly when you upgraded. Did the underlying DB stay the same or get upsized from MDB to ACCDB? You didn't indicate what is in the tSQL, so maybe you are joining on non-indexed fields or something that is causing that particular tSQL to run slowly and just need to add a primary key.

If your 'tsSQL' involves queries, I've read that when upgrading it is important to recompile all your queries -- go into design mode with each one, then run them, then save them again.

You could try connecting to the DB with your own connection string -- there is one format for MDB/Jet and another for ACCDB/Ace. There are providers for both that one usually uses from Excel to MDB or ACCDB, but maybe could work within Access, at least to debug your problem.