HandsOn Creating a Disconnected Recordset

^^ Note: Because this hands-on retrieves data from the Northwind database, adjust the path found in the procedure code to point to the correct location of this file on your computer.

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Rst_Disconnected procedure shown below.

Sub Rst_Disconnected()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String Dim strSQL As String Dim strRst As String strSQL = "Select * From Orders where CustomerID = 'VINET'"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" strConn = strConn & "Data Source = C:\Program Files\" & _ "Microsoft Office\Office11\Samples\Northwind.mdb"

Set conn = New ADODB.Connection conn.ConnectionString = strConn conn.Open

Set rst = New ADODB.Recordset Set rst.ActiveConnection = conn

Creating and Manipulating Databases with ADO

' retrieve the data rst.CursorLocation = adUseClient rst.LockType = adLockBatchOptimistic rst.CursorType = adOpenStatic rst.Open strSQL, , , , adCmdText

' disconnect the recordset

Set rst.ActiveConnection = Nothing

' change the CustomerID in the first record to 'OCEAN' rst.MoveFirst

Debug.Print rst.Fields(0) & " was " & rst.Fields(1) & " before."

rst.Fields("CustomerID").Value = "OCEAN"

rst.Update

' stream out the recordset as a comma-delimited string strRst = rst.GetString(adClipString, , ",") Debug.Print strRst End Sub

Notice that to create a disconnected recordset that gets its data from a data source, you need to set the CursorLocation, LockType, and CursorType properties of the Recordset object. CursorLocation should be set to adUseClient. This setting indicates that the cursor will reside on the client computer that is creating the recordset. Set LockType to adLockBatchOptimistic to enable multiple records to be updated. Finally, set CursorType to adOpenStatic to retrieve the snapshot of the data.

To disconnect a recordset, you must set the Recordset object's Active-Connection property to Nothing after you've called the recordset's Open method.

When the recordset is disconnected from the database, you can freely manipulate its data or pass it to another application or process. In the example procedure above, we manipulate our recordset by changing the value of the CustomerID field in the first retrieved record from VINET to OCEAN. Then we create a comma-delimited string using the Recordset object's GetString method. The content of the disconnected recordset is then printed out to the Immediate window, as shown here:

10274 was VINET before.

10274,CCEAN,6,8/6/1996,9/3/1996,8/16/1996,1,6.01,Vins et alcools Chevalier,59 rue de l'Abbaye,Reims,,51100,France

10295,VINET,2,9/2/1996,9/30/1996,9/10/1996,2,1.15,Vins et alcools Chevalier,59 rue de l'Abbaye,Reims,,51100,France

10737,VINET,2,U/n/1997,12/9/1997,n/18/1997,2,7.79,Vins et alcools Chevalier,59 rue de l'Abbaye,Reims,,51100,France

10739,VINET,3,n/12/1997,12/10/1997,n/17/1997,3,11.08,Vins et alcools Chevalier,59 rue de l'Abbaye,Reims,,51100,France

Part II

0 0

Post a comment