To create a reference to the object library you need, follow these steps:

You can't select both libraries at the same time. And if you don't include the correct library, you'll get a compile error when you try to execute one of the objects in that library (such as a DAO.Recordset). The message will refer to this as a "user-defined" object because it can't find the object in the currently referenced libraries—so the editor thinks it's a new object introduced by you, the programmer, but that you forgot to declare it.

It's possible to establish connections to databases in a variety of ways, but in this chapter we'll use a simple, direct line of code. In Chapter 28, "Understanding the Access Object Model and Key Objects," you saw what steps to take to go online and obtain the Northwind.accdb sample database and where to store it on your hard drive so you could experiment with the example code in these final chapters of the book. If you haven't already taken those steps, see the sidebar in Chapter 28 titled "Practical Database Exploration Tips."

To open a connection to the Northwind sample database, you can use this code if you're employing DAO:

Dim myDatabase As DAO.Database
Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")

You'll see this approach used in examples later in this chapter. You'll also see how to manipulate Northwind while it's loaded into Access.

To open a recordset using ADO, you use the Open method of the RecordSet object. The syntax for the Open method is as follows:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

Here are the components of the syntax:

You'll see examples of opening a recordset a little later in this chapter. First, you must decide how to access the data in the recordset. The easiest methods are to use an existing table or an SQL SELECT statement.

How you actually get to the data in the recordset you open depends on whether you want all the data in a table or just part of it. If you want all the data in a table, you can use a table to access the data. If you want to return only particular records, you can use an SQL SELECT statement.

Using a Table to Access the Data in an ADO Recordset

To open in a recordset a whole table from a database, specify the table name as the Source argument in the Open statement. The following example declares a RecordSet object variable, uses a Set statement to assign the appropriate recordset type to it, uses the ActiveConnection method to connect to the currently active database (whatever you have currently loaded into Access at the time), and then uses the Open method to open the entire Customers table. We'll use the Northwind sample database (that you installed in Chapter 28) which has a Customers table.

This example demonstrates how to bring into an ADO recordset the data from an entire table and then move around within this recordset. Your code will not need to instantiate a database object but instead will work with the database that's currently loaded into Access. (This is a very simple example to illustrate some basic concepts. Normally when accessing a database, you'll want to employ an SQL statement and check for recordset boundary conditions—using BOF and EOF properties. SQL and BOF/EOF are described later in this chapter. For now, just consider the following example code an illustration of elementary principles, to which you'll add sophistication using the code examples later in this chapter.)

As always, it's necessary for you to first ensure that the ADO library is referenced. So in the VBA Editor, choose Tools

1.  Sub ExploreRecordset()
2.      Dim myRecordset As ADODB.Recordset
3.      Set myRecordset = New ADODB.Recordset
4.
5.  'point to the currently loaded database
6.      myRecordset.ActiveConnection = CurrentProject.Connection
7.      myRecordset.CursorType = adOpenStatic
8.      myRecordset.Open Source:="Customers"
9.
10. 'Display the First Name from the first row
11.     MsgBox myRecordset("First Name")
12.
13. 'Move to the last row and show the Last Name
14.     myRecordset.MoveLast
15.     MsgBox myRecordset("Last Name")
16.
17. 'Move to the previous row and display the Job Title
18.     myRecordset.MovePrevious
19.     MsgBox myRecordset("Job Title")
20.
21. 'Move back to the first row and display the Phone Number
22.     myRecordset.MoveFirst
23.     MsgBox myRecordset("Business Phone")
24.
25. 'Move to the next row and show the Last Name
26.     myRecordset.MoveNext
27.     MsgBox myRecordset("Last Name")
28.
29.
30.     myRecordset.Close
31.     Set myRecordset = Nothing
32. End Sub

In this code, you first declare a recordset variable, and in line 6 you point it to the database currently loaded in Access. Line 7 defines the cursor type as static, and line 8 loads the data—the entire Customers table—into your recordset.

Line 11 doesn't move anywhere within the recordset, so by merely supplying the recordset's name, MyRecordset, along with one of the table's field names, Last Name, to a MsgBox function, you can display the first record in the table.

Line 14 does move to a different record within the recordset—the last record—before displaying the data in that record's Last Name field. Line 18 moves to the penultimate record, line 22 moves to the first record, and line 26 moves to the second record. Finally, line 30 closes the recordset and line 31 assigns Nothing to the object variable, which has the effect of eliminating it.

What Is a "First Record" in a Table?

It's important for beginners to understand the practical difference between a table of raw data in a database and an organized recordset extracted from that database. The concept of "first record" within a relational database is essentially meaningless until you use an SQL statement to organize (sort or group) the records in some fashion.

