The Connection object defines a session for a user for a data source. Although the ADO object model is not considered to be hierarchical, the Connection object is considered the highest-level ADO object. After you have established a Connection object, you can use it with multiple recordsets. This improves performance and greatly simplifies your programming code.

A Connection object must be declared before it is used. The declaration looks like this:

Dim cnn as ADODB.Connection

After the Connection object has been declared, a new Connection object must be instantiated. The code looks like this:

Set cnn = New ADODB.Connection

The Connection must then be opened. The Open method of the Connection object receives a connection string, and optionally a user ID, password, and options as a parameter. The following is an example of the simplest use of the Open method:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "User ID=Admin;" & _
    "Data Source=" & CurrentProject.Path & _
        "Chap14Ex.mdb;"

The Connection string contains three pieces of information:

Table 14.1 lists the most commonly used Connection string properties used by the Jet OLEDB provider.

The complete routine required to establish a connection appears in Listing 14.1.

Sub CreateConnection()
    'Declare and instantiate the connection
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    'Open the connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "User ID=Admin;" & _
    "Data Source=" & CurrentProject.Path & _
        "Chap14Ex.mdb;"

    'Close the connection
    cnn.Close

    'Destroy the connection object
    Set cnn = Nothing
End Sub

Dim rst as New ADODB.Recordset

Note

Listing 14.1 and most code in this chapter is located in the CHAP14EX.MDB file included with this book’s CD-ROM.

A Recordset object is used to look at records as a group. A Recordset object refers to the set of rows returned from a request for data. As with a Connection object, to use a Recordset object, you must first declare it. The code looks like this:

Dim rst as ADODB.Recordset

After the Recordset object has declared, it must be instantiated. The code looks like this:

Set rst = New ADODB.Recordset

As with a Connection object, the Open method is used to point the Recordset object at a set of records. The code looks like this:

rst.Open "Select * From tblClients", CurrentProject.Connection

The first parameter of the Open method is the source of the data. The source can be a table name, a SQL statement, a stored procedure name, a Command object variable name, or the filename of a persisted recordset. In the example, the source is a SQL Select statement.

The second parameter of the Open method must be either a valid connection string, or the name of a Connection object. In the example, the Connection property of the CurrentProject object returns a reference to a copy of the connection associated with the current project. The reference supplies the connection for the Recordset object. The completed code appears in Listing 14.2.

Sub CreateRecordset1()
    'Declare and instantiate the recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Open the recordset
    rst.Open "Select * From tblClients", CurrentProject.Connection

    'Print its contents
    Debug.Print rst.GetString

    'Close and destroy the recordset
    rst.Close
    Set rst = Nothing
End Sub

Notice that after the recordset is opened, the result of the GetString method of the Recordset object is printed to the Immediate window. The GetString method of the Recordset object builds a string based on the data contained in the recordset. For now, this is a simple way of verifying that your code works as expected. Also note that the Close method of the Recordset object is used to close the recordset. The Close method, when applied to either a Connection object, or to a Recordset object, has the effect of freeing the associated system resources. The Close method does not eliminate the object from memory. Setting the Recordset object equal to Nothing eliminates the object from memory.

Although this syntax works quite well, I prefer to set the parameters of the Open method as properties of the Recordset object, before the Open method is issued. You will see that this makes your code much more readable as you add parameters to the Open method. The code appears in Listing 14.3.

Sub CreateRecordset2()
    'Declare and instantiate the recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the connection of the recordset to the connection
    'associated with the current project
    rst.ActiveConnection = CurrentProject.Connection
    'Open the recordset and print its contents
    rst.Open "Select * From tblClients"
    Debug.Print rst.GetString

    'Close and destroy the recordset object
    rst.Close
    Set rst = Nothing
End Sub

Finally, a Connection object, rather than a copy of the Connection object associated with the CurrentProject object, can be used to provide a connection for the recordset. In fact, the same Connection object can be used for multiple recordsets. The code appears in Listing 14.4.

Sub CreateRecordset3()
    'Declare and instantiate one connection object
    'and two recordset objects
    Dim cnn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset

    Set cnn = New ADODB.Connection
    Set rst1 = New ADODB.Recordset
    Set rst2 = New ADODB.Recordset

    'Point the Connection object
    'to the Connection associated with the CurrentProject object
    Set cnn = CurrentProject.Connection

    'Utilize the connection just opened as the connection for
    'two different recordsets
    rst1.ActiveConnection = cnn
    rst1.Open "Select * From tblClients"
    rst2.ActiveConnection = cnn
    rst2.Open "Select * From tblPayments"

    'Retrieve data out of the recordsets
    Debug.Print rst1.GetString
    Debug.Print rst2.GetString

    'Close the recordsets and the connection and destroy the objects
    rst1.Close
    rst2.Close
    cnn.Close

    Set rst1 = Nothing
    Set rst2 = Nothing
    Set cnn = Nothing

End Sub

Notice that both rst1 and rst2 use the same Connection object.

The ADO Command object represents a query, SQL statement, or stored procedure that is executed against a data source. Although not always necessary, a Command object is particularly useful when executing parameterized queries and stored procedures. Just as with the Connection object and the Recordset object, the Command object must be declared before it is used:

Dim cmd as ADODB.Command

Next, the Command object must be instantiated:

Set cmd = New ADODB.Command

After the Command object has been instantiated, you must set its ActiveConnection property and its CommandText property. As with a Recordset object, the ActiveConnection property can be either a connection string or a reference to a Connection object. The CommandText property is the SQL statement or stored procedure used by the Command object. The ActiveConnection and the CommandText properties look like this:

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "tblClients"

The completed code appears in Listing 14.5.

Sub CommandObject()
    'Declare a recordset and a command object
    Dim rst As ADODB.Recordset
    Dim cmd As ADODB.Command
    'Instantiate the command object
    Set cmd = New ADODB.Command

    'Designate where the data comes from
    cmd.CommandText = "Select * from tblClients"

    'Establish the connection information
    cmd.ActiveConnection = CurrentProject.Connection

    'Use the execute method to return a result set
    'into the recordset object
    Set rst = cmd.Execute

    'Display the resulting data
    Debug.Print rst.GetString

    'Close the recordset and destroy the objects
    rst.Close

    Set cmd = Nothing
End Sub

In the example, the Command object is instantiated. The CommandText property is set to a SQL Select statement, and the ActiveConnection property is pointed to the connection associated with the current database. The Execute method of the Command object is used to return the results of the SQL statement into the Recordset object.

By default, when you open a recordset, the CursorType parameter is set to adOpenForwardOnly. This means that you can only move forward through the records in the recordset. You will not see any adds, edits, or deletions that other users make. Furthermore, many properties and methods, such as the RecordCount property and the MovePrevious method, are unavailable. Listing 14.6 illustrates this.

Sub ForwardOnlyRecordset()

    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish a connection and open a forward-only recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from tblClients"

    'Attempt to retrieve the recordcount
    Debug.Print rst.RecordCount

    'Close and destroy the recordset
    rst.Close
    Set rst = Nothing


End Sub

The value -1 displays in the Immediate window because the RecordCount property is not supported with a forward-only recordset. Because you did not explicitly designate the cursor type, a forward-only recordset was created.

Three other values are available for the CursorType. They are adOpenStatic, adOpenKeyset, and adOpenDynamic. The adOpenStatic option allows forward and backward movement through the records in the recordset, but changes that other users make to the underlying data are not seen by the recordset. The adOpenKeyset option offers everything offered by the adOpenStatic option, but in addition, edits that other users make are seen by the recordset. Finally, with the adOpenDynamic option, adds, edits, and deletions made by other users are seen by the recordset. Table 14.2 illustrates each of these options in further detail.

The CursorType property of the recordset can be set in one of two ways. It can be set as a parameter of the Open method of the Recordset object. Listing 14.7 illustrates this.

Sub StaticRecordset1()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish a connection and open a static recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from tblClients", _
        CursorType:=adOpenStatic

    'Retrieve the recordcount
    Debug.Print rst.RecordCount

    rst.Close
    Set rst = Nothing


End Sub

Notice that, in Listing 14.7, the CursorType appears as a parameter of the Open method. Contrast Listing 14.7 with Listing 14.8.

Sub StaticRecordset2()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType properties
    'of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic

    'Open the recordset
    rst.Open "Select * from tblClients"

    'Retrieve the recordcount
    Debug.Print rst.RecordCount

    rst.Close
    Set rst = Nothing

End Sub

In Listing 14.8, the CursorType is set as a property of the Recordset object, prior to the execution of the Open method. Separating the properties from the Open method improves the readability of the code.

Although the CursorType property of a Recordset object determines how movements can occur within the recordset, and whether other users’ changes are seen, the CursorType in no way affects the updateability of the recordset’s data. In fact, when a recordset is opened, it is opened as read-only by default. It is only by changing the LockType property that you can make the recordset updateable.

The options for lock type are adLockReadOnly, adLockPessimistic, adLockOptimistic, and adLockBatchOptimistic. The default, adLockReadOnly, does not allow changes to the recordset. The other options all provide updateability for the recordset’s data. The difference lies in when the records are locked. With the adLockPessimistic option, locking occurs as soon as the editing process begins. With the adLockOptimistic option, the record is locked when the Update method is issued. Finally, with the adLockBatchOptimistic, you can postpone locking until a batch of records are updated. All these options are discussed in extensive detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

As with the CursorType property, the LockType property can be set as a parameter of the Open method or as a property of the Recordset object. Listing 14.9 shows the configuration of the lock type as a property of the Recordset object.

Sub OptimisticRecordset()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic

    'Open the recordset
    rst.Open "Select * from tblClients"

    'Modify the contents of the city field
    rst("City") = "Westlake Village"
    rst.Update
    Debug.Print rst("City")

    rst.Close
    Set rst = Nothing

End Sub

In Listing 14.9, the LockType property is set to adLockOptimistic. The record is locked when the Update method of the Recordset object is issued.

Collection("Name")
Collection(VariableName)
Collection!Name
Collection(Ordinal)

The Options parameter determines how the provider should evaluate the source argument. The valid choices are illustrated in Table 14.1.

