ADO and Record Sets

Introduction to Record Sets in ADO

Overview

As you may know already, the series of records of a table or query is called a record set. To support record sets, the ADO library is equipped with a class named Recordset. This class in turn has many properties and methods.

Practical Learning: Introducing ADO's Record Sets

1. Start Microsoft Access
2. In the list of files, click Bethesda Car Rental1 from the previous lesson
3. On the Ribbon, click Create and, in the Forms section, click Form Design
4. Using the Property Sheet, change the following characteristics:
   Auto Center: Yes
   Navigation Buttons: No
5. Right-click the form and click Form Header/Footer
6. Save the form as UpdateRentalOrder
7. Design the form as follows:

   

   Control		Name	Caption	Other Properties
   Text Box		txtReceiptNumber	Receipt #:
   Button		cmdFind	Find
   Text Box		txtEmployeeNumber	Employee #:
   Text Box		txtEmployeeName
   Text Box		txtCustomerFirstName	First Name:
   Text Box		txtTagNumber	Tag Number:
   Text Box		txtCustomerLastName	Last Name:
   Text Box		txtMake	Make:
   Text Box		txtCustomerAddress	Address:
   Text Box		txtModel	Model:
   Text Box		txtCustomerCity	City:
   Text Box		txtDoorsPassengers	Doors/Seats:
   Text Box		txtCustomerState	State:
   Combo Box		cbxConditions	Condition:	Column Count: 1 Column Widths: 1" List Width: 1" Row Source: "Excellent";"Good Shape";"Needs Repair" Row Source Type: Value List
   Text Box		txtCustomerZIPCode	ZIP Code:
   Combo Box		cbxTankLevels	Tank Levels:	Column Count: 1 Column Widths: 1" List Width: 1" Row Source: "Empty";"1/4 Empty";"Half";"3/4 Full";"Full" Row Source Type: Value List
   Text Box		txtMileageStart	Mileage Start:
   Text Box		txtMileageEnd	End:
   Text Box		txtTotalMileage	Total Miles:	Format: General Number
   Text Box		txtStartDate	Start Date:
   Text Box		txtEndDate	End:
   Text Box		txtTotalDays	Total Days:	Format: General Number
   Text Box		txtRateApplied	Rate Applied:
   Text Box		txtSubTotal	Sub Total:	Control Source: =Nz([txtRateApplied])*Nz([txtTotalDays])
   Text Box		txtTaxRate	Tax Rate:	Default Value: 0.075
   Text Box		txtTaxAmount	Tax Amount:	Control Source: =CLng(Nz([txtSubTotal])*Nz([txtTaxRate])*100)/100
   Combo Box		cbxOrdersStatus	Orders Status:	Column Count: 1 Column Widths: 1" List Width: 1" Row Source: "Reserved";"Processing";"Rented - Car On Road";"Car Returned/Order Complete" Row Source Type: Value List
   Text Box		txtRentTotal	Rent Total:	Control Source: =Nz([txtSubTotal])+Nz([txtTaxAmount])
   Text Box		txtNotes	Notes:
   Button		cmdSubmit	Submit
   Button		cmdReset	Reset
   Button		cmdClose	Close

8. Right-click the Reset button and click Build Event...
9. Double-click Code Builder
10. Implement the event as follows:

    Private Sub cmdReset_Click()
        txtReceiptNumber = ""
        txtEmployeeNumber = ""
        txtEmployeeName = ""
        txtCustomerFirstName = ""
        txtCustomerLastName = ""
        txtCustomerAddress = ""
        txtCustomerCity = ""
        txtCustomerState = ""
        txtCustomerZIPCode = ""
        txtTagNumber = ""
        txtMake = ""
        txtModel = ""
        txtDoorsPassengers = ""
        cbxConditions = ""
        cbxTankLevels = ""
        txtMileageStart = ""
        txtMileageEnd = ""
        txtTotalMileage = ""
        txtStartDate = Date
        txtEndDate = Date
        txtTotalDays = ""
        txtRateApplied = ""
        cbxOrdersStatus = ""
        txtNotes = ""
    End Sub

11. In the Object combo box, select txtMileageEnd
12. In the Procedure combo box, select LostFocus
13. Implement the event as follows:

    Private Sub txtMileageEnd_LostFocus()
        If IsNull(txtMileageEnd) Then
            Exit Sub
        End If
        
        txtTotalMileage = CStr(CLng(txtMileageEnd) - CLng(txtMileageStart))
    End Sub

14. Close Microsoft Visual Basic and return to Microsoft Access

    

15. Close the form
16. When asked whether you want to save, click Yes
17. In the Navigation Pane, right-click UpdateRentalOrder and click Copy
18. Right-click any section of the Navigation Pane and click PasteForm Design
19. Type NewRentalOrder as the name of the new form
20. Click OK
21. In the Navigation Pane, right-click NewRentalOrder and click Design View
22. Change the design of the form as follows (you will mostly delete some controls that are not necessary, then you will adjust the design):

    

23. Right-click the Reset button and click Build Event
24. Change the code of the event as follows:

    Private Sub cmdReset_Click()
        txtEmployeeNumber = ""
        txtEmployeeName = ""
        txtCustomerFirstName = ""
        txtCustomerLastName = ""
        txtCustomerAddress = ""
        txtCustomerCity = ""
        txtCustomerState = ""
        txtCustomerZIPCode = ""
        txtTagNumber = ""
        txtMake = ""
        txtModel = ""
        txtDoorsPassengers = ""
        cbxConditions = ""
        cbxTankLevels = ""
        txtMileageStart = ""
        txtStartDate = Date
        txtRateApplied = ""
        cbxOrdersStatus = ""
        txtNotes = ""
    End Sub

25. Return to Microsoft Access
26. Close the form
27. When asked whether you want to save, click Yes

