FIX: ADO Recordset Opened from File May Not Update Database

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