CODEWITHSUNDEEP
ms-access-2007
Christine
Christine

Reputation:

MS Access 2007 and SQL Server 2000

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?

Upvotes: 1

Views: 543

Answers (4)

Aaron Kempf
Aaron Kempf

Reputation: 588

I would use Access Data Projects with SQL Server 2000. It works great when your SQL backend is that old.

Upvotes: 0

JimS-CLT
JimS-CLT

Reputation: 675

When I converted to SQL Server backend, I used SQL Server Migration Assistant. I recommend it highly. It's very good at what it does.

Having said that, I assume you're using linked tables in your FE. I convert slow-moving queries by copying the SQL from Access, then pasting it into a "new query" window on SQL Server Management Studio. Then, working through all the syntax changes one at a time, I convert the query to T-SQL and save it as a view with the same name as the query in Access.

I have a little routine that then renames the Access query to "Local_" and then creates a linked table entry to the view on SQL Server. You'll find that a query that used to run for minutes will run for seconds this way. You can, of course, do this manually.

SQL Server Migration Assistant, by the way, will convert many queries (it doesn't try to convert action queries, only select queries...) with little or no intervention.

Upvotes: 0

Mark3308
Mark3308

Reputation: 1343

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.

Upvotes: 0

David-W-Fenton
David-W-Fenton

Reputation: 23067

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

Upvotes: 1

Related Questions