Creating a Record Set

As you may know already, the Microsoft Access Object Library (MAOL) is the default library of Microsoft Access. It is available whenever you start a database, and it is equipped with a class named Recordset. Because of this, when you declare a Recordset variable, you must indicate which one you are refering to: MAOL or ADO's. If you want the ADO's Recordset class, you must qualify its name with the ADODB namespace. Therefore, here is an example of declaring a Recordset variable:

Private Sub cmdCreateRecordset_Click()
    Dim rsEmployees As ADODB.Recordset
End Sub

Before using the record set, you must initialize its variable. You have many options. The primary way of using the Recordset variable is to allocate memory for it. This is done using the New operator. As mentioned for the Connection class, you have two possibilities. You can initialize the variable using the Set operator and assigning it the ADODB.Recordset class. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim rsEmployees As ADODB.Recordset

    Set rsEmployees = New ADODB.Recordset
End Sub

Or you can use the New operator when declaring the variable. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim rsEmployees As New ADODB.Recordset
End Sub

After initializing the variable, you have a record set that you can use as you see fit. After using the record set, you should (must) close it.

While a record set is active, it consumes resources. After using the record set, you should free the memory it was using so they can be made available to other objects of the computer. This is done by assing Nothing to the Recordset object. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim rsEmployees As ADODB.Recordset
    
    Set rsEmployees = New ADODB.Recordset
    
    . . .
    
    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

The Record Set of a Form or Report

If you declare a Recordset variable, in order to use it, you must indicate where and how it gets its records. You have many options.

A form is commonly used to show the record(s) of a table or query. A report is usually used to print the record(s) of a table or query. This means that the records of a form or report are considered a set. To support this set, the Form and the Report classes are equipped with a property named Recordset. Therefore, one way you can create or get a Recordset object is to retrieve the one from a form or a report.

To get the record set of a form or report, you can declare a variable of type Recordset, initialize the variable, get its record set, and then assign it to your Recordset variable. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = Me.Recordset
End Sub

Opening a Record Set

As seen above, you can use an object (form or report) that already has records and store those records in your Recordset object. In most or some cases, you will (must) explicitly indicate the source of records. To assist you with this, the Recordset class is equipped with a method named Open. Its syntax is:

Open Source, ActiveConnection, CursorType, LockType, Options

All these arguments are semi-optional. That is, except for the last, you should (must) provide a value for each before actually using the record set. You have various options. The most used two options are, either you pass the argument or you use one of the properties of the Recordset class to provide the necessary value. Based on this description, you can call the Open() method without passing any argument:

recordset.Open

A Record Set From an ADO Connection

We saw the syntax of the Execute method of the Connection class. In reality, the Connection.Execute() method is a function and it returns a Recordset object. In this case, its syntax is:

Function Execute(CommandText, RecordsAffected, Options) As Recordset

This means that, after calling the Execute method, you can get the Recordset object it returns and do what you want with it.

Characteristics of a Record Set in ADO

The Source of Records

As its name implies, a record set is a series of records. This means that the first piece of information you should (must) provide about a record set is where its record(s) is(are) coming from. If you are calling the Open() method of a Recordset object, you can pass the name of a table or query as the Source argument. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim rsEmployees As ADODB.Recordset
    
    Set rsEmployees = New ADODB.Recordset
    
    rsEmployees.Open "Employees"

    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

If you call the Recordset.Open() method without the Source argument, to help you specify the source, the Recordset class is equipped with a property named Source to which you can assign the name of the table or query. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim rsEmployees As ADODB.Recordset
    
    Set rsEmployees = New ADODB.Recordset
    
    rsEmployees.Open
    rsEmployees.Source = "Employees"

    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

The Connection to a Record Set

As seen in our introductory section, you need a connection to a database before doing anything on it. This is the role of the second argument of the Recordset class. We saw how to get the connection of the current database, using the CurrentProject static class that is equipped with a property named Connection. To specify that you want to use the connection of the current database, use it as the ActiveConnection argument of the Open method. Here is an example:

Private Sub cmdConnection_Click()
    Dim rsEmployees As ADODB.Recordset
        
    Set rsEmployees = New ADODB.Recordset
    
    rsEmployees.Open "Employees", CurrentProject.Connection
 
    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

You can also declare an ADODB.Connection variable, assign the connection of the current database, and use it as the ActiveConnection argument. Here is an example:

Private Sub cmdConnection_Click()
    Dim connector As ADODB.Connection
    Dim rsEmployees As ADODB.Recordset
        
    Set rsEmployees = New ADODB.Recordset
    Set connector = CurrentProject.Connection
    
    rsEmployees.Open "Employees", connector
    
    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

If you want to work on a database other than the one that is currently opened, declare a Connection variable and appropriately initiailize it, then pass its name as the ActiveConnection argument of the Recordset.Open() method. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim connector As ADODB.Connection
    Dim rsEmployees As ADODB.Recordset
    
    Set connector = New ADODB.Connection
    connector.Open "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = Exercise.accdb"
    
    Set rsEmployees = New ADODB.Recordset
    
    rsEmployees.Open "Employees", connector

    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

If you are planning to call the Open() method without specifying the ActivConnection, the Recordset class is equipped with a property named ActiveConnection. To use this approach, first assign the connection fo the ActiveConnection property of the Recordset variable. Here is an example:

Private Sub cmdCreateRecordset_Click()
    Dim connector As ADODB.Connection
    Dim rsEmployees As ADODB.Recordset
    
    Set connector = New ADODB.Connection
    connector.Open "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = Exercise.accdb"
    
    Set rsEmployees = New ADODB.Recordset
    rsEmployees.ActiveConnection = connector
    
    rsEmployees.Open "Employees"
    
    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

The Cursor of a Record Set