Records in a relational database (the type of database Access employs) are not necessarily organized. For example they are not necessarily alphabetized by any particular field (such as LastName) or numerically listed by an ID number, or organized using some other scheme. True, data is stored in tables, and a table does have structure: its fields separate the data into logical categories such as LastName, Address, CellPhone, and so on. But its records (rows) are not necessarily maintained in any particular order—such as grouping all the customers by zip code or alphabetizing all customers by LastName.

A set of records (a recordset) is extracted from a database when you execute an SQL statement. This statement allows you to specify how you want the records organized (grouped by city, alphabetized, or whatever). SQL is flexible: You can organize records in many ways when you extract a recordset from a database. You can sort records by any of their fields; you can also sort in either ascending (the default) or descending order (specify DESC for descending). Which record is first also depends on which field you sort the recordset by, as specified in the ORDER BY statement.

In the example in the preceding section, "Using a Table to Access the Data in an ADO Recordset," the records are moved into the recordset unsorted. As each action is carried out in this code—moving forward and backward through the recordset—message boxes display the records in their unsorted order. However, if you want to organize the records in alphabetical order by each customer's last name, add an ORDER BY keyword to your SQL statement, like this:

myRecordset.Open "Select * from Customers
ORDER BY 'Last Name'
"

Just remember that you can get a recordset without using an SQL statement, like this:

myRecordset.Open Source:="Customers"

But the concept of "first record" in this recordset probably will have no meaning.

However, you can get a recordset by using an SQL statement, like this:

myRecordset.Open strSQL

In this case, the "first record" will have meaning to you—based on the criteria you specified in the SQL statement. The following section, "Using an SQL SELECT Statement to Access a Subset of the Data in an ADO Recordset," explains how to use an SQL statement.

SELECT [DISTINCT] fields FROM table WHERE criteria ORDER BY fields [DESC]

Sub SubSet()

    Dim strSQL As String

    Dim myRecordset As ADODB.Recordset
    Set myRecordset = New ADODB.Recordset
    myRecordset.ActiveConnection = CurrentProject.Connection

strSQL = "Select * FROM Customers WHERE ID > 17"
    myRecordset.Open strSQL

    Do Until myRecordset.EOF
        Debug.Print myRecordset("Last Name")
        myRecordset.MoveNext
    Loop

End Sub

When working with DAO, you use a different approach than the ADO techniques explored so far in this chapter. You use the OpenRecordset method of the Database object to create a new recordset and add it to the Recordsets collection.

The syntax for the OpenRecordset method is as follows:

Set recordset = object.OpenRecordset (Name, Type, Options, LockEdit)

Here are the components of the syntax:

Sub DAOTest()
Dim myRecordset As DAO.Recordset
Dim myDatabase As DAO.Database

   'Open the copy of Northwind on the hard drive
   Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")

   'Create the DAO-style Recordset

Set myRecordset = myDatabase.OpenRecordset(Name:="Customers", _
    Type:=dbOpenTable)

   MsgBox myRecordset("ID")
   MsgBox myRecordset("Company")
   MsgBox myRecordset("Address")
   MsgBox myRecordset("City")

   Set myRecordset = Nothing
End Sub

Sub DAOSelect()

Dim myDatabase As DAO.Database
   Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")

Dim myRecordset As DAO.Recordset
   Set myRecordset = myDatabase.OpenRecordset _
    (Name:="SELECT * FROM Customers WHERE City ='Boston'", _
    Type:=dbOpenDynaset)

Do Until myRecordset.EOF
        Debug.Print myRecordset("Last Name")
        myRecordset.MoveNext
    Loop

   Set myRecordset = Nothing
End Sub

The MoveFirst method and MoveLast method are always safe to use because as long as the recordset contains one or more records, there's always a first record and a last record. (If the recordset contains only one record, that record is both the first record and the last record.) But if you use the MovePrevious method from the first record in the recordset or the MoveNext method from the last record, you move beyond the recordset, accessing what is sometimes called a "phantom record"—one that isn't there. When you try to access the contents of the record, VBA gives the runtime error 3021 ("No current record"). Figure 29.1 shows this error.

Figure 29.1. The runtime error "No current record" usually means that you've moved outside the recordset by using the MoveNext method from the last record or the MovePrevious record from the first record in the recordset.

BOF means the beginning of the file, and EOF means the end of the file. To check whether you're at the beginning or end of the recordset, use the BOF property or the EOF property of the RecordSet object. The BOF property returns True when the current record is at the beginning of the file, and the EOF property returns True when the current record is at the end of the file. To avoid errors, check after using the MovePrevious method whether the beginning of the file has been reached, as in this example:

