HOWTO: Using ADO Disconnected and Persisted Recordsets

• Microsoft Visual FoxPro for Windows, version 6.0
• Microsoft Data Access Components version 2.1 SP2

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://www.microsoft.com/data/

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 : kbActiveX kbADO kbMDAC kbSQL kbVFp600 kbGrpFox kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto