By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,584 Members | 1,803 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,584 IT Pros & Developers. It's quick & easy.

Cannot update: record locked by another session

P: 4
I have the following code in an Access 2003 database:

Expand|Select|Wrap|Line Numbers
  1. Dim myConnecion As ADODB.Connection
  2. Set myConnection = CurrentProject.Connection
  3.  
  4. Dim myTable As ADODB.Recordset
  5. With myTable
  6.     .ActiveConnection = myConnection
  7.     .Open "someTableInMyDB", , ad???Dynamic,adLockOptimistic
  8.     End With
  9.  
  10. If Not myTable.EOF Then myTable.MoveFirst
  11.  
  12. Do Until myTable.EOF
  13.     If myTable.Fields("WorkOrderNumber") = woNum Then
  14.         Exit Do
  15.         End If
  16.     myTable.MoveNext
  17.     Loop
  18.  
  19. myTable.Delete
  20. 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???
Sep 25 '07 #1
Share this Question
Share on Google+
2 Replies


P: 4
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!
Sep 26 '07 #2

Scott Price
Expert 100+
P: 1,384
You could try the adLockBatchOptimistic instead of the adLockOptimistic, although I don't have much confidence that it will solve your problem. The adLockOptimistic is supposed to only lock the record upon calling the .Update command. The batch optimistic is required for batch updating..

Regards,
Scott
Sep 26 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.