Microsoft KB Archive/317913

= BUG: Data Loss When You Use the Save Method with adPersistADTG and adUseServer =

Article ID: 317913

Article Last Modified on 4/6/2004

-

APPLIES TO


 * Microsoft Data Access Components 2.7
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q317913



SYMPTOMS
When you save a large recordset to the Advanced Data Tablegram (ADTG) format by using the Microsoft SQL Server OLE DB Provider (SQLOLEDB) and setting the CursorLocation property to adUseServer, some data may be lost when the recordset is reopened from the file.

NOTE: This behavior does not occur with Microsoft Data Access Components (MDAC) version 2.5.



CAUSE
When you use the Save method to persist the recordset to the file, certain properties are saved with it, including the maximum number of rows. In MDAC 2.6 and MDAC 2.7, this value (DBPROP_MAXROWS) is incorrectly updated.



RESOLUTION
To work around this issue, use any of the following methods:
 * Set the CursorLocation property to adUseClient instead of adUseServer.
 * Use the adPersistXML value instead of adPersistADTG.
 * Use MSDASQL and the SQL Server ODBC Driver instead of the SQL Server OLEDB Provider.



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



Steps to Reproduce the Problem
 Create a Standard EXE project in Microsoft Visual Basic.  Add the following code to the Form_Load event: Private Sub Form_Load Const sFileLocation As String = &quot;C:\MyRecordset.adtg&quot; Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection con.CursorLocation = adUseServer con.Provider = &quot;SQLOLEDB&quot; con.ConnectionString = &quot;Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;&quot; con.Open Set rs = New ADODB.Recordset rs.Open &quot;SELECT * FROM Orders, Shippers&quot;, con, adOpenStatic, adLockReadOnly, adCmdText Debug.Print &quot;RecordCount before Save: &quot; & rs.RecordCount If Dir(sFileLocation, vbNormal) <> &quot;&quot; Then Kill sFileLocation rs.Save sFileLocation, adPersistADTG rs.Close rs.Open sFileLocation, con, adOpenStatic, adLockReadOnly, adCmdFile Debug.Print &quot;RecordCount after Save: &quot; & rs.RecordCount rs.Close con.Close Set rs = Nothing Set con = Nothing End Sub  Change the ConnectionString setting to connect to the Northwind database on your SQL Server computer. Run the application. The Immediate window displays the following:

RecordCount before Save: 2490

RecordCount after Save: 1297



<div class="references_section">