The default for the Options parameter is adCmdUnknown. If you do not explicitly specify the Options parameter, the provider attempts to evaluate it while the code is running. This degrades performance. It is therefore important to specify the parameter. Listing 14.9 illustrates the use of the Options parameter of the Open method.

Sub OptionsParameter()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic

    'Open the recordset, designating that the source
    'is a command
    rst.Open "Select * from tblClients", _
        Options:=adCmdText

    'Modify the contents of the city field
    rst("City") = "Westlake Village"
    rst.Update
    Debug.Print rst("City")

    rst.Close
    Set rst = Nothing

End Sub

In Listing 14.9, the Options parameter is set to adCmdText. This causes the source to be evaluated as a SQL command.

When a recordset is based on data from more than one table, Jet automatically allows you to make changes to the foreign key field. For example, if a recordset is based on data from the Customers table and the Orders table, you are able to make changes to the CustomerID in the Orders table. This is referred to as a consistent update. At times you might want to make changes to the primary key field. This could result in a violation of referential integrity and is therefore referred to as an inconsistent update.

If you’ve established referential integrity and have designated that you want to cascade updates, consistent and inconsistent updates yield the same results. On the other hand, without cascade updates activated, a change to the primary key field causes referential integrity to be violated.

Listing 14.10 shows you how to open a recordset with inconsistent updates.

Sub InconsistentUpdates()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic

    'Open the recordset, designating that the source
    'is a SQL statement based on more than one table
    rst.Properties("Jet OLEDB:Inconsistent") = True
    rst.Open Source:="Select * from tblClients " & _
        "INNER JOIN tblProjects " & _
        "ON tblClients.ClientID = tblProjects.ClientID", _
        Options:=adCmdText

    'Modify the contents of the foreign key field
    rst("tblProjects.ClientID") = 1
    rst.Update
    Debug.Print rst("tblProjects.ClientID")

    rst.Close
    Set rst = Nothing

End Sub

Notice that the Jet OLEDB: Inconsistent property is set prior to the Open method of the recordset. This causes the recordset to be opened so that you can use inconsistent updates if you want.

A cursor refers to the set of rows or row pointers that are returned when you open a recordset. With DAO, the location of the cursor is not an issue. On the other hand, ADO supports two cursor locations. As its name implies, the client manages a client-side cursor. The server manages a server-side cursor.

If you are using Jet, the client machine always manages the cursor because Jet only runs on the client machine. You might think that this means that you should always designate a client-side cursor when working with Jet. Actually, the opposite is true. If you designate a client-side cursor when working with Jet, the data is cached twice on the client machine. When a client-side cursor is specified, the Microsoft Cursor Service for OLE DB requests all the data from the OLEDB provider and then caches it and presents it to the application as a static recordset. For this reason, when working with JET, you should only designate a client-side cursor when you want to take advantage of functionality provided only by a client-side cursor.

Listing 14.11 illustrates how to designate the cursor location.

Sub CursorLocation()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType, and CursorLocation properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.CursorLocation = adUseServer

    'Open the recordset, designating that the source
    'is a SQL statement
    rst.Open Source:="Select * from tblClients ", _
        Options:=adCmdText

    'Modify the contents of the city field
    rst("City") = "New City"
    rst.Update
    Debug.Print rst("City")

    rst.Close
    Set rst = Nothing

End Sub

In the example, a server-side cursor is designated.

Depending on which CursorType, LockType, CursorLocation, and Provider are used to open a recordset, the functionality of the recordset varies. The Supports method of a recordset determines which features a particular recordset supports. It returns a Boolean value designating whether the selected feature is supported. Listing 14.12 provides an example.

Sub SupportsMethod()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType, and CursorLocation properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.CursorLocation = adUseServer

    'Open the recordset, designating that the source
    'is a SQL statement
    rst.Open Source:="Select * from tblClients ", _
        Options:=adCmdText

    'Determine whether the recordset supports certain features
    Debug.Print "Bookmark " & rst.Supports(adBookmark)
    Debug.Print "Update Batch " & rst.Supports(adUpdateBatch)
    Debug.Print "Move Previous " & rst.Supports(adMovePrevious)
    Debug.Print "Seek " & rst.Supports(adSeek)
    rst.Close
    Set rst = Nothing

End Sub

When you have a Recordset object variable set, you probably want to manipulate the data in the recordset. Table 14.3 shows several methods you can use to traverse through the records in a recordset.

Listing 14.13 shows an example of using the record-movement methods on a Recordset object.

Sub RecordsetMovements()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type and open
    'the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblProjects"

    'Print the ProjectID of the first row
    Debug.Print rst("ProjectID")
    'Move to the next row and print the ProjectID
    rst.MoveNext
    Debug.Print rst("ProjectID")

    'Move to the last row and print the ProjectID
    rst.MoveLast
    Debug.Print rst("ProjectID")

    'Move to the previous row and print the ProjectID
    rst.MovePrevious
    Debug.Print rst("ProjectID")

    'Move to the first row and print the ProjectID
    rst.MoveFirst
    Debug.Print rst("ProjectID")

    rst.Close
    Set rst = Nothing
End Sub

This code opens a recordset based on the tblProjects table. When the recordset is open, the ProjectID of the first record is printed to the Immediate window. The MoveNext method of the Recordset object is used to move to the next record in the recordset. The ProjectID of the record is printed. The MoveLast method of the Recordset object is used to move to the last record in the recordset. Once again, the ProjectID is printed. The MovePrevious method moves the record pointer back one record and the ProjectID is printed again. Finally, the MoveFirst method moves the record pointer to the first record and the ProjectID is printed. The recordset is closed, and the Recordset object is destroyed.

Before you begin to traverse through recordsets, you must understand two recordset properties: BOF and EOF. The names of these properties are outdated acronyms that stand for beginning of file and end of file. They determine whether you have reached the limits of your recordset. The BOF property is True when the record pointer is before the first record, and the EOF property is True when the record pointer is after the last record.

You commonly will use the EOF property when moving forward through your recordset with the MoveNext method. This property becomes True when your most recent MoveNext has moved you beyond the bounds of the recordset. Similarly, BOF is most useful when using the MovePrevious method.

You must keep in mind some important characteristics of the BOF and EOF properties:

Listing 14.14 shows an example of using the EOF property to determine the bounds of a recordset.

Sub DetermineLimits()
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type and open
    'the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblProjects"

    'Loop through the recordset, printing the
    'ClientID of each row
    Do Until rst.EOF
        Debug.Print rst("ClientID")
        rst.MoveNext
    Loop

    rst.Close
End Sub

In Listing 14.14, a recordset is opened based on tblProjects. The EOF property is evaluated. As long as the EOF property equals False, the contents of the ClientID field are printed, and the record pointer is advanced to the next record in the recordset.

The RecordCount property returns the number of rows in the recordset. Not all types of recordsets and providers support the RecordCount property. If the RecordCount property is not supported, no error occurs. Instead, the RecordCount is -1. Listing 14.15 provides an example.

Sub CountRecordsBad()
    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and open a
    'forward-only cursor
    rst.ActiveConnection = CurrentProject.Connection
    rst.Open "Select * from tblProjects"

    'Print the RecordCount property
    Debug.Print rst.RecordCount  'Prints -1

    rst.Close
    Set rst = Nothing
End Sub

Because the default CursorType is adOpenForwardOnly, and a forward-only cursor does not support the RecordCount property, -1 prints to the Immediate window. Listing 14.16 rectifies this problem.

Sub CountRecordsGood()
    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type and open
    'the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblProjects"
    'Print the RecordCount property
    Debug.Print rst.RecordCount  'Prints Recordcount

    rst.Close
    Set rst = Nothing

End Sub

Notice that the CursorType is set to adOpenStatic. Because the RecordCount property is supported with static cursors, the correct number of records is printed to the Immediate window.

One of the important uses of the RecordCount property is to determine if a recordset contains any rows. Listing 14.17 illustrates this important use of the RecordCount property.

Sub CheckARecordset()
    'Declare and instantiate the recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type and open
    'the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblEmpty"

    'Call a routine to determine if the recordset contains
    'any records
    If Not AreThereRecords(rst) Then
      MsgBox "Recordset Empty...Unable to Proceed"
    End If

    rst.Close
    Set rst = Nothing
End Sub

Function AreThereRecords(rstAny As ADODB.Recordset) As Boolean
    'Return whether or not there are any rows
   AreThereRecords = rstAny.RecordCount
End Function

The CheckARecordset routine opens a recordset based on a table called tblEmpty, which contains no data. The CheckARecordset routine calls the AreThereRecords function, passing a reference to the recordset. The AreThereRecords function evaluates the RecordCount property of the recordset that it is passed. It returns False if the RecordCount is zero, and True, if the RecordCount is non-zero.

Sometimes it is necessary to sort, filter, or find data within an existing recordset. The Sort property, Filter property, and Find method of the Recordset object allow you to accomplish these tasks. The sections that follow cover these properties and this method.

Sub SortRecordset()
    Dim intCounter As Integer

    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor location and open
    'the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorLocation = adUseClient
    rst.Open "Select * from tblTimeCardHours"

    'Loop through the recordset, printing
    'the contents of the DateWorked field
    Debug.Print "NOT Sorted!!!"
    Do Until rst.EOF
        Debug.Print rst("DateWorked")
        rst.MoveNext
    Loop

    'Sort the recordset and the loop through
    'it, printing the contents of the DateWorked field
    Debug.Print "Now Sorted!!!"
    rst.Sort = "[DateWorked]"
    Do Until rst.EOF
        Debug.Print rst("DateWorked")
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
End Sub

Sub FilterRecordSet()
    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type,
    'and lock type, and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic
    rst.Open "Select * from tblTimeCardhours"

    'Loop through the recordset, printing the contents of
    'the DateWorked field
    Debug.Print "Without Filter"
    Do Until rst.EOF
        Debug.Print rst("DateWorked")
        rst.MoveNext
    Loop

    'Filter the recordset and then loop through it, printing the
    'contents of the DateWorked field
    rst.Filter = "DateWorked >= #1/1/1995# and DateWorked <= #1/5/1995#"
    Debug.Print "With Filter"
    Do Until rst.EOF
        Debug.Print rst("DateWorked")
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
End Sub

Sub FindProject(lngValue As Long)
    Dim strSQL As String

    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type,
    'and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblProjects"

    'Attempt to find a specific project
    strSQL = "[ProjectID] = " & lngValue
    rst.Find strSQL
    'Determine if the specified project was found
    If rst.EOF Then
        MsgBox lngValue & " Not Found"
    Else
        MsgBox lngValue & " Found"
    End If

    rst.Close
    Set rst = Nothing
End Sub

Call basADORecordsets.FindProject(1)

Select * FROM tblClients WHERE ClientID = 1

Select * FROM tblClients WHERE IntroDate = #12/31/2001#

Select * FROM tblClients WHERE City = 'Oak Park'

Sub DelimitString()

    Dim strCompanyName As String
    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Ask for the company to locate
    strCompanyName = InputBox("Please Enter a Company")

    'Set the ActiveConnection and CursorType, and
    'LockType, and CursorLocation properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic
    rst.CursorLocation = adUseServer

    'Open the recordset, designating that the source
    'is a SQL statement
    rst.Open Source:="Select * from tblClients " & _
    "WHERE CompanyName = " & ReplaceApostrophe(strCompanyName), _
        Options:=adCmdText

    'Display a message as to whether the selected company
    'was found
    If rst.EOF Then
        MsgBox strCompanyName & " NOT Found!"
    Else
        MsgBox rst("ClientID")
    End If

    rst.Close
    Set rst = Nothing

End Sub

Public Function ReplaceApostrophe(strCompanyName As String) As String
    'Surround text with apostrophes and replace any
    'apostrophes in the string with two apostrophes
    ReplaceApostrophe = "'" & _
        Replace(strCompanyName, "'", "''") & "'"
End Function

The AbsolutePosition property of the Recordset object sets or returns the ordinal position of the current row in the recordset. Its use is illustrated in Listing 14.22.

Sub FindPosition(lngValue As Long)
    Dim strSQL As String

    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type,
    'and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblProjects"

    'Attempt to find a specific project
    strSQL = "[ProjectID] = " & lngValue
    rst.Find strSQL

    'If record is found, print its position
    If rst.EOF Then
        MsgBox lngValue & " Not Found"
    Else
        Debug.Print rst.AbsolutePosition
    End If

    rst.Close
    Set rst = Nothing

End Sub

In the example, the Find method is used to locate a project with a specific ProjectID. If the project is found, the ordinal position of the record that is located is printed to the Immediate window.

The Bookmark property of a Recordset object returns a variant variable that acts as a unique identifier for that particular record in the recordset. You can use the Bookmark property to save the current position and then quickly and easily return to it at any time. Listing 14.23 illustrates the use of a bookmark.

Sub UseBookMark()
    Dim strSQL As String
    Dim vntPosition As Variant

    'Instantiate and declare a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection and cursor type,
    'and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.Open "Select * from tblProjects"

   'Store bookmark in a variant variable
    vntPosition = rst.Bookmark

    'Perform some operation
    'on the records in the recordset
    Do Until rst.EOF
        Debug.Print rst("ProjectID")
        rst.MoveNext
    Loop

    'Return to the bookmarked record by setting
    'the Bookmark property of the recordset to the
    'value stored in the variant variable
    rst.Bookmark = vntPosition
    Debug.Print rst("ProjectID")

    rst.Close
    Set rst = Nothing


End Sub

In the example, a unique identifier to the current record is stored into a variant variable. The code then loops through the remainder of the records in the recordset. When it is done, the Bookmark property of the Recordset object is set equal to the unique identifier stored in the variant variable.

You will not always know the criteria for a recordset at design time. Fortunately, ADO allows you to supply parameters to the CommandText property of the Command object. Listing 14.24 provides an example.

Sub RunParameterQuery(datStart As Date, datEnd As Date)
    'Declare Command and Recordset objects
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset

    'Instantiate the Command object
    Set cmd = New ADODB.Command

    'Establish the connection, command text,
    'and command type of the Command object
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "Select * from tblTimeCardHours " & _
        "Where DateWorked Between ? and ?"
    cmd.CommandType = adCmdText

    'Use the Execute method of the command object to
    'return results into the recordset object; Notice that
    'an array is passed to the Parameters parameter of
    'the Command object
    Set rst = cmd.Execute(Parameters:=Array(datStart, datEnd))

    'Loop through the resulting recordset, printing the
    'contents of the TimeCardID and DateWorked fields
    Do Until rst.EOF
        Debug.Print rst("TimeCardID"), rst("DateWorked")
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set cmd = Nothing

End Sub

Notice that in the example, the CommandText property contains two question marks. Each of these is considered a parameter. The parameters are supplied when the Execute method of the Command object is used. Notice that the Parameters argument of the Execute method receives an array containing the parameter values. Note that unless you specify basADORecordsets.RunParameterQuery, you get an “ambiguous name detected” error.

Two methods are used to refresh the data in a recordset: Requery and Resync. The Requery method is roughly equivalent to once again opening the recordset. The Requery method forces the OLEDB provider to perform all the steps it performed when first creating the recordset. New rows are added to the recordset, changes to data made by other users are reflected in the recordset, and deleted rows are removed from the recordset. The Requery method requires significant resources to execute. The Resync method is much more efficient. It updates the recordset to reflect changes made by other users. It does not show added rows or remove deleted rows from the recordset.

Using ADO, recordsets cannot only exist in memory, but can also be written to disk. A recordset written to disk is referred to as a persisted recordset. Listing 14.25 illustrates how to persist a recordset to disk.

Sub PersistRecordset()

    Dim strFileName As String

    'Prompt user for file name and path
    strFileName = InputBox("Please enter file name and path")

    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType, and CursorLocation properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic

    'Open the recordset, designating that the source
    'is a SQL statement
    rst.Open Source:="Select * from tblClients ", _
        Options:=adCmdText

    'Destroy existing file with that name
    On Error Resume Next
    Kill strFileName

    'Save the recordset
    rst.Save strFileName, adPersistADTG

    rst.Close
    Set rst = Nothing

End Sub

Notice that the Save method of the Recordset object is used to persist the recordset to disk. The Format parameter of the Save method allows you to designate whether you want to save the recordset in the Microsoft proprietary Advanced Data Tablegram format, or whether you want to save the recordset as XML. Listing 14.26 shows you how to read a persisted recordset.

Sub ReadPersistedRecordset()
    Dim strFileName As String

    'Prompt user for file name and path to read
    strFileName = InputBox("Please enter file name and path")

    'Ensure that the selected file exists
    If Len(Dir(strFileName)) = 0 Then
        MsgBox "File Not Found"
        Exit Sub
    End If

    'Declare and instantiate a recordset object
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Set the ActiveConnection and CursorType, and
    'LockType, and CursorLocation properties of the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenStatic
    rst.LockType = adLockOptimistic

    'Open the recordset, designating that the source
    'is a SQL statement
    rst.Open Source:=strFileName, _
        Options:=adCmdFile

    'Loop through the recordset, printing ClientIds
    Do Until rst.EOF
        Debug.Print rst("ClientID")
        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing

End Sub

After prompting the user for a filename, the code ensures that the designated file is found. It then opens a recordset, using the file as the source argument. The adCmdFile constant is used for the Options parameter of the Open method. The adCmdFile value notifies ADO that the source is a persisted recordset.

It is possible to loop through a recordset, modifying all the records in the recordset. This technique is shown in Listing 14.27.

Sub IncreaseEstimate()

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Dim strSQL As String
    Dim lngUpdated As Long

    'Establish the connection, cursor type,
    'and lock type, and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open ("Select * from tblProjectsChange")

    strSQL = "ProjectTotalEstimate < 30000"
    lngUpdated = 0

    'Find the first row meeting the designated criteria
    rst.Find strSQL

    'Loop through the recordset, locating all rows meeting
    'the designated criteria, increasing the ProjecTotalEstimate
    'field by ten percent
    Do Until rst.EOF
        lngUpdated = lngUpdated + 1
        rst("ProjectTotalEstimate") = rst("ProjectTotalEstimate") * 1.1
        rst.Update
        rst.Find strSQL, 1, adSearchForward
    Loop

    'Print how many rows are updated
    Debug.Print lngUpdated & " Records Updated"

    rst.Close
    Set rst = Nothing


End Sub

In Listing 14.27, a recordset is opened based on all the records in the tblProjectsChange table. The first record where the ProjectTotalEstimate is less than 30,000 is located. The ProjectTotalEstimate is increased by 10%, and the record is updated. The next record that meets the specified criteria is located. The process is repeated until all records meeting the specified criteria are located.

This code is very inefficient from several standpoints. The first problem is that a recordset is opened based on all the records in the tblProjectsChange table, when only those with a ProjectTotalEstimate less than 30,000 needed to be updated. A more efficient approach is to open a recordset containing only those records that you need to update. Listing 14.28 illustrates this technique.

Sub IncreaseEstimateImproved()

    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Dim lngUpdated As Long

    'Establish the connection, cursor type,
    'and lock type, and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open ("Select * from tblProjectsChange " & _
        "WHERE ProjectTotalEstimate < 30000")

    'Loop through the recordset, locating all rows meeting
    'the designated criteria, increasing the ProjecTotalEstimate
    'field by ten percent
    Do Until rst.EOF
        lngUpdated = lngUpdated + 1
        rst("ProjectTotalEstimate") = rst("ProjectTotalEstimate") * 1.1
        rst.Update
        rst.MoveNext
    Loop

    'Print how many rows are updated
    Debug.Print lngUpdated & " Records Updated"

    rst.Close
    Set rst = Nothing

End Sub

Furthermore, it would be more efficient to simply execute an action query that performs the update. This technique is covered in the section that follows.

If you use a client-side cursor, along with a static or keyset cursor, you can take advantage of batch updates. Using batch updates, all changes you make to a recordset are sent to the underlying OLEDB provider as a batch. The process is illustrated in Listing 14.29.

