MS Access holds locks on table rows indefinitely

Question

We're using MS Access as the GUI for one of our systems, but we've run into an issue where Access is holding locks on the underlying tables or rows, which prevents SQL server from running any update queries on this data. This is problematic because while our Access frontend only requires read only access to this data, we have systems in place that are refreshing the data at regular intervals. These refresh operations fail (or are delayed indefinitely) due to Access already holding locks on the data.

This problem is illustrated by opening the Access frontend and using the sys.dm_tran_locks DMV to show locks on the data. The steps I take to reproduce the problem are:

1. Open the Access frontend. This shows a scrollable form with several thousand records
2. Use SQL server DMVs to show locks on the data. This shows 5 "object" type locks with request mode of "IS" (Intent shared). Using sys.dm_exec_requests shows the command status as "suspended" and the wait type as "ASYNC_NETWORK_IO". These locks are held as long as the user has the Access frontend open, and prevent any update/delete/truncate operations on the tables involved. Now if the user scrolls to the end of the record set in Access, the locks are released!

The second issue occurs when the user clicks through to show a single record in the frontend. When a single record is displayed onscreen, the SQL server DMVs show these locks: 3x object, 1x key, 1x page. The key is a shared lock, others are intent shared. Again, command status is suspended and wait type is ASYNC_NETWORK_IO. And these locks are held as long as the user is viewing the record

We need to stop access from holding these locks on an indefinite basis. Unfortunately MS Access is not part of my skill set so I don't know what needs to be done to fix this.

Answer 1

I didn't solve this problem, but a colleague did. What was done is that instead of creating linked tables to SQL Server tables he created linked tables to views. The views looked like this:

CREATE VIEW dbo.acc_tblMyTable
AS
  SELECT * FROM tblMyTable WITH (NOLOCK)

No locking, and as a bonus Access treated the data as read-only.

Make sure you understand what can happen when you use NOLOCK, however.

Unfortunately MS Access is not part of my skill set so I don't know what needs to be done to fix this.

Get rid of Access :)