Microsoft KB Archive/195082

= How to use ADO disconnected and persisted recordsets =

Article ID: 195082

Article Last Modified on 7/6/2006

-

APPLIES TO


 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft Data Access Components 2.8

-



This article was previously published under Q195082



SUMMARY
Two of the most exciting new features of ActiveX Data Objects (ADO) are disconnected recordsets and saved recordsets. Disconnected recordsets allow you to work with a recordset that is no longer connected to a data source. A saved recordset is saved to a file that can be closed and reopened without an active connection.

You should use a disconnected recordset when the application needs to drop a connection to the data source and still retain the ability to view or manipulate the data.

A saved or persisted recordset is data that is saved to a file. You can close and reopen the file later without an active connection. For example, an application may need to download data to a laptop computer that updates the data while disconnected from the network. When you reconnect the laptop to the network, the application then updates the shared network database with the additions and changes that have been made.

NOTE: There are many issues to consider when performing batch updates that are beyond the scope of this article.



MORE INFORMATION
The following example demonstrates disconnected and saved Recordsets based on a SQL Server table. It first creates a connection to the SQL Server AUTHORS sample table in the PUBS database. It then disconnects the Recordset, adds a record to it, saves the recordset to a local file, opens this local Recordset, reconnects to SQL Server and performs a batch update of all the changes made. It displays WAIT WINDOWs at various locations in the code to indicate the status.

You can uncomment the calls to the function ShowRS if you want to display the AU_IDs of the Recordset.

Substitute a Server, User ID and Password appropriate to your SQL Server installation in the definition of the lcConnString, lcUID and lcPWD variables in the code.

In order to use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from the following Web address:

http://msdn.microsoft.com/dataaccess

Sample Code
* DISCONNECTEDRS.PRG *  * Demonstrates disconnecting a recordset based on a SQL Server * table, adding a new record to the disconnected recordset, * saving it to a local file, reopening this file, * reconnecting to SQL Server and performing a batch update * of all the changes made.

#DEFINE adModeReadWrite 3 #DEFINE adUseClient 3 #DEFINE adOpenDynamic 2 #DEFINE adLockBatchOptimistic 4 #DEFINE adSaveOverwrite 0 #DEFINE adOpenStatic 3

oConnection = CREATEOBJECT("ADODB.Connection") oRecordSet = CREATEOBJECT("ADODB.Recordset")

lcConnString = "DRIVER={SQL Server};" + ; "SERVER=YourServer;" + ; "DATABASE=PUBS"

lcUID = "YourUserID" lcPWD = "YourPassword"

WITH oConnection .ATTRIBUTES = adModeReadWrite .OPEN(lcConnString, lcUID, lcPWD) ENDWITH

WITH oRecordSet .ActiveConnection = oConnection .CursorLocation = adUseClient .CursorType = adOpenDynamic .LockType = adLockBatchOptimistic .OPEN("select * from authors") ENDWITH

WAIT WINDOW "Recordset open" TIMEOUT 5

* Uncomment this to view the original contents of AUTHORS. *? "Original authors table: " *=ShowRS

* Disconnect the recordset and close the connection. * Can't use an empty string, must use .NULL. oRecordSet.ActiveConnection = .NULL. oConnection.CLOSE

WAIT WINDOW "Recordset detached" TIMEOUT 5

* Now, add a new record to the disconnected recordset. =AddRec

* Uncomment these to see the newly-added record *? "After AddRec - 987-65-4321 should be displayed last" *=ShowRs

* Now, save the recordset locally and close it  oRecordSet.SAVE("C:\LocalAuthor.TXT", adSaveOverwrite) oRecordSet.CLOSE

WAIT WINDOW "Recordset saved locally" TIMEOUT 5

* Now, reopen the local copy of the recordset * re-establish the connection * and perform a batch update. oRecordSet.OPEN("C:\LocalAuthor.TXT",, ;     adOpenStatic, adLockBatchOptimistic)

WITH oConnection .ATTRIBUTES = adModeReadWrite .OPEN(lcConnString, lcUID, lcPWD) ENDWITH WITH oRecordSet .ActiveConnection = oConnection .UpdateBatch ENDWITH

WAIT WINDOW "Batch update completed" TIMEOUT 5

* Must close the current recordset before reusing. oRecordSet.CLOSE

* Now, reuse the recordset. * Requery it to show that the update occurred. oRecordSet.OPEN("select * from authors")

WAIT WINDOW "Requery server to double check" TIMEOUT 5

* Uncomment to display the returned AU_Ids. *? "Requery of data from the server" *=ShowRS

* Code to remove the record that was added from SQL Server. oRecordSet.MoveFirst oRecordSet.FIND("Au_ID='987-65-4321'") IF NOT oRecordSet.EOF oRecordSet.DELETE =MESSAGEBOX("Record Deleted - server cleaned up") ENDIF WITH oRecordSet .ActiveConnection=oConnection .UpdateBatch ENDWITH ***

* Despite the adSaveOverwrite flag, if the local copy is not deleted * there are problems on the second and subsequent passes. DELETE FILE C:\LOCALAUTHOR.TXT

* Function AddRec: * Add a new record to the authors table. FUNCTION AddRec

oRecordSet.AddNew oRecordSet.FIELDS("au_id")= '987-65-4321' oRecordSet.FIELDS("au_lname") = "Smith" oRecordSet.FIELDS("au_fname") = "John" oRecordSet.FIELDS("phone") = 9999999999 oRecordSet.FIELDS("address") = "123 4th Street" oRecordSet.FIELDS("city") = "New York" oRecordSet.FIELDS("state") = "NY" oRecordSet.FIELDS("zip") = "99999" oRecordSet.FIELDS("contract") = .T.  oRecordSet.UPDATE =MESSAGEBOX("Record added")

* Function ShowRs: print the returned recordset * on the desktop. FUNCTION ShowRS CLEAR oRecordSet.MoveFirst ? "Records returned: ", oRecordSet.RecordCount * and print the au_id field values DO WHILE ! oRecordSet.EOF ? oRecordSet.FIELDS("au_id").VALUE + ; " "+oRecordSet.FIELDS("au_lname").VALUE oRecordSet.MoveNext ENDDO ?

Additional query words: detached persisted saved offline view batchupdate

Keywords: kbhowto kbsqlprog kbdatabase KB195082

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.