The code prints the search string (always useful for debugging) and the number of records in the
recordset, before and after adding the new record, to the Immediate window:
Search string: [Category] = ‘Firmware’
29 records initially in recordset
30 records in recordset after addingStatic
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
adLockReadOnlylock type, as in the following TestStaticReadOnlyprocedure. 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 TestStaticReadOnlyprocedure 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 NextDim 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 StringCreate a connection to an external database.
strCurrentPath = Application.CurrentProject.Path & “\”
strDBName = “Northwind.mdb”
strDBNameAndPath = strCurrentPath & strDBNameWorking with Access Data 5