HOWTO: Using ADO Disconnected and Persisted RecordsetsID: Q195082
|
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.
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/
* 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
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbhowto Last Reviewed: July 28, 1999