UtterAccess.com
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    
 
   
WyoDave
post May 18 2017, 01:48 PM
Post#1



Posts: 2
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
 
WyoDave
post May 31 2017, 12:40 PM
Post#2



Posts: 2
Joined: 18-May 17



Well I have finally given up on making Access 2010 run the original code the same as Access 2000. There is definitely a lock caused by a form but I can't find where.

I ended up rewriting the logic to save a few pieces of data from the form, closing the form, running the process and finally reopening the form.

I'm concerned about what other similar circumstances might crop up in my testing. I'm not looking forward to the prospect of multiple rewrites just to get it to run in Access 2010.


Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2017 - 02:02 PM