The static cursor type (DAO equivalent: dbOpenSnapshot) provides a static copy of a set of records, for viewing or printing data. All types of movement through the recordset are allowed. Additions, changes, or deletions made by other users are not shown. For fast access to data that you don't need to modify, where you don't need to view other users' changes and you do need to be able to move both forward and backward in the recordset, use a static cursor and the adLockReadOnly lock type, as in the following TestStaticReadOnly procedure. If you do need to modify the data, but don't need to see other users' changes, use the adLockOptimistic lock type instead (the cursor type will change to keyset, as noted previously).
The TestStaticReadOnly procedure sets up a connection to the Northwind database, opens a filtered recordset based on a table in the database, and then iterates through the recordset, printing information from its fields to the Immediate window. Note that once an ADO recordset has been created, many of the same methods can be used to work with it as for a DAO database (BOF, EOF, Find*, Move*):
Private Sub TestStaticReadOnly()
On Error Resume Next
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strDBName As String
Dim strDBNameAndPath As String
Dim strConnectString As String
Dim strSQL As String
Dim strCurrentPath As String
Dim fso As New Scripting.FileSystemObject
Dim fil As Scripting.File
Dim strPrompt As String
Create a connection to an external database.
strCurrentPath = Application.CurrentProject.Path & strDBName = "Northwind.mdb"
strDBNameAndPath = strCurrentPath & strDBName
Attempt to find the database, and put up a message if it is not found.
Set fil = fso.GetFile(strDBNameAndPath) If fil Is Nothing Then strPrompt = "Can't find " & strDBName & " in " _ & strCurrentPath & "; please copy it from the " & "OfficeiiXSamples subfolder under the main " & "Microsoft Office folder " _ & "of an earlier version of Office" MsgBox strPrompt, vbCritical + vbOKOnly GoTo ErrorHandlerExit End If
On Error GoTo ErrorHandler
Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset
Need to specify the Jet 4.0 provider for connecting to Access databases. With cnn
.Provider = "Microsoft.Jet.OLEDB.4.C>" .Open strDBNameAndPath strConnectString = .ConnectionString End With
strSQL = "SELECT CompanyName, ContactName, " _ & "City FROM Suppliers " _ & "WHERE Country = 'Australia' " _ & "ORDER BY CompanyName;" rst.Open Source:=strSQL, _
ActiveConnection:=strConnectString, _ CursorType:=adOpenStatic, _ LockType:=adLockReadOnly
Iterate through the recordset, and print values from fields to the Immediate window.
Debug.Print .RecordCount _
& " records in recordset" & vbCrLf Do While Not .EOF
Debug.Print "Australian Company name: " & ![CompanyName] _
& vbCrLf & vbTab & "Contact name: " & ![ContactName] _
& vbCrLf & vbTab & "City: " & ![City] _ & vbCrLf rst.MoveNext Loop End With
Close the Recordset and Connection objects.
If Not rst Is Nothing Then
If rst.State = adStateOpen Then rst.Close Set rst = Nothing End If End If
If Not cnn Is Nothing Then
If cnn.State = adStateOpen Then cnn.Close Set cnn = Nothing End If End If
& "; Description: " & Err.Description Resume ErrorHandlerExit End Sub
The following information is printed to the Immediate window: 2 records in recordset
Australian Company name: G'day, Mate Contact name: Wendy Mackenzie City: Sydney
Australian Company name: Pavlova, Ltd. Contact name: Ian Devling City: Melbourne
Was this article helpful?