brevai
asked on
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("S
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
I think it's time to leave DAO and start using ADO
Try:
"DELETE FROM a"
"DELETE FROM a"
ASKER
"DELETE FROM a" fails too.
we have 100.000 lines of legacy code. it would be a lot of effort converting to ado.
we have 100.000 lines of legacy code. it would be a lot of effort converting to ado.
Try adding
SET rstLocal = Nothing
before the delete line
Leon
SET rstLocal = Nothing
before the delete line
Leon
ASKER
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.
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.
Ok, let me ask a stupid question: Why are you updating a table and then Deleting everything form it?
ASKER
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("S ELECT * 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
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("S
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
What does your connection string look like?
ASKER
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.
i create an empty mdb file, paste the above source code, and start "test" subroutine.
ASKER
finally we downgraded to access 2000 in order to solve this problem.
thank you again for the effort.
thank you again for the effort.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, but are you sure this answer helped you?
Leon
Leon
ASKER
no it did not, but i appreciate your effort.