BUG: "Could Not Update; Currently Locked" Error Message Raised by Second Edit Attempt in a Transaction (331594)


The information in this article applies to:

  • Microsoft OLE DB Provider for Jet 4.0
  • Microsoft Access 2000
  • Microsoft Access 2002
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
  1. Create a new Visual Basic 6.0 Standard Exe Application. By default, Form1 is created.
  2. On the Project menu, click References.
  3. In the Available References list, double-click Microsoft ActiveX Data Objects 2.0 Library to add the reference to the project.
  4. 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 "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=<Path to Access 2000 or Access 2002 database>"
     
   ' 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 "SELECT * FROM CUSTOMERS", cn
   rs.MoveFirst
       sCustomerID = rs.Fields("CUSTOMERID").Value
       rs.Fields("CITY").Value = "CLEVELAND"
   rs.Update
   ' Close the recordset.
   rs.Close
   Set rs = Nothing
   
   ' Update the customers table through SQL command.
   cn.Execute "UPDATE CUSTOMERS SET REGION = 'OH' WHERE customerid = '" & sCustomerID & "'"
   
   ' Close the transaction.
   cn.CommitTrans
End Sub
  5. Modify the Data Source string as appropriate for your environment. The sample Northwind Access database must have Access 2000 or Access 2002 format.
  6. On the Run menu, click Start to verify the error message that is described in the "Symptoms" section.
  7. To resolve this problem, replace the connection string for cn.Open with the following connection string:
    "cn.Open "Provider=MSDASQL.1;DBQ=<path to Access 2000 or 2002 database>;Driver={Microsoft Access Driver (*.mdb)}"
  8. Modify the DBQ string as appropriate for your environment.
Modification Type:MajorLast Reviewed:5/8/2003
Keywords:kbBug kbTSQL kbDatabase kbMsg kbnofix KB331594
©2002 Microsoft Corporation. All rights reserved.