Hello DAO

“Hello World” is the first code example in the now-classic book, The C Programming Language, by Brian Kernighan and Dennis Ritchie. The following examples show you how to perform a simple task with DAO using Hello World. But instead of printing a string variable to the screen, our version searches for a value in a table and retrieves a message for display.

The NorthwindTables database includes a table with one text field, Greeting, that contains different greetings. The following table shows the data that the Greeting field contains.

The following code shows one way to retrieve the greeting. In this example, strDbPath is the path to the NorthwindTables database, and strGreeting is the string for which you are searching:

Dim dbs As Database
Dim rst As Recordset

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Greetings", dbOpenTable)

With rst
	.Index = "GreetingIndex"
	.Seek "=", strGreeting
	If Not .NoMatch Then
		MsgBox !Greeting
	Else
		MsgBox "Could not find " & strGreeting & "."
	End If
End With
Set rst = Nothing
Set dbs = Nothing

The following explains what’s happening when this code runs:

1. First, two object variables named dbs and rst are declared. These are used to point to the actual Database and Recordset objects used in the example.
   
   
2. The Set statement and the OpenDatabase method return a reference to the NorthwindTables database, represented by the variable strDbPath, and assign the variable dbs to the database. If you install the sample databases to the default folder, then the value of strDbPath is "C:\JetBook\Samples\NorthwindTables.mdb". If you open NorthwindTables.mdb in Microsoft Access and paste this code into a module, you can set the Database object variable by using the CurrentDb function.
   
   
3. The Set statement is used again, this time to assign the rst object variable to the table named Greetings. This is done by using the OpenRecordset method of the Database object. This method tells DAO to look in the NorthwindTables database (NorthwindTables.mdb) for the Greetings table.
   
   
4. Next, the code tells DAO which index to use when searching the table. This is done by setting the Index property of the Recordset object to the value "GreetingIndex". This index exists on the Greeting field in the Greetings table.
   
   
5. Then the Recordset object’s Seek method is called to find the record where the value of the indexed field is equal to value of strGreeting. You can experiment with searching for other values in the table by using the Seek method to look for other values in the table.
   
   
6. Next, the NoMatch property of the Recordset object is checked to see if a match was found. If a match was found, the value of the Greeting field is displayed. If not, an error message is displayed.
   
   
7. Finally, the Set statement is used at the end of the procedure to set the value of the Database and Recordset object variables to Nothing, freeing up any memory that they may have used while they existed.

The end result is a program that displays one of the greetings (such as “Hello World!”) stored in the Greetings table of the NorthwindTables database. The key concepts in this example are:

• DAO is a hierarchy of objects. The Greetings TableDef object is in the TableDefs collection of the Database object, and the GreetingIndex Index object is in the Indexes collection of the Greetings TableDef object.
  
  
• Methods initiate actions on objects. This example uses methods to open a database and a table, and to find a record.
  
  
• Properties define characteristics of objects. In this example, the value of the Index property of the Recordset object is set to tell DAO how to order the records that you are going to search.

As you progress through the rest of this chapter, you’ll see how these elements work together to form a cohesive DAO object model that provides flexible access to the objects and data in your database.