The DAO Hierarchy

So, DAO here we come If we are to use DAO effectively, then we must start with an understanding of the DAO hierarchy. We saw in the previous chapter how we can interact programmatically with the Access application via the Access object model. This contained a number of objects, grouped together into collections, all arranged in a hierarchical manner.

For example, the Forms collection contains a number of Form objects representing every form that is currently open .

Each of these Form objects has a Controls collection that contains a Control object for every control appearing on that form.

Each Control object has a Properties collection that contains a Property object for every property of that control.

Important 

Make sure that you appreciate the difference between the Access object model and the DAO object model. The Access object model is what we use to interact programmatically with the Access user interface (forms and reports ). The DAO hierarchy is what we use to programmatically interact with JET databases.

The good news is that the DAO hierarchy is arranged in the same similar manner to the Access object model, with collections of objects, each of which has its own methods and properties. However, before we look at the DAO hierarchy, we need to make sure that we have a reference to DAO in our database.

Forms and reports in Access 2002 still use DAO rather than ADO to fetch the data that they display. Originally it was intended that they should use ADO, but Microsoft changed tack during the development cycle when it was clear that the limitations of ADO/JOLT would mean the loss of a great deal of functionality to which Access developers had grown accustomed.

However, despite the fact that DAO is the mechanism used for retrieving data in forms and reports, the default database access method from Visual Basic is now ADO. What do we mean by that? Well, if you open the IceCream.mdb database, switch to the VBA IDE by hitting Alt+F11 and then look at the references that are set by default (by choosing References from the Tools menu) you should see this, if you scroll down:

As you can see from the References dialog box, there are quite a number of libraries that we can reference from within VBA. We will look at libraries in more detail in Chapters 13 and 15, but for the moment you can think of them as collections of pre-built objects and functions that we can use in our code. Even though there might be many such libraries installed on our computer, it is unlikely that we will want to use them all in every database. So we can use the References dialog box to select those libraries that we want to be available from VBA within each database. To make a library available from within VBA, we simply check the checkbox beside it. Unchecking the checkbox makes the library unavailable, which means that we will get an error if we try to refer to any of the objects or functions in that library.

The IceCream.mdb database has references to the four libraries that are available by default in new Access 2002 databases:

• Visual Basic for Applications

• Microsoft Access 10.0 Object Library

• OLE Automation

• Microsoft ActiveX Data Objects 2.6 Library (ADO)

Now we are not going to be using ADO in this chapter, but will be using DAO. So we need to remove the reference to ADO and add a reference to DAO. We do that by unchecking Microsoft ActiveX Data Objects 2.6 Library a nd checking Microsoft DAO 3.6 Object Library . Once we have done that and clicked OK , we will be able to use DAO 3.6 from within our code.

Important 

Strictly speaking, we don't need to remove the reference to ADO. However, some of the objects in the ADO hierarchy have the same names as objects in the DAO hierarchy and retaining the reference to ADO could lead to confusion when writing code that uses the DAO version of those objects.

So let's have a look at the DAO 3.6 hierarchy:

Two collections ( Users and Groups ) have been omitted from this diagram for the sake of simplicity. Don't worry; we'll be looking at these in a lot more detail when we deal with security in Chapter 15.

Hopefully, after reading the previous chapter, you should be familiar with the concept of object hierarchies. But probably the best way to learn how the DAO hierarchy works is to walk through the object model by using the Immediate window. And that's what we will do. So, pour yourself a nice strong cup of coffee, make sure you are sitting comfortably, open the Immediate window and get ready for a whistle -stop tour of the DAO hierarchy, starting from the top.

The DBEngine Object

In the Access object hierarchy, the topmost object was the Application object, representing the Access application. Well, as you can see from the diagram above, the topmost object in the DAO hierarchy is the DBEngine object. This represents the DAO interface into the JET engine - the database engine which Access uses for all of its native database management - and ODBCDirect, which is used for accessing remote ODBC databases. So what can we do with the DBEngine object? Well, we can start by determining what version of DAO we are using. To do this, type the following in the Immediate window and hit Enter :

   ? DBEngine.Version   

The result 3.6 should be displayed, indicating that we are using version 3.6 of DAO.

The DBEngine object contains two collections, the Errors collection and the Workspaces collection. The Errors collection is populated with Error objects every time a database- related error occurs, and we will look at this in more detail in Chapter 12 when we get to grips with error handling.

To be honest, there isn't that much else that you can do with the DBEngine object per se. In fact, you will probably find that most of the time you won't use the DBEngine object for anything other than as a way of getting to the Errors or Workspaces collections.

The Workspace Object

The other collection is the Workspaces collection, which we can think of as representing a single session or instance of a user interacting with the database engine.

For example, to find out the name of the currently logged-on user of Access, we could execute the following code in the Immediate window:

   ? DBEngine.Workspaces(0).UserName   

If you are using an unsecured system database, and have not logged into Access, the name of the default user will be displayed:

   admin   

But if you have manually logged into Access, the user name you logged in with will be displayed instead. Notice that to get to this property we used the syntax DBEngine.Workspaces(0) . This expression returns the first (and most of the time only) Workspace object in the Workspaces collection. This represents the currently logged on interactive session that you have with the database engine.

Earlier in this chapter we mentioned that Access 97 (or, more accurately, version 3.5 of DAO) saw the introduction of ODBCDirect, a technology that enables Access developers programmatic access to remote ODBC databases such as SQL Server or Oracle. In order to give developers tight control over the security and session management features of these databases, there is a special type of Workspace object that should be used when working with ODBCDirect. However, we don't need to worry about that because in this book we won't be dealing with ODBCDirect.

The Workspace object used by JET contains a Users collection and a Groups collection that contain details of all users and groups defined in the current system database.

It also contains a Databases collection that holds a Database object representing every database that the current user for that Workspace object has open.

The Workspace object will be dealt with more fully in Chapter 17, when we consider multi-user and security issues.

The Database Object

Normally, we will only have one database open at a time, so there will only be one Database object in the Databases collection for the current workspace. You can see this for yourself by typing this in the Immediate window and hitting Enter :

   ? DBEngine.Workspaces(0).Databases.Count   

which should return 1 . To inspect this Database further, we could refer to it by its position in the Databases collection like this:

   DBEngine.Workspaces(0).Databases(0)   

So, to find the name of the currently open database we could type this in the Immediate window:

   ? DBEngine.Workspaces(0).Databases(0).Name   

Alternatively, because the Workspaces collection is the default collection of the DBEngine object and the Databases collection is the default collection of the Workspace object, we could omit the collection names and use a shorthand notation like this:

   ? DBEngine(0)(0).Name   

Or we could use this more friendly shortcut:

   ? CurrentDB.Name()   

Although practically synonymous, the CurrentDB() function and DBEngine(0)(0) are subtly different. CurrentDB() creates a new instance of the current database and returns a reference to it, whereas DBEngine(0)(0) returns a reference to the current instance of the open database. For subtle reasons, which we will not go into here, you should use CurrentDB() rather than DBEngine(0)(0) wherever possible. If you want to know more, take a look at Microsoft Knowledge Base article Q131881 http://support.microsoft.com/support/kb/articles/q131/8/81.asp).

To inspect the properties of the currently open database, we simply examine the properties of the object returned by CurrentDB() . For example:

   ? CurrentDB.Name   

will return the file name and location of the currently open database, and

   ? CurrentDB.Version   

will return the version of JET with which the current database was created. So, if we are in a database which was created with Access 2000 or Access 2002, the value of CurrentDB.Version would be 4.0 , because Access 2002 uses JET 4.0. However, if we were to use Access 2002 to open (not convert) a database created in Access 97, the value of CurrentDB.Version would be 3.0 instead, indicating that the database was created by JET 3.0.

So, what collections does the Database object contain? Well, if we look at the diagram, we can see that there are five of them.

This collection...	...contains these objects
Containers	One Container object for every collection of Documents
QueryDefs	One QueryDef object for every saved query that exists within the database
Recordsets	One Recordset object for every recordset that is currently open in the database
Relations	One Relation object for every relationship defined between tables in the database
TableDefs	One TableDef object for every table (including system tables, but excluding linked tables) which exists within the database

We'll take a brief take look at the TableDefs and QueryDefs collections, just to make sure that we understand what they represent. Then we will focus on one collection in particular - the Recordsets collection - in a fair amount of detail. We shall not look at the Relations collection, as it is used so infrequently, and we shall leave our examination of the Containers collection to Chapter 17 when we discuss multi-user security.

The TableDefs Collection

First of all, let us use the TableDefs collection to determine the number of tables in our database. Going back to the Immediate window in the IceCream.mdb database, if we type the following and hit the Enter key:

   ? CurrentDB.TableDefs.Count   

