How to use ADO disconnected and persisted recordsets

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
   ?