MS Access query design hangs on connection to SQL Server

Question

Microsoft Access is a slick way to access data in a MS SQL Server backend database, but I've always had problems accessing (so to speak) large tables of data, especially when trying to toggle between results and design mode in Access.

Access gives me a number of nifty things, not the least of which is Crosstabs, but this hung connection to the server drives me a little crazy!

Does any MS Access gurus know how to optimize the ODBC connection so it isn't doing what appears to be full table scans when I just want to tweak and build my queries?

Answer 1

Have you considered an Access project (ADP)? It can be much faster than an mdb for SQL Server back-end.

http://office.microsoft.com/en-us/access/HP052731031033.aspx

Answer 2

Unfortunately Access is not able to push a lot of that work to the server, and yes, it will do huge table scans when designing queries against multiple tables or views in SQL Server.

You can build and tweak queries (views) in SQL Server using SSMS and store the views in SQL Server for a massive performance boost and still use Access for your front end.

Answer 3

The ODBC driver will pass as much work as possible to SQL Server but as soon as you use a vba function like Nz or non-SQL Server syntax like PIVOT then the ODBC driver must pull back more data and indexes to get the work done on the client side.

As per other answer either build your views in SQL Server and link to the views or else use an Access Data Project.

NB: PIVOT queries with unknown number of columns cannot be handled in SQL Server in the same way that Access will do this natively - so if you run a pivot in Access against SQL Server data you will likely pull the whole table back. Pivot queries must be built in SQL Server using dynamic SQL techniques or else pre-saved views that have all the columns hard coded. Check out this link for one way to do this:

http://www.sqlservercentral.com/articles/Advanced+Querying/pivottableformicrosoftsqlserver/2434/

Answer 4

As others have said, the only way to improve performance on large tables is to have the SQL Server database engine do the work for you. A method of doing this which hasn't been mentioned is to use a pass-through query, which will enable you to keep all your code in MS Access, without having to create objects on the SQL Server:

http://support.microsoft.com/kb/303968

You will have to write SQL Server T-SQL rather than the Access dialect; however, SQL 2005 (when running in compatibility mode 90) does support a PIVOT command.

Answer 5

My similar problem was that the ORACLE ODBC connection hung after selecting the Link table/ODBC connection. Task manager said not responding after 10's of minutes. The connection then pings ORACLE for all available tables. I had turned on logging on the ORACLE ODBC Administrator, so it had to write all these things to the log, slowing any results by perhaps hours. The log was 60 MB one hour later, when I turned it off, then everything was fine!

To turn it off go to the Oracle installation/Network Administration/MS ODBC Adminstrator/Tracing tab and turn it OFF!

A good resource on ODBC is here: http://eis.bris.ac.uk/~ccmjs/odbc_section.html