The ADO Connection object

As its name suggests, the Connection object provides a connection to a data source. Having access to a data source is necessary for any data operation, so the Connection object is required in virtually any scenario involving ADO.

After the ADO library has been referenced, creating a Connection object is simple (the ADO library is referenced as ADODB in VBA code):

Dim adConn as ADODB.Connection
Set adConn = New ADODB.Connection

These two statements are typical of VBA's approach to object-oriented programming. In the first statement, an object variable (adConn) is established as an ADODB.Connection object type. This means that VBA recognizes adConn as a Connection, with all the properties and methods associated with Connection objects, as defined by the ADO library. However, at this point, adConn is just a placeholder—it doesn't yet exist in memory.

The second statement instantiates the adConn object variable. As this statement executes, VBA creates a Connection object in the computer's memory, points the adConn variable to the object in memory, and prepares it for use.

The Connection must be opened before it can be used. The following statement is the easiest way to open an ADO Connection:

adConn.Open CurrentProject.Connection

In this case, the Connection connects to the current database. As you'll soon see, a Connection object requires a number of properties to be set before it can successfully open, but opening a Connection on the current database's Connection property provides all those settings. CurrentProject.Connection is actually a long string (specifically, a connection string) that includes all the information needed about the current database. A typical Connection property setting is as follows:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;
Data Source=C:…Chapter28.accdb;
Mode=Share Deny None;Extended Properties="";
Jet OLEDB:System database=C:…MicrosoftAccessSystem.mdw;
Jet OLEDB:Registry Path=Software…Access Connectivity Engine;
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=6;
Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False;
Jet OLEDB:Support Complex Data=True;
Jet OLEDB:Bypass UserInfo Validation=False;
Jet OLEDB:Limited DB Caching=False;
Jet OLEDB:Bypass ChoiceField Validation=False

This is actually considerably more than the Connection object actually needs, but Microsoft wanted to make sure nothing was missing.

Notice the Data Source portion of the ConnectionString property. This is the part that points to a specific ACCDB file. Changing this path means the Connection object can open virtually any Access database as long as the path is valid and terminates at an ACCDB file.

The following procedure opens a Connection against the current database, prints the Connection object's Provider property, and then closes and discards the Connection object:

Public Sub OpenConnection()

    Dim adConn As ADODB.Connection

    Set adConn = New ADODB.Connection
    adConn.Open CurrentProject.Connection

    ' Connection is open
    Debug.Print adConn.Provider

    adConn.Close
    Set adConn = Nothing

End Sub

When working with ADO, it's very important to close an object (if the object supports a Close method) and set it to Nothing when your code is done with the object. ADO objects tend to stay in memory once they've been opened, and must be explicitly closed and discarded (set to Nothing) to clear them from memory. If an ADO object is not properly terminated, it may remain in memory causing problems for users.

A Connection object requires the provider information and the data source. The provider specifies which ADO provider (essentially a driver) to attach to the Connection object. For example, there are providers for SQL Server databases: one for the Jet database engine, and another for the ACE database engine. Each provider knows how to connect to a different type of data and endows the Connection object with features specific to the data source.

The downside to the Connection object, and one that causes a lot of problems for Access developers, is the correct syntax to use for the Connection object's ConnectionString property. The ConnectionString must be properly composed and must reference a provider that is installed on the local machine.

One way to get the correct connection string syntax is to use Excel's Get External Data feature to get data from Access. Pick a small table in your Access database to bring into Excel and inspect the connection string under the Connection's properties. Figure 28.4 shows the Connection Properties dialog box.

You may notice from Figure 28.4 that the tblCustomers table from Chapter28.accdb was used. It's a smaller table, so it takes less time to import into Excel. Since we're just using it so that Excel creates the connection string, it doesn't matter which table is used.

You can copy the connection string from the Connection Properties dialog box with the Ctrl+C keyboard shortcut and paste it into your VBA code.

The ADO Command object

The second major ADO topic is the Command object. As its name implies, a Command object executes a command against the data source opened through a Connection. The command can be as simple as the name of an Access query or as complex as a long SQL statement that selects dozens of fields and includes WHERE and ORDER BY clauses. In fact, the Command object is the most common way to execute SQL Server stored procedures from Access applications.

As you'll see later in this chapter, the output from executing a Command object can be directed into a recordset. The data in the recordset can then be used to populate a form or controls such as text boxes, combo boxes, and list boxes.

There are many, many ways to use Command objects. The following procedure is just one example of using a Command object. In this case, the Command object populates a recordset with data taken directly from tblCustomers. (Recordsets are discussed in the next section.) The following procedure, ExecuteCommand, is included in modADO_Commands in the Chapter 28.accdb example database:

Public Sub ExecuteCommand()

    Dim adRs As ADODB.Recordset
    Dim adCmd As ADODB.Command

    Const sTABLE As String = "tblCustomers"

    Set adRs = New ADODB.Recordset
    Set adCmd = New ADODB.Command

    adCmd.ActiveConnection = CurrentProject.Connection
    adCmd.CommandText = sTABLE

    Set adRs = adCmd.Execute

    Debug.Print adRs.GetString

    adRs.Close
    Set adRs = Nothing
    Set adCmd = Nothing

End Sub

Notice the following actions in this procedure:

• A Recordset and a Command object are both declared and instantiated.
• The Command object's ActiveConnection property is set to the current project's Connection property.
• The Command object's CommandText property is set to the name of a table in the database.
• The recordset is populated by setting it to the value returned when the Command object is executed.

Notice the use of the recordset's GetString method. GetString is a handy way to output everything that's in the recordset. Figure 28.5 shows the output from ExecuteCommand in the Immediate window.

This little example illustrates the basics of what you need to know about ADO Command objects. A Command object must be attached to an available Connection through its ActiveConnection property. The ActiveConnection can be a connection string or an open Connection object. It doesn't make any difference where the Connection is pointing—an Access or SQL Server database, Oracle or any other data source. The Command object uses the Connection's special knowledge of the data source to get at the data.

Command objects are most valuable when working with parameterized queries. Each Command object includes a Parameters collection containing, naturally, Parameter objects. Each parameter corresponds to a parameter required by the query or stored procedure referenced by the Command's CommandText property.

Very often the CommandText property is set to a SQL statement that includes parameters:

SELECT * FROM tblCustomers
WHERE State = 'NY' OR State = 'NJ';

You'll see many examples of using the ADO Command object to populate recordsets and perform actions on data throughout this book.

The ADO Recordset object

Recordset is a very versatile ADO object. Most often, it's populated by executing a Command or directly through its Open method. Open_ADO_Recordset illustrates how easily the Recordset object opens an Access table:

Public Sub Open_ADO_Recordset()

    Dim adRs As ADODB.Recordset

    Set adRs = New ADODB.Recordset

    adRs.Open "SELECT * FROM tblCustomers;", _
      CurrentProject.Connection

    Debug.Print adRs.GetString

    adRs.Close
    Set adRs = Nothing

End Sub

In this example, the recordset is populated by selecting records from the Customers table using a SQL statement. The SQL statement could include WHERE or ORDER BY clauses to filter and sort the data as it's selected.

An alternative way to write this procedure is to use a separate statement for assigning the ActiveConnection property:

Public Sub Open_ADO_Rs_Connection()

  Dim adRs As ADODB.Recordset

  Set adRs = New ADODB.Recordset
  adRs.ActiveConnection = CurrentProject.Connection

  adRs.Open "SELECT * FROM tblCustomers;"

  Debug.Print adRs.GetString

  adRs.Close
  Set adRs = Nothing

End Sub

Many developers prefer the approach in Open_ADO_Rs_Connection because it's easier to see exactly what's happening to the Recordset object and where its properties are being set. Although these very small procedures are easily understood, in larger code segments finding all the references to an object like adRs can be challenging, especially when the VBA statements become long and complex.

As with the other ADO objects, a Recordset object must be declared and instantiated. Like the Command object, if the Open method is used to populate a Recordset object, an open connection must be provided as an argument to the Open method.

Recordset objects are used in many different places in this book. Depending on context, the most commonly used Recordset methods include Open, Close, MoveFirst, MoveNext, MovePrevious, and MoveLast.

Navigating recordsets

Recordsets wouldn't be much use if all you could do is open and close them, or if the GetString method were the only way to use the data in a recordset. Depending on context, the word recordset means several different things:

• The rows of data returned by a query
• The data bound to an Access form
• The object filled with data as the result of an ADO operation

In all cases, however, a recordset is a data structure containing rows and columns of data. The rows, of course, are records, while the columns are fields.

It makes sense that ADO provides ways to navigate through a recordset. When viewing a table or query results as a datasheet, you can use the vertical and horizontal scroll bars or arrow keys to move up and down, left and right, through the Datasheet view of the recordset. It's not surprising, then, that ADO Recordset objects support methods for moving through the records contained in a recordset.

