| The DAO library offers several different types of recordsets. When you assign a table or a query to a recordset by means of a Set statement, you specify the type of recordset that you want to use. Your choice has implications for the demands on system resources that your code will make, as well as for the ways that your code can use the recordset. Your Set statement can take any of several forms. In each case, the OpenRecordset method is used, and it takes several arguments: Source This is the only required argument. It's a string, and is the name of the object that contains the records. The source is usually a table or an existing query, but it could also be a SQL statement that defines a query. Type There are five types of DAO recordsets. The requirements for each type are set out in the sections that follow. This is an optional argument. If you don't specify a type, a table-type recordset is opened. Options This is an optional argument. There are 11 options available. They're largely concerned with permitting or denying read and write permissions, or are provided for backward compatibility with earlier versions. As a practical matter, you don't normally need to set these options in your code, and this book won't discuss them. They're described in Access's Help documentation of the OpenRecordset method. LockEdits This is an optional argument. The possible settings include specifying optimistic or pessimistic updates. When a recordset is locked pessimistically, a record that one user is editing is unavailable for editing by any other user as soon as the recordset's Edit method executes, and made available again after the Update method executes. If optimistic locking is in place, the record is locked only while the Update method is executing. The default is pessimistic locking. Again, your code does not usually need to specify LockEdits.
You can execute the OpenRecordset method against a database or a connection to a database, using either of these two forms: Set Recordset = Database.OpenRecordset _ (Source, Type, Options, LockEdits) Set Recordset = Connection.OpenRecordset _ (Source, Type, Options, LockEdits)
You can also execute the OpenRecordset method against a table, a query, or another recordset. In that case, you would use this form: Set Recordset = Object.OpenRecordset (type, options, lockedits)
where Object is a table, query, or recordset. For example Set rsProcedures = dbDataFile.TableDefs("Procedures") _ .OpenRecordset (dbOpenDynaset)
or Set tblProcedures = dbDataFile.TableDefs("Procedures") Set rsProcedures = tblProcedures.OpenRecordset (dbOpenDynaset)
The most commonly used DAO recordset types are discussed in the next three sections. Setting a Table-Type Recordset You set a table-type recordset with a statement such as the next one: Set rsProcedures = dbDataFile.OpenRecordset("Procedures", dbOpenTable)
In this statement, Procedures is the source of the recordset and dbOpenTable is its type. Procedures must be a table, not a query, and the table must be physically within the database, not a link to a table in another database. A table-type recordset is the default, so if Procedures is a table, this is an equivalent statement: Set rsProcedures = dbDataFile.OpenRecordset("Procedures")
If Procedures is instead a query or a linked table, rsProcedures is by default a dynaset recordset. If the table is a very large one, you might be able to make your code more efficient by accessing records using the Seek method. For example Sub SeekARecord() Dim dbDatabaseFile As DAO.Database Dim rsProcs As DAO.Recordset Dim SourceName As String Dim WhichID As Integer WhichID = InputBox("Enter a procedure ID") SourceName = ThisWorkbook.Path & "\ShortStay.mdb" Set dbDatabaseFile = OpenDatabase(SourceName) Set rsProcs = dbDatabaseFile.OpenRecordset _ ("Procedures", dbOpenTable) rsProcs.Index = "PrimaryKey" rsProcs.Seek "=", WhichID MsgBox rsProcs.Fields("ProcedureName") End Sub
This subroutine prompts the user for the value of a table's primary key. It then opens the database, and establishes a recordset based on the database's Procedures table. It sets the table's index to the index named PrimaryKey. Then it executes the recordset's Seek method. This is how, using DAO, you access a record very rapidly. VBA can use a b-tree search to locate the ID supplied by the user in the table's index, and return the record's value on the ProcedureName field. This can speed up processing with very large tables. But there's a tradeoff: Before you can use the Seek method, you must set the table's current index. With smaller recordsets, it might take longer to set the index and execute the seek than to look directly for the record, as in the following code: Sub FindARecord() Dim dbDatabaseFile As DAO.Database Dim rsProcs As DAO.Recordset Dim SourceName As String Dim WhichID As Integer WhichID = InputBox("Enter a procedure ID") SourceName = ThisWorkbook.Path & "\ShortStay.mdb" Set dbDatabaseFile = OpenDatabase(SourceName) Set rsProcs = dbDatabaseFile.OpenRecordset("Procedures", dbOpenSnapshot) rsProcs.FindFirst "ProcedureID = " & WhichID MsgBox rsProcs.Fields("ProcedureName") End Sub
The subroutine named FindARecord doesn't involve the table's index. It just looks through the Procedure table's ProcedureID field until it finds the ID that the user supplies. Then it reports that record's value on the ProcedureName field. Notice that the recordset is declared not as dbOpenTable but as dbOpenSnapshot, a DAO recordset type discussed next. To use the FindFirst method, the recordset must be dbOpenSnapshot or dbOpenDynaset. NOTE You cannot use DAO's Seek method with a linked table. It's available only with recordsets typed as dbOpenTable, and as noted at the beginning of this section, a linked table cannot be typed as dbOpenTable.
Setting a Snapshot-Type Recordset You set a snapshot recordset with the dbOpenSnapshot type: Set rsProcs = dbDatabaseFile.OpenRecordset _ ("Procedures", dbOpenSnapshot)
A snapshot recordset, unlike other recordset types, is read only. This is because all the records in its source are brought into memory when the Set statement is executed. Any changes to the source's records that subsequently occur will not be reflected in the recordset. Furthermore, you cannot edit, add, or delete records from the recordset. A snapshot recordset can be useful if you're just accessing fields and records and your code is not intended to modify them. Because it's less flexible than other recordset types, it can make more efficient use of system resources than other types. However, it can run slightly more slowly than other types because it brings all the source's records and fields into memory. This is in contrast to, for example, the dynaset type of recordset, which brings only bookmarks into memory when the recordset is established. The term snapshot can be a source of confusion. This section discusses DAO recordsets, and a DAO snapshot recordset is not updatable (another term for editable). There is a type of ADO recordset, a static recordset, that Access projects refer to as a snapshot. But an ADO snapshot recordset can be made updatable. Setting a Dynaset-Type Recordset A dynaset recordset is the most flexible of the DAO recordset types. It can be based on a table or a query, and it allows the code to edit, append, or delete the source's records. You call for a dynaset, as usual, in the recordset's Set statement. Set rsProcs = dbDatabaseFile.OpenRecordset _ ("Procedures", dbOpenDynaset)
When this statement runs, records are brought into memory, but only their bookmarks. (A bookmark is a property of the recordset. It's analogous to a primary key in that it uniquely identifies each of a recordset's records.) It's only when the code modifies records that the fields are made available. This is the reason that dynasets can initially open more quickly than other recordset types. | 