Access 2010 INSERT locks SQL but works in Access 2000
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 32bit machine and the Access 2010 front end is being run on a Windows 7 64 bit machine. Both systems are using ODBC Driver 11 for SQL Server.
Any suggestions on how to get around this locking issue would be greatly appreciated.