Sub BatchUpdates()
    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Dim strSQL As String
    Dim lngUpdated As Long

    'Establish the connection, cursor type,
    'and lock type, and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.CursorLocation = adUseClient
    rst.LockType = adLockBatchOptimistic
    rst.Open ("Select * from tblProjectsChange")

    strSQL = "ProjectTotalEstimate < 30000"
    lngUpdated = 0

    'Find the first row meeting the designated criteria
    rst.Find strSQL

    'Loop through the recordset, locating all rows meeting
    'the designated criteria, increasing the ProjecTotalEstimate
    'field by ten percent
    Do Until rst.EOF
        lngUpdated = lngUpdated + 1
        rst("ProjectTotalEstimate") = rst("ProjectTotalEstimate") * 1.1
        rst.Find strSQL, 1, adSearchForward
    Loop

    'Send all changes to the provider
    rst.UpdateBatch

    'Print how many rows are updated
    Debug.Print lngUpdated & " Records Updated"

    rst.Close
    Set rst = Nothing

End Sub

In the example, the CursorLocation property of the recordset is set to adUseClient, the CursorType is set to adOpenKeyset, and the LockType is set to adLockBatchOptimistic. Notice that the Update method is not included in the Do Until loop. Instead, the UpdateBatch method is used to send all of the changes to the server at once.

As mentioned in the previous section, it is inefficient to open a recordset and then update each record individually. It is much more efficient to execute an action query. Listing 14.30 illustrates this process.

Sub RunUpdateQuery()
    'Declare and instantiate a Connection object
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection

    'Establish the connection and execute an action query
    Set cnn = CurrentProject.Connection
    cnn.Execute "qryIncreaseTotalEstimate"
    cnn.Close

End Sub

In Listing 14.30, the Execute method of the Connection object executes a stored query called qryIncreaseTotalEstimate. Any criteria contained within the query is applied.

You can use ADO code to delete a record in a recordset. The code appears in Listing 14.31. Note that it must be called using basADORecordset.DeleteCusts.

Sub DeleteCusts(lngProjEst As Long)

    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the connection, cursor type,
    'and lock type, and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Open "Select * from tblProjectsChange"

    intCounter = 0

    'Loop through the recordset, deleting all projects
    'with an estimate lower than the specified amount
    Do Until rst.EOF
        If rst("ProjectTotalEstimate") < lngProjEst Then
            rst.Delete
            intCounter = intCounter + 1
        End If
        If Not rst.EOF Then
            rst.MoveNext
        End If
    Loop

    'Designate how many customers were deleted
    Debug.Print intCounter & " Customers Deleted"

    rst.Close
    Set rst = Nothing

End Sub

In Listing 14.31, a recordset is opened, based on all the records in the tblProjectsChange table. The code loops through all the records in the recordset. If the ProjectTotalEstimate is less than the value passed as a parameter to the routine, the Delete method of the Recordset object removes the record from the recordset.

As previously discussed, this example is very inefficient. You should either build a recordset containing only the records you want to delete, or use an action query to accomplish the task.

Not only can you edit and delete data using ADO, but you can also add records as well. Listing 14.32 illustrates this process.

Private Sub cmdAddADO_Click()
    Dim rst As ADODB.Recordset

    'Ensure that the project name and clientid are entered
    If IsNull(Me.txtProjectName) Or _
        IsNull(Me.cboClientID) Then

        MsgBox "The Project Name and Client must be Filled In"

    Else

        'Instantiate a recordset
        Set rst = New ADODB.Recordset

        'Set the connection, cursor type and lock type
        'and open the recordset
        With rst
            .ActiveConnection = CurrentProject.Connection
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open "Select * from tblProjectsChange Where ProjectID = 0"

            'Add a new row to the recordset, populating its values with
            'the controls on the form
            .AddNew
                !ProjectName = Me.txtProjectName
                !ProjectDescription = Me.txtProjectDescription
                !ClientID = Me.cboClientID
            .Update

            'Populate the txtProjectID text box with the
            'autonumber value assigned to the new row
            Me.txtProjectID = !ProjectID
        End With

    End If

End Sub

This code, an event procedure for a command button on frmUnbound, begins by setting the CursorType property of the recordset to adOpenKeyset and the LockType property to adLockOptimistic. The AddNew method creates a buffer for a new record. All the field values are assigned, based on values in the text boxes on the form. The Update method writes the data to disk. Because the ProjectID field is an Autonumber field, the txtProjectID text box must be updated to reflect the Autonumber value that was assigned.

It is relatively easy to add a table using ADO code. Listing 14.33 provides an example.

Sub CreateTable()
   Dim tdf As ADOX.Table
    Dim idx As ADOX.Index

    'Declare and instantiate a Catalog object
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog

    'Establish a connection
    cat.ActiveConnection = CurrentProject.Connection

    ' Instantiate a Table object
    Set tdf = New ADOX.Table
    ' Name the table and add field to it
    With tdf
        .Name = "tblFoods"
        Set .ParentCatalog = cat
        .Columns.Append "FoodID", adInteger
        .Columns("FoodID").Properties("AutoIncrement") = True
        .Columns.Append "Description", adWChar
        .Columns.Append "Calories", adInteger
    End With

    'Append the table to the Tables collection
    cat.Tables.Append tdf

    'Instantiate an Index object
    Set idx = New ADOX.Index

    'Set properties of the index
    With idx
        .Name = "PrimaryKey"
        .Columns.Append "FoodID"
        .PrimaryKey = True
        .Unique = True
    End With

    'Add the index to the Indexes collection
    'of the table
    tdf.Indexes.Append idx

    Set idx = Nothing
    Set cat = Nothing


End Sub

Listing 14.33 begins by instantiating an ADOX table object. It sets the Name and ParentCatalog properties of the Table object. Then it uses the Append method of the Columns collection of the table to append each field to the table. After all the columns are appended, it uses the Append method of the Tables collection of the Catalog object to append the Table object to the database.

After the table is appended to the Catalog, you can add indexes to the table. An Index object is instantiated. The Name property of the index is set. Next, the Append method of the Columns object of the Index adds a column to the Index. The PrimaryKey and Unique properties of the index are both set to True. Finally, the Index object is appended to the Indexes collection of the Table object.

Sometimes it is necessary to remove a table from a database. Fortunately, this is very easily accomplished using ADO code. Listing 14.34 illustrates the process.

Sub DeleteTable()
    'Ignore error if it occurs
    On Error Resume Next

    'Declare and instantiate a Catalog object
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog

    'Establish the connection for the Catalog object
    cat.ActiveConnection = CurrentProject.Connection

    'Delete a table from the tables collection
    cat.Tables.Delete "tblFoods"

End Sub

First, a Catalog object is declared and instantiated. Then the Delete method of the Tables collection of the Catalog object removes the table from the database.

If your application adds new tables to a database, it might be necessary to establish relationships between those tables, as demonstrated in Listing 14.35.

Sub CreateRelation()
    Dim tbl As ADOX.Table
    Dim fk As ADOX.Key

    'Declare and instantiate a Catalog object
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog

    'Establish a connection
    cat.ActiveConnection = CurrentProject.Connection

    'Point the Table object at the tblPeople table
    Set tbl = cat.Tables("tblPeople")

    'Instantiate a Key object
    Set fk = New ADOX.Key

    'Set properties of the Key object to relate the
    'tblPeople table to the tblFoods table
    With fk
        .Name = "PeopleFood"
        .Type = adKeyForeign
        .RelatedTable = "tblFoods"
        .Columns.Append "FoodId"
        .Columns("FoodID").RelatedColumn = "FoodID"
    End With

    'Append the Key object to the Keys collection of
    'the tblPeople table
    tbl.Keys.Append fk

    Set cat = Nothing
    Set tbl = Nothing
    Set fk = Nothing
End Sub

The code begins by pointing a Table object at the foreign key table in the relationship. A Key object is instantiated. The Name property of the Key object is set. Next, the Type property of the Key object is established. The RelatedTable property is set equal to the name of the primary key table involved in the relationship. The Append method of the Columns collection of the Key object appends the foreign key field to the Key object. Then the RelatedColumn property of the column is set equal to the name of the primary key field. Finally, the Key object is appended to the Keys collection of the Table object.

At times, you will want to build a query on-the-fly and permanently store it in the database. Listing 14.36 illustrates this process.

Sub CreateQuery()
    Dim cmd As ADODB.Command
    Dim strSQL As String

    'Declare and instantiate a Catalog object
    Dim cat As ADOX.Catalog
    Set cat = New ADOX.Catalog

    'Establish a connection
    cat.ActiveConnection = CurrentProject.Connection

    'Instantiate a Command object and set its
    'CommandText property
    Set cmd = New ADODB.Command
    cmd.CommandText = "Select * From tblClients Where State='CA'"

    'Append the Command object to the Views collection
    'of the Catalog object
    cat.Views.Append "qryCAClients", cmd
    cat.Views.Refresh

    Set cat = Nothing
    Set cmd = Nothing

End Sub

The code begins by creating and instantiating a Catalog object and a Command object. The CommandText property of the Command object is set equal to the SQL statement that underlies the query. The Append method of the Views collection of the Catalog object appends the Command object to a query with the specified name. The View collection of the Catalog object is then refreshed.

The Workspaces collection contains Workspace objects. Each Workspace object defines the area in which a particular user operates. All security and transaction processing for a given user takes place within a particular workspace. You can programmatically create multiple workspaces. This is of great value because, by using this technique, you can log in as another user behind the scenes and accomplish tasks not allowed by the security level of the current user. You can log in as a member of the Admins group, for example, change the structure of a table that the current user does not have rights to, and log back out without the user of the system ever knowing that anything happened.

The Users collection contains the User objects for a particular workspace. Each User object is a user account defined by a workgroup database. Because each user is a member of one or more groups, each User object contains a Groups collection that consists of each group of which a particular user is a member. User objects easily can be added and manipulated at runtime.

The Groups collection contains all Group objects for a particular workspace. Each Group object is a group defined by a workgroup database. Because each group contains users, the Group object contains a Users collection that consists of each user who is a member of the group. Like User objects, Group objects can be added and manipulated at runtime.

