Static

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

Use a SQL string to create a filtered recordset.

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.

With rst

.MoveLast .MoveFirst

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

ErrorHandlerExit:

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

Exit Sub

ErrorHandler:

& "; 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

0 0

Post a comment