Access 2010 INSERT locks SQL but works in Access 2000 (1 Viewer)

WyoDave

New member
Local time
Today, 12:06
Joined
May 18, 2017
Messages
5
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.
 

static

Registered User.
Local time
Today, 18:06
Joined
Nov 2, 2015
Messages
823
Search your code for .begintrans and place breakpoints.
Run the code and make sure it hits a .commitrans.
If not find the sql/query that is failing.

Code:
Private Sub SomeProc()
On Error GoTo woopsie

    Dim ws As DAO.Workspace
    Set ws = DBEngine(0)
    
    ws.BeginTrans
    CurrentDb.Execute SomeSQL, dbFailOnError
    
    ws.CommitTrans 'If the above errors this line won't run and the table will remain locked.

woopsie:
    Err.Clear
End Sub
 

WyoDave

New member
Local time
Today, 12:06
Joined
May 18, 2017
Messages
5
Static,

Thanks for the reply.

The original code does not have any .begintrans or .committrans in the code. I did try rewriting the code to use execute rather than DoCmd.RunSQL so that I could try adding the .begintrans and .committrans. Unfortunately this did not have any positive effect.

Today I wrote a simple test procedure that does nothing more than and Insert and a Select from the table that is giving me grief. That worked perfectly and did not lock the table.

This leads me to believe that one of the forms/subforms that is open when this process runs is somehow locking the table. That will be my next direction for testing.

Still this is very frustrating since everything works fine when running with Access 2000.
 

static

Registered User.
Local time
Today, 18:06
Joined
Nov 2, 2015
Messages
823
Well you didn't give any detail how you ran your test so it didn't help me or anybody else that might read this.

Obviously there are locking options in Access (record, page, table) so you'd probably want to check what it's set to.

I had a quick look but I find modern Office menus horrendous and couldn't find the setting anywhere. :banghead:
 

WyoDave

New member
Local time
Today, 12:06
Joined
May 18, 2017
Messages
5
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.


Thanks for your input.
 
  • Like
Reactions: Rx_

Rx_

Nothing In Moderation
Local time
Today, 12:06
Joined
Oct 22, 2009
Messages
2,803
Search on Rx_ user and Upsized
I could not find my exact post years ago. After upgrading none of my previous Execute statements worked. It seemed that a dbSeeChanges was needed.

Just in case: in another situation, it had to do with a sub-form updating.

Take a look at this to see if it might help:
130 strSQLUpdate = "UPDATE Wells SET Wells.ID_WellsStatus1 = " & Status1_Recommended & " WHERE (((Wells.ID_Wells)=" & R_ID_Wells & "));"
140 db.Execute strSQLUpdate, dbFailOnError + dbSeeChanges
 

WyoDave

New member
Local time
Today, 12:06
Joined
May 18, 2017
Messages
5
RX,

Thanks for chiming in.

I had tried that before but gave it one more try at your suggestion. Still can't get around the lock without closing the form.

Moving on.
 

Users who are viewing this thread

Top Bottom