Normally, some operations don't require you to have access to all records, at least not all the time. When working on records, that is, when creating a Recordset object, you can specify a way to isolate a range of records and deal only with that range. The range of records that you select is called a cursor.

Because a cursor plays a tremendous role in a record set, there are different types of cursors. To support cursors, the Recordset class is equipped with a property named CursorType that is based on the CursorTypeEnum enumeration. Each type of cursor holds a constant value that is a member of this enumeration.

To specify the type of cursor you want to use, you can pass a member of the CursorTypeEnum enumeration as the third argument of the Recordset.Open() method. If you call the method without passing the third argument, to let you specify the type of cursor, the Recordset class is equipped with a property named CursorType. This means that you can specify the desired cursor to that property.

The available cursors and options are:

• A static cursor holds a constant set of records. Suppose you create a record set and open it. Also suppose that either you only or other people besides you are working on the same record set. You get to a record and start viewing it (or even working on it). After using that record, you move to another record, and you can do this back and forth as you wish. Suppose that, while doing this back and forth navigation, another person has accessed a record that is part of your record set and made a change. If using a static cursor, every time you visit the record set, it shows the same records the way they were when you opened the record set. It would not show the changes that have taken place. This is why it is called a static cursor. A static cursor is appropriate if you are not interested to know what changes have taken place ever since you opened the record set.
  In ADO, a static cursor is represented with the adOpenStatic constant. Here is an example of passing it as the third argument to the Record

  Private Sub cmdConnection_Click()
      Dim connector As ADODB.Connection
      Dim rsEmployees As ADODB.Recordset
          
      Set rsEmployees = New ADODB.Recordset
      Set connector = CurrentProject.Connection
      
      rsEmployees.Open "Employees", connector, adOpenStatic
      
      rsEmployees.Close
      Set rsEmployees = Nothing
  End Sub

  Here is an example where the type of cursor is assigned to the CursorType property:

  Private Sub cmdConnection_Click()
      Dim connector As ADODB.Connection
      Dim rsEmployees As ADODB.Recordset
          
      Set rsEmployees = New ADODB.Recordset
      Set connector = CurrentProject.Connection
      
      rsEmployees.Open "Employees", connector
      rsEmployees.CursorType = adOpenStatic
      
      rsEmployees.Close
      Set rsEmployees = Nothing
  End Sub

• A cursor is referred to as forward-only if it allows you to move forward through the records. Here is how it works. Suppose that you create a Recordset object and specify its records. Suppose that, while using the record set, you get to a record that was set as a starting. Also, suppose that either you only or other people besides you are working on the same record. If you make a change on the current record, the other people will be notified. If other people make a change on the current record, you also would know. After using that record, you move to the next. With the forward-only cursor, you cannot move back to a record you left already. This means that, even if you are still working on the record set, if there are changes performed on a record you left behind (for example, if another person who is working on the same record changes something on a record that you passed already), you cannot know and you cannot find out because you cannot go back to a record left behind. If this becomes a necessity, you can close the recordset and re-open it. A forward-only cursor is appropriate if you don't need to navigate back and forth among the records of a record set. Because of the way it works, if you access the RecordCount property of a forward-only cursor, it would produce -1.
  In ADO, this type or cursor is represented by the adOpenForwardOnly constant
• A cursor is called dynamic if it detects and shows all changes that are occurring in a record set, whether the changes are caused by you or by other people who are accessing the record set at the same time. This type of cursor is appropriate if you want to know, live, what is going on with the record set you are working with.
  In an ADO database, to specify a dynamic cursor, use the adOpenDynamic constant
• A key set cursor creates and saves a key for each record that has been modified since the record set was opened. If you access the record, the key is used to check the data of the record set.
  A key set cursor is created using the adOpenKeyset constant
• If you don't want to specify a cursor when creating a record set, you can use the adOpenUnspecified constant

The Lock Type

Imagine that, after creating a record set and working on it, you want to control who else can have access to the records of the set you are using. To exercise this control, you can create a "lock". This allows you, for example, to prevent other people from changing the records until you have finished with them.

To support locking, the ADO's Recordset class is equipped with a property named LockType. The available types are stored in an enumeration named LockTypeEnum. To specify the lock, pass the fourth argument of hte Recordset.Open method and specify it as a member of the LockTypeEnum enumeration.

There are different types of locks and each category is represented by a constant value and name. The available options are:

• When a computer connects to a database, its user may need to make changes to various records at the same time, such as deleting a range of records or changing many records at the same time (such as giving a raise to many employees), instead of making one change, then another, then another. For this type of scenario, when the user accesses the records, instead of monopolizing them and waiting for the user to finish an operation that could take long, you can download the records on the user's computer, and disconnect the user from the database. The user would then make the necessary changes. When the user is ready to commit the changes, you can then reconnect to the data source and submit the changes. This type of lock is referred to as batch optimistic. ADO supports this through a constant value named adLockBatchOptimistic. Here is an example of passing it as the fourth argument:

  Private Sub cmdConnection_Click()
      Dim connector As ADODB.Connection
      Dim rsEmployees As ADODB.Recordset
          
      Set rsEmployees = New ADODB.Recordset
      Set connector = CurrentProject.Connection
      
      rsEmployees.Open "Employees", connector, adOpenStatic, adLockBatchOptimistic
      
      rsEmployees.Close
      Set rsEmployees = Nothing
  End Sub

  The Recordset class provides an alternative in a property named LockType
• You may have a database that a few different people access at the same time. If the database is small enough, which is the case for a restricted environment, the likelihood of two people editing or updating the same record (at the same time) may be low. In this case, you can indicate that you want to lock the record only when necessary. In this case, you use what is referred to as optimistic locking. This is implemented using the adLockOptimistic value
• The above two options assume that you would lock many records to apply the indicated scenarios. If you prefer to lock one record at a time, you can use what is referred to as pessimistic locking. This is done using the adLockPessimistic constant
• The above three scenarios allow a user to edit and/or update the records that are included in the set. In some cases, you may want to prevent any editing or update on the records while the set is being accessed. In this case, you can set the records to read-only. To do this, you can lock the set using the adLockReadOnly lock
• If you don't want to specify the type of lock to use on a record set, specify the lock type as adLockUnspecified

The Type of Source of a Record Set

The last argument is used to identify the type of the Source argument. The values of this argument are members of an enumeration named CommandTypeEnum. If the source is a table as we have used it so far, this argument can be passed as adCmdTable. Here is an example:

Private Sub cmdConnection_Click()
    Dim connector As ADODB.Connection
    Dim rsEmployees As ADODB.Recordset
        
    Set rsEmployees = New ADODB.Recordset
    Set connector = CurrentProject.Connection
    
    rsEmployees.Open "Employees", _
 	   	     connector, _
	    	     adOpenStatic, _
	    	     adLockBatchOptimistic, _
	    	     adCmdTable
    
    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

So far, we have considered the Source factor as a SQL statement. ADO allows you to use the name of a table as the source. If you do this, the database engine would complete the Source with a SELECT statement.

If you pass the Source factor as the name of a table, then the last argument of the Open() method can be passed as adCmdTable. Here is an example:

Private Sub cmdTableReference_Click()
    Dim rstEmployees As ADODB.Recordset
    
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Open "Employees", _
                      Application.CodeProject.Connection, _
                      adOpenStatic, adLockOptimistic, adCmdTable
    
End Sub

Introduction to Fields

A record set is is made of records. These records are organized in columns or fields. To represent each field, the ADO library provides a class named Field. The fields of a record set are stored in a collection named Fields.

As is always the case for all collections, to locate an item, that is, a field, of the Recordset class, you can use either the index of a field or its name. For example, if you have a field named FirstName in a record set named rsEmployees, and if that field is the second column of the record, you can refer to it with any of the following:

rsEmployees(1)
rsEmployees.Item(1)
rsEmployees("FirstName")

One of the significant characteristics of a field is its name. It is represented by a property named Name. You can use it whenever you want to refer to the field by name.

Another valuable characteristic of a column is its value. This is represented in the Field class by a property named Value. You can refer to the value of a field based on its index or its name. Here are examples:

rsEmployees(1).Value
rsEmployees.Item(1).Value
rsEmployees("FirstName").Value

Data Entry in an ADO's Record Set

Data Entry Using the ADO's Recordset Class

The record set in ADO supports data entry the same way it is done in the Microsoft Access Object Library (MAOL) and DAO. Remember the way data entry is done in MAOL. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim dbExercise As Object
    Dim rsEmployees As Object
    
    Set dbExercise = CurrentDb
    Set rsEmployees = dbExercise.OpenRecordset("Employees")
    
    rsEmployees.AddNew
    rsEmployees("EmployeeNumber").Value = 924806
    rsEmployees("FirstName").Value = "Patricia"
    rsEmployees("LastName").Value = "Graham"
    rsEmployees("HourlySalary").Value = 18.85
    rsEmployees.Update
    
    MsgBox "A new employee has been hired.", _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
    
    Set rsEmployees = Nothing
    Set dbExercise = Nothing
End Sub

ADO provides more options. Because the ADO's Recordset class allows you to prevent record addition, before performing data entry, you can first check whether your Recordset object allows a new record to be added. The class is equipped with a method named Supports. Its syntax is:

Public Function Supports(ByVal CursorOptions As CursorOptionEnum) AS Boolean

The argument passed to this method is a member of the CursorOptionEnum enumeration. If you want to check whether the record set supports record addition, pass this argument as adAddNew. If the record set allows addition, the method returns True. Otherwise it returns False. After checking this, if the record set supports record addition, you can then assign each desired value to the appropriate column (as done in MAOL and DAO). After assigning the values, call the Update() method of the Recordset object. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim rsEmployees As ADODB.Recordset
    
    Set rsEmployees = New ADODB.Recordset
    rsEmployees.Open "Employees", _
                     CurrentProject.Connection, _
                     adOpenStatic, _
                     adLockOptimistic
    
    If rsEmployees.Supports(adAddNew) Then
        rsEmployees.AddNew
        rsEmployees("EmployeeNumber").Value = 597740
        rsEmployees("FirstName").Value = "Colin"
        rsEmployees("LastName").Value = "Ridge"
        rsEmployees("HourlySalary").Value = 20.05
        rsEmployees.Update
    End If
    
    MsgBox "A new employee has been hired.", _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
    
    rsEmployees.Close
    Set rsEmployees = Nothing
End Sub

Practical Learning: Adding a Record to a Record Set

1. In the Navigation Pane, right-click NewRentalOrder and click Design View
2. On the form, right-click the Close button and click Build Event...
3. Double-click Code Builder
4. Implement the event as follows:

   Private Sub cmdClose_Click()
   On Error GoTo cmdClose_ClickError
   
       DoCmd.Close
   
       Exit Sub
   
   cmdClose_ClickError:
       MsgBox "An error occured as follows." & vbCrLf & _
              "Error #: " & Err.Number & vbCrLf & _
              "Message: " & Err.Description, _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
               
       Resume Next
   End Sub

5. In the Object combo box, select cmdSubmit
6. Implement the event as follows:

   Private Sub cmdSubmit_Click()
   On Error GoTo cmdSubmit_ClickError
   
       Dim rsRentalOrders As ADODB.Recordset
       
       Set rsRentalOrders = New ADODB.Recordset
       rsRentalOrders.Open "RentalOrders", _
                           CurrentProject.Connection, _
                           adOpenStatic, _
                           adLockOptimistic
       
       With rsRentalOrders
           If .Supports(adAddNew) Then
               .AddNew
               .Fields("EmployeeNumber").Value = txtEmployeeNumber
               .Fields("CustomerFirstName").Value = txtCustomerFirstName
               .Fields("CustomerLastName").Value = txtCustomerLastName
               .Fields("CustomerAddress").Value = txtCustomerAddress
               .Fields("CustomerCity").Value = txtCustomerCity
               .Fields("CustomerState").Value = txtCustomerState
               .Fields("CustomerZIPCode").Value = txtCustomerZIPCode
               .Fields("TagNumber").Value = txtTagNumber
               .Fields("CarCondition").Value = cbxConditions
               .Fields("TankLevel").Value = cbxTankLevels
               .Fields("MileageStart").Value = txtMileageStart
               .Fields("StartDate").Value = txtStartDate
               .Fields("RateApplied").Value = CDbl(Nz(txtRateApplied))
               .Fields("TaxRate").Value = CDbl(Nz(txtTaxRate))
               .Fields("OrderStatus").Value = cbxOrdersStatus
               .Fields("Notes").Value = txtNotes
               .Update
           End If
       End With
       
       MsgBox "The new rental order has been processed and submitted.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
       
       rsRentalOrders.Close
       Set rsRentalOrders = Nothing
   
       cmdClose_Click
       Exit Sub
       
   cmdSubmit_ClickError:
       MsgBox "An error occurred when trying to create the new rental order. " & _
              "Please report the error as follows." & vbCrLf & _
              "Error #:     " & Err.Number & vbCrLf & _
              "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
               
       Resume Next
   End Sub

7. Implement the event as follows:

   Private Sub cmdClose_Click()
   On Error GoTo cmdClose_ClickError
   
       DoCmd.Close
   
       Exit Sub
   
   cmdClose_ClickError:
       MsgBox "An error occured as follows." & vbCrLf & _
              "Error #: " & Err.Number & vbCrLf & _
              "Message: " & Err.Description, _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
               
       Resume Next
   End Sub

SQL Data Entry in an ADO Record Set

The ADODB.Rrecordset.Open() method can take a SQL statement and execute it. Such as a statement can be an INSERT expression to create a record. This is one more way ADO supports data entry. Here is an example:

Private Sub cmdCreateEmployee_Click()
    Dim rsEmployees As ADODB.Recordset
    
    Set rsEmployees = New ADODB.Recordset
    rsEmployees.Open "INSERT INTO Employees(EmployeeNumber, FirstName, " & _
                     "                       LastName, HourlySalary)" & _
                     "VALUES(294057, 'Hermine', 'Thomason', 15.52);", _
                     CurrentProject.Connection, _
                     adOpenStatic, _
                     adLockOptimistic
    
    MsgBox "A new employee has been hired.", _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
    
    Set rsEmployees = Nothing
End Sub

Operations on a Record Set

Navigating Among Records

The Recordset class of the ADO library supports the ability to navigate among records. This is done using various methods:

• To move to the first record, you can call a method named MoveFirst. Here is an example of calling it:

  Private Sub cmdVideoAnalyze_Click()
      Dim rstVideos As ADODB.Recordset
      Dim fldEach As ADODB.Field
      
      Set rstVideos = New ADODB.Recordset
      rstVideos.Source = "Blah Blah Blah"
      rstVideos.ActiveConnection = Application.CodeProject.Connection
      rstVideos.CursorType = adOpenStatic
      rstVideos.LockType = adLockOptimistic
      rstVideos.Open
      
      rstVideos.MoveFirst
  
      For Each fldEach In rstVideos.Fields
          MsgBox fldEach.Value
      Next
      
      rstVideos.Close
      Set rstVideos = Nothing
  End Sub

• To move to the next record, you can call the MoveNext() record
• To move to the previous record, you can call the MovePrevious() method
• To move to the last record, you can call the MoveLast() method
• To move to a specific record based on its position, call a method named Move. It takes as argument the numeric position of the record

Using the techniques of data navigation, you can use conditional statements and loops to perform further operations.

Practical Learning: Navigating Among Records of a Record Set

1. The Form_NewRentalOrder module should still be opened.
   In the Object combo box, select txtEmployeeNumber
2. In the Procedure combo box, select LostFocus
3. Implement the event as follows:

   Private Sub txtEmployeeNumber_LostFocus()
   On Error GoTo txtEmployeeNumber_LostFocus_Error
   
       Dim rsEmployees As New ADODB.Recordset
       ' This variable will be used to find if the user
       ' entered a valid employee number
       Dim EmployeeFound As Boolean
       
       ' If there is no employee number, don't do anything
       If IsNull(txtEmployeeNumber) Then
           Exit Sub
       End If
       
       ' To start, assume that we haven't found an employee
       EmployeeFound = False
       
       rsEmployees.Open "Employees", _
                        CodeProject.Connection, _
                        adOpenStatic, _
                        adLockOptimistic, _
                        adCmdTable
   
       If IsNull(rsEmployees) Then
           MsgBox "Invalid employee number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
           Exit Sub
       Else
           ' We will refer to the recordset many times
           With rsEmployees
           ' Check each employee record from the first to the last
               Do While Not .EOF
                   ' Every time you get to the EmployeeNumber column,
                   ' if its value is the same as the one entered
                   ' in the Employee #...
                   If rsEmployees("EmployeeNumber").Value = txtEmployeeNumber Then
                       ' ... display its full name
                       txtEmployeeName = .Fields("FullName").Value
                       ' Make a note that we found the employee number
                       EmployeeFound = True
                   End If
                   .MoveNext
               Loop
           End With
       
           ' If no employee was found, let the user know
           If EmployeeFound = False Then
               txtEmployeeName = ""
               MsgBox "There is no employee with that number.", _
                      vbOKOnly Or vbInformation, "Bethesda Car Rental"
           End If
       End If
       
       Set rsEmployees = Nothing
       
       Exit Sub
   
   txtEmployeeNumber_LostFocus_Error:
       If Err.Number = 3021 Then
           MsgBox "No employee was found with that number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       Else
           MsgBox "An error occurred when trying to get the employee's information.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       End If
       
       Resume Next
   End Sub

Record Selection in ADO

We already know that, when creating a record set, you can use the name of a table or query as the source of data when calling the Open() method of the Recordset class. Remember that you can call this method with or without arguments:

recordset.Open

So far, we specified the Source argument in one name. In reality, the Source can be a SQL statement that will be used to select the records. This means that the Source can use a regular SELECT statement. Here is an example:

Private Sub cmdAnalyze_Click()
    Dim rstEmployees As ADODB.Recordset
    
    Set rstEmployees = New ADODB.Recordset
    
    rstEmployees.Open "SELECT * FROM Employees;"
End Sub

Remember that the Recordset class is equipped with a property named Source that can hold the value of the Source argument of the Open method. You can assign the SQL statement to that property and the Recordset variable would use it just fine. Here is an example:

Private Sub cmdAnalyze_Click()
    Dim rstEmployees As ADODB.Recordset
    
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Source = "SELECT * FROM Employees;"
    
    rstEmployees.Open
End Sub

We saw that when the value of the Source argument is the name of a table or query, the last argument of the Open method is specified as acCmdTable. Here is an example:

Private Sub cmdTableReference_Click()
    Dim rstEmployees As ADODB.Recordset
    
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Open "Employees", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
End Sub

When the source is a SQL statement, the last argument of Open must be specified as acCmdText. Here is an example:

Private Sub cmdAnalyzeEmployees_Click()
    Dim rstEmployees As ADODB.Recordset
    
    Set rstEmployees = New ADODB.Recordset
    rstEmployees.Open "SELECT * FROM Employees;", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdText
End Sub

Practical Learning: Selecting a Value Using a Record Set

1. In the Object combo box, select txtTagNumber
2. In the Procedurecombo box, select LostFocus
3. Implement the event as follows:

   Private Sub txtTagNumber_LostFocus()
   On Error GoTo txtTagNumber_LostFocus_Error
   
       Dim rsCars As New ADODB.Recordset
       
       If IsNull(txtTagNumber) Then
           Exit Sub
       End If
       
       rsCars.Open "SELECT Make, Model, Doors, Passengers " & _
                   "FROM Cars " & _
                   "WHERE TagNumber = '" & txtTagNumber & "';", _
                   CodeProject.Connection, _
                   adOpenStatic, _
                   adLockOptimistic, _
                   adCmdText
   
       If IsNull(rsCars("Make")) Then
           MsgBox "Invalid tag number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
           Exit Sub
       Else
           txtMake = rsCars("Make").Value
           txtModel = rsCars("Model").Value
           txtDoorsPassengers = rsCars("Doors").Value & " / " & rsCars("Passengers").Value
       End If
       
       Set rsCars = Nothing
       
       Exit Sub
   
   txtTagNumber_LostFocus_Error:
       If Err.Number = 3021 Then
           MsgBox "No car was found with that tag number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       Else
           MsgBox "An error occurred when trying to retrieve the car's information.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       End If
       
       Resume Next
   End Sub

4. In the Project window, double-click Form_UpdateRentalOrder
5. In the Object combo box, select cmdFind
6. Implement the Click event as follows:

   Private Sub cmdFind_Click()
   On Error GoTo cmdFind_Click_Error
   
       Dim rsCars As New ADODB.Recordset
       Dim rsEmployees As New ADODB.Recordset
       Dim rsCustomers As New ADODB.Recordset
       Dim rsRentalOrders As New ADODB.Recordset
       
       rsRentalOrders.Open "SELECT * FROM RentalOrders WHERE ReceiptNumber = " & CLng(txtReceiptNumber), _
                           CurrentProject.Connection, _
                           adOpenStatic, _
                           adLockOptimistic, _
                           adCmdText
       
       If IsNull(txtReceiptNumber) Then
           Exit Sub
       Else
           With rsRentalOrders
               txtEmployeeNumber = .Fields("EmployeeNumber").Value
               txtCustomerFirstName = .Fields("CustomerFirstName").Value
               txtCustomerLastName = .Fields("CustomerLastName").Value
               txtCustomerAddress = .Fields("CustomerAddress").Value
               txtCustomerCity = .Fields("CustomerCity").Value
               txtCustomerState = .Fields("CustomerState").Value
               txtCustomerZIPCode = .Fields("CustomerZIPCode").Value
               txtTagNumber = .Fields("TagNumber").Value
               cbxConditions = .Fields("CarCondition").Value
               cbxTankLevels = .Fields("TankLevel").Value
               txtMileageStart = .Fields("MileageStart").Value
               txtStartDate = .Fields("StartDate").Value
               txtRateApplied = .Fields("RateApplied").Value
               txtTaxRate = .Fields("TaxRate").Value
               cbxOrdersStatus = .Fields("OrderStatus").Value
               txtNotes = .Fields("Notes").Value
           End With
       
           rsEmployees.Open "SELECT * FROM Employees " & _
                            "WHERE EmployeeNumber = '" & txtEmployeeNumber & "';", _
                            CodeProject.Connection, _
                            adOpenStatic, _
                            adLockOptimistic, _
                            adCmdText
       
           With rsEmployees
               Do While Not .EOF
                   If rsEmployees("EmployeeNumber").Value = txtEmployeeNumber Then
                       txtEmployeeName = .Fields("FirstName").Value & " " & .Fields("LastName").Value
                       Exit Do
                   End If
                   .MoveNext
               Loop
           End With
       
           rsCars.Open "SELECT ALL * FROM Cars " & _
                       "WHERE TagNumber = '" & txtTagNumber & "';", _
                       CodeProject.Connection, _
                       adOpenStatic, _
                       adLockOptimistic, _
                       adCmdText
              
           txtMake = rsCars("Make").Value
           txtModel = rsCars("Model").Value
           txtDoorsPassengers = rsCars("Doors").Value & " / " & rsCars("Passengers").Value
           
       End If
       
       rsCars.Close
       rsEmployees.Close
       rsCustomers.Close
       rsRentalOrders.Close
       
       Set rsCars = Nothing
       Set rsEmployees = Nothing
       Set rsCustomers = Nothing
       Set rsRentalOrders = Nothing
       
       Exit Sub
   
   cmdFind_Click_Error:
       If Err.Number = 3021 Then
           MsgBox "No receipt with that number was found.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       Else
           MsgBox "An error occurred when trying to retrieve the car's information.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       End If
   
       Resume Next
   End Sub

