| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The following code demonstrates how to open a forward-only, read-only Recordset object.
Sub OpenReadOnlyRecordset(strDBPath As String, _
strSQL As String)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Set cnn = New ADODB.Connection
' Open the connection.
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath
End With
Set rst = New ADODB.Recordset
With rst
' Open a forward-only, read-only Recordset object.
.Open Source:= strSQL, _
ActiveConnection:= cnn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly
' Display the records in the Debug window.
Do While Not .EOF
For Each fld In .Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
.MoveNext
Loop
'Close the Recordset.
.Close
End With
' Close connection and destroy object variables.
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
For example, to use this procedure to open the Customers table in the Northwind database and return only customers from Washington State, you can use a line of code like this:
OpenReadOnlyRecordset _
"c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"SELECT * FROM Customers WHERE Region = 'WA'"
The OpenReadOnlyRecordset procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.
In the ADO code above, the Recordset object is opened and then the data in the Recordset object is printed to the Immediate pane by moving through each record in the Recordset object and then iterating through each field in the Fields collection.
This can be rewritten to use the Recordset object's GetString method to print the data to the Immediate pane. The GetString method returns a formatted, delimited string that contains some or all of the records in the Recordset object. If you use the GetString method, the Do While loop in the previous example could be replaced with the single line:
Debug.Print rst.GetString(StringFormat:=adClipString, _
ColumnDelimeter:=";", _
RowDelimeter:=vbCrLf)
Note The ColumnDelimeter and RowDelimeter named arguments are intentionally misspelled because this is how they are defined in the ADO object library.
This method is useful for populating grids and other controls that allow you to pass in a formatted string representing the data. The GetString method is also faster than looping through the Recordset object and generating the string.