Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I use MSSQL MS for most administrative tasks on SQL Server 2008 R2 databases, but I find using Access (via an ADP, not linked tables) to view and edit raw table data to be much more efficient. (This is for sysadmin use only, of course, user access is via a web app, and the tables in question are relatively small, maybe a few thousand rows.)

However, since switching to Access 2010, performance tanked when filtering and sorting tables in an ADP, and watching it via Profiler, I know why -- Access is not only not preparing a WHERE clause for MSSQL to implement the filters, it is asking for the raw table multiple times, regardless which filters are in place. In other words, it does something like this:

SET ROWCOUNT 10000 
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
SET ROWCOUNT 0 
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
SET ROWCOUNT 10000 
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"
SET ROWCOUNT 0 
SELECT "dbo"."MYTABLE".* FROM "dbo"."MYTABLE"

(I left out a few extra statements where it is reading extended column properties.)

What the heck is Access doing here, and how can I make it use SQL Server to perform the filtering and sorting, and to not make multiple trips every time the table filters are changed?

share|improve this question
    
This is a good question, and I don't know the answer, but my intuition tells me that it's using client-side recordset objects and/or bringing the server data over as a copy (in RAM) into a JET/OLEDB database and then using OLEDB to do the filtering. Why in the world it would do that when backed by a SQL server, I have no idea. Maybe it's a setting in the ADP? Maybe it's the driver you used to connect to SQL from the ADP? Certain drivers only support client-side recordsets, while others are more featureful and support server-side. –  ÃŁŁǫǛȉЖΦΤїҪ Oct 24 '12 at 21:51
    
A long shot : try this. –  harrymc Oct 25 '12 at 6:52
    
Apparently I don't have a high enough reputation here to answer my own question, but I found a workable solution. I created a VIEW in MSSQLSM that selects rows from my table based on filters I have in a row in a scratch table designed just for that. I can then edit rows in Access by editing within the view and it is ORDERS OF MAGNITUDE faster. Just have to edit my filter row (which I keep open in another window) and refresh the view as needed. –  richardtallent Apr 5 '13 at 18:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.