Microsoft KB Archive/331594

= BUG: &quot;Could Not Update; Currently Locked&quot; Error Message Raised by Second Edit Attempt in a Transaction =

Article ID: 331594

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft OLE DB Provider for Jet 4.0
 * Microsoft Access 2000 Standard Edition
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q331594



SYMPTOMS
When you try to execute a SQL statement that changes a record of a table that was edited earlier in the same transaction, you may receive the following error message:

Run-time error '-2147467259 (80004005)':

Could not update; currently locked.



CAUSE
The error may occur in the following scenario:
 * 1) You connect to an Access 2000 database or an Access 2002 database by using Microsoft OLE Provider for Jet 4.0.
 * 2) You open a transaction, and then edit a record.
 * 3) In the same transaction, you try to execute a SQL statement that updates or deletes the records.



WORKAROUND
To work around this problem, use one of the following methods:  Use Microsoft OLEDB Provider for ODBC Drivers (MSDASQL) with the Microsoft Access Driver, instead of with Microsoft OLE Provider for Jet 4.0. Use the ADODB.Recordset object for subsequent edits, instead of an ADODB.Connection obect, or an ADODB.Command object.  Use client-side cursor to access the data. For example: Dim cn As New ADODB.Connection cn.CursorLocation = adUseClient -or- Dim rs As New ADODB.Recordset rs.CursorType = adOpenStatic rs.CursorLocation = adUseClient 



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of the article.



MORE INFORMATION
Steps to Reproduce the Behavior  Create a new Visual Basic 6.0 Standard Exe Application. By default, Form1 is created.</li> On the Project menu, click References.</li> In the Available References list, double-click Microsoft ActiveX Data Objects 2.0 Library to add the reference to the project.</li>  On the View menu, click Code. Add the following code to Form1: Private Sub Form_Load Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sCustomerID As String

' Open a connection to Access 2000 or Access 2002 format database. cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=<Path to Access 2000 or Access 2002 database>&quot; ' Begin a new transaction. cn.BeginTrans ' Specify the lock type for the table. rs.LockType = adLockOptimistic rs.CursorType = adOpenKeyset ' Open a recordset for customers table. rs.Open &quot;SELECT * FROM CUSTOMERS&quot;, cn  rs.MoveFirst sCustomerID = rs.Fields(&quot;CUSTOMERID&quot;).Value rs.Fields(&quot;CITY&quot;).Value = &quot;CLEVELAND&quot; rs.Update ' Close the recordset. rs.Close Set rs = Nothing ' Update the customers table through SQL command. cn.Execute &quot;UPDATE CUSTOMERS SET REGION = 'OH' WHERE customerid = '&quot; & sCustomerID & &quot;'&quot; ' Close the transaction. cn.CommitTrans End Sub </li> Modify the Data Source string as appropriate for your environment. The sample Northwind Access database must have Access 2000 or Access 2002 format.</li> On the Run menu, click Start to verify the error message that is described in the &quot;Symptoms&quot; section.</li>  To resolve this problem, replace the connection string for cn.Open with the following connection string: &quot;cn.Open &quot;Provider=MSDASQL.1;DBQ=<path to Access 2000 or 2002 database>;Driver={Microsoft Access Driver (*.mdb)}&quot; </li> Modify the DBQ string as appropriate for your environment.</li></ol>

Keywords: kbbug kbnofix kbtsql kbmsg kbdatabase KB331594

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.