The Databases collection contains all the databases that are currently open within a particular workspace. You can open multiple databases at a time. These open databases can be Jet databases or external databases. A Database object refers to a particular database within the Databases collection. It is easy to loop through the Databases collection, printing the name of each Database object contained in the collection, as shown in Listing 14.37.

Sub EnumerateDBs()
    Dim ws As dao.Workspace
    Dim db As dao.Database
    Dim db1 As dao.Database
    Dim db2 As dao.Database

    Set ws = DBEngine(0)

    'Point the db1 database object at a reference to the
    'Current Database
    Set db1 = CurrentDb

    'Point the db2 database object a a reference to a
    'database called Chap2.mdb
    Set db2 = ws.OpenDatabase(CurrentProject.Path & "Chap2.MDB")
    'Loop through all of the databases in the workspace
    'printing their names
    For Each db In ws.Databases
        Debug.Print db.Name
    Next db
End Sub

This code loops through the open databases in the current workspace. It prints the name of each open database. It also is easy to perform all the other tasks required to build, modify, and manipulate database objects at runtime.

The TableDefs collection contains all the tables contained in a particular database—whether or not they are open. The TableDefs collection also includes linked tables and detailed information about each table. It is easy to loop through the TableDefs collection, printing various properties (for example, the name) of each Table object contained within the collection. Listing 14.38 shows an example of using the TableDefs collection to print the properties of each Table object, in addition to printing the properties of each index on the table.

Each TableDef object contains an Indexes collection, which enumerates all the indexes on the table. Each index contains a Fields collection to describe the fields in the index.

Sub EnumerateTablesAndIndexes()
    Dim db As dao.Database
    Dim tbl As dao.TableDef
    Dim idx As dao.Index
    Dim fld As dao.Field

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Loop through each TableDef object in the TableDefs
    'Collection in this database
    For Each tbl In db.TableDefs

        'Print the name of the table
        Debug.Print "Table: "; tbl.Name
        'Loop through all indexes associated with the table
        For Each idx In tbl.Indexes

            'Print the name, primary, and unique properties of
            'the index
            Debug.Print " Index: "; idx.Name
            Debug.Print " Primary=";  idx.PRIMARY; ", Unique="; idx.Unique

            'Loop through each field in the index printing its name
            For Each fld In idx.Fields
                Debug.Print "   Field:"; fld.Name
            Next fld
        Next idx
    Next tbl
End Sub

This code loops through the TableDefs in the current database and prints the name of each table in the database. It then prints the name of every index on the table and every field in the index. It is easy to write code that adds, deletes, modifies, and otherwise manipulates tables and indexes at runtime.

The QueryDefs collection includes all the queries contained within a particular database. It contains information about each query. It is easy to loop through the QueryDefs collection, printing various pieces of information about each query, as Listing 14.39 shows.

Sub EnumerateQueries()
    Dim db As dao.Database
    Dim qry As dao.QueryDef

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Loop through each QueryDef object in the QueryDefs
    'collection of the database
    For Each qry In db.QueryDefs

        'Print the name and the SQL statement behind the query
        Debug.Print qry.Name
        Debug.Print qry.SQL
    Next qry
End Sub

This code loops through the QueryDefs in the current database and prints the name and SQL statement associated with each QueryDef. It is easy to write code that adds, deletes, modifies, and otherwise manipulates queries at runtime.

Fields collections are contained within the TableDef, QueryDef, Index, Relation, and Recordset objects. The Fields collection of an object is the collection of Field objects within the parent object. A TableDef object contains Field objects that are contained in the specific table, for example. Using the parent object, you can get information about its Fields collection, as shown in Listing 14.40.

Sub EnumerateFields()
    Dim tbl As dao.TableDef
    Dim fld As dao.Field

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Loop through each TableDef object in the TableDefs
    'collection of the database
    For Each tbl In db.TableDefs

        'Loop through each Field object in the Fields
        'collection of the table
        For Each fld In tbl.Fields

            'Print the name and type of each field
            Debug.Print fld.Name
            Debug.Print fld.Type
        Next fld
    Next tbl
End Sub

This code loops through the TableDefs in the current database. As it loops through each TableDef, it prints the name and type of each field contained within the Fields collection of the TableDef. Code also can be used to add, delete, or change the attributes of fields at runtime. With a large database, this code is likely to output more information than can be contained in the Immediate window buffer. You might want to pause the code at some point to view the contents of the Immediate window.

Access queries can contain parameters. These parameters are created so that the user can supply information required by the query at runtime. Each QueryDef object has a Parameters collection, which consists of Parameter objects. You can write code to manipulate these parameters at runtime, as Listing 14.41 shows.

Sub EnumerateParameters()
    Dim db As dao.Database
    Dim qry As dao.QueryDef
    Dim prm As dao.Parameter

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Loop through each QueryDef object in the QueryDefs
    'collection of the database
    For Each qry In db.QueryDefs

        'Print the Name of the Query
        Debug.Print "*****" & qry.Name & "*****"

        'Loop through each Parameter object in the Parameters
        'collection of the query
        For Each prm In qry.Parameters

            'Print the name of the parameter
            Debug.Print prm.Name
        Next prm
    Next qry
End Sub

This code loops through the QueryDefs object within the current database. It prints the name of the QueryDef object and then loops through its Parameters collection, printing the name of each parameter. Parameter objects can be added, deleted, and manipulated through code at runtime.

Recordset objects exist only at runtime. A Recordset object is used to reference a set of records coming from one or more tables. The Recordsets collection contains all the Recordset objects that are currently open within the current Database object. Recordset objects are covered extensively in “Understanding DAO Recordset Types,” later in this chapter.

The Relations collection contains all the Relation objects that describe the relationships established within a Database object. The code in Listing 14.42 loops through the current database, printing the Table and ForeignTable of each Relation object.

Sub EnumerateRelations()
    Dim db As dao.Database
    Dim rel As dao.Relation

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Loop through each Relation object in the Relations
    'collection of the database
    For Each rel In db.Relations

        'Print the names of the Primary and Foreign key tables
        Debug.Print rel.Table & " Related To: " & rel.ForeignTable
    Next rel
End Sub

Relationships can be created, deleted, and modified at runtime using VBA code.

The Containers collection contains information about each saved Database object. Using the Containers collection, you can view and modify all the objects contained within the current database, as demonstrated in Listing 14.43.

Sub EnumerateContainers()
    Dim db As dao.Database
    Dim cnt As dao.Container

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Loop through each Container object in the Containers
    'collection of the database
    For Each cnt In db.Containers

        'Print the name of the container
        Debug.Print cnt.Name
    Next cnt
End Sub

This code loops through the Containers collection, printing the name of each Container object. The results are DataAccessPages, Databases, Forms, Modules, Relationships, Reports, Scripts, SysRel, and Tables objects.

A Document object represents a specific object in the Documents collection. You can loop through the Documents collection of a Container object, as shown in Listing 14.44.

Sub EnumerateForms()
   Dim db As dao.Database
    Dim cnt As dao.Container
    Dim doc As dao.Document

    'Point the db object at a reference to the current database
    Set db = CurrentDb

    'Point the Container object at the Forms collection of
    'the Container
    Set cnt = db.Containers!Forms

    'Loop through each Document object in the Documents
    'collection of the container
    For Each doc In cnt.Documents
        'Print the name of the document
        Debug.Print doc.Name
    Next doc

End Sub

This code points a Container object to the forms in the current database. It then loops through each document in the Container object, printing the name of each Document object (in this case, the name of each form).

Each Data Access Object has a Properties collection. The Properties collection of an object is a list of properties associated with that particular object. This gives you a generic way to view and modify the properties of any object, as shown in Listing 14.45.

You can use this collection to create generic routines to handle common tasks. You could write a routine to set the font size of any control to 8 points, for example. Your routine could use the Properties collection to verify that the control has a Font property before attempting to set the size.

Sub EnumerateProperties()
    Dim db As dao.Database
    Dim cnt As dao.Container
    Dim doc As dao.Document
    Dim prp As dao.Property
    'Point the db object at a reference to the current database
    Set db = CurrentDb


    'Point the Container object at the Forms collection of
    'the Container
    Set cnt = db.Containers!Forms

    'Loop through each Document object in the Documents
    'collection of the container
    For Each doc In cnt.Documents
        Debug.Print doc.Name

        'Loop through each Property object in the
        'Properties collection of the document
        For Each prp In doc.Properties

            'Print the name and value of the property
            Debug.Print prp.Name & " = " & prp.Value
        Next prp
    Next doc
End Sub

This code loops through each form in the current database, printing all the properties of each Form object.

The Errors collection consists of Error objects. An Error object contains information about the most recent error that occurred. Each time an operation generates an error, the Errors collection is cleared of any previous errors. Sometimes a single operation can cause more than one error, so one or more Error objects might be added to the Errors collection when a single data access error occurs.

You can use a Recordset object of the dynaset type to manipulate local or linked tables or the results of queries. A dynaset is actually a set of references to table data. Using a dynaset, you can extract and update data from multiple tables—even tables from other databases. In fact, the tables containing the data included in a dynaset can even come from databases that are not of the same type (for example, Microsoft SQL Server, Paradox, and dBASE).

True to its name, a dynaset is a dynamic set of records. This means that changes made to the dynaset are reflected in the underlying tables, and changes made to the underlying tables by other users of the system are reflected in the dynaset. Although a dynaset is not the fastest type of Recordset object, it is definitely the most flexible.

A Recordset object of the snapshot type is similar to a dynaset. The major difference is that the data included in the snapshot is fixed at the time it is created. The data within the snapshot, therefore, cannot be modified and is not updated when other users make changes to the underlying tables. This trait can be an advantage or a disadvantage. It is a disadvantage, of course, if it is necessary for the data in the recordset to be updateable. It is an advantage if you are running a report and want to ensure that the data does not change during the time in which the report is being run. You, therefore, can create a snapshot and build the report from the Snapshot object.