The following procedure, RecordsetNavigation, demonstrates the fundamental ADO recordset navigation methods. (As you'll see in the “Understanding DAO Objects” section, later in this chapter, DAO recordsets support identically named methods.)

Public Sub RecordsetNavigation()

    Dim adRs As ADODB.Recordset

    Set adRs = New ADODB.Recordset
    adRs.ActiveConnection = CurrentProject.Connection

    adRs.CursorType = adOpenStatic

    adRs.Open "SELECT * FROM tblCustomers;"

    Debug.Print adRs!CustomerID, adRs!Company

    adRs.MoveNext
    Debug.Print adRs!CustomerID, adRs!Company

    adRs.MoveLast
    Debug.Print adRs!CustomerID, adRs!Company

    adRs.MovePrevious
    Debug.Print adRs!CustomerID, adRs!Company

    adRs.MoveFirst
    Debug.Print adRs.Fields("CustomerID").Value, _
      adRs.Fields("Company").Value

    adRs.Close
    Set adRs = Nothing

End Sub

This procedure begins by opening a Recordset object populated with data from tblCustomers. It immediately displays the CustomerID and Company from the very first record; then it moves around the recordset a few rows at a time, displaying the CustomerID and Company for each record along the way. It ends by returning to the first record and displaying its data. The output produced by RecordsetNavigation is shown in Figure 28.6.

Obviously, this is a trivial example meant to demonstrate how easily ADO recordsets can be navigated. As a developer, you're free to work with any record in the recordset, moving up and down the rows as needed.

Recordsets support the concept of a current record pointer. Only one record at a time within a recordset is current. When you make changes to a recordset or navigate through its rows, your code affects only the current record.

The RecordsetNavigation procedure also demonstrates two methods for referencing individual fields within a record: using the bang operator (!) and the Fields collection. After moving to a row, individual fields are referenced as members of the recordset. Access works on just one record at a time, so any reference to a field evaluates to the field within the current record.

Public Sub Use_EOF_BOF()

    Dim adRs As ADODB.Recordset

    Set adRs = New ADODB.Recordset
    adRs.ActiveConnection = CurrentProject.Connection
    adRs.CursorType = adOpenStatic

    adRs.Open "SELECT * FROM tblCustomers " _
        & "WHERE State = 'NY' " _
        & "ORDER BY Company;"

    Debug.Print "RecordCount: " & adRs.RecordCount

    If adRs.BOF And adRs.EOF Then
        Debug.Print "No records to process"
        Exit Sub
    End If

    Do Until adRs.EOF
        Debug.Print adRs!Company
        adRs.MoveNext
    Loop

    adRs.MoveLast

    Do Until adRs.BOF
        Debug.Print adRs!Company
        adRs.MovePrevious
    Loop

    adRs.Close
    Set adRs = Nothing

End Sub

Public Sub UseRecordCount()

  Dim adRs As ADODB.Recordset
  Dim lCnt As Long

  Set adRs = New ADODB.Recordset
  adRs.ActiveConnection = CurrentProject.Connection
  adRs.CursorType = adOpenStatic

  adRs.Open "SELECT * FROM tblCustomers;"

  Do While Not adRs.EOF
    lCnt = lCnt + 1
    Debug.Print "Record " & lCnt & " of " & adRs.RecordCount
    adRs.MoveNext
  Loop

  adRs.Close
  Set adRs = Nothing

End Sub

If adRs.BOF And rs.EOF Then
    Debug.Print "No records to process"
    Exit Sub
End If

The DAO DBEngine object

The DBEngine object, the object representing the ACE engine, is at the very top of the DAO hierarchy. It isn't a member of any collection, and all collections are children of DBEngine. There is only one instance of this object, and it's one of the few data access objects that you can't create yourself. You open the DBEngine object when you start Access and issue a DAO operation. It has relatively few properties and methods. For property changes to take effect, they must be issued before a data access object operation has been performed; otherwise, you'll receive an error. Because DBEngine is at the top of the hierarchy, you almost always begin a DAO code sequence with DBEngine.

The DAO Workspace object

A Workspace object represents an open, active session for each user working with Access. All databases are opened within a workspace, either as a default database session or one that has been created using the CreateWorkspace method of the DBEngine object.

Security is also implemented from Workspace objects (but, only for the MDB file format). The security methods available to Workspace objects allow you to create your own security interfaces and routines. If necessary, you can create users or groups using the CreateUser or CreateGroup methods of the Workspace object.

The DAO Database object

A Database object represents a data source and is analogous to an ADO Connection object. Access is able to directly open a number of different database formats. When you work directly with the ACE or Jet database engines, your database could be any number of sources, such as another ACCDB or an ODBC data source. The distinguishing feature is how you set your database object variables.

The following code refers to the currently open Access database:

Dim daDb As DAO.Database
Set daDb = CurrentDb

CurrentDb is a method of the Access Application object, which represents the entire Access environment and all its objects. CurrentDb is a fast, easy way to access the database that the user is currently working with.

It's also possible to open an Access database outside the current database:

Dim daDb As DAO.Database
Set daDb = OpenDatabase("C:Northwind.mdb")

Notice that the OpenDatabase method accepts the path to an existing MDB or ACCDB file. The OpenDatabase method may fail, depending on whether the external Access database is available, or whether its current state prevents opening from another Access application.

As with ADO objects, be sure to prefix DAO object type declarations with DAO so that Access is clear as to which library to use when setting up the object.

The DAO TableDef object

The DAO TableDef object represents a table in an Access database. The table may be local or linked to the current database. The following procedure (which is included in the Chapter28.accdb example database) creates a new table named MyTempTable, adds three text fields to it, and adds the table to the current database's TableDefs collection.

Public Sub CreateNewTableDef()

  Dim daDb As DAO.Database
  Dim daTdf As DAO.TableDef

  Const sTABLENAME As String = "MyTempTable"

  Set daDb = Application.CurrentDb

 'Delete an existing table, but ignore the error
 'if table doesn't exist
  On Error Resume Next

    daDb.TableDefs.Delete sTABLENAME
  On Error GoTo 0

  ' Create a new TableDef object:
  Set daTdf = daDb.CreateTableDef(sTABLENAME)

  With daTdf
      ' Create fields and append them to the TableDef
      .Fields.Append .CreateField("FirstName", dbText)
      .Fields.Append .CreateField("LastName", dbText)
      .Fields.Append .CreateField("Phone", dbText)
  End With

  ' Append the new TableDef object to the current database:
  daDb.TableDefs.Append daTdf

  daDb.Close
  Set daDb = Nothing

End Sub

Running this code in the Chapter28.accdb database creates a new table named MyTempTable, a permanent addition to the database. Notice that the CreateNewTableDef procedure deletes this table if it exists, before creating it as a new TableDef. Access won't be able to append the new TableDef object to its TableDefs collection if a table with the same name already exists in the database.

TableDef objects are stored in the TableDefs collection. The following procedure displays the names of all TableDef objects (including hidden and system tables) in the current database:

Public Sub DisplayAllTableDefs()

  Dim daDb As DAO.Database
  Dim daTdf As DAO.TableDef

  Set daDb = CurrentDb

  With daDb

    Debug.Print .TableDefs.Count & _
      " TableDefs in " & .Name

    For Each daTdf In .TableDefs
       Debug.Print , daTdf.Name
    Next daTdf
  End With

  daDb.Close
  Set daDb = Nothing

End Sub

The DAO QueryDef object

A QueryDef object represents a saved query in an Access database. Using VBA code, you can point a QueryDef object variable at an existing query (or create a new query), and change the query's SQL statement, populate parameters used by the query, and execute the query. The query could be a select query that returns a recordset, or an action query that modifies code in the tables underlying the query.

Creating a QueryDef in code is similar to creating a TableDef except that the new QueryDef doesn't have to be explicitly appended to the database's QueryDefs collection. The CreateNewQueryDef procedure shown here creates a new QueryDef that queries records from the Customers table:

Public Sub CreateNewQueryDef()

  Dim daDb As DAO.Database
  Dim daQdf As DAO.QueryDef

  Const sQRYNAME As String = "MyQueryDef"

  Set daDb = CurrentDb

  Set daQdf = daDb.CreateQueryDef(sQRYNAME, _
    "SELECT * FROM tblCustomers;")

  daDb.Close
  Set daDb = Nothing

End Sub

In fact, as soon at the CreateQueryDef method is executed, Access adds the new QueryDef to the database. You must explicitly delete the QueryDef if you don't want it to appear in the Navigation pane:

CurrentDb.TableDefs.Delete "QueryDefName"

You could, if desired, create a QueryDef without a name. In this case, the new QueryDef is not saved and does not show up in the Navigation pane. This technique might be useful, for instance, if you're filling a combo box or list box with data and you don't want to create a permanent QueryDef because the criteria changes every time the code is executed.

One time-honored advanced Access technique is dynamically changing an existing QueryDef object's SQL statement. Once the SQL property has been changed, the query returns the recordset specified by the new SQL statement:

Public Sub ChangeQueryDefSQL()

  CurrentDb.QueryDefs("MyQueryDef").SQL = _
    "SELECT * FROM tblProducts;"

End Sub

Notice that the ChangeQueryDefSQL procedure doesn't declare any object variables, such as daDb or daQdf, to refer to the Database or QueryDef. Instead, the procedure uses CurrentDb to refer to the Database and accesses the SQL property directly on the object returned by the QueryDefs property. It is advisable to use object variables for longer procedures, but for short procedures like this one, using CurrentDb directly is easier and can actually improve readability.

It's very easy to populate a DAO Recordset object directly from a QueryDef (see the next section for more on the Recordset object). Notice how much simpler this procedure is than the equivalent ADO process:

Public Function GetRecordset() As DAO.Recordset

  Dim daRs As DAO.Recordset
  Dim daQdf As DAO.QueryDef

  Set daQdf = CurrentDb.QueryDefs("MyQueryDef")

  'Open Recordset from QueryDef.
  Set daRs = daQdf.OpenRecordset(dbOpenSnapshot)

  daRs.MoveLast
  Debug.Print "Number of records = " & daRs.RecordCount

  Set GetRecordset = daRs

End Sub

The locally declared Recordset object (daRs) is assigned to the function just before the function ends. This is one way for a procedure to build recordsets without having to duplicate the code setting up the recordset and running the QueryDef every place a recordset is needed by an application.

The DAO Recordset object

Recordset objects are declared and set to a particular table, query, or ODBC data source within your application. Using a Recordset object's methods you can update, edit, and delete records, move forward and backward within the recordset, or locate specific records using the Find and Seek methods.

A Recordset object can be a Table, a Dynaset, or a Snapshot type; the type you specify depends on your needs. For example, suppose you only wanted to scan through a table to search for a particular value of a field. A Snapshot, which is a read-only view of your data, would probably be a good choice. Or maybe you'd like to query a table on the fly, but the query depends on user input. In this case, you might build a SQL statement based on an input value, and use the SQL statement to build a Dynaset-type recordset.

You specify the type of recordset using the dbOpenTable, dbOpenDynaset, and dbOpenSnapshot constants as arguments of the OpenRecordset method of a Database object. The following example shows how to open a Snapshot-type recordset based on a SQL string.

Dim daDb As DAO.Database
Dim daRs As DAO.Recordset
Dim sSql As String
sSql = "SELECT * FROM tblCustomers;"
Set daDb = CurrentDb
Set daRs = daDb.OpenRecordset(sSql, dbOpenSnapshot)

If you don't explicitly choose a type of Recordset, Access uses what it believes to be the most efficient method. You can't open an ODBC data source using the dbOpenTable option. Instead, you must use the dbOpenDynaset and dbOpenSnapshot constants.

As you'll see in many different places in this book, there are a number of different ways to open DAO recordsets. The following procedure illustrates just one of these techniques. In this particular example, the recordset is created directly against tblCustomers, and each field in every row in the table is displayed in the debug window (the Field object and Fields collection are discussed in the next section):

Public Sub OpenDAORecordset()

  Dim daDb As DAO.Database
  Dim daRs As DAO.Recordset
  Dim i As Long

  Set daDb = CurrentDb

  'Open recordset directly against a table:
  Set daRs = daDb.OpenRecordset("tblCustomers")

  Debug.Print "Table-type recordset: " & daRs.Name

  ' Enumerate records.
  Do While Not daRs.EOF
    For i = 0 To daRs.Fields.Count - 1
        Debug.Print daRs.Fields(i).Name & ": " & daRs.Fields(i).Value
    Next i

    Debug.Print

    daRs.MoveNext
  Loop

  daRs.Close
  Set daRs = Nothing
  Set daDb = Nothing

End Sub

The DAO Field objects (recordsets)

Field objects within recordsets represent a column of data from a table or returned by a query. Recordset Field objects differ from their TableDef and QueryDef counterparts in that they actually contain a data value. Each TableDef object contains a Fields collection containing the data held within the table represented by the TableDef.

You'll see many, many references to DAO (and ADO) fields in this book, so there isn't much to discuss at this point. In the meantime, it's enough to know that the DAO Field object supports many more properties than are visible in the Access Table Designer. The Chapter28.accdb example database includes the following procedure that enumerates all the “valid” properties of the Company field in tblCustomers:

Public Sub DisplayFieldProperties()

  Dim daDb As DAO.Database
  Dim daTdf As DAO.TableDef
  Dim daFld As DAO.Field
  Dim daProp As DAO.Property

  Set daDb = CurrentDb
  Set daTdf = daDb.TableDefs("tblCustomers")

  Set daFld = daTdf.Fields("Company")

  Debug.Print "Properties in Company field:"

  For Each daProp In daFld.Properties
    On Error Resume Next
      Debug.Print Space(2) & daProp.Name & " = " & daProp.Value
    On Error GoTo 0
  Next daProp

  daDb.Close

End Sub

Not every property associated with a Field object is valid at a particular time. Some properties are set only after the field contains data, or when the field is involved in an index. For example, the Value property of the Field object cannot be referenced directly from code. Instead, you set or get the value of a field only through the field's membership in a Recordset object. The On Error Resume Next statement allows this code to run, in spite of invalid properties. The errors that may occur when invalid properties are referenced by this code are ignored.

Updating fields in a record using ADO

Use the AfterUpdate event procedure to update LastSalesDate (see Figure 28.9). This procedure uses ADO syntax to operate directly on tblCustomers.

The programming syntax used to access and manipulate the data in an Access database is ADO. ADO defines a number of different objects, each with a set of properties and methods for performing a variety of data-oriented operations.

ADO is not a programming language; instead, it's a VBA syntax specifically designed for data access. Syntax simply refers to the words and phrases you use in your VBA code to accomplish a particular task.

ADO is a versatile means of accessing data from various locations. The examples you've seen so far show you how to use Access to update data in a local Access database. All tables, queries, forms, and reports are stored in a single Access database file located either in a folder on your desktop or on a file server. But Access, as a generic database development tool, can interact with all kinds of databases. You can develop forms and reports in one Access database that get their data from another Access database that may be on your local desktop or on a remote file server. You can even link to non-Access server databases, like Oracle and SQL Server, just as easily as you can link to an Access database.

As a data access interface, ADO allows you to write programs to manipulate data in local or remote databases. Using ADO, you can perform database functions including querying, updating, data-type conversion, indexing, locking, validation, and transaction management.

Here is a fragment of a procedure showing how to use the ADO Recordset object to open a table:

  Dim adRs As ADODB.Recordset

  Set adRs = New ADODB.Recordset

  adRs.ActiveConnection = CurrentProject.Connection
  adRs.Source = "tblCustomers"
  adRs.CursorType = adOpenDynamic
  adRs.LockType = adLockOptimistic

  adRs.Open

The ADO Recordset object provides the Open method to retrieve data from a table or query. A recordset is simply a set of records from a database table or the set of records returned by a query.

The Open method has four parameters:

• Source: The data source to open. Source can be the name of a table (as in this example), the name of a query, or a SQL statement that retrieves records. When referencing a table, the table can be a local or linked table.
• ActiveConnection: A connection to a database. A connection is a communication line into the database. CurrentProject.Connection refers to the current Access database.
• CursorType: A cursor is a pointer, or set of pointers, to records. Think of a cursor the way ADO keeps track of records. Depending on the property settings used to retrieve data, ADO cursors can move only forward through records (adOpenForwardOnly) or permit forward and backward movement (adOpenDynamic). A dynamic cursor (adOpenDynamic) allows movement in both directions, while adOpenForwardOnly permits only forward movement. (The CursorType property is explained in detail in the “Understanding CursorType” section, earlier in this chapter.)
• LockType: Determines how ADO locks records when updating. adLockOptimistic allows other users to work with a record that is locked by the ADO code, while adLockPessimistic completely locks other users out of the record while changes are made to the record's data.

This same ADO statement can be rewritten in a somewhat more condensed fashion:

  Dim adRs As ADODB.Recordset

  Set adRs = New ADODB.Recordset

  adRs.Open "tblCustomers", CurrentProject.Connection, _
    adOpenDynamic, adLockOptimistic

In this example, the recordset properties are set as part of the Open statement. Either syntax is correct; it's completely the choice of the developer. Also, because we are directly accessing the table, there is no way to specify an ORDER BY for the data. The data is likely to be returned in an unpredictable order.

Here is another example extracting a single record, based on a CustomerID:

Dim adRs As ADODB.Recordset

Set arRs = New ADODB.Recordset

adRs.ActiveConnection = CurrentProject.Connection
adRs.Source = _
  "SELECT * FROM tblCustomers WHERE CustomerID = 17"
adRs.CursorType = adOpenDynamic
adRs.LockType = adLockOptimistic

adRs.Open

Notice that, rather than specifying a table in Figure 28.9, the Source property is a SQL SELECT statement. The SQL statement used to extract records returns a single record, based on the CustomerID. In this case, because the LockType property is set to adLockOptimistic, the user can change the data in the record.

Both CursorType and LockType are optional. If you don't specify a CursorType or LockType, ADO creates the recordset as an adOpenForwardOnly/adLockReadOnly type of recordset by default. This type of recordset is not updatable. If you need to make changes to the data in the recordset, you need an understanding of the various CursorType and LockType combinations and how they affect the capabilities of a recordset.

When you use ADO, you interact with data almost entirely through Recordset objects. Recordsets are composed of rows containing fields, just like database tables. Once a recordset has been opened, you can begin working with the values in its rows and fields.

You've seen recordsets many times in this book. The records returned by a query are delivered as a recordset. Actually, when you open an Access table, Access arranges the table's records as a recordset and presents it in Datasheet view. You never really “see” an Access table—you see only a representation of the table's data as a recordset displayed in Datasheet view.

When you open an updatable recordset—by using the adOpenDynamic or adOpenKeySet cursor type and specifying the adLockOptimistic lock type—the recordset opens in edit mode.

One major difference between a table open in Datasheet view and a recordset is that a recordset provides no visual representation of the data it contains. A datasheet provides you with rows and columns of data, and even includes column headings so you know the names of the fields in the underlying table.

A recordset exists only in memory. There is no easy way to visualize the data in a recordset. As a developer you must always be aware of the field names, row count, and other data attributes that are important to your application.

When you are working with datasheets and recordsets, only one record is active. In a datasheet the active record is indicated by a color difference in the row. Recordsets have no such visual aid, so you must always be aware of which record is current in a recordset.

Fortunately, both ADO and DAO provide a number of ways to keep track of records in a recordset, and different techniques for moving around within a recordset. It's also quite easy to learn the field names in a recordset and to modify the data within each field.

This chapter (and many of the chapters in this book) demonstrates many of the data management techniques available through the VBA language. As an Access developer, you'll almost certainly learn new and more effective ways to work with data every time you work on an Access application.

Before you change data in any of the recordset's fields, however, you need to make sure that you're in the record you want to edit. When a recordset opens, the current record is the first record in the set. If the recordset contains no records, the recordset's EOF property is True.

To update a field in the current record of the recordset, in an ADO recordset, you simply assign a new value to the field. When using DAO, you must execute the Recordset object's Edit method before assigning a new value. In the Form_AfterUpdate procedure in Figure 28.9, you assign the value of txtSaleDate on the frmSales form to the recordset's LastSaleDate field.

After you change the record, use the recordset's Update method to commit the record to the database. The Update method copies the data from the memory buffer to the recordset, overwriting the original record. The entire record is replaced, not just the updated field(s). Other records in the recordset, of course, are not affected by the update.

Changes to an ADO recordset are automatically saved when you move to another record or close the recordset. In addition, the edited record is also saved if you close a recordset or end the procedure that declares the recordset or the parent database. However, you should use the Update method for better code readability and maintainability.

Use the record's CancelUpdate method to cancel pending changes to an ADO recordset. If it's important to undo changes to a record, you must issue the CancelUpdate method before moving to another record in an ADO recordset because moving off of a record commits the change, and an undo is no longer available.

The rsContacts.Close statement near the end of the Form_AfterUpdate procedure closes the recordset. Closing recordsets when you're done with them is good practice. In Figure 28.9, notice also that the Recordset object is explicitly set to nothing (Set rsContacts = Nothing) to clear the recordset from memory. Omitting this important step can lead to “memory leaks” because ADO objects tend to persist in memory unless they're explicitly set to Nothing and discarded.

Updating a calculated control

In the frmSales example, the txtTaxAmount control displays the tax to collect at the time of the sale. The tax amount's value is not a simple calculation. The tax amount is determined by the following items:

• The sum of the item amounts purchased that are taxable
• The customer's tax rate in effect on the sale date
• The value in txtOtherAmount and whether the txtOtherAmount is a taxable item

When the user changes information for the current sale, any one or all three of these factors can change the tax amount. The tax amount must be recalculated whenever any of the following events occur in the form:

• Adding or updating a line item
• Deleting a line item
• Changing the buyer to another customer
• Changing txtTaxLocation
• Changing txtOtherAmount

You use VBA procedures to recalculate the tax amount when any of these events occurs.

Recalculating a control when updating or adding a record

Figure 28.10 shows the code for adding or updating a line item on frmSales.

A single event can handle recalculating the tax amount when new line items are added or when a line item is changed—when an item's price or quantity is changed, for example. In any case, you can use the subform's AfterUpdate event to update the sales tax. AfterUpdate occurs when a new record is entered or when any value is changed for an existing record.

The Form_AfterUpdate procedure for fsubSalesLineItems executes when a line item is added to the subform, or when any information is changed in a line item. The Form_AfterUpdate procedure recalculates the tax amount control (txtTaxAmount) on frmSales. The dTaxRate variable holds the customer's tax rate (the value of txtTaxRate on frmSales) and cTaxAmount stores the value returned by the CalcTax() function. CalcTax() calculates the actual tax amount. When the After_Update procedure calls CalcTax(), it passes two parameters: the value of dTaxRate and the current line item's invoice number (Me.InvoiceNumber). Figure 28.11 shows the CalcTax() function.

CalcTax() uses ADO syntax to create a recordset that sums the quantities and prices for the taxable items in tblSalesLineItems for the current sale. The function receives two parameters: the tax rate (dTaxPercent) and the invoice number (lInvoiceNum). The ADO code checks to see whether the recordset returned a record. If the recordset is at the end of the field (EOF), the recordset did not find any line items for the current sale—and CalcTax returns 0. If the recordset does contain a record, the return value for CalcTax is set to the recordset's Subtotal field times the tax rate (dTaxPercent).

At the end of a procedure that calls CalcTax, shown in Figure 28.12, txtTaxAmount is set to the cTaxAmount value.

When the Buyer, Tax Location, or Tax Rate controls are changed in frmSales, you use the AfterUpdate event for the individual control to recalculate the tax amount. Figure 28.12 shows the code for the txtTaxRate_AfterUpdate event.

The logic implemented in txtTaxRate_AfterUpdate is identical to the logic in fsubSalesLineItems_AfterUpdate. In fact, you can use the same code for the Buyer and Tax Location controls as well. The only difference between the code in Figure 28.10 and the code in Figure 28.12 is that the procedure in Figure 28.10 runs whenever a change occurs in the sales line items subform, whereas the code in Figure 28.12 runs whenever a change is made to txtTaxRate on the main form.

Checking the status of a record deletion

Use the form's AfterDelConfirm event to recalculate the txtTaxAmount control when deleting a line item. The form's AfterDelConfirm event (shown in Figure 28.13) is similar to the code for the subform's AfterUpdate event. Notice, however, that txtTaxAmount on the main sales form is set by this procedure, even though this code runs in fsubSalesLineItems subform embedded on frmSales.

Access always confirms deletions initiated by the user. Access displays a message box asking the user to confirm the deletion. If the user affirms the deletion, the current record is removed from the form's recordset and temporarily stored in memory so that the deletion can be undone if necessary. The AfterDelConfirm event occurs after the user confirms or cancels the deletion. If the BeforeDelConfirm event isn't canceled, the AfterDelConfirm event occurs after the delete confirmation dialog box is displayed. The AfterDelConfirm event occurs even if the BeforeDelConfirm event is canceled.

The AfterDelConfirm event procedure returns status information about the deletion. Table 28.2 describes the deletion status values.

Status value	Description
acDeleteOK	Deletion occurred normally.
acDeleteCancel	Deletion canceled programmatically.
acDeleteUserCancel	User canceled deletion.

The Status argument for the AfterDelConfirm event procedure can be set to any of these values within the procedure. For example, if the code in the AfterDelConfirm event procedure determines that deleting the record may cause problems in the application, the Status argument should be set to acDeleteCancel:

If <Condition_Indicates_a_Problem> Then
  Status = acDeleteCancel
  Exit Sub
Else
  Status = acDeleteOK
End If

The Status argument is provided to enable your VBA code to override the user's decision to delete a record if conditions warrant such an override. In the case that Status is set to acDeleteCancel, the copy of the record stored in the temporary buffer is restored to the recordset, and the delete process is terminated. If, on the other hand, Status is set to acDeleteOK, the deletion proceeds and the temporary buffer is cleared after the user moves to another record in the recordset.

Public Sub UpdateTaxRate(frmMain As Form, _
  dTaxRate As Double, _
  lInvoiceNumber As Long)

  Dim cTaxAmount As Currency

  cTaxAmount = CalcTax(dTaxRate, lInvoiceNumber)

  frmMain.txtTaxAmount.Value = cTaxAmount
End Sub

Private Sub txtTaxRate_AfterUpdate()

  UpdateTaxRate Me, CDbl(Nz(Me.txtTaxRate.Value, 0)), _
    Me.InvoiceNumber.Value

End Sub

Private Sub Form_AfterUpdate()

  UpdateTaxRate Me.Parent, _
    CDbl(Nz(Me.Parent.txtTaxRate.Value, 0)), _
    Me.InvoiceNumber.Value

End Sub

Adding a new record

You can use ADO to add a record to a table just as easily as updating a record. Use the AddNew method to add a new record to a table. The following shows an ADO procedure for adding a new customer to tblCustomerContacts:

Public Sub AddNewContact(sFirstName As String, sLastName As String)

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

    adRs.Open "tblCustomerContacts", CurrentProject.Connection, _
        adOpenDynamic, adLockOptimistic

    With adRs
        .AddNew  'Add new record

        'Add data:
        .Fields("LastName").Value = sLastName
        .Fields("FirstName").Value = sFirstName

        .Update  'Commit changes
    End With

    adRs.Close
    Set adRs = Nothing

End Sub

As you see in this example, using the AddNew method is similar to using ADO to edit recordset data. AddNew creates a buffer for a new record. After executing AddNew, you assign values to fields in the new record. The Update method adds the new record to the end of the recordset, and then to the underlying table.

Deleting a record

To remove a record from a table, you use the ADO method Delete. The following code shows an ADO procedure for deleting a record from tblCustomerContacts.

Public Sub DeleteContact(ContactID As Long)

    Dim adRs As ADODB.Recordset

    Dim sSQL As String

    Set adRs = New ADODB.Recordset

    sSQL = "SELECT * FROM tblCustomerContacts " _
        & "WHERE ID = " & ContactID & ";"

    adRs.Open sSQL, CurrentProject.Connection, _
        adOpenDynamic, adLockOptimistic

    With adRs
        If Not .EOF Then
            .Delete  'Delete the record
        End If
    End With

    adRs.Close
    Set adRs = Nothing

End Sub

Deleting records using ADO doesn't trigger the deletion confirmation dialog box. Generally speaking, changes made to data with ADO code are not confirmed because confirmation would interrupt the user's workflow. This means that, as the developer, you're responsible for making sure that deletions are appropriate before proceeding. Once the record is deleted, there is no way to undo the change to the underlying table. Access does, however, still enforce referential integrity. If you attempt to delete a record that violates referential integrity, you'll get an error.

Deleting related records in multiple tables

When you write ADO code to delete records, you need to be aware of the application's relationships. The table containing the record that you're deleting may be participating in a one-to-many relationship with another table.

Take a look at the relationships diagram (see Figure 28.14) for the tables used in the frmSales example. tblSales has two dependent tables associated with it: tblSalesLineItems and tblSalesPayments.

The Edit Relationships dialog box shows how the relationship is set up between tblSales and tblSalesLineItems. The relationship type is a one-to-many (1:M) and referential integrity is enforced. A one-to-many relationship means that each record in the parent table (tblSales) may have one or more records in the child table (tblSalesLineItems). Each record in the parent table must be unique—you can't have two sales records with exactly the same InvoiceNumber, SalesDate, and other information.

In a one-to-many relationship each child record (in tblSalesLineItems) must be related to one record (and only one record) in the parent table (tblSales). But each sales record in tblSales may be related to more than one record in tblSalesLineItem.

When you enforce referential integrity on a one-to-many relationship, you're telling Access that a record in tblSales can't be deleted if records with the same invoice number value exist in tblSalesLineItems. If Access encounters a delete request that violates referential integrity, Access displays an error message and the delete will be canceled, unless cascading deletes have been enabled in the Edit Relationships dialog box (refer to Figure 28.14).

As you'll recall from Chapter 4, you have the option of setting Cascade Update Related Fields and Cascade Delete Related Fields in the Edit Relationships dialog box. By default, these options are not enabled—and for good reason. If cascading deletes is turned on, when you use VBA code to delete a sales record, all the related records in tblSalesLineItems and tblSalesPayments are also deleted. Depending on the situation, this may or may not be a good thing. In the case of a canceled sales order, there is probably no harm done by deleting the unsold sales line items. However, when working on a canceled order where payment has been made, deleting the customer's payment history may be an issue. Surely, they'll expect a refund of payments made on the order, but Access just deleted the payment records.

In most cases, you're far better off using an Active field (Yes/No data type) to indicate a parent record's status. The Active field is set to Yes when the order is placed, and only set to No when the order has been canceled or completed. You might also consider adding a CancellationDate field to tblSales, and set it to the date on which an order is canceled. If CancellationDate is null, the order has not been canceled.

When you write ADO code to delete a record, you need to first check to see whether there are any one-to-many relationships between the table containing the record to delete and any other tables in the database. If there are dependent tables, the records in the dependent tables need to be deleted before Access allows you to delete the record in the parent table.

Fortunately, you can write a single procedure to delete records in both the dependent table(s) and the parent table. Figure 28.15 shows the code for the cmdDelete command button in frmSales.

The cmdDelete_Click event procedure deletes records in tblSalesPayments, tblSalesLineItems, and tblSales that have an invoice number matching the current invoice number.

The first statement in cmdDelete_Click (If Me.NewRecord Then) uses the NewRecord property to see whether the current sales record is new. If the record is new, Me.Undo rolls back changes to the record. If the current record is not new, the procedure displays a message box to confirm that the user really wants to delete the record. If the user clicks the Yes button, the procedure deletes the record from the tables.

Two constants, sSQL_DELPMTS and sSQL_DELLINE, hold the SQL statements for locating and deleting records in tblSalesPayments and tblSalesLineItems, respectively, with an invoice number that matches the invoice number on frmSales. The invoice number is concatenated to the end of the constants, and they're passed as a parameter to the Execute method of CurrentDb. You can pass either the name of a query or a SQL statement as a parameter to the Execute method. The Execute method simply runs the specified query or SQL statement.

After the tblSalesPayments and tblSalesLineItems records are deleted, the tblSales record can then be deleted.