Creating New Data Access Objects

As mentioned in the section Creating Object Variables earlier in this chapter, Access 97 lets you create new DAOs with Access VBA code. The most common DAOs you create with Access VBA are Recordset and QueryDef objects. QueryDef objects, like TableDef objects, are called persistent objects because QueryDef and TableDef objects also are Document objects: Query and Table objects, respectively. QueryDef and TableDef objects are stored as elements of your .mdb file and appear in the Queries and Tables lists, respectively, of the Database window. Recordset objects are impersistent objects; they exist in memory only from the time you open them until they are closed. You can close a Recordset object by applying the Close method; Recordset objects are closed automatically when the variable that points to them goes out of scope. When you close a Recordset object, the memory that the object consumes is released to your application.

Opening a New Recordset Object

You create a new Recordset object with the OpenRecordset method of Database, TableDef, and QueryDef objects. The general syntax of the OpenRecordset method has two forms:

							Set rsdName = dbName.OpenRecordset(str Source[, intType[,_
       intOptions[, intLockEdits]]])
     Set rsoName = objName. OpenRecordset([intType[, intOptions[, intLockEdits]]])

The first form, which is applicable only to Recordset objects created over Database objects, requires a value for the strSource argument. The value of the strSource argument can be the name of a TableDef object, or QueryDef object, or a Jet SQL statement. You also can use VBA's named arguments feature in the following statements:

							Set rsdName = OpenRecordset(Name:=str Source[, Type:=intType[,_
       Options:=intOptions[, LockEdits:=intLockEdits]]])
     Set rsoName = objName.OpenRecordset([Type: =intType[,_
       Options:=intOptions[, LockEdits:=intLockEdits]]])

Tables 27.1, 27.2, and 27.3 list values allowed for the intType, intOptions, and intLockEdits arguments for both of the preceding syntax examples. The values shown in the three tables are the names of predefined (intrinsic) global constants that are defined by the Jet 3.5 DAO. In earlier versions of Access, Access intrinsic global database constant names were uppercase and included underscore separators for readability, as in DB_OPEN_TABLE. Database intrinsic constants now are supplied by enumerations of the Jet 3.5 DAO and use the lowercase prefix db with intermediate capitalization to improve readability. A MethodArgumentEnum selection in the Class list of the Object Browser displays the names and values of applicable constants in the Members list (see Figure 27.9). In the case of the intOptions values listed in Table 27.2, you can combine the options you want with the VBA Or operator, as in dbDenyWrite Or dbDenyRead.

The following two sections show how to create two different types of Recordset objects.

A Recordset Object that Represents the Image of a Table. You can create the following two types of Recordset objects over a table, both of which create a virtual table in memory:

• Recordset object of the Dynaset type created from the current Database object. To open a Dynaset-type Recordset object over the Orders table, add the PrivaterstCurrent AsRecordset variable declaration statement to the Declarations section of the module. Then use the following statement to assign the pointer, assuming that you have previously executed the Set dbCurrent = CurrentDB() statement:

  									Set rsdCurrent = dbCurrent.OpenRecordset("Orders", dbOpenDynaset)
  

• A Recordset object of the Table type created from a TableDef object. To open a Table Recordset object over the Orders table, add the Private tdfCurrentAsTableDef variable declaration statement to the Declarations section of the module. Then use the following statements to assign the pointers:

  									Set tdfCurrent = dbCurrent.TableDefs("Orders")
  Set rstCurrent = tdfCurrent.OpenRecordset(dbOpenTable)
  

Figure 27.10 illustrates the creation of both of the preceding types of Recordset objects in the Debug Window.

Moving to a Specific Record in a Recordset Object. Opening a Recordset of the Table type lets you quickly find a record in a table with the Seek method. This method is applicable only to Table-type Recordset objects whose underlying table contains an index on the field in which the value you want to find is located. To apply the Seek method, you first must specify the index name. The following example sets the record pointer to the record in the Orders table of Northwind.mdb with a value of 10833 in the OrderID field, the primary key of the table:

							Set dbCurrent = CurrentDB()
      Set tdfCurrent = dbCurrent.TableDefs("Orders")
      Set rstCurrent = tdfCurrent.OpenRecordset()
      rstCurrent.Index = "PrimaryKey"
      rstCurrent.Seek "=", 10833
      If rstCurrent.NoMatch Then
         'Record not found, display message box
      Else
        'Record found, add code to process the record here
      End If
						

If the field in which you are Seeking the value is not the primary key field, you specify the field's name. The NoMatch property returns False if the value specified by the Seek expression is found; it returns True if a matching record is not found. Figure 27.11 shows typical entries to test the Seek method in the Debug Window. Test the Indexes collection of the TableDef object to verify that the index you specify exists. The Seek method allows the use of a variety of other operators, such as < or >=, in place of the = operator.

You use the Find... methods to locate a specific record in Recordset objects of the Dynaset and Snapshot type. There are four Find... methods: FindFirst, FindNext, FindLast, and FindPrevious. Not surprisingly, each method does what its name indicates. The general syntax of the Find... methods is

      rsdName.Find{First|Next|Last|Previous} strCriteria

The single strCriteria argument of the Find... methods must be a valid SQL WHERE clause without the WHERE reserved word. As with the Seek method, the NoMatch property returns False if a match occurs. Figure 27.12 shows examples of the use of the four Find… methods.

A Recordset that Emulates a QueryDef Object. You can substitute a valid Jet SQL statement for a table or query name as the value of the strSource property to create a Recordset object of the Dynaset or Snapshot type. You only can create such a Recordset over the Database object because more than one TableDef object may participate in the query. Such a Recordset is the equivalent of an "impersistent QueryDef" object (a QueryDef object that is not a Document object). Figure 27.13 shows you how to create a Recordset object by using a SQL statement in the Debug Window. You must add a Private strSQLAs String statement to the Declarations section of the module if the OptionExplicit statement has been executed. The entire strSQL = _ statement is

      strSQL = "SELECT * FROM Orders, [Order Details]
      WHERE [Order Details].OrderID = Orders.OrderID
      AND Orders.CustomerID = 'BERGS'"

If you need to manipulate a Recordset's records only temporarily, creating the "impersistent QueryDef" Recordset is faster than creating a legitimate, persistent QueryDef object.

Defining a New QueryDef Object

You use the CreateQueryDef method to create a new, persistent QueryDef object and add it to the QueryDefs collection. One of the advantages of creating a QueryDef is that you can use the name you give the QueryDef in place of a table name in a Jet SQL statement. The name of the QueryDef must not duplicate the name of an existing QueryDef or TableDef object. The syntax for creating a new QueryDef object is

							Set qdfName = dbName.CreateQueryDef(str Name, strSQL)

QueryDef objects are what their object class name implies: the definition of a query. If strSQL's SQL statement represents an action query, you apply the Execute method to the QueryDef object to execute the query. You must create a Recordset over QueryDef objects that define select queries in order to read values in the query result set. Figure 27.14 illustrates how you create a new QueryDef object and open a Recordset object based on the QueryDef. You need to add Private qdfCurrent As QueryDef to the Declarations section of your code before you can execute the code shown in the Debug Window of Figure 27.14. When you execute a statement that includes the CreateQueryDef() method, the new QueryDef appears in the Queries page of the Database window. You can open a QueryDef created by Access VBA, as shown in Figure 27.15.