we should see that the current database has 11 tables in it. "Hold on!", you might say, as you switch to the Database window, "There are only six tables there!"

There certainly are six visible user-created tables, but the TableDefs collection also includes any hidden tables and system tables. If we view these, by checking the Hidden Objects and System Objects checkboxes on the View tab of the Options dialog, we will see that there are indeed 11 tables in the database:

The QueryDefs Collection

If we examine the QueryDefs collection, then we would expect to see that the number of objects in the collection is equal to the number of queries in the database. So let's try it:

   ? CurrentDB.QueryDefs.Count   

Now this is even more perplexing. If you evaluate this line of code in the Immediate window in IceCream.mdb , you will find that there are a great number of them! But if you go to the Query pane of the Database window, you will see that there are no saved queries. So where did these QueryDef objects come from?

The answer is that there are occasions when queries are stored in an Access database even though they may not be visible in the Database window. For example, if you create a form which contains a combo box, and then use a SQL statement as the Row Source t o populate that combo box, Access will compile the SQL and save the resulting query internally as a QueryDef . So the QueryDefs collection contains not just the queries that you explicitly created as queries and saved, but also any internal queries that exist within forms and reports.

The Recordsets Collection

The third collection contained within the Database object is the Recordsets collection. Recordsets are fundamental to data access through VBA and are something that you will really need to get to grips with. But don't worry; it's really not that tough! Now even if you don't know what a recordset is, you will almost certainly have used one already. Put simply, a Recordset is just what its name suggests - a set of records. When you open a table in Datasheet view, you are looking at a set of records. When you open a form, it will normally have a set of records behind it that supply the data for the form. Relational databases are all about sets of records (as opposed to flat-file databases which tend to deal with records on an individual basis) and you will find that the Recordset object will probably become the single most used of all of the Data Access Objects that you will come across in VBA. So, we will spend some time now looking in detail at the different types of Recordset and how we can use them in code.

Try It Out-Recordsets

1. Open up IceCream.mdb, create a new standard module and call it Chapter 6 Code . In the new module, add a subprocedure called OpeningARecordset .

2. Add the following code to the subprocedure:

      Public Sub OpeningARecordset()     Dim db As Database     Dim rec As Recordset     Dim intRecords As Integer     Set db = CurrentDb()     Set rec = db.OpenRecordset("tblIceCream")     intRecords = rec.RecordCount     MsgBox "There are " & intRecords & " records in the tblIceCream table"     rec.Close     End Sub   

3. Now run the procedure. Remember, there are four ways to do this: you can hit F5 , select Run Sub/User Form from the Run menu, hit the Run Sub/User Form button on the toolbar or type OpeningARecordset in the Immediate window and hit the Return key. You will get a message box telling you how many records there are in the table tblIceCream :

   

   If you get a Compile Error when trying to run this code, go to References in the Tools menu of the VBE, and make sure Microsoft DAO 3.6 Object Library is checked, and not Microsoft ActiveX Data Objects 2.1 Library. Note that depending on what software or Office service packs you may have installed on your computer, there may be newer versions of ADO libraries listed. Make sure that none of them are checked.

You can check that this is correct by opening the table in Datasheet view and having a look.

How It Works

You should be getting a feel for the VBA code by now, so we won't explain every line that we write. Instead, we'll concentrate on the new or interesting parts .

In this example, we fill a variable with the data from the table tblIceCream ,

 Set rec = db.OpenRecordset("tblIceCream") 

and then use the MsgBox function to display the count of the records in the Recordset :

 intRecords = rec.RecordCount MsgBox "There are " & intRecords & " records in the tblIceCream table" 

Notice also that we close the Recordset at the end of the procedure:

 rec.Close 

Once a Recordset has been closed, you can't do anything else with it. This allows VBA to free any resources associated with the Recordset and is particularly necessary in a multi-user environment or when you are dealing with linked tables. (These are tables that are stored in other currently open Access or non-Access databases, but which are linked and so can be manipulated as such if they were in the current Access database).

As we mentioned above, a Recordset is just that - a set of records. While a Recordset is open in your code (after it has been filled with records with the OpenRecordset method and before it is closed with the Close method), you can do what you like with the records in that Recordset - edit them, delete them, or even add new records.

Different Types of Recordset

In VBA there are five different types of Recordset object that you can use. Which one you use depends on a combination of factors, such as:

• How many tables the underlying data comes from

• Whether you want to update the records or just view them

• Whether the tables are in Access or some other type of database

• How many records there are in the recordset

We'll look in detail at when to use each of the five types of Recordset a little later, but first let's have a look at what they are. The five types of Recordset object are:

• Table - type Recordset objects

• Dynaset - type Recordset objects (normally just called dynasets)

• Snapshot - type Recordset objects (or just snapshots)

• Forward-only - type Recordset objects

• Dynamic - type Recordset objects

Dynamic Recordset objects aren't actually part of JET and are instead part of ODBCDirect technology. They are used for accessing data in remote ODBC databases, rather than data in Access databases, so we need not concern ourselves with them here. Instead, we will concentrate on the four JET recordsets: table-type, dynaset-type, snapshot-type and forward-only-type recordsets.

We'll look at the differences between these four types in just a moment.

You open all four different types of Recordset object in the same way - using the OpenRecordset method against a Database object. Have another look at the portion of code that we used just now:

 Dim db As Database Dim rec As Recordset Set db = CurrentDb() Set rec = db.OpenRecordset("tblIceCream") 

First we create a Database object that corresponds to the database we are currently in. Then we create a Recordset object within the current database and fill it with records from the table tblIceCream .

By default, this statement creates a table-type Recordset object because it is based on a single table. If we had wanted to be more explicit, we could have used the intrinsic constant, dbOpenTable , as a parameter to the OpenRecordset method just to make sure that the Recordset object would be a table-type Recordset object:

   Set rec = db.OpenRecordset("tblIceCream", dbOpenTable)   

If we had wanted a dynaset-type Recordset object instead, we would have used the dbOpenDynaset constant instead:

   Set rec = db.OpenRecordset("tblIceCream ", dbOpenDynaset)   

And, not surprisingly, if we had wanted a snapshot-type Recordset object or a forward-only-type Recordset object, we would have created them like this:

   Set rec = db.OpenRecordset("tblIceCream ", dbOpenSnapshot)     Set rec = db.OpenRecordset("tblIceCream", dbOpenForwardOnly)   

We can see from the above statements, then, that there are three things we need to think about when we are creating a Recordset object:

• Which database are the records in?

• Whereabouts in that database are those records?

• What type of Recordset object do we want?

There are, in fact, further optional levels of control we can apply when we open Recordset objects, but we'll look at those a little later in Chapter 17 when we consider multi-user issues.

Access allows you to create a Recordset object in a single line of code, without having to create a Database object. So you could say:

Set rec = CurrentDB.OpenRecordset("tblIceCream", dbOpenDynaset)

However, the method we have used throughout this chapter, using an intermediate Database object, is usually preferable, and is more efficient if you need to refer to the same database somewhere else in your procedure.

Now we know how to create Recordset objects, which type should we use? Let's look at the different types in turn , and see which situations they are best suited to.

Table-type Recordset Objects

This is the default type for any Recordset objects where the records come from a single local or attached Access tables. In other words, if we try to create a Recordset object to retrieve records from a single Access table and we do not specify the type of Recordset object we want to open, Access will create a table-type Recordset object:

   Set rec = db.OpenRecordset("tblIceCream")   

Table-type Recordset objects are updateable (which means that we can make changes to the records in the Recordset and the changes will be reflected in the underlying table) objects. Another great advantage of using a table-type Recordset object is that you can use indexes on the table to speed up the process of searching for specific records. By contrast, you cannot use indexes against the other Recordset objects. In fact, we look at using indexes to locate records in a table-type Recordset later on in this chapter.

Dynaset-type Recordset Objects

A dynaset-type Recordset object can be based on either a local or attached table, or it can be based on the result of a query. There are two key features of dynaset-type Recordset objects:

• You can edit a dynaset and the results will be reflected in the underlying tables.

• While a dynaset is open, Access will update the records in your dynaset to reflect the changes that other people are making in the underlying tables.

To understand better the way that dynasets operate , it is probably helpful to see how they are created. Whenever a dynaset-type Recordset object is created, Access starts to build a copy of the key values from the result. That is, it copies the field or group of fields that uniquely identifies each of the records in the result. The copy that it creates is, sensibly enough, called a keyset, because it is the set of key values. Then, whenever you want to view the records in the Recordset object, Access fetches the latest version of the non-key fields from the database based on the key values it has stored. Because of this behavior, you might sometimes see dynasets referred to as keyset-driven cursors . We will look at some of the implications of this behavior a little later.

Note that the keyset is not fully complete until the last record (key value) in the table has been accessed.

You should use dynaset-type Recordset objects if:

• You will need to update the records in the Recordset object.

• You want to see updates other users are making to those records.

• The Recordset object is very large.

• The Recordset object contains OLE objects such as bitmaps or Word documents.

Snapshot-type Recordset Objects

In contrast, snapshot-type Recordset objects are not updateable and do not reflect the changes that other users make to the records. In fact, just as the name suggests, you are taking a 'snapshot' of the data at a certain point in time. Whereas Access creates a copy of just the key values to create a dynaset, it takes a copy of the entire set of results to form a snapshot.

One of the advantages of snapshots is that with modestly sized Recordset objects, snapshots are generally faster to create than dynasets. This is because the entire structure sits in memory without references back to the data source. You would use a snapshot-type Recordset object in a situation where you don't wish to update the data and when the recordset won't contain too many records - let's say no more than about 500 records.

The terms dynaset and snapshot were introduced in early versions of Access, but Microsoft suggests that they should both be referred to just as recordsets. Throughout this chapter, if we mention dynasets and snapshots, we will be referring to dynaset-type Recordset objects and snapshot-type Recordset objects respectively.

Forward-Only-type Recordset Objects

Conceptually, forward-only-type Recordset objects are very similar to snapshots. They are read-only, do not reflect other users' changes, and are created by taking a copy of the entire qualifying set of results. Where they differ from snapshots is that they only allow you to move through them in one direction. In other words, with a forward-only recordset, you can read the records from it one after the other, but you can't then move back to previous records. Forward-only recordsets are sometimes referred to as firehose cursors.

What makes forward-only recordsets so attractive is the fact that they are very fast for moderately sized sets of results. So, if you are concerned about performance, if you can put up with the limited functionality then forward-only recordsets are a good choice.

Building Recordsets Dynamically

We have already seen how Recordset objects can be created with the OpenRecordset method. The examples that we have looked at so far have all involved creating Recordset objects directly from tables. However, you can also create a Recordset object from a saved query or from a SQL SELECT statement. To do this, you simply substitute the query's name or the SQL SELECT statement for the table name. For example, if you had a query called qryTotalOrders in your database, you could create a dynaset-type Recordset object that contained the records from the query like this:

   Set db = CurrentDb     Set rec = db.OpenRecordset("qryTotalOrders", dbOpenDynaset)   

or by entering a SQL SELECT statement directly:

   Set db = CurrentDb     Set rec = db.OpenRecordset("SELECT * FROM Order", dbOpenDynaset)   

Using saved queries (as in the first of these two examples) will typically give slightly better performance, as the query will already be compiled and so JET will not have to go through the process of compiling the query before it is run. However, the second technique affords more flexibility and is often the only way out if you want to build up a query's definition dynamically in code (for example, in response to a user's selections on a form). There is an extended example of just this technique in the next chapter.

Default Types

If you do not specify the type of Recordset object that you want to open, Access will choose what is normally the best-performing type of Recordset object available:

• If the Recordset object is based on a single named table in the current database, Access will return a table-type Recordset object.

• If the Recordset object is based on a query or a SQL SELECT statement (or if it's from a table in a non-Access database), and if the underlying query or table can be updated, Access will return a dynaset-type Recordset object.

• In all other situations, Access will return a snapshot-type Recordset object.

However, there may be situations where you want to return a recordset of a different type than the one Access would normally return. For example, dynaset-type recordsets are generally quicker to open than snapshots, if the recordset will contain more than a few hundred records. So in a situation where Access would otherwise have created the recordset as a snapshot, you might want to explicitly create a dynaset instead.

Requerying Data in Recordsets

If you want to make sure that the data in your Recordset is up-to-date, you can refresh it by executing the Requery method of the Recordset object:

   rec.Requery   

This re-executes the query that the Recordset object is based on, thus ensuring that the data is up to date. You can only do this, however, if the Recordset object supports requerying. In order to determine whether this is so, you should inspect the Recordset object's Restartable property. If the Recordset object's Restartable property is True , you can use the Requery method. You could test for this as follows :

   If rec.Restartable = True Then rec.Requery   

However, if the Recordset object's Restartable property is False , attempting to requery the recordset will generate an error. Table-type recordsets always have a Restartable property of False and so can never be requeried.