• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

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


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
brevai
Asked:
brevai
  • 6
  • 6
1 Solution
 
PePiCommented:
I think it's time to leave DAO and start using ADO
0
 
leonstrykerCommented:
Try:

"DELETE FROM a"
0
 
brevaiAuthor Commented:
"DELETE FROM a" fails too.

we have 100.000 lines of legacy code. it would be a lot of effort converting to ado.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
leonstrykerCommented:
Try adding

SET rstLocal = Nothing

before the delete line

Leon
0
 
brevaiAuthor Commented:
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
 
leonstrykerCommented:
Ok, let me ask a stupid question: Why are you updating a table and then Deleting everything form it?
0
 
brevaiAuthor Commented:
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
 
leonstrykerCommented:
What does your connection string look like?
0
 
brevaiAuthor Commented:
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
 
brevaiAuthor Commented:
finally we downgraded to access 2000 in order to solve this problem.

thank you again for the effort.
0
 
leonstrykerCommented:
Ok, ask for a refund.

Leon
0
 
leonstrykerCommented:
Thanks, but are you sure this answer helped you?

Leon
0
 
brevaiAuthor Commented:
no it did not, but i appreciate your effort.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now