Need help with DAO and SQL Server locking issue

From: George M. (GeorgeM_at_discussions.microsoft.com)
Date: 08/10/04 

Date: Tue, 10 Aug 2004 15:59:02 -0700

Problem: SQL Server 2000 blocking locks on Access upsized database.

Tools used: SQL Server 2000 SP3a, Access 2003, DAO 3.6

My project is to upsize an Access 2000 database to SQL Server 2000. I have
migrated the data to SQL Server, re-linked the tables, and converted the
database to Access 2002 file format.

I am using Access 2003 for this project.

Now that all my data is stored on SQL Server, I am having problems with
blocking locks in some of the VBA code routines that perform calculations and
update the database. I did not have this problem when the data was stored in
the Access 2000 database.

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 transaction processing. 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)

Unfortunately the client does not have the time (or money) to recode the
entire system to use SQL statements or ADO. I must use the existing DAO
Recordset objects that use AddNew or Edit tables.

All SQL Server settings are the default settings. The SQL Server “locks”
setting is the default value of 0, which means SQL Server will create
additional locks as needed.

Once the blocking starts I get ODBC Timeout error messages DAO error number
3146.

Can someone suggest a way for me to force SQL Server to release these locks?

Thank you!

Relevant Pages

  • Re: Multiple problems with a running job
    ... SQL Server MVP ... > database that I know is unchanging), making it impossible for me to run ... I'm declaring the cursors as LOCAL STATIC. ... > to prevent locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Back end database lock-up
    ... of access for this application (no SQL Server, ... database into front end and back end databases and put the front end ... only lock the records that are being updated, but it locks the entire ... I think in Novell for rights; read, write, erase, modify, create. ...
    (comp.databases.ms-access)
  • Re: row vs page locking...
    ... so they automatically escalate to page level locks. ... 'Lock Escalation' - see Books Online. ... SQL Server 2005 is going to have a Snapshot ... Good old fashioned locking is less sexy, but I find, more productive! ...
    (microsoft.public.sqlserver.server)
  • Re: Repeatable read. What is it exactly...
    ... SQL Server will hold locks on the data you have read ... can change change the data until you commit or rollback. ... SQL Server locks the data you read. ... > as transaction Isolation levels go. ...
    (microsoft.public.sqlserver.server)
  • Re: Locking in SQL Server and Oracle
    ... I am glad you would like to stay with SQL Server :-) ... > hard it would be to convert to Oracle during our next PeopleSoft upgrade. ... > had thought that since databases are databases, tables are tables, and SQL ... > uses row-level locks exclusively. ...
    (microsoft.public.sqlserver.server)