Record Maintenance in ADO: Updating a Value

Using a Record Set

The approach to editing a record using ADO follows the same algorithms as reviewed for the Microsoft Access Object Library except that, with ADO, you don't call the Edit() method. Here is an example:

Private Sub cmdMovePosition_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEnumerator As ADODB.Field

    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", CurrentProject.Connection, _
                  adOpenForwardOnly, adLockOptimistic, adCmdTable

    ' Scan the records from beginning to each
    While Not rstVideos.EOF
        ' Check the current column
        For Each fldEnumerator In rstVideos.Fields
            ' If the column is named Title
            If fldEnumerator.Name = "Title" Then
                ' If the title of the current record is "Congo"
                If fldEnumerator.Value = "Congo" Then
                    ' then change its value
                    rstVideos("Director").Value = "Frank Marshall"
                    rstVideos.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstVideos.MoveNext
    Wend
   
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

Practical Learning: Updating a Record

1. In the Object combo box, select cmdSubmit
2. Implement the event as follows:

   Private Sub cmdSubmit_Click()
   On Error GoTo cmdSubmit_Click_Error
   
       Dim rsRentalOrders As ADODB.Recordset
       
       Set rsRentalOrders = New ADODB.Recordset
       rsRentalOrders.Open "SELECT * FROM RentalOrders " & _
   	                "WHERE ReceiptNumber = " & CLng(Me.txtReceiptNumber), _
                           CurrentProject.Connection, adOpenStatic, _
                           adLockOptimistic, adCmdText
       
       If IsNull(txtReceiptNumber) Then
           MsgBox "You must enter a valid receipt number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       Exit Sub
       End If
       
       With rsRentalOrders
           .Fields("EmployeeNumber").Value = txtEmployeeNumber
           .Fields("CustomerFirstName").Value = txtCustomerFirstName
           .Fields("CustomerLastName").Value = txtCustomerLastName
           .Fields("CustomerAddress").Value = txtCustomerAddress
           .Fields("CustomerCity").Value = txtCustomerCity
           .Fields("CustomerState").Value = txtCustomerState
           .Fields("CustomerZIPCode").Value = txtCustomerZIPCode
           .Fields("TagNumber").Value = txtTagNumber
           .Fields("CarCondition").Value = cbxConditions
           .Fields("TankLevel").Value = cbxTankLevels
           .Fields("MileageStart").Value = txtMileageStart
           .Fields("MileageEnd").Value = txtMileageEnd
           .Fields("TotalMileage").Value = txtTotalMileage
           .Fields("StartDate").Value = txtStartDate
           .Fields("EndDate").Value = txtEndDate
           .Fields("TotalDays").Value = txtTotalDays
           .Fields("RateApplied").Value = txtRateApplied
           .Fields("TaxRate").Value = txtTaxRate
           .Fields("OrderStatus").Value = cbxOrdersStatus
           .Fields("Notes").Value = txtNotes
           .Update
       End With
       
       MsgBox "The customer's record has been updated.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
       
       cmdReset_Click
       
       rsRentalOrders.Close
       Set rsRentalOrders = Nothing
   
       Exit Sub
       
   cmdSubmit_Click_Error:
       MsgBox "An error occurred when trying to update the customer's rental order. " & _
              "Please report the error as follows." & vbCrLf & _
              "Error #:     " & Err.Number & vbCrLf & _
              "Description: " & Err.Description, _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
       Resume Next
   End Sub

3. In the Project combo box, double-click Form_Switchboard
4. In the Object combo box, select cmdCategoriesRecords and implement the event as follows:

   Private Sub cmdCategoriesRecords_Click()
       DoCmd.OpenForm "Categories"
   End Sub

5. In the Object combo box, select cmdCarsRecords and implement the event as follows:

   Private Sub cmdCarsRecords_Click()
       DoCmd.OpenForm "Cars"
   End Sub

6. In the Object combo box, select cmdCarsRecords and implement the event as follows:

   Private Sub cmdEmployeesRecords_Click()
       DoCmd.OpenForm "Employees"
   End Sub

7. In the Object combo box, select cmdNewRentalOrder and implement the event as follows:

   Private Sub cmdNewRentalOrder_Click()
       DoCmd.OpenForm "NewRentalOrder"
   End Sub

8. In the Object combo box, select cmdNewCustomer and implement the event as follows:

   Private Sub cmdUpdateRentalOrder_Click()
       DoCmd.OpenForm "UpdateRentalOrder"
   End Sub

9. In the Object combo box, select cmdClose and implement the event as follows:

   Private Sub cmdClose_Click()
       DoCmd.Close
   End Sub

10. Close Microsoft Visual Basic and return to Microsoft Access
11. Close the form
12. When asked whether you want to save, click Yes

Using SQL

