Shared Access table locks up

Rick Danger
Rick Danger used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Does each user have their own copy of the front-end?
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<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.

Author

Commented:
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
Any other thoughts?
ste5anSenior Developer

Commented:
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.

Author

Commented:
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.

Author

Commented:
As an update, I've discovered that I get a timeout even if I try to amend a record directly using SSMS.
ste5anSenior Developer

Commented:
The activity monitor has the column blk_by, which points to spid. These processes are blocked. Use the context menu for details.

Author

Commented:
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.
ste5anSenior Developer

Commented:
In this case run EXECUTE sp_who2 in a secondary SSMS window.

btw, Is this a shared SQL Server instance?

Author

Commented:
ste5an
I've done that, but what am I supposed to learn from that please?
ste5anSenior Developer

Commented:
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.

Author

Commented:
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.
ste5anSenior Developer

Commented:
When you don't have your own SQL Server instance, then you need to contact the hoster.

Author

Commented:
yes thanks - they're not being that helpful...

Author

Commented:
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?
ste5anSenior Developer

Commented:
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.

Author

Commented:
because the users are not all based in the one office, in some cases not in the same country
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Sorry, no way with Access and linked tables. Cause it was built as desktop database system.

 This is not true.  Access dealing with linked tables is separate from the fact that Access was designed as a desktop database system.   Case in point; Microsoft has done work with ODBC in 2019  to make it much more robust in handling connections.   See "ODBC connection retry logic"  here:

https://support.office.com/en-us/article/what-s-new-in-access-2019-f52c5317-3494-4105-9c56-5a2abb8e0f87

 That was done without changing the fundamental nature of Access.

 I'd give you that the re-try logic wasn't there because of the original design and that it simply wasn't needed, but that doesn't imply a fault with the design and one is not dependent on the other.

Jim.

Author

Commented:
Jim
Interesting insight into the future of Access. The part about retrying is also very encouraging so thanks for this.

Author

Commented:
Thanks for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial