MS-Access / Getting Started

---

Object Methods

When you want to apply an action to an object in your database (such as open a query as a recordset or go to the next row in a recordset), you apply a method of either the object or an object variable that you have assigned to point to the object. In some cases, you'll use a method to create a new object. Many methods accept parameters that you can use to further refine how the method acts on the object. For example, you can tell the DAO Open- Recordset method whether you're opening a recordset on a local table, a dynaset (a querybased recordset), or a read-only snapshot.

Visual Basic supports many different object methods-far more than there's room to properly document. Perhaps one of the most useful groups of methods is the group you can use to create a recordset and then read, update, insert, and delete rows in the recordset.

Working with DAO Recordsets

To create a recordset, you must first declare a Recordset object variable. Then open the recordset using the DAO OpenRecordset method of the current database (specifying a table name, a query name, or an SQL statement to create the recordset) or the OpenRecordset method of a DAO.QueryDef, DAO.TableDef, or other DAO.Recordset object.

In DAO, you can specify options to indicate whether you're opening the recordset as a local table (which means you can use the Seek method to quickly locate rows based on a match with an available index), as a dynaset, or as a read-only snapshot. For updateable recordsets, you can also specify that you want to deny other updates, deny other reads, open a read-only recordset, open the recordset for append only, or open a read-only forward scroll recordset (which allows you to move only forward through the records and only once). The syntax to use the OpenRecordset method of a Database object is as follows:

Set RecordSetObject = DatabaseObject.OpenRecordset(source, [type], [options], [lockoptions])

RecordSetObject is a variable you have declared as DAO.Recordset, and DatabaseObject is a variable you have declared as DAO.Database. Source is a string variable or literal containing the name of a table, the name of a query, or a valid SQL statement. Table-2 describes the settings you can supply for type, options, and lockoptions.

Table-2 OpenRecordset Parameter Settings

Setting	Description
Type (Select one)
dbOpenTable	Returns a table recordset. You can use this option only when source is a table local to the database described by the Database object. Source cannot be a linked table. You can establish a current index in a table recordset and use the Seek method to find rows using the index. If you do not specify a type, OpenRecordset returns a table if source is a local table name.
dbOpenDynaset	Returns a dynaset recordset. Source can be a local table, a linked table, a query, or an SQL statement. You can use the Find methods to search for rows in a dynaset recordset. If you do not specify a type, OpenRecordset returns a dynaset if source is a linked table, a query, or an SQL statement.
dbOpenSnapshot	Returns a read-only snapshot recordset. You won't see any changes made by other users after you open the recordset. You can use the Find methods to search for rows in a snapshot recordset.
dbOpenForwardOnly	Returns a read-only snapshot recordset that you can move forward through only once. You can use the MoveNext method to access successive rows.
Options (You can select multipl e options, placin g a plus sign betw een option names to add them together)
dbAppendOnly	Returns a table or dynaset recordset that allows inserting new rows only. You can use this option only with the dbOpenTable and dbOpenDynaset types.
dbSeeChanges	Asks Access to generate a run-time error in your code if another user changes data while you are editing it in the recordset.
dbDenyWrite	Prevents other users from modifying or inserting records while your recordset is open.
dbDenyRead	Prevents other users from reading records in your open recordset.
dbInconsistent	Allows you to make changes to all fields in a multiple table recordset (based on a query or an SQL statement), including changes that would be inconsistent with any join defined in the query. For example, you could change the customer identifier field (foreign key) of an orders table so that it no longer matches the primary key in an included customers table-unless referential integrity constraints otherwise prevent you from doing so. You cannot include both dbInconsistent and dbConsistent.
dbConsistent	Allows you to only make changes in a multiple table recordset (based on a query or an SQL statement) that are consistent with the join definitions in the query. For example, you cannot change the customer identifier field (foreign key) of an orders table so that its value does not match the value of any customer row in the query. You cannot include both dbInconsistent and dbConsistent.
dbPessimistic	Asks Access to lock a row as soon as you place the row in an editable state by executing an Edit method. This is the default if you do not specify a lock option.
dbOptimistic	Asks Access to not attempt to lock a row until you try to write it to the database with an Update method. This generates a run-time error if another user has changed the row after you executed the Edit method.

For example, to declare a recordset for the tblFacilities table in the Hotel Reservations (Hotel.accdb) database and open the recordset as a table so that you can use its indexes, enter the following:

Dim dbHotel As DAO.Database
Dim rcdFacilities As DAO.RecordSet
Set dbHotel = CurrentDb
Set rcdFacilities = dbHotel.OpenRecordSet("tblFacilities", _ dbOpenTable)

To open the qryContactProducts query in the Conrad Systems Contacts database (Contacts.accdb) as a dynaset, enter the following:

Dim dbContacts As DAO.Database
Dim rcdContactProducts As DAO.RecordSet
Set dbContacts = CurrentDb
Set rcdContactProducts = _
  dbContacts.OpenRecordSet("qryContactProducts")

(Note that opening a recordset as a dynaset is the default when the source is a query.)

Note: Any table recordset or dynaset recordset based on a table is updateable. When you ask Access to open a dynaset on a table, Access internally builds a query that selects all columns from the table. A dynaset recordset based on a query will be updateable if the query is updateable.