BUG: ADO Recordset Opened from File May Not Update Database

ID: Q195221

The information in this article applies to:

ActiveX Data Objects (ADO), version 2.0
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
Microsoft Visual Studio versions 6.0, 97

SYMPTOMS

Opening an ActiveX Data Objects (ADO) recordset from a file, setting it's ActiveConnection property to a valid ADO connection object and attempting to update the database (using either Update or UpdateBatch, depending on the chosen LockType) does not create an error. However, it does not modify the database.

CAUSE

In order for a recordset opened from a file to successfully update the back- end database, you must modify the ActiveConnection property after calling the Open method.

RESOLUTION

The easiest workaround is to set the ActiveConnection property to Nothing in the Open method. Next, set the ActiveConnection property to the desired ADO connection object in order to update the database.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

The following code may or may not successfully update the back-end database:

   rsCustomers.Open strPath, , adOpenStatic, _
                    adLockBatchOptimistic, adCmdFile
   Set rsCustomers.ActiveConnection = cnNWind
   rsCustomers!CompanyName = InputBox("Enter new CompanyName")
   rsCustomers.Update
   rsCustomers.UpdateBatch
   rsCustomers.Close

This code successfully updates the back-end database if the recordset's ActiveConnection property is set to something other than cnNWind before executing.

The following code does not successfully update the back-end database:

   Set rsCustomers = New ADODB.Recordset
   rsCustomers.Open strPath, cnNWind, adOpenStatic, _
                    adLockBatchOptimistic, adCmdFile
   rsCustomers!CompanyName = InputBox("Enter new CompanyName")
   rsCustomers.Update
   rsCustomers.UpdateBatch
   rsCustomers.Close

Steps to Reproduce Behavior

1. Start a new Standard .exe project in Visual Basic. Form1 is created by

   default.

2. From the Project menu, choose References and select the "Microsoft

   ActiveX Data Objects 2.0 Library".

3. Add the following code to your form:

      Private Sub Form_Load()
          Dim cnNWind As ADODB.Connection
          Dim rsCustomers As ADODB.Recordset
          Dim strConn As String, strSQL As String, strPath As String

       strConn = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
                 "Data Source=C:\VS98\VB98\NWind.MDB;"
       strSQL = "SELECT CustomerID, CompanyName FROM Customers"
       strPath = "C:\rsCustomers.adtg"

       'Delete the file if it exists.
       If Dir(strPath) <> "" Then
           Kill strPath
       End If

       'Establish a connection to the Northwind database.
       Set cnNWind = New ADODB.Connection
       cnNWind.CursorLocation = adUseClient
       cnNWind.Open strConn

       'Query database for customer information.
       'Save results to file.
       Set rsCustomers = New ADODB.Recordset
       rsCustomers.Open strSQL, cnNWind, adOpenStatic, _
                        adLockBatchOptimistic, adCmdText
       MsgBox "Original CompanyName = " & rsCustomers!CompanyName
       rsCustomers.Save strPath
       rsCustomers.Close

       'Open saved recordset, modify it and attempt to
       'update the database.
       rsCustomers.Open strPath, cnNWind, adOpenStatic, _
                        adLockBatchOptimistic, adCmdFile
       rsCustomers!CompanyName = InputBox("Enter new CompanyName")
       rsCustomers.Update
       rsCustomers.UpdateBatch
       rsCustomers.Close

       'Query the database to see if it was successfully updated.
       rsCustomers.Open strSQL, cnNWind, adOpenStatic, _
                        adLockReadOnly, adCmdText
       MsgBox "CompanyName = " & rsCustomers!CompanyName
       rsCustomers.Close
       Set rsCustomers = Nothing

       cnNWind.Close
       Set cnNWind = Nothing
      End Sub

4. Modify the strConn variable to find the copy of NWind.mdb on your

   computer.

5. Run the code. When prompted, enter a new value for CompanyName. When the

   code queries the database after the attempted update, you should see
   that no updates were made in the database.

6. Alter the code that opens the saved recordset and attempts to update the

   database as follows:

      'Open saved recordset, modify it and attempt to
      'update the database.
      rsCustomers.Open strPath, Nothing, adOpenStatic, _
                       adLockBatchOptimistic, adCmdFile
      Set rsCustomers.ActiveConnection = cnNWind
      rsCustomers!CompanyName = InputBox("Enter new CompanyName")
      rsCustomers.Update
      rsCustomers.UpdateBatch
      rsCustomers.Close

7. Run the code. When prompted, enter a new value for CompanyName. When the

   code queries the database after the attempted update, you should see
   that the database updated.

REFERENCES

Additional query words:

Keywords          : kbADO kbADO200bug kbVBp 
Version           : WINDOWS:2.0,5.0,6.0,97
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: November 10, 1998