CREATING A RECORDSET WITH ADO
Now it's time to see how to use ADO to perform one of the most common tasks—opening recordsets.
Opening a Simple Recordset
To open a recordset, you will first create a connection object off the current project, and then specify a new ADO recordset object:
Sub OpenRecordsetWithGetStringDisplayExample()
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection
rstCurr.Open "Select * from tblMovieTitles where ReleaseDate _
= #02/01/99#", cnnLocal, _
adOpenStatic, adOpenPessimistic
Debug.Print rstCurr.GetString
rstCurr.Close
End Sub
Although this code is very similar to DAO, in ADO you use the Recordset object's Open method rather than an OpenRecordset method.
Note
The GetString method allows you to put the full recordset into a Variant-type variable. In this case, the results are printed to the Immediate window (see Figure 6.6).
Figure 6.6. Printing a full recordset to the Immediate window using the GetString method.
In Production, this feature of ADO is useful usually for serializing the data for network or DCOM transfer. For example, if you tried to do the venerable While Not rs.EOF rs.MoveNext type of loop and pass the data cross-process inside the loop, performance would be hideous. On the other hand, sending a string all at once is relatively fast.
Also note that the recordset was opened with a cursor type of adOpenStatic and adLockPessimistic locking. (Locking isn't really necessary because the recordset is static, but it allows me to introduce the following tables.) Check out the choices you have in Tables 6.1 and 6.2.
| Cursor Type | Description |
|---|---|
| adOpenKeyset | Recordset is fully updateable, but you don't see others' additions and deletions. |
| adOpenStatic | Same as a DAO snapshot—recordset is read-only. |
| adOpenForwardOnly | Same as Static type recordset, except you can only go forward. This gives the best performance, especially if you need to go through the records quickly. |
Tip
By combining adOpenKeyset with the adCmdTableDirect option, you can basically create a table-type recordset. This isn't as useful, however, because there's no Seek method.
Tip
In code, I tend to use pessimistic locking (adLockPessimistic) because that way I make sure no one will slip under my edits. By using code, the edits and updates are so quick that it doesn't hurt others' performance noticeably.
In the next example, you see how to open the recordset for editing, as well as how to look through the fields in each record.
Looping Through and Editing Recordsets
The example in Listing 6.1 shows a number of features of working with recordsets.
Listing 6.1. Chap06.mdb: Opening a Recordset, Looping Through, and Editing the Records
Sub OpenRecordsetWithEditingExample()
Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Dim fldCurr As ADODB.Field
Set cnnLocal = CurrentProject.Connection
rstCurr.Open "Select * from tblMovieTitles where ReleaseDate =
#02/01/99#", cnnLocal, _
adOpenKeyset, adLockPessimistic
With rstCurr
Do Until .EOF
'-- Print each of the fields
For Each fldCurr In .Fields
Debug.Print fldCurr.Value
Next
'-- Updating the release date, look ma, no Edit or Update!
!ReleaseDate.Value = #3/1/1999#
Debug.Print
.MoveNext
Loop
End With
rstCurr.Close
End Sub
|
The actual structure of moving through the recordset with Do Until..Loop and the use of the MoveNext method is the same as in DAO. As with DAO, you can use MoveLast, MoveFirst, MoveNext, and MovePrevious.
Note
When using an adForwardOnly cursor type, you can't use some of the Move type methods. This makes sense because you can't MovePrevious in a forward-only recordset. You can trick ADO by using the recordset's Cachesize property; that's not recommended, however, because the execute command will be performed again, and you could load memory with the cached records and slow things down worse.
You can see from the line of code that reads
!ReleaseDate.Value = #3/1/1999#
that you can edit records without using the Edit method. You will also update the record by moving off the record—unlike DAO, which causes the information to be lost unless the Update method is called. This is convenient, but be careful—you normally still want to call the Update method to be consistent.
In order to cancel updating the record, you can use the CancelUpdate method, off the Recordset object.
The Delete method works the same as DAO and the AddNew method, except that you don't need the Update method.
Note
You can also see the use of the Fields collection in Listing 6.1, similar to the use in DAO.
Creating Persistent Recordsets
You might be asking yourself why anyone would want to create persistent recordsets when he has Make-Table queries and temporary tables. This feature (taking a recordset and creating a file outside Access with it) is handy because if you use an .ADP for your application, you can have temporary tables. No data is stored in the Access Database Projects, only a connection to the back.
You would use temporary tables and have them in the front end for these reasons:
You need to use intermediary data for reports and analysis, where queries just don't do the job.
By having the tables in the front end, you don't have to worry about other users overwriting your data.
The way the issue has been solved is to use persistent recordsets.
To create a persistent recordset, you use the Save method off the ADO Recordset object, passing it the name and path of the file to create. When you want to load the recordset back into memory, you use the Open method, again passing the filename of the saved recordset. Listing 6.2 shows an example of this.
Listing 6.2. Chap06.mdb: Saving and Loading a Recordset to/from a File
Tip
You also can use this feature to help send an update file to another location. That way, the other location can read the update file's records, after assigning them to a recordset variable with the Open method, and process the data as needed.
The Save method also supports XML, which can be used with IE5.
Note
Although the recordset file has an .rst extension in this example, ADO doesn't care what extension is used.
Using the RecordCount, BOF, and EOF Properties
When attempting to see whether there are any records in a recordset using DAO, you could look at the recordset's RecordCount property. If the RecordCount isn't zero, there were records in the recordset. It has been long understood that the RecordCount property wasn't going to necessarily be accurate without first performing the MoveLast method off the recordset.
However, with ADO you can no longer use RecordCount as straightforwardly as DAO. To see whether your recordset has any records in it, use the BOF and EOF properties. When used as follows on a newly opened recordset, you get True or False as to whether there are any records:
If rstCurr.BOF And rstCurr.EOF Then
For more information on using the RecordCount property accurately, look it up in Help.
Checking to See What Operations a Recordset Will Support
A number of operations won't be supported by some providers for the data you will work with. The method that helps with this is Supports off the Recordset object. The Supports method accepts one argument, which tells the method which feature you're checking on to see whether the recordset supports it. The following feature constants can be used:
adAddNew adResync adApproxPosition adUpdate adBookmark adUpdateBatch adDelete adIndex adHoldRecords adSeek adMovePrevious
Tip
With the Supports method, if adApproxPosition or adBookmark returns True, the RecordCount property returns the correct number of records, even without using the MoveLast method on the recordset.
Cloning Recordsets
One task that I perform in DAO on occasion is to open a recordset off another, so that I can move around in the new one without affecting the original. Although you can do this in DAO by using the OpenRecordset command off the original recordset, in DAO and ADO there is a faster way.
The faster way to create another recordset set off the original is to use the Clone method. To use this method in ADO, you would use something similar to this syntax:
Set rstClose = rstOriginal.Clone
You can also specify one parameter that affects the locking method of the new recordset. The choices are adLockUnspecified (default) or adLockReadOnly.
Tip
One nice thing about using the Clone method is that the bookmarks in both recordsets are identical. This means you can set a bookmark, move around in the other recordset, and then set either recordset's bookmark to the others.
Bookmarks
Bookmarks allow you to mark where you are in a recordset, and then move back to that location. In DAO you can store a bookmark to a string, move around in the recordset, and then set the Bookmark property to the saved string. You would then be repositioned to the bookmarked record. You can read more of this in Chapter 16, “Extending Your VBA Library Power with Class Modules and Collections.”
In ADO, Microsoft gives you more functionality in the bookmarks. By using the CompareBookmarks method off a recordset, you can tell which bookmark occurs later in the recordset.