July 27, 2011 at 1:49 pm
I am a SQL guy not an Access guy, but someone cam to me today with an Access problem.
Essentially when the employee tries to run a query using two (SQL SERVER) linked tables it takes several minutes to return results. A second employee can run the same query and get results returned in just a few seconds. The two employees use separate copies of the access database, but the linked tables and queries are the same. I ended up running SQL profiler and watch while each employee ran the query on their machine. The slower employee's Access DB is actually sending two queries to SQL server and then combing the results on the employees PC. The faster employee's Access DB is sending the SQL command as it is written causing SQL server to do the joining which take's advantage of indexes etc. I seem to remember some option in earlier editions of Access called something like "group queries on server" and I think checking that option would fix my problem. However I am unable to find this option in the unnavigable mess that is the Office 2007 ribbon. Can anyone help me find this option or an option that will achieve my desired results.
Thanks in advance!
July 27, 2011 at 2:35 pm
In general, if Access sees the proper indexing and constraints on the SQL Server tables, it will pass the query syntax to the ODBC driver with then converts it so that SQL Server does the join and passes back just the desired data. With a two table join, it seems to do that pretty reliably - go to 3 or 4 SQL Server tables and all bets are off. I don't know of any option per se in Access 2007 or earlier that will force the query to be run on the server, but another option is to create a "pass-through" query which is native T-SQL that will run on the server. One other point you might check is the version of ODBC driver being used by both users. We recommend you always use the Native Client 10 version - the standard one that comes installed on workstations is the driver for SQL Server 2000, and doesn't always behave well with SQL Server 2005 or 2008.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
July 27, 2011 at 2:40 pm
Ya but if it works on user 1 and not user 2 (assuming the same exact query) what would it behave differently?
Any way to just copy the "working" db to the 2nd user and overwrite his "wrong" db?
July 27, 2011 at 2:48 pm
Thanks for the suggestion, but unfortunately not everything is the same between the two files so I can't simply copy it over.
July 27, 2011 at 2:51 pm
thermanson (7/27/2011)
Thanks for the suggestion, but unfortunately not everything is the same between the two files so I can simply copy it over.
Ok, how about re-importing the object(s) under new name and see if that helps. Maybe you'll be able to spot a really simple change in the code that solves the issue.
July 28, 2011 at 9:33 am
I tried to import the query from the "fast" db to the "slow" one, but got the same results. I was inspired by Ninja's suggestion and ultimately I made a copy of the "fast" one, deleted all the objects in it and imported everything from the "slow" db. Now everything works fine. Somewhere there must be an option that controls how SQL is sent to the server, but good luck finding it.
Thanks for the help.
July 28, 2011 at 9:42 am
Glad it work.
Maybe make sure to keep a backup of the "correct" objects in case both start failing at the same time.
August 2, 2011 at 11:18 am
It may have to do with refreshing the links to the database so it gets all of the current info about the tables and indexes. Creating a new query might force it to review that information again. It would be a good idea to refresh all the links to the tables any time any SQL Server database changes are made - including indexes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply