Shared Access table locks up
I have a table in Access linked to a hosted SQL Server DB. It is one of many tables in the DB but it is the only one that randomly, at least once per day, seems to lock up. When users try to add a record they get the ODBC linked table message with a timeout.
It is a standalone table with no relationships anywhere else. I do not host the database so don't necessarily have access to logs etc.
What can I do to either stop this happening, or see what the problem is?
It is a standalone table with no relationships anywhere else. I do not host the database so don't necessarily have access to logs etc.
What can I do to either stop this happening, or see what the problem is?
Does each user have their own copy of the front-end?
<<What can I do to either stop this happening, or see what the problem is?>>
The first can be due to any number of reasons. All the timeout means is that whatever you are attempting to do is taking longer than the current timeout setting. That can be due to:
1. A busy server.
2. Someone holding a table lock
3. A deadlock situation; user A has a lock on table 1, B has a lock on table 2. B is trying to lock a record in table 1, and A is trying to lock something in 2. Neither can move forward until one quits. When SQL sees this, the operation will time out.
A little more detail would help.
Jim.
The first can be due to any number of reasons. All the timeout means is that whatever you are attempting to do is taking longer than the current timeout setting. That can be due to:
1. A busy server.
2. Someone holding a table lock
3. A deadlock situation; user A has a lock on table 1, B has a lock on table 2. B is trying to lock a record in table 1, and A is trying to lock something in 2. Neither can move forward until one quits. When SQL sees this, the operation will time out.
A little more detail would help.
Jim.
ASKER
Thanks to both of you for your comments. To answer in order:
- each user has their own copy
- I am wondering if it is due to a busy server, but how do I find out?
- Each time this happens, all users are asked to come out of the database
- Therefore in view of the above I don't think it could be a deadlock
Have a SSMS open and run the activity monitor. Look for blocking process when your table "locks up". Also try to isolate the process in Access which does this.
ASKER
OK thanks - what do I look for in terms of blocking process? Also, although the users experience this problem in an Access form, I can re-create it directly in the table in Access.
ASKER
As an update, I've discovered that I get a timeout even if I try to amend a record directly using SSMS.
The activity monitor has the column blk_by, which points to spid. These processes are blocked. Use the context menu for details.
ASKER
Can't get to Activity Monitor. Every time I open it, it goes to "Paused". If I select "Resume" it just goes back to "Pause". I've raised this issue with the hosting company.
In this case run EXECUTE sp_who2 in a secondary SSMS window.
btw, Is this a shared SQL Server instance?
btw, Is this a shared SQL Server instance?
ASKER
ste5an
I've done that, but what am I supposed to learn from that please?
I've done that, but what am I supposed to learn from that please?
When your table "seems to lock up" and you see a blocking process, then you have an database issues in data model, your indices or the way you access the data. Cause then the reason is blocking. Dead locks are normally detected by SQL Server and automatically removed. Blocking on the other hand is not. Here SQL Server or the connection waits until a timeout happens, In this case you need to take a look at the actual reason for the blocking and take appropriate measures.
When during those "lock ups" no blocking occurs, then it is more likely an network issue or IO server issue.
When during those "lock ups" no blocking occurs, then it is more likely an network issue or IO server issue.
ASKER
ste5an
I am not able to run the query you mentioned. I think without access to the diagnostics in SQL Server I am not able to find out anything further.
I am not able to run the query you mentioned. I think without access to the diagnostics in SQL Server I am not able to find out anything further.
When you don't have your own SQL Server instance, then you need to contact the hoster.
ASKER
yes thanks - they're not being that helpful...
ASKER
I have been told by my hosting company that to avoid timeouts such as this the Access application should be catering for this and re-authenticating.
Does anybody have experience of doing this within Access?
Does anybody have experience of doing this within Access?
Sorry, no way with Access and linked tables. Cause it was built as desktop database system.
If the error is caused by the network, then there is no way. For DAO and linked tables you need a stable network.
The question is: Why do you use a hosted SQL Server instance? How much data and uses are involved? Cause for smaller project SQL Server Express hosted locally does often enough a pretty good job.
When you need a mobile solution, the most common approaches are:
- Using a terminal server, where you host the Access application, in the same network as your SQL Server instance.
- Using a master SQL Server and use replication to local SQL Server instances. May result in higher license fees, when the local SQL Server replicas require a full SQL Server edition.
If the error is caused by the network, then there is no way. For DAO and linked tables you need a stable network.
The question is: Why do you use a hosted SQL Server instance? How much data and uses are involved? Cause for smaller project SQL Server Express hosted locally does often enough a pretty good job.
When you need a mobile solution, the most common approaches are:
- Using a terminal server, where you host the Access application, in the same network as your SQL Server instance.
- Using a master SQL Server and use replication to local SQL Server instances. May result in higher license fees, when the local SQL Server replicas require a full SQL Server edition.
ASKER
because the users are not all based in the one office, in some cases not in the same country
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jim
Interesting insight into the future of Access. The part about retrying is also very encouraging so thanks for this.
Interesting insight into the future of Access. The part about retrying is also very encouraging so thanks for this.
ASKER
Thanks for the help