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.
2006-07-13 13:49:41 · 3 answers · asked by Tenn_Tazz26 2 in Computers & Internet ➔ Programming & Design
I don't have access to the actual SQL server management console. The way it works is we connect to the software using a Citrix server connection. When we connect, the only thing we have access to is an icon to connect to their actual software, which is a SQL server based database. Since I also pay a license fee to run Access2002 I also have an icon for Access. When I use Access to be able to run queries and create reports, I simply link to the SQL database they have in the Machine Datasource. To my knowledge I should only have read access using Access because I cannot physically change any of the data that resides in the table. I have to use their actual software to make changes. The company told me that they felt the slowness was because they said my queries are locking the tables and supposedly they must have used the SQL manager to determine this. If I am supposed to only be read only when using Access, how do I make it prevent locking in the method I described?
2006-07-14 02:06:19 · update #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.
2006-07-13 16:45:23 · answer #1 · answered by Jim T 1 · 0⤊ 0⤋
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.
2006-07-13 13:58:11 · answer #2 · answered by Jeremy S 1 · 0⤊ 0⤋
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
2006-07-14 08:43:09 · answer #3 · answered by Piyush 2 · 0⤊ 0⤋