Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-18-2017, 10:18 AM   #1
WyoDave
Newly Registered User
 
Join Date: May 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
WyoDave is on a distinguished road
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.

WyoDave is offline   Reply With Quote
Old 05-19-2017, 03:45 AM   #2
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 609
Thanks: 2
Thanked 152 Times in 145 Posts
static will become famous soon enough
Re: Access 2010 INSERT locks SQL but works in Access 2000

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
static is offline   Reply With Quote
Old 05-19-2017, 11:38 AM   #3
WyoDave
Newly Registered User
 
Join Date: May 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
WyoDave is on a distinguished road
Re: Access 2010 INSERT locks SQL but works in Access 2000

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.

WyoDave is offline   Reply With Quote
Old 05-19-2017, 01:38 PM   #4
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 609
Thanks: 2
Thanked 152 Times in 145 Posts
static will become famous soon enough
Re: Access 2010 INSERT locks SQL but works in Access 2000

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.
static is offline   Reply With Quote
Old 05-31-2017, 09:43 AM   #5
WyoDave
Newly Registered User
 
Join Date: May 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
WyoDave is on a distinguished road
Re: Access 2010 INSERT locks SQL but works in Access 2000

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.
WyoDave is offline   Reply With Quote
Old 05-31-2017, 10:36 AM   #6
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,727
Thanks: 598
Thanked 322 Times in 295 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: Access 2010 INSERT locks SQL but works in Access 2000

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
Rx_ is offline   Reply With Quote
Old 06-01-2017, 12:28 PM   #7
WyoDave
Newly Registered User
 
Join Date: May 2017
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
WyoDave is on a distinguished road
Re: Access 2010 INSERT locks SQL but works in Access 2000

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.


WyoDave is offline   Reply With Quote
Reply

Tags
access 2000 , access 2010 , sql 2005

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access 2010 Query Linked to a SQL 2008 database do INSERT to table in MS Access fi Lars Queries 0 07-24-2014 04:31 PM
Question Access 2010 Runtime x64 vs Access 2000 x32 Problem Lee D General 5 02-06-2013 11:31 AM
Can't convert an Access 2000 db to Access 2010 - Reference problem? kato239 General 19 01-30-2013 01:08 PM
Data lost in Access 2000 tables with Access 2010 mlai08 Tables 1 10-25-2012 09:30 PM
Shortcut error in Access 2000 works in 2003. Bosch General 1 06-27-2005 10:14 AM




All times are GMT -8. The time now is 10:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World