With myRecordset
    .MovePrevious
    If .BOF = True Then .MoveNext
End With

Similarly, check after using the MoveNext method whether the end of the file has been reached:

myRecordset.MoveNext
If myRecordset.EOF Then myRecordset.MovePrevious

To move by several records at once, but not to the first record or last record in the recordset, use the Move method. The syntax for ADO differs from that used with DAO.

Here's the syntax for the Move method with ADO:

recordset.Move NumRecords, Start

The syntax for the Move method with DAO is as follows:

recordset.Move Rows, StartBookmark

Here, recordset is the recordset involved, NumRecords or Rows is the number of records by which to move (use a positive number to move forward or a negative number to move back), and Start or StartBookmark is an optional argument that you can use to specify a bookmark from which you want to start the movement. If you omit Start or StartBookmark, movement starts from the current record.

For example, the following statement moves 10 records forward from the current record in an ADO recordset:

myRecordset.Move NumRecords:=10

The following statement moves 5 records backward from the current record in a DAO recordset:

myRecordset.Move Rows:=-5

To create a bookmark, move to the record that you want to mark, and then use the Bookmark property of the RecordSet object. The following example declares a Variant variable named myBookmark and then assigns to it a bookmark representing the current record in an ADO recordset:

Dim myBookmark As Variant
myBookmark = myRecordset.Bookmark

After setting a bookmark, you can use it as the starting point of a move. For example, the following statement moves to the eighth record after the bookmark myBookmark in an ADO recordset:

myRecordset.Move NumRecords:=8, Start:=myBookmark

To search for a record in an ADO recordset, you can use the Find method of the RecordSet object. The syntax is as follows:

recordset.Find Criteria, SkipRows, SearchDirection, Start

Here are the components of the syntax:

When you run the search, it stops at the first matching record. If no record matches and you're searching forward, it stops at the end of the recordset; if you're searching backward, it stops at the beginning of the recordset. If the end or beginning of the recordset is reached, you know that there was no match for the search.

The following example begins by moving to the first record in the recordset (represented by the object variable myRecordset) and then searches for the first record that matches the criterion "City = 'Denver'". The example checks the EOF property to ensure that the end of the recordset has not been reached. If it has not, the example displays a message box with the last name data for the record matching Denver. However, if the end of the recordset has been reached, the example displays a message box stating that no match was found:

Sub SearchADO()

    Dim strSQL As String

    Dim myRecordset As ADODB.Recordset
    Set myRecordset = New ADODB.Recordset
    myRecordset.ActiveConnection = CurrentProject.Connection

    myRecordset.Open Source:="Select * from Customers", _
        Options:=adCmdText

    With myRecordset
    .MoveFirst
    .Find Criteria:="City='Denver'"

    If Not .EOF Then
        MsgBox .Fields("Last Name")
    Else
        MsgBox "No matching record was found."
    End If

End With

End Sub

To continue your search for the same criteria, you can use the SkipRows argument to specify an offset so that you don't simply find the current record again. For example, you'll likely want to move ahead just one row, like this:

myRecordset.Find Criteria="City='Denver'", SkipRows:=1

To search for a record in a DAO recordset, you can use one of these four methods:

The syntax for these four methods is as follows:

recordset.FindFirst Criteria
recordset.FindNext Criteria
recordset.FindPrevious Criteria
recordset.FindLast Criteria

Here, recordset is a required object variable that represents the RecordSet object involved. Criteria is a required String argument that specifies the criteria for the search. Criteria works in the same way as the WHERE clause in an SQL statement, except that it does not use the word WHERE.

The following example uses the FindFirst method to search from the beginning of the recordset for the first record that matches the criterion City = 'Las Vegas':

Sub DAOSearch()

Dim myDatabase As DAO.Database
Set myDatabase = DBEngine.OpenDatabase("C:	empNorthwind.accdb")
Dim myRecordset As DAO.Recordset
Set myRecordset = myDatabase.OpenRecordset _
    (Name:="SELECT * FROM Customers", _
    Type:=dbOpenDynaset)

myRecordset.FindFirst "City = 'Las Vegas'"

MsgBox myRecordset("Last Name")

   Set myRecordset = Nothing
End Sub

When you start a search in a DAO recordset using one of the four Find methods, the NoMatch property of the RecordSet object is set to True. If the method finds a match, the NoMatch property is set to False. So you can test the NoMatch property to tell whether or not the search found a match, as in this example:

If myRecordset.NoMatch = False Then
   MsgBox myRecordset("Last Name")
End If