SQl Server, Access2002, and locking of tables?

Question

Our company uses a database program that runs on SQL server. I use Access2002 and run some basic queries on the database to retrieve a list of data. We connect to all of this via a Citrix connection. Periodically users are experiencing very bad slowness issues, lockups, etc. When I contacted the company about it they said it is because I am using Access and my queries lock the tables and that cause other users who are actually in the program to experience their problems. The query I run just takes about a second or two to display the data. They said even if the query is completed, as long as it remains open it will keep those tables locked. Does this sound likely? If so, how do I make Access queries not lock the data. If it does not sound like the case to you, do you have any suggestions on a reputable site that may contain some facts to back that up. Unless I can start pointing them to prove that they may be wrong, they will never fix the problem and put the blame on my queries.

Answer 1

The best way to make sure that Access does not lock your data is to force the MS Access users to connect to SQL Server using an ID that has Read Only privileges. If the data cannot be updated, then the data will not be locked.



But to verify, you can connect to SQL Server using Enterprise Manager and check for locks on the data. Go to the server, Go to Management, then Current Activity, then Locks/Process Ids and review the items for locks - try to tie them to users/apps.

Answer 2

Here is a way to check out for yourself what might be going on...this should help you find out what is actually going on in your particular situation. You should have access to the Microsoft SQL Server Enterprise Manager console, which installs with SQL Server itself, and can also be installed on a management workstation. Open up the Management Console, and open the database in question. Open the "Management" subfolder, and expend the "Current Activity" tree. There is a "Locks/Process ID" option that will not only tell you who is in the system at any given time, it will tell you what objects are locked, and by whom.

Answer 3

I have in same type of problem when i was programming in Ingres. But i have found it's solution. The moment you run the query access locks the tables this can be solved by using transaction commit your transaction after the excution of query is completed. This can be done by creating query through macro dialoge box.

You can also try by changing setting

Tools -> Options-> Advanced

And then select No Locks..

Have a good luck