A Recordset object of the table type often is used to manipulate local or linked tables created using Microsoft Access or the Jet Database Engine. When you open a table-type recordset, all operations are performed directly on the table.

Certain operations, such as a Seek, can be performed only on a table-type recordset. You get the best performance for sorting and filtering records when using a table type of recordset.

The downside of a table-type recordset is that it can contain the data from only one table. It cannot be opened using a join or union query. It also cannot be used with tables created using engines other than Jet (for example, ODBC and other ISAM data sources).

When working with a recordset, you first must create a Recordset variable. You use the OpenRecordSet method to create a Recordset object variable. You first must declare a generic Recordset variable and then point a specific recordset at the variable using a Set statement, as shown in the example in Listing 14.48.

Sub OpenTable()
    Dim dbInfo As dao.Database
    Dim rstClients As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set dbInfo = CurrentDb()

    'Open a recordset based on the tblClients table
    Set rstClients = dbInfo.OpenRecordset("tblClients")

    'Print the Updatable property of the recordset
    Debug.Print rstClients.Updatable
End Sub

This code creates a Database object variable and a Recordset object variable. It then uses the CurrentDB function to point the Database object variable to the current database. Next, it uses the OpenRecordSet method to assign the recordset based on tblClients to the object variable rstClients.

The type of recordset that is created is determined by the default type for the object or by a second parameter of the OpenRecordSet method. If the OpenRecordSet method is executed on a table and no second parameter is specified, the recordset is opened as the table type. If the OpenRecordSet method is performed on a query and no second parameter is specified, the recordset is opened as the dynaset type. You can override this default behavior by passing a second parameter to the OpenRecordSet method, as Listing 14.49 shows.

Sub OpenDynaSet()
    Dim dbInfo As dao.Database
    Dim rstClients As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set dbInfo = CurrentDb()
    'Open a dynaset type recordset based on the tblClients table
    Set rstClients = dbInfo.OpenRecordset("tblClients", dbOpenDynaset)

    'Print the Updateable property of the recordset
    Debug.Print rstClients.Updateable
End Sub

This code opens the recordset as a dynaset. dbOpenTable, dbOpenDynaset, and dbOpenSnapshot are all intrinsic constants that can be used to open a Recordset object. A query can be opened only as a dynaset or snapshot Recordset object. Listing 14.50 shows the code to open a recordset based on a query.

Sub OpenQuery()
    Dim dbInfo As dao.Database
    Dim rstClients As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set dbInfo = CurrentDb()

    'Open a snapshot type recordset based on the qryHoursByProject query
    Set rstClients = dbInfo.OpenRecordset("qryHoursByProject", dbOpenSnapshot)

    'Print the Updateable property of the recordset
    Debug.Print rstClients.Updatable
End Sub

Microsoft provides several arguments that control the way in which a recordset is opened. The arguments and their uses follow:

The arguments described can be used in combination to accomplish the desired objectives. Listing 14.51 shows the use of an OpenRecordSet argument.

Sub OpenRecordsetArgs()
    Dim db As dao.Database
    Dim rst As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Open a dynaset type recordset based on the tblProjects table,
    'but designate that the recordset is read only
    Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset, dbReadOnly)

    'Print the Updateable property of the recordset
    Debug.Print rst.Updateable
End Sub

This code opens a recordset as read-only.

When you have a Recordset object variable set, you probably want to manipulate the data in the recordset. Table 14.4 shows several methods you can use to traverse through the records in a recordset.

Listing 14.52 shows an example of using the record-movement methods on a dynaset.

Sub RecordsetMovements()
    Dim db As dao.Database
    Dim rst As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Open a dynaset type recordset based on the tblProjects table
    Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset)

    'Print the contents of the ProjectID field
    Debug.Print rst("ProjectID")

    'Move to the next row, printing the ProjectID
    rst.MoveNext
    Debug.Print rst("ProjectID")

    'Move to the last row, printing the ProjectID
    rst.MoveLast
    Debug.Print rst("ProjectID")

    'Move to the previous row, printing the ProjectID
    rst.MovePrevious
    Debug.Print rst("ProjectID")

    'Move to the first row, printing the ProjectID
    rst.MoveFirst
    Debug.Print rst("ProjectID")
    rst.Close
End Sub

This code opens a dynaset. The record pointer automatically is placed on the first record of the dynaset when the recordset is opened. The routine prints the contents of the ProjectID field and then moves to the next record, printing its ProjectID. It then moves to the last record of the dynaset, printing its ProjectID; moves to the previous record, printing its ProjectID; and moves to the first record, printing its ProjectID. The Close method is applied to the Recordset object. It is a good idea to always close an open recordset before exiting a routine. After changes are made to the recordset, the Close method properly closes the recordset, ensuring that all changes are written to disk.

All the information discussed in the section about determining the limits of an ADO recordset apply when dealing with a DAO recordset. Listing 14.53 shows a DAO code sample that uses the EOF property with the MoveNext method.

Sub DetermineLimits()
    Dim db As dao.Database
    Dim rstClients As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a snapshot type recordset based on the tblClients table
    Set rstClients = db.OpenRecordset("tblClients", dbOpenSnapshot)

    'Loop through all of the records in the recordset, printing
    'the ClientID
    Do Until rstClients.EOF
        Debug.Print rstClients("ClientID")
        rstClients.MoveNext
    Loop
    rstClients.Close
End Sub

This code traverses through a snapshot recordset, printing the value of the ClientID field for each record until it reaches the position after the last record in the recordset. It then exits the loop and closes the recordset.

The RecordCount property of a recordset returns the number of records in a recordset that have been accessed. The problem with this is evident if you open a recordset and view the RecordCount property. You will discover that the count is equal to 0, if no records exist in the recordset, or equal to 1, if there are records in the recordset. The record count is accurate only if you visit all the records in the recordset, which you can do by using the MoveLast method, as Listing 14.54 shows.

Sub CountRecords()
    Dim rstProjects As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a snapshot type recordset based on the tblClients table
    Set rstProjects = db.OpenRecordset("tblProjects", dbOpenSnapshot)

    'Print the recordcount
    Debug.Print rstProjects.RecordCount 'Prints 0 Or 1

    'Move to the last row
    rstProjects.MoveLast

    'Print the recordcount
    Debug.Print rstProjects.RecordCount 'Prints an accurate record Count
    rstProjects.Close
End Sub

The MoveLast method has its problems, though. It is slow and inefficient, especially in a client/server environment. Furthermore, in a multiuser environment, the RecordCount property becomes inaccurate as other people add and remove records from the table. This means that, if determining the record count is not absolutely necessary, you should avoid it.

The RecordCount property has one good use, though: You can use it to see whether there are any records in a recordset. If you are performing an operation that might return an empty recordset, you easily can use the RecordCount property to determine whether records were returned, as Listing 14.55 shows.

Sub CheckARecordset()
    Dim db As dao.Database
    Dim rstProjects As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a snapshot type recordset based on the tblEmpty table
    Set rstProjects = db.OpenRecordset("tblEmpty", dbOpenSnapshot)
    'Execute the AreThereRecords function to determine if the
    'recordset contains any rows
    If Not AreThereRecords(rstProjects) Then
        MsgBox "Recordset Empty...Unable to Proceed"
    End If
End Sub
Function AreThereRecords(rstAny As Recordset) As Boolean
    'Return the RecordCount property of the recordset
    'received as a parameter
    AreThereRecords = rstAny.RecordCount
End Function

The CheckARecordset procedure opens a recordset based on the tblEmpty table. It then calls the AreThereRecords function to determine whether any records are found in the recordset. If the AreThereRecords function returns False, an error message is displayed to the user.

Sometimes you might need to sort or filter an existing recordset. You also might want to locate each record in the recordset that meets some specified criteria. The following techniques enable you to sort, filter, and find records within a Recordset object.

Sub SortRecordset()
    Dim db As dao.Database
    Dim rstTimeCardHours As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Open a Dynaset type recordset based on tblTimeCardHours
    Set rstTimeCardHours = db.OpenRecordset("tblTimeCardHours", dbOpenDynaset)

    'Loop through the unsorted recordset
    Debug.Print "NOT Sorted!!!"
    Do Until rstTimeCardHours.EOF
        Debug.Print rstTimeCardHours("DateWorked")
        rstTimeCardHours.MoveNext
    Loop

    'Loop through the sorted recordset
    Debug.Print "Now Sorted!!!"
    rstTimeCardHours.Sort = "[DateWorked]"
    Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
    Do Until rstTimeCardHours.EOF
        Debug.Print rstTimeCardHours("DateWorked")
        rstTimeCardHours.MoveNext
    Loop
End Sub

Sub FilterRecordSet()
    Dim db As dao.Database
    Dim rstTimeCardHours As Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Open a Dynaset type recordset based on tblTimeCardHours
    Set rstTimeCardHours = db.OpenRecordset("tblTimeCardHours", dbOpenDynaset)

    'Loop through the unfiltered recordset
    Debug.Print "Without Filter"
    Do Until rstTimeCardHours.EOF
        Debug.Print rstTimeCardHours("DateWorked")
        rstTimeCardHours.MoveNext
    Loop

    'Loop through the filtered recordset
    rstTimeCardHours.Filter = "[DateWorked] Between #1/1/95# and #1/5/95#"
    Debug.Print "With Filter"
    Set rstTimeCardHours = rstTimeCardHours.OpenRecordset
    Do Until rstTimeCardHours.EOF
        Debug.Print rstTimeCardHours("DateWorked")
        rstTimeCardHours.MoveNext
    Loop
End Sub

Sub SeekProject(lngProjectID As Long)
    Dim db As dao.Database
    Dim rstProjects As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a table type recordset based on the tblProjects table
    Set rstProjects = db.OpenRecordset("tblProjects", dbOpenTable)

    'Set the Index property of the recordset and
    'use the Seek method to find a project
    rstProjects.Index = "PrimaryKey"
    rstProjects.Seek "=", lngProjectID
    'Determine if the requested row was found
    If rstProjects.NoMatch Then
        MsgBox lngProjectID & " Not Found"
    Else
        MsgBox lngProjectID & " Found"
    End If
End Sub

Sub FindProject(lngValue As Long)
    Dim db As dao.Database
    Dim rstProjects As dao.Recordset
    Dim sSQL As String

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a Dynaset type recordset based on tblProjects
    Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)

    'Find the first row meeting the specified criteria
    sSQL = "[ProjectID] = " & lngValue
    rstProjects.FindFirst sSQL

    'Determine if a match was found
    If rstProjects.NoMatch Then
        MsgBox lngValue & " Not Found"
    Else
        MsgBox lngValue & " Found"
    End If
End Sub

The AbsolutePosition property returns the position of the current record. It is a zero-based value. You can use it to specify where in a recordset a specific record was found, as shown in Listing 14.60.

Sub FindPosition(lngValue As Long)
    Dim db As dao.Database
    Dim rstProjects As dao.Recordset
    Dim sSQL As String

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a Dynaset type recordset based on tblProjects
    Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)

    'Find the first row meeting the specified criteria
    sSQL = "[ProjectID] = " & lngValue
    rstProjects.FindFirst sSQL

    'If a match is found, print the position of the row
    If rstProjects.NoMatch Then
        MsgBox lngValue & " Not Found"
    Else
        Debug.Print rstProjects.AbsolutePosition
    End If
End Sub

This code finds the first record with a ProjectID equal to the long integer received as a parameter. If the ProjectID is found, the value in the AbsolutePosition property of the record is printed.

A bookmark is a system-generated byte array that uniquely identifies each record in a recordset. The Bookmark property of a recordset changes as you move to each record in the recordset. It often is used if you need to store the current position in the recordset so that you can perform some operation and then return to the position after the operation is completed. Three steps are involved in this process:

Listing 14.61 shows an example of using a bookmark.

Sub UseBookMark()
    Dim db As dao.Database
    Dim rstProjects As dao.Recordset
    Dim sSQL As String
    Dim vntPosition As Variant

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a Dynaset type recordset based on tblProjects
    Set rstProjects = db.OpenRecordset("tblProjects", dbOpenDynaset)
    'Store the current position in a variant variable
    vntPosition = rstProjects.Bookmark

    'Process the recordset
    Do Until rstProjects.EOF
        Debug.Print rstProjects("ProjectID")
        rstProjects.MoveNext
    Loop

    'Move back to the bookmarked row
    rstProjects.Bookmark = vntPosition
    Debug.Print rstProjects("ProjectID")
End Sub

This code begins by opening a recordset and storing the bookmark of the first record into a Variant variable. It then loops through each record in the recordset, printing the value in the ProjectID. After the loop completes, the Bookmark property of the recordset is set equal to the Variant variable, setting the current position of the recordset back to where it was before the loop began processing.

You use the RecordsetClone property of a form to refer to the recordset underlying the form. This property often is used when you want to perform an operation and then synchronize the form with its underlying recordset. Listing 14.62 shows an example of the RecordsetClone property.

Private Sub cmdFindClient_Click()
    'This code is not found in the sample database
    Me.RecordsetClone.FindFirst "ClientID = " & Me.txtClientID
    If Me.RecordsetClone.NoMatch Then
        MsgBox Me.txtClientID & " Not Found"
    Else
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
End Sub

This routine performs the FindFirst method on the RecordsetClone property of the current form. If the record is found, the Bookmark property of the form is set equal to the bookmark of the recordset. This matches the form’s position to the underlying recordset’s position.

Access parameter queries are very powerful. They enable the user to specify criteria at runtime. This capability often is helpful if your user wants to fill out a form at runtime and have the values on that form fed to the query. This also can be a useful way to protect your code from changes in the database schema. Creating a parameterized query is like writing a subroutine, in which the details of implementing that routine are hidden from the caller. This programming technique is called encapsulation. Listing 14.63 shows an example of using parameter queries.

Sub RunParameterQuery(datStart As Date, datEnd As Date)
    Dim db As dao.Database
    Dim qdf As dao.QueryDef
    Dim rst As dao.Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Point the QueryDef object at the qryBillAmountByClient query
    Set qdf = db.QueryDefs("qryBillAmountByClient")

    'Set the parameters of the QueryDef object
    qdf.Parameters("Please Enter Start Date") = datStart
    qdf.Parameters("Please Enter End Date") = datEnd

    'Open a recordset based on the QueryDef object
    Set rst = qdf.OpenRecordset

    'Loop through the resulting recordset
    Do Until rst.EOF
        Debug.Print rst("CompanyName"), rst("BillAmount")
        rst.MoveNext
    Loop
End Sub

This subroutine receives two date variables as parameters. It just as easily could receive form controls as parameters. It opens a query definition called qryBillAmountByClient. It then sets the values of the parameters called Please Enter Start Date and Please Enter End Date to the date variables passed into the subroutine as parameters. The query then is executed by issuing the OpenRecordset method on the Recordset object.

Often, you want to loop through a recordset, modifying all the records that meet a specific set of criteria. Listing 14.64 shows the code required to accomplish this task.

Sub IncreaseEstimate()
    Dim db As dao.Database
    Dim rstProjectst As dao.Recordset
    Dim sSQL As String
    Dim intUpdated As Integer

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a Dynaset type recordset based on tblProjectsChange
    Set rstProjectst = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)

    'Locate the first project that meets the designated criteria
    sSQL = "ProjectTotalEstimate < 30000"
    intUpdated = 0
    rstProjectst.FindFirst sSQL

    'Loop as long as records meet the designated criteria
    'increasing the ProjectTotalEstimate by 10%
    Do Until rstProjectst.NoMatch
        intUpdated = intUpdated + 1
        rstProjectst.Edit
        rstProjectst("ProjectTotalEstimate") = rstProjectst("ProjectTotalEstimate") * 1.1
        rstProjectst.Update
        rstProjectst.FindNext sSQL
    Loop

    'Display the number of rows that were updated
    Debug.Print intUpdated & " Records Updated"
    rstProjectst.Close
End Sub

This code finds the first record with a ProjectTotalEstimate of less than 30,000. It uses the Edit method to prepare the current record in the dynaset for editing. It replaces the ProjectTotalEstimate with the ProjectTotalEstimate multiplied by 1.1. It then issues the Update method to write the changes to disk. Finally, it uses the FindNext method to locate the next occurrence of the criteria.

Many of the tasks that you can perform by looping through a recordset also be can accomplished with an Update query. Executing an Update query often is more efficient than the process of looping through a recordset. If nothing else, it takes much less code. Therefore, it is important to understand how to execute an Update query through code.

Suppose that you have a query called qryChangeTotalEstimate that increases the ProjectTotalEstimate for all projects where the ProjectTotalEstimate is less than 30,000. The query is an Update query. The code in Listing 14.65 executes the stored query definition.

Sub RunUpdateQuery()
    Dim db As dao.Database
    Dim qdf As dao.QueryDef

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Point the QueryDef object at qryIncreaseTotalEstimate
    Set qdf = db.QueryDefs("qryIncreaseTotalEstimate")

    'Use the Execute method of the QueryDef object to
    'execute the update query
    qdf.Execute
End Sub

Notice that the Execute method operates on the query definition, executing the Updatequery.

The Delete method enables you to programmatically delete records from a recordset, as shown in Listing 14.66.

Sub DeleteCusts(lngProjEst As Long)
    Dim db As dao.Database
    Dim rstProjects As dao.Recordset
    Dim intCounter As Integer

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Open a Dynaset type recordset based on tblProjectsChange
    Set rstProjects = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)

    'Loop through the entire table, deleting all projects where
    'the ProjectTotalEstimate is less than a designated amount
    intCounter = 0
    Do Until rstProjects.EOF
        If rstProjects("ProjectTotalEstimate") < lngProjEst Then
            rstProjects.Delete
            intCounter = intCounter + 1
        End If
        rstProjects.MoveNext
    Loop

    'Print the number of affected rows
    Debug.Print intCounter & " Customers Deleted"
End Sub

This code loops through the rstProjects recordset. If the ProjectTotalEstimate amount is less than the value passed in as a parameter, the record is deleted. This task also can be accomplished with a Delete query.

The AddNew method enables you to programmatically add records to a recordset, as shown in Listing 14.67.

Private Sub cmdAddDAO_Click()
    Dim db As Database
    Dim rstProject As Recordset
    'Ensure that the project name and clientid are entered
    If IsNull(Me.txtProjectName) Or _
        IsNull(Me.cboClientID) Then

        MsgBox "The Project Name and Client must be Filled In"

    Else

        'Point the database object at a reference to the
        'current database
        Set db = CurrentDb()

        'Open a Dynaset type recordset based on tblProjectsChange
        Set rstProject = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)

        'Add a new row to the recordset, populating its values with
        'the controls on the form
        With rstProject
            .AddNew
            !ProjectName = Me.txtProjectName
            !ProjectDescription = Me.txtProjectDescription
            !ClientID = Me.cboClientID
            .Update
        End With

        'Populate the txtProjectID text box with the
        'autonumber value assigned to the new row
        Me!txtProjectID = rstProject!ProjectID

    End If
End Sub

This code is used on an Unbound form called frmUnbound. The code issues an AddNew method, which creates a buffer ready to accept data. Each field in the recordset then is populated with the values from the controls on the form. The Update method writes the data to disk. If you forget to include the Update method, the record is never written to disk.

The last line of code does not work. The ProjectID field is an AutoNumber field, so Access will assign its value during the update. The offending line is supposed to copy the newly created ProjectID value into a text field on the form. The line is there to illustrate a problem: When an AddNew method is issued, the record pointer is not moved within the dynaset. Even after the Update method is issued, the record pointer remains at the record it was on prior to the AddNew method.

Therefore, this code will add a record, but it will place the ProjectID value of the previously existing record into the txtProjectId text box on the form. To get around this, you must explicitly move to the new record before populating the text box. This can be accomplished easily by using the LastModified property.

The LastModified property contains a bookmark of the most recently added or modified record. By setting the bookmark of the recordset to the LastModified property, the record pointer is moved to the most recently added record. Listing 14.68 is a modified version of Listing 14.67, using the LastModified property to fix the problem described previously.

Private Sub cmdLastModified_Click()
    Dim db As Database
    Dim rstProject As Recordset

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Open a Dynaset type recordset based on tblProjectsChange
    Set rstProject = db.OpenRecordset("tblProjectsChange", dbOpenDynaset)

    'Add a new row to the recordset, populating its values with
    'the controls on the form
    With rstProject
        .AddNew
        !ProjectName = Me.txtProjectName
        !ProjectDescription = Me.txtProjectDescription
        !ClientID = Me.cboClientID
        .Update

        'Move to the row you just added
        .Bookmark = .LastModified
    End With

    'Populate the txtProjectID text box with the
    'autonumber value assigned to the new row
    Me!txtProjectID = rstProject!ProjectID

End Sub

Notice that the bookmark of the recordset is set to the LastModified property of the recordset.

Many properties and methods are available for adding and modifying Jet Engine objects. The code in Listing 14.69 creates a table, adds some fields, and then adds a primary key index.

Sub CreateTable()
    Dim db As dao.Database
    Dim tbl As dao.TableDef
    Dim fld As dao.Field
    Dim idx As dao.Index

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb()

    'Point a TableDef object at a new TableDef
    Set tbl = db.CreateTableDef("tblFoods")

    'Add fields to the TableDef object
    Set fld = tbl.CreateField("FoodID", dbLong, 5)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Description", dbText, 25)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Calories", dbInteger)
    tbl.Fields.Append fld
    db.TableDefs.Append tbl

    'Add an index to the TableDef object
    Set idx = tbl.CreateIndex("PrimaryKey")

    'Add a field to the Index object
    Set fld = idx.CreateField("FoodID")

    'Set properties of the index
    idx.PRIMARY = True
    idx.Unique = True
    'Add the field to the Fields collection of the Index object
    idx.Fields.Append fld

    'Add the index to the Indexes collection of the Table object
    tbl.Indexes.Append idx
End Sub

This code first creates a table definition called tblFoods. Before it can add the table definition to the TableDefs collection, it must add fields to the table. Three fields are added to the table. Notice that the field name, type, and length are specified. After the table definition is added to the database, indexes can be added to the table. The index added in Listing 14.69 is a primary key index.

Just as you can add a table using code, you can remove a table using code, as shown in Listing 14.70

Sub DeleteTable()
    Dim db As dao.Database

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Use the Delete method of the TableDefs collection
    'to delete a table called tblFoods
    db.TableDefs.Delete "tblFoods"
End Sub

The Delete method is issued on the TableDefs collection. The table you want to delete is passed to the Delete method as an argument.

When you create tables using the Access environment, you normally create relationships between the tables at the same time. If you are creating tables using code, you probably want to establish relationships between those tables using code as well. Listing 14.71 shows an example.

Sub CreateRelation()
    Dim db As dao.Database
    Dim rel As dao.Relation
    Dim fld As dao.Field

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Use the CreateRelation method of the database object
    'the create a Relation object
    Set rel = db.CreateRelation()

    'Set properties of the Relation object
    With rel
        .Name = "PeopleFood"
        .Table = "tblFoods"
        .ForeignTable = "tblPeople"
        .Attributes = dbRelationDeleteCascade
    End With

    'Set the Primary Key field of the Relation object
    Set fld = rel.CreateField("FoodID")

    'Set the Foreign Key field of the Relation object
    fld.ForeignName = "FoodID"
    'Add the Field object to the Fields collection of
    'the Relation object
    rel.Fields.Append fld

    'Append the Relation object to the Relations
    'collection of the Database object
    db.Relations.Append rel

End Sub

This code begins by creating a new Relation object. It then populates the Name, Table, Foreign Table, and Attributes properties of the relationship. After the properties of the relationship are set, the field is added to the Relation object. Finally, the Relation object is appended to the Relations collection.

If you are running your application from the Access runtime, your users won’t be able to design their own queries unless they have their own full copies of Access. You might want to build your own query designer into your application and then allow the users to save the queries they build. This requires that you build the queries yourself, after the user designs them. Listing 14.72 shows the code needed to build a query.

Sub CreateQuery()
    Dim db As dao.Database
    Dim qdf As dao.QueryDef
    Dim strSQL As String

    'Point the database object at a reference to the
    'current database
    Set db = CurrentDb

    'Create a QueryDef object called qryBigProjects
    Set qdf = db.CreateQueryDef("qryBigProjects")

    'Designate the SQL associated with the QueryDef object
    strSQL = "Select ProjectID, ProjectName, ProjectTotalEstimate " _
        & "From tblProjects " _
        & "Where ProjectTotalEstimate >= 30000"
    qdf.SQL = strSQL
End Sub

This code uses the CreateQueryDef method of the Database object to create a new query definition. It then sets the SQL statement associated with the query definition. This serves to build and store the query.

At times, you might want to disable the default record movement and add, edit, or delete functionality from a form and code all the functionality yourself. You might want to perform these actions if you are going against client/server data and want to execute additional control over the data-entry environment. You also might want to use these techniques when you are developing applications for both the Access and Visual Basic environments and are striving for maximum code compatibility. Regardless of your reasons for using the following techniques, it is a good idea to know how to assign a Recordset object to a form and then use the form’s underlying recordset to display and modify data.

Figure 14.3 shows a form in which the navigation buttons and record selectors have been removed. The form contains six command buttons: Move Previous (<), Move Next (>), Add, Delete, Find, and Exit. All the buttons use the recordset underlying the form to move from record to record in the form and modify the data contained within the form.

Figure 14.3. The frmRecordsets dialog box.

The RecordSource property of the form is not set. The Load event of the form is responsible for assigning a Recordset object to the form. Listing 14.74 shows the Load event of the form.

Private Sub Form_Load()
    'Declare and instantiate a recordset
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    'Establish the Connection, Cursor Type, and
    'Lock Type and open the recordset
    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic
    rst.Open "Select * from tblClients", Options:=adCmdText

    'Set the form's recordset to the recordset just created
    Set Me.Recordset = rst
End Sub

The code begins by declaring and instantiating an ADODB Recordset object. It then sets three properties of the Recordset object: the ActiveConnection, the CursorType, and the LockType. The Open method is used to open a recordset, based on the tblClients table. Finally, a Set statement is used to assign the recordset to the recordset underlying the form.

Listing 14.75 shows the code for the Move Previous button.

Private Sub cmdPrevious_Click()
    'Move to the next record in the recordset
    Me.Recordset.MovePrevious

    'If at BOF, move to the next record
    If Me.Recordset.BOF Then
        Me.Recordset.MoveNext
        MsgBox "Already at First Record!!"
    End If

    'Set the bookmark of the form to the bookmark
    'of the recordset underlying the form
    Me.Bookmark = Me.Recordset.Bookmark
End Sub

This routine performs the MovePrevious method on the Recordset property of the form. If the BOF property becomes True, indicating that the record pointer is before the first valid record, the MoveNext method is performed on the Recordset property of the form to return the record pointer to the first record in the recordset. Finally, the bookmark of the form is synchronized with the bookmark of the Recordset property. Listing 14.76 shows the code for the Move Next button.

Private Sub cmdNext_Click()
    'Move to the next record in the recordset
    Me.Recordset.MoveNext

    'If at EOF, move to the previous record
    If Me.Recordset.EOF Then
        Me.Recordset.MovePrevious
        MsgBox "Already at Last Record!!"
    End If

    'Set the bookmark of the form to the bookmark
    'of the recordset underlying the form
    Me.Bookmark = Me.Recordset.Bookmark
End Sub

The code for the Add button is a little tricky, as Listing 14.77 shows.

Private Sub cmdAdd_Click()
    'Add a new row to the recordset
    Me.Recordset.AddNew
    Me.Recordset("CompanyName") = "New Company"
    Me.Recordset.Update

    'Move to the row that was added
    Me.Bookmark = Me.Recordset.Bookmark
End Sub

The AddNew method is performed on the Recordset property of the form. This method creates a buffer in memory that is ready to accept the new data. When the Update method is issued, the record pointer is moved to the new record. Because the CompanyName field is a required field, you must populate it with data before issuing the Update method on the Recordset property.

By setting the bookmark of the form to the Bookmark property of the recordset, you synchronize the form with the new record. In a production environment, you would want to clear out all the text boxes and force the user to save or cancel before the AddNew or Update methods are issued.

The process of deleting a record is quite simple, as Listing 14.78 shows.

Private Sub cmdDelete_Click()
    'Ask user if they really want to delete the row
    intAnswer = MsgBox("Are You Sure???", _
        vbYesNo + vbQuestion, _
        "Delete Current Record?")

    'If they respond yes, delete the row and
    'move to the next row
    If intAnswer = vbYes Then
        Me.Recordset.Delete
        Call cmdNext_Click
        Me.Refresh
    End If
End Sub

This code verifies that the user actually wants to delete the record and then issues the Delete method on the Recordset property of the form. Because the current record no longer is valid, the code calls the Click event of the cmdNext button.

The last piece of code involved in the form is the code for the Find button, as shown in Listing 14.79.

Private Sub cmdFind_Click()
    Dim strClientID As String
    Dim varBookmark As Variant

    'Store the book of the current record
    varBookmark = Me.Recordset.Bookmark

    'Attempt to locate another client
    strClientID = InputBox("Enter Client ID of Client You Want to Locate")
    Me.Recordset.Find "ClientID = " & strClientID, Start:=1
    'If client not found, display a message and return to
    'the original record
    If Me.Recordset.EOF Then
        MsgBox "Client ID " & strClientID & " Not Found!!"
        Me.Recordset.Bookmark = varBookmark

    'If client found, synchronize the form with the
    'underlying recordset
    Else
        Me.Bookmark = Me.Recordset.Bookmark
    End If
End Sub

This routine begins by storing the bookmark of the current record to a Variant variable. Users are prompted for the client ID they want to locate, and then the Find method is issued on the Recordset property of the form. If the EOF property is True, the user is warned, and the bookmark of the recordset is set to the value within the Variant variable, returning the record pointer to the position it was in prior to the search. If the client ID is found, the bookmark of the form is synchronized with the bookmark of the Recordset property.