Microsoft KB Archive/195221

= FIX: ADO Recordset Opened from File May Not Update Database =

Article ID: 195221

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q195221



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 and in MDAC versions 2.5 and later.



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
 Start a new Standard .exe project in Visual Basic. Form1 is created by default. From the Project menu, choose References and select the "Microsoft ActiveX Data Objects Library."  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  Modify the strConn variable to find the copy of NWind.mdb on your computer. 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.</li>  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 </li> 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.</li></ol>

<div class="references_section">