Link to home
Start Free TrialLog in
Avatar of brevai
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("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
Avatar of PePi
PePi

I think it's time to leave DAO and start using ADO
Try:

"DELETE FROM a"
Avatar of brevai

ASKER

"DELETE FROM a" fails too.

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
Avatar of brevai

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.
Ok, let me ask a stupid question: Why are you updating a table and then Deleting everything form it?
Avatar of brevai

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("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
What does your connection string look like?
Avatar of brevai

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.
Avatar of brevai

ASKER

finally we downgraded to access 2000 in order to solve this problem.

thank you again for the effort.
ASKER CERTIFIED SOLUTION
Avatar of leonstryker
leonstryker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, but are you sure this answer helped you?

Leon
Avatar of brevai

ASKER

no it did not, but i appreciate your effort.