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

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.

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.

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.

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

1. Create a Standard EXE project in Microsoft Visual Basic.
2. Add the following code to the Form_Load event:

   Private Sub Form_Load()
   Const sFileLocation As String = "C:\MyRecordset.adtg"
   Dim con As ADODB.Connection
   Dim rs As ADODB.Recordset
       Set con = New ADODB.Connection
       con.CursorLocation = adUseServer
       con.Provider = "SQLOLEDB"
       con.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;"
       con.Open
       Set rs = New ADODB.Recordset
       rs.Open "SELECT * FROM Orders, Shippers", con, adOpenStatic, adLockReadOnly, adCmdText
       Debug.Print "RecordCount before Save: " & rs.RecordCount
       If Dir(sFileLocation, vbNormal) <> "" Then Kill sFileLocation
       rs.Save sFileLocation, adPersistADTG
       rs.Close
       rs.Open sFileLocation, con, adOpenStatic, adLockReadOnly, adCmdFile
       Debug.Print "RecordCount after Save: " & rs.RecordCount
       rs.Close
       con.Close
       Set rs = Nothing
       Set con = Nothing
   End Sub
   					

3. Change the ConnectionString setting to connect to the Northwind database on your SQL Server computer.
4. Run the application. The Immediate window displays the following:
   RecordCount before Save: 2490
   RecordCount after Save: 1297

The following MSDN Web site states the following: For more information about rows and row handles, browse to the following MSDN Web site: For more information about the DBPROP_MAXROWS value, browse to the following MSDN Web site: