X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Access 2010 Insert Locks SQL But Works In Access 2000, Access 2010    
post May 18 2017, 01:48 PM

Posts: 1
Joined: 18-May 17

Looking for some direction please.

A little background first. We have a complex, but stable, Access 2000 front end and SQL 2005 backend. It has been determined that we need to upgrade to Access 2010.

My initial testing is to attempt to run the .mdb using Access 2010. This allows me to run the exact code, without conversion, in both Access versions using the same SQL backend.

My issue: When running the same process, Access 2010 leaves a SQL table locked after an INSERT so that the subsequent SELECT on that table times out. I have run SQL profiler to compare what is happening in Access 2000 vs Access 2010. What I found is that when running in Access 2000, SQL will perform a COMMIT TRAN after the INSERT. However, when running in Access 2010, there is no COMMIT TRAN after the INSERT and the subsequent SELECT times out.

I have also run sp_who2 and found that the SPID of the INSERT is blocking the SPID of the SELECT.

These tests are all run with me as the only user on the SQL database. The Access 2000 front end is being run on a Windows 7 32 bit machine and the Access 2010 front end is being run on a Windows 7 64 bit machine. Both systems are running ODBC Driver 11 for SQL Server.

Any suggestions on how to get around this locking issue would be greatly appreciated.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    22nd May 2017 - 08:11 PM