?
Solved

access 2003, recordset update plus currentdb.execute fails in transaction, "3218 could not update; currently locked"

Posted on 2005-03-10
13
Medium Priority
?
462 Views
Last Modified: 2013-12-25

the following (very simple) code
 - fails in access 2003 ("CurrentDb.Execute "DELETE * FROM a"" fails, error message is: "3218 could not update; currently locked")
 - works in access 2000
 - works in access 97


any idea to make it work in access 2003? i noticed that it does not fail in access 2003, if i comment begintrans and committrans out, but that is not the solution.

thank you for the effort.

Function test()

On Error Resume Next
    CurrentDb.Execute "DROP TABLE a", dbFailOnError + dbSeeChanges
On Error GoTo Err_

    CurrentDb.Execute "CREATE TABLE a (b int NULL)", dbFailOnError + dbSeeChanges
    CurrentDb.Execute "INSERT INTO a VALUES (1)", dbFailOnError + dbSeeChanges

    Dim wsp As Workspace
    Set wsp = DBEngine.Workspaces(0)
    wsp.BeginTrans
   
On Error GoTo Err_Rollback
   
    Dim rstLocal As Recordset
    Set rstLocal = CurrentDb.OpenRecordset("SELECT * FROM a", dbOpenDynaset, dbSeeChanges)

    rstLocal.Edit
    rstLocal!b = 76409
    rstLocal.Update
    rstLocal.Close
   
    CurrentDb.Execute "DELETE * FROM a", dbFailOnError + dbSeeChanges
   
    wsp.CommitTrans

Exit_:
    Exit Function
   
Err_Rollback:
    wsp.Rollback
    GoTo Err_
Err_:
    MsgBox Err & ": " & Error$
End Function
0
Comment
Question by:brevai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 6

Expert Comment

by:PePi
ID: 13508422
I think it's time to leave DAO and start using ADO
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13509197
Try:

"DELETE FROM a"
0
 

Author Comment

by:brevai
ID: 13514851
"DELETE FROM a" fails too.

we have 100.000 lines of legacy code. it would be a lot of effort converting to ado.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 29

Expert Comment

by:leonstryker
ID: 13516951
Try adding

SET rstLocal = Nothing

before the delete line

Leon
0
 

Author Comment

by:brevai
ID: 13518083
thank you for your effort.

i added "SET rstLocal = Nothing", i get the same error.


it seems as if currentdb.execute were executed in a different workplace, that is why the record it is trying to delete seems to be locked...
...or records updated by using recordset remains locked after closing.

very strange.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13518142
Ok, let me ask a stupid question: Why are you updating a table and then Deleting everything form it?
0
 

Author Comment

by:brevai
ID: 13518575
yes, it was a weird example, here is a more realistic one:

the following code updates field a.b using a recordset, then updates field a.c using currendb.execute. this one also fails in access 2003 (error 3218):

Function test()

On Error Resume Next
    CurrentDb.Execute "DROP TABLE a", dbFailOnError + dbSeeChanges
On Error GoTo Err_

    CurrentDb.Execute "CREATE TABLE a (b int NULL, c int NULL)", dbFailOnError + dbSeeChanges
    CurrentDb.Execute "INSERT INTO a VALUES (1, 2)", dbFailOnError + dbSeeChanges

    Dim wsp As Workspace
    Set wsp = DBEngine.Workspaces(0)
    wsp.BeginTrans
   
On Error GoTo Err_Rollback
   
    Dim rstLocal As DAO.Recordset
    Set rstLocal = CurrentDb.OpenRecordset("SELECT * FROM a", dbOpenDynaset, dbSeeChanges)

    rstLocal.Edit
    rstLocal!b = 76409
    rstLocal.Update
    rstLocal.Close
    Set rstLocal = Nothing

    CurrentDb.Execute "UPDATE a SET a.c = 3", dbFailOnError + dbSeeChanges

    wsp.CommitTrans

Exit_:
    Exit Function
   
Err_Rollback:
    wsp.Rollback
    GoTo Err_
Err_:
    MsgBox Err & ": " & Error$
End Function
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13518632
What does your connection string look like?
0
 

Author Comment

by:brevai
ID: 13555022
there is no connection string. this is the only code i run.

i create an empty mdb file, paste the above source code, and start "test" subroutine.
0
 

Author Comment

by:brevai
ID: 13599014
finally we downgraded to access 2000 in order to solve this problem.

thank you again for the effort.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 13601308
Ok, ask for a refund.

Leon
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13612430
Thanks, but are you sure this answer helped you?

Leon
0
 

Author Comment

by:brevai
ID: 13650368
no it did not, but i appreciate your effort.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question