From the Recordset class in ADO, you can update a record using the SQL. To do this, create an UPDATE TableName SET ... expression and pass it to the Open() method.

Record Maintenance in ADO: Deleting a Record

Using SQL

As you may know already, the SQL provides a mechanism to delete a record. Using such a statement, pass it to the Open() method of a Recordset variable.

Using an ADO's Record Set

In ADO, to delete a record, first locate it. To do this, you can create a SQL statement to locate the record. Then call the Delete method of the ADO.Recordset class. Here is an example:

Private Sub cmdDeleteLast_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT * FROM Videos WHERE Title = 'Leap of Faith'", _
	   CurrentProject.Connection, _
                   adOpenDynamic, adLockPessimistic, adCmdText
                   
    rstVideos.Delete
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

Practical Learning: Simulating a Car Rental Business

1. In the Navigation Pane, double-click Switchboard
2. On the Switchboard form, below the Forms label, click Cars
3. The resources that accompany these lessons include a Microsoft Excel document named Bethesda Car Rental. It inludes a Cars section. Use it to create the records for the cars
4. Close the Cars form
5. On the Switchboard form, below the Forms label, click Employees
6. Close the Employees form
7. On the Switchboard form, below the Forms label, click Categories
8. Click New Rental Order
9. Close the RentalRates form
10. Create a record with the following values:
    
    

    Employee #:	20480
    Customer First Name:	Marcel
    Last Name	Buhler
    Address:	6800 Haxell Crt
    City:	Alexandria
    State:	VA
    ZIP Code	22314
    Tag Number:	8AG3584
    Condition:	Excellent
    Tank Level:	Empty
    Mileage Start:	12728
    Start Date:	04/10/2017
    Rate Applied:	69.95
    Order Status:	Vehicle With Customer

11. Click the Submit button
12. Create another record with the following value:
    
    

    Employee #:	24793
    Customer First Name:	Joan
    Last Name	Altman
    Address:	3725 South Dakota Ave NW
    City:	Washington
    State:	DC
    ZIP Code	20012
    Tag Number:	KER204
    Condition:	Good
    Tank Level:	3/4 Full
    Mileage Start:	24715
    Start Date:	04/14/2017
    Rate Applied:	62.95
    Order Status:	Vehicle With Customer

13. Click the Submit button
14. Create another record with the following values:
    
    

    Employee #:	38240
    Customer First Name:	Thomas
    Last Name	Filder
    Address:	4905 Herrenden St
    City:	Arlington
    State:	VA
    ZIP Code	22204
    Tag Number:	8AL8033
    Condition:	Excellent
    Tank Level:	Full
    Mileage Start:	6064
    Start Date:	04/14/2017
    Rate Applied:	34.95
    Order Status:	Vehicle With Customer

15. Click the Submit button
16. On the Switchboard form, click Update Rental Order
17. In the Receipt # text box, type 100001
18. Click Find
19. Change the following values, ignoring any information that is not given:
    

    Tank Level:	Half Tank
    Mileage End:	13022
    End Date:	04/15/2017
    Total Days:	5
    Rate Applied:	69.95
    Tax Rate:	7.75%
    Order Status:	Rental Order Complete

20. Click the Submit button
21. In the Receipt # text box, type 100003
22. Click Find
23. Change the following values, ignoring any information that is not given:
    

    Tank Level:	Full
    Mileage End:	629
    End Date:	04/17/2017
    Total Days:	3
    Rate Applied:	34.95
    Tax Rate:	7.75%
    Order Status:	Rental Order Complete

24. Click the Submit button
25. Close the Update Rental Order form
26. Click New Rental Order
27. Create a record with the following values:
    
    

    Employee #:	73948
    Customer First Name:	Gregory
    Last Name	Strangeman
    Address:	5530 Irving St
    City:	College Park
    State:	MD
    ZIP Code	20740
    Tag Number:	2AT9274
    Condition:	Excellent
    Tank Level:	1/2 Tank
    Mileage Start:	8206
    Start Date:	4/16/2017
    Rate Applied:	28.95
    Order Status:	Vehicle With Customer

28. Click the Submit button
29. Close the New Rental Order form
30. Click Update Rental Order
31. In the Receipt # text box, type 100002
32. Click Find
33. Change the following values:
     

    Tank Level	Full
    Mileage End:	25694
    End Date:	4/18/2017
    Total Days:	4
    Rate Applied:	62.95
    Tax Rate:	7.75%
    Order Status:	Rental Order Complete

34. Click the Submit button
35. Close the Update Rental Order form
36. Click New Rental Order
37. Create a record with the following values:
    
    

    Employee #:	38240
    Customer First Name:	Michelle
    Last Name	Russell
    Address:	10070 Weatherwood Drv
    City:	Rockville
    State:	MD
    ZIP Code	20853
    Tag Number:	8AE9294
    Condition:	Excellent
    Tank Level:	Full
    Mileage Start:	3659
    Start Date:	4/17/2017
    Rate Applied:	38.95
    Order Status:	Vehicle With Customer

38. Close the New Rental Order form
39. Click Update Rental Order
40. In the Receipt # text box, type 100005
41. Click Find
42. Change the following values:
     

    Tank Level	Full
    Mileage End:	3806
    End Date:	4/19/2017
    Total Days:	1
    Rate Applied:	38.95
    Tax Rate:	7.75%
    Order Status:	Rental Order Complete

43. Click the Submit button
44. Create another record with the following values and ignore any iniformation that is not given:
     

    Tank Level	3/4 Full
    Mileage End:	8412
    End Date:	4/19/2017
    Total Days:	2
    Rate Applied:	28.95
    Tax Rate:	7.75%
    Order Status:	Rental Order Complete

45. Click the Submit button
46. Close the Update Rental Order form
47. Close the Switchboard form
48. Close Microsoft Access