DAO: Data Access Object

A DAO is an automation interface for Accessing the Jet Database engine. In fact, Access is an IDE/GUI for Jet. DAO is described by a containment hierarchy beginning with the DBEngine object. The DAO object hierarchy is shown on page 16-5. To access DAO from VB, its object library must be loaded in via the Project References dialog. Currently the Microsoft DAO 3.5 Object Library is used.

The Recordset Object

VB interacts with DAO data sets via the Recordset object. This object can be instanced using the TableDef object, the QueryDef object or a SQL string against a valid Database object. There are three types of record sets: table-, snapshot-, and dynaset-type record sets. The most commonly used record set is the dynaset type.

The OpenDatabase Method

Use the OpenDatabase method to open a Jet .MDB or compatible Indexed Sequential Access Method (ISAM) file. The opening (and closing) has the general form:

Set g_dbDAO = DBEngine.Workspaces(0).OpenDatabase("C:\\MyData.MDB")
'Use database object.
Set g_dbDAO = Nothing

where MyData.MDB is opened against the default DAO Workspace object. An alternative way to close the database object is to use the Close method. This has the form:

g_dbDAO.Close

This method is "better" if the programmer wishes to recognize the specific qualities of the object instead of handling the object variable itself.

The RecordCount Property of the Recordset Object

The RecordCount property does not return the number of rows in the record set. It only provides the current number of rows accessed. However, it can always be used to determine if a given record set contains any rows at all. This is very useful because any operations on zero-row record sets will generate runtime errors.

This is one form of checking for zero-row record sets:

Set g_rsDAO = g_dbDAO.OpenRecordset("qselZeroRows", dbOpenDynaset)

With g_rsDAO
    If .Recordcount Then
        'Operate on record set.
    Else
        'Do not operate on record set.
    End If
End With

Set g_rsDAO = Nothing
Set g_dbDAO = Nothing

Another form implicitly checks for zero-row record sets. This code fragment uses the MoveNext method of the Reocrdset object to move through all of the rows of the record set. The Boolean EOF property of the Recordset object is used to stop this loop:

With m_rsDAO
    Do Until .EOF
       'Operate on record set.
        .MoveNext
    Loop
End With

If a design calls for the MovePrevious method, the BOF property of the Recordset object should be used as well.