MS Access 2007 and SQL Server 2000

Question

I've recently been upgraded to Office 2007. I have several Access databases (that I've kept in the Access 2000 format for several reasons) that are linked to SQL Server 2000 databases. I have dozens of queries in these databases that I use often. I create new queries daily, sorting, summarizing and generally analyzing the data.

Since the upgrade, some queries take an extremely long time to complete (minutes rather than seconds), and one new one I've tried to run doesn't complete at all, I have to end task on Access. It's a rather simple query, it joins 3 tables, and sorts on one of the fields. I do this ALL THE TIME, and now it appears I can't.

I've searched for discussions of similar problems, but haven't seen specific recommendations.

Any ideas?

Answer 1

I would suggest deleting all your ODBC linked tables and recreating them from scratch as a starting point.

Answer 2

If your queries do not need to make any changes to the data you may find converting them to SQL Pass through queries will speed them up considerable. Note these queries are not parsed through the Jet DB Engine but sent directly to the server and bypass any linked tables.

You will have to use MS SQL syntax and lose the QBE grid though and the result will be read only.

If you need to update data then maybe stored procedures are the way to go.