G
GreydogMe
Guest
First, sorry for the length of this post, but I'm trying to give as much information as possible. If I am missing information you feel is key, please let me know and i can provide it too. I am using vb.net (VS2008) and accessing an Access database (2007/.accdb) using OLEDB. I am running into this "Could not update; currently locked" error which I have searched for and read about in many locations. In particular, this one Record level locking using OLEDB describes what I am encountering. It is a problem with Access, but not with SQL Server (2012 Express) for me as well. However, unlike the post, I am not using a transaction. And the link to the Microsoft kb article (http://support.microsoft.com/kb/331594/EN-US) is broken and I can't seem to come up with the original kb article in other searches. But the article title also suggests a transaction is involved, which in my case it is not.
I have a particular account record from the Customer table (let's say account '01') in use by another function of the program and I am using a Try-catch block of the current function to catch when the contention is encountered. This is working perfectly. However, when I move on to the next account ('02'), which is not in use, I also run into the same Could not Update, Currently locked error. I know this is related to OLEDB because I can stop the code at this "fake" error and go into MS Access directly and edit the second account ('02') without a problem, and I cannot edit account '01' from here.
I originally had this coded with ADO and an ADODB connection with a client-side cursorlocation. My connection string is for cn.Provider = "Microsoft.ACE.OLEDB.12.0" and I have specified
cn.Properties("Jet OLEDB
atabase Locking Mode").Value = 1 '1=row level, 0=page level.
In the below code, the Opendbconnection is just a sub that formulates the connection string and opens the connection.
This is a simplified version of the code as things are printed using rstCustomer but when ready to update, the code used the execute method of the connection object:
Dim cn as new ADODB.connection
Dim rstCustomer as new ADODB.recordset
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Opendbconnection(cn)
mySQL = "Select acctnum, field1 from Customer"
rstCustomer.Open(mySQL, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
Do while not rstCustomer.EOF
mySQL = "Update mytable set field1='xyz' where acctnum= '" & rstCustomer.fields("acctnum").value & "'"
Try
cn.Execute(mySQL, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)
Catch
[handle the contention]
End Try
rstCustomer.movenext
Loop
After piecing together the suggestions in kb/331594 from other posts (since I couldn't find the article), I am not interested in the ODBC workaround. But I tried the suggestion to use a recordset object instead. So instead of the cn.execute, I updated the recordset directly:
do while not rstcustomer.eof
rstCustomer.fields("field1").value = 'xyz'
rstCustomer.Update
rstCustomer.movenext
Loop
Same problem where account '01' throws the locked error correctly, and account '02' throws the locked error incorrectly. Incidentally, account '03' does not throw the ghost 'locked' error. Also incidentally I tried both of these options with a transaction around either the cn.execute or the rstcustomer.Update statements, with no change in results.
So by now I decided I just had to bite the bullet and convert to ADO.NET, figuring that would solve all my problems...NOT!
Now I have this inside the rstCustomer loop, hoping (and praying) disposal of the connection might help free the lock.
Using cn As New OleDb.OleDbConnection
OpenDBConnection(cn)
Using cmdupdate As New OleDb.OleDbCommand
mySQL = "Update Customer Set field1 = 'xyz' Where Acctnum = '" & rstCustomer.fields("acctnum").value & "'"
cmdupdate.Connection = cn
cmdupdate.CommandText = mySQL
Try
cmdupdate.ExecuteNonQuery()
Catch
[Handle contention]
End Try
End Using
End Using
SAME RESULTS! when on account '01', the contention is caught and handled. Account '02' STILL throws the Currently locked error though I can still go to Access and edit no problem. Account '03' processes fine.
The posts on kb/331594 are old. Do we have no fix for this bug since then? I don't see any recent posts on this so maybe I am missing something else. what am I missing? I feel like I have done my homework, but am just stuck. Any help out there? Just let me know if I need to supply more information.
Thank you!!
Continue reading...
I have a particular account record from the Customer table (let's say account '01') in use by another function of the program and I am using a Try-catch block of the current function to catch when the contention is encountered. This is working perfectly. However, when I move on to the next account ('02'), which is not in use, I also run into the same Could not Update, Currently locked error. I know this is related to OLEDB because I can stop the code at this "fake" error and go into MS Access directly and edit the second account ('02') without a problem, and I cannot edit account '01' from here.
I originally had this coded with ADO and an ADODB connection with a client-side cursorlocation. My connection string is for cn.Provider = "Microsoft.ACE.OLEDB.12.0" and I have specified
cn.Properties("Jet OLEDB
atabase Locking Mode").Value = 1 '1=row level, 0=page level. In the below code, the Opendbconnection is just a sub that formulates the connection string and opens the connection.
This is a simplified version of the code as things are printed using rstCustomer but when ready to update, the code used the execute method of the connection object:
Dim cn as new ADODB.connection
Dim rstCustomer as new ADODB.recordset
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Opendbconnection(cn)
mySQL = "Select acctnum, field1 from Customer"
rstCustomer.Open(mySQL, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
Do while not rstCustomer.EOF
mySQL = "Update mytable set field1='xyz' where acctnum= '" & rstCustomer.fields("acctnum").value & "'"
Try
cn.Execute(mySQL, , ADODB.ExecuteOptionEnum.adExecuteNoRecords)
Catch
[handle the contention]
End Try
rstCustomer.movenext
Loop
After piecing together the suggestions in kb/331594 from other posts (since I couldn't find the article), I am not interested in the ODBC workaround. But I tried the suggestion to use a recordset object instead. So instead of the cn.execute, I updated the recordset directly:
do while not rstcustomer.eof
rstCustomer.fields("field1").value = 'xyz'
rstCustomer.Update
rstCustomer.movenext
Loop
Same problem where account '01' throws the locked error correctly, and account '02' throws the locked error incorrectly. Incidentally, account '03' does not throw the ghost 'locked' error. Also incidentally I tried both of these options with a transaction around either the cn.execute or the rstcustomer.Update statements, with no change in results.
So by now I decided I just had to bite the bullet and convert to ADO.NET, figuring that would solve all my problems...NOT!
Now I have this inside the rstCustomer loop, hoping (and praying) disposal of the connection might help free the lock.
Using cn As New OleDb.OleDbConnection
OpenDBConnection(cn)
Using cmdupdate As New OleDb.OleDbCommand
mySQL = "Update Customer Set field1 = 'xyz' Where Acctnum = '" & rstCustomer.fields("acctnum").value & "'"
cmdupdate.Connection = cn
cmdupdate.CommandText = mySQL
Try
cmdupdate.ExecuteNonQuery()
Catch
[Handle contention]
End Try
End Using
End Using
SAME RESULTS! when on account '01', the contention is caught and handled. Account '02' STILL throws the Currently locked error though I can still go to Access and edit no problem. Account '03' processes fine.
The posts on kb/331594 are old. Do we have no fix for this bug since then? I don't see any recent posts on this so maybe I am missing something else. what am I missing? I feel like I have done my homework, but am just stuck. Any help out there? Just let me know if I need to supply more information.
Thank you!!
Continue reading...