Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. BUG: "Could Not Update; Currently Locked" Error Message Raised by Second Edit Attempt in a Transaction

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

View products that this article applies to.

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.

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Status

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

↑ Back to the top

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.

↑ Back to the top

Keywords: KB331594, kbnofix, kbmsg, kbdatabase, kbtsql, kbbug

↑ Back to the top

Article Info
Article ID : 331594
Revision : 3
Created on : 5/8/2003
Published on : 5/8/2003
Exists online : False
Views : 423