MS Access 2003 linked to SQL Server tables, blocking lock problem
Posted on 2004-08-10
I'm having a trouble with a system that was developed by someone else originally. The system uses an Access 2003 database which links to tables in another database server. Originally the system was linking to tables in a Sybase server, but I was brought in to convert it to connect to a SQL Server 2000 server. The problem that I'm having at this point is with blocking locks on SQL Server 2000.
Locks are being placed on tables inside of a transaction each time the Update method is being called on a DAO.Recordset object. These locks will not release after this point. They persist until Access is closed. This causes a problem on any further SELECT queries ran on these tables where the blocking exclusive locks from the Recordset.Update block the SELECT statement from reading.
I've tried committing the transaction, rolling back the transaction, closing the recordset objects, setting the recordset variables to Nothing, and doing this without using transactions. None of that worked. I tried this using pessimistic locking and then using DBEngine.Idle(dbFreeLocks) and this still does not work.
Here's an example of the statement being used to open the recordset:
Set rsHRA = dbs.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges, dbPessimistic)
Originally this was being done without the dbPessimistic option, which defaults to Optimistic locking. Unfortunately I don't have the time to recode the entire system to use built SQL statements rather than Recordset objects that AddNew or Edit tables, so I must find a way to release these locks.