FIX: ADO Recordset Opened from File May Not Update Database

• ActiveX Data Objects (ADO), versions 2.0, 2.1
• 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.

This problem has been fixed in the 2.10 service pack 2 release of MDAC.

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 

   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 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.

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by David Sceppa, Microsoft Corporation

REFERENCES

MDAC 2.10 sp2 may be obtained by going to downloads at the following site:

http://www.microsoft.com/data

Additional query words:

Keywords : kbADO kbADO200bug kbDatabase kbVBp kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix kbMDAC210SP2fix
Version : WINDOWS:2.0,2.1,5.0,6.0,97
Platform : WINDOWS
Issue type : kbbug