Cannot update: record locked by another session

[gbergeson]

I have the following code in an Access 2003 database:

Dim myConnecion As ADODB.Connection
Set myConnection = CurrentProject.Connection
 
Dim myTable As ADODB.Recordset
With myTable
    .ActiveConnection = myConnection
    .Open "someTableInMyDB", , ad???Dynamic,adLockOptimistic
    End With
 
If Not myTable.EOF Then myTable.MoveFirst
 
Do Until myTable.EOF
    If myTable.Fields("WorkOrderNumber") = woNum Then
        Exit Do
        End If
    myTable.MoveNext
    Loop
 
myTable.Delete
myTable.Close

For one particular table, in one module this code locks up at the .Delete line. Access throws an error that says the record is locked by another session on my machine. When I look at the call stack there are two modules open. The one that is running and fails, and the one that called it. The module that calls this one doesn't access this particular table.

Is there a way to get VBA to show all running sessions? What other method could I use to track down where this record is being locked so I can fix this error? Ideas???

[gbergeson]

Okay, I solved this for now. The code above is in a "Utility" module. It was being called from a form that locked the record being edited. Thus, the form had the record locked and then called the module to edit the locked record. Oops!

[Scott Price]

You could try the adLockBatchOpti mistic instead of the adLockOptimisti c, although I don't have much confidence that it will solve your problem. The adLockOptimisti c is supposed to only lock the record upon calling the .Update command. The batch optimistic is required for batch updating..

Regards,
Scott