To use a record set, you have many options.

1. Start Microsoft Access
2. Open the BethesdaCarRental1 database from the previous lesson

The primary way of creating a record set is to declare a variable of type Recordset. 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 ADODB. Therefore, here is an example of declaring the variable:

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

Before using the record set, you must initialize the 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. 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

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 class and the Report are equipped with a property named Recordset. Another 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

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 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 without passing any argument:

recordset.Open

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.

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) comming 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

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

Although a desktop database is usually used by only one person, in many environments, a database is accessed by more than one computer. This means that, when creating a Recordset object, you need to keep different issues in mind. For example, you (actually your user) may be accessing a record or a series of records at the same time with someone else from another computer. In some cases, there may usually be only one person using a database and there might occasionally be someone else but unlikely, although possible. In some other cases, such as on the Internet or in a big enterprise, there might be many people accessing, or trying to access, a database, or a specific set of records, at the same time.

Imagine you are working on a large database such as a bank application that has thousands or millions of records (for thousands or millions of customers). If you want to perform an operation on the customers, you may have to deal with many or all records. You may also have to deal with the fact that other people are accessing the same records with you, at the same time.

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 curcors, 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

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 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

The ADO library does not have its own means of creating tables. That job is handled by a related library named ADOX. Actually, you can use SQL in ADO to create a table and use it as you see fit.

We already saw that, to create a table, you could use a Connection object ob which you would call its Execute() method. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim acExercise As ADODB.Connection
    
    Set acExercise = CurrentProject.Connection
    acExercise.Execute "CREATE TABLE Employees" & _
                       "(" & _
                       "    EmployeeNumber Long," & _
                       "    FirstName Text(25)," & _
                       "    LastName Text(25)," & _
                       "    HourlySalary Double" & _
                       ");"
    MsgBox "A table named Employees has been created.", _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
End Sub

Once again, remember that ADO doesn't inherantly create or manage tables. It relies on SQL. Therefore, as we saw with the connection, to delete a table, formulate a DROP TABLE ... statement and execute it throught the Connection object.

A record set is is made of records. These records are organized in sections named 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

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

• 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 and that 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.

1. In the Navigation Pane, right-click NewRentalOrder and click Design View
2. On the form, double-click the Employee # text box
3. In the Properties window, click Event and double-click On Lost Focus
4. Click its ellipsis button and implement the event as follows:

   Private Sub txtEmployeeNumber_LostFocus()
   On Error GoTo txtEmployeeNumber_LostFocusError
   
       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_LostFocusError:
       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

5. In the Object combo box, select txtDrvLicNumber
6. In the Procedure combo box, select LostFocus
7. Implement the event as follows:

   Private Sub txtDrvLicNumber_LostFocus()
   On Error GoTo txtDrvLicNumber_LostFocusError
   
       Dim rsCustomers As New ADODB.Recordset
       ' This variable will be used to find if the user
       ' entered a valid employee number
       Dim CustomerFound As Boolean
       
       If IsNull(txtDrvLicNumber) Then
           Exit Sub
       End If
       
       ' To start, assume that we haven't found an employee
       CustomerFound = False
       
       rsCustomers.Open "Customers", _
                        CodeProject.Connection, _
                        adOpenStatic, _
                        adLockOptimistic, _
                        adCmdTable
   
       If IsNull(rsCustomers) Then
           MsgBox "Invalid customer number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
           Exit Sub
       Else
           With rsCustomers
               Do
                   If rsCustomers("DrvLicNumber").Value = txtDrvLicNumber Then
                       txtCustomerName = .Fields("FullName").Value
                       txtCustomerAddress = .Fields("Address").Value
                       txtCustomerCity = .Fields("City").Value
                       txtCustomerState = .Fields("State").Value
                       txtCustomerZIPCode = .Fields("ZIPCode").Value
                       CustomerFound = True
                   End If
                   .MoveNext
               Loop While Not .EOF
           End With
       
           ' If no employee was found, let the user know
           If CustomerFound = False Then
   	    txtDrvLicNumber = ""
               txtCustomerName = ""
               txtCustomerAddress = ""
               txtCustomerCity = ""
               txtCustomerState = ""
               txtCustomerZIPCode = ""
               
               MsgBox "There is no customer with that number.", _
                      vbOKOnly Or vbInformation, "Bethesda Car Rental"
           End If
       End If
       
       Set rsCustomers = Nothing
       
       Exit Sub
   
   txtDrvLicNumber_LostFocusError:
       If Err.Number = 3021 Then
           MsgBox "No employee was found with that number.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       Else
           MsgBox "There was an error when trying to retrieve the customer's record.", _
                  vbOKOnly Or vbInformation, "Bethesda Car Rental"
       End If
       
       Resume Next
   End Sub

8. Return to Microsoft Access
9. Close the NewRentalOrder form
10. When asked whether you want to save it, click Yes

The record set in ADO supports data entry the same way it is done in the Microsoft Access Object Library (MAOL). 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. To do this, you can call the Supports() method. Its syntax is:

boolean = recordset.Supports(CursorOptions)

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). 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

1. In the Navigation Pane, right-click NewRentalOrder and click Design View
2. On the form, right-click the Submit button and click Build Event...
3. Double-click Code Builder
4. 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("DrvLicNumber").Value = txtDrvLicNumber
               .Fields("TagNumber").Value = txtTagNumber
               .Fields("CarCondition").Value = cbxConditions
               .Fields("TankLevel").Value = cbxTankLevels
               .Fields("MileageStart").Value = txtMileageStart
               .Fields("StartDate").Value = txtStartDate
               .Fields("RateApplied").Value = txtRateApplied
               .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"
       
       cmdReset_Click
       rsRentalOrders.Close
       Set rsRentalOrders = Nothing
   
       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

5. In the Object combo box, select cmdClose
6. 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

7. Return to Microsoft Access
8. Close the NewRentalOrder form
9. When asked whether you want to save, click Yes
10. In the Navigation Pane, right-click NewCustomer and click Design View
11. On the form, right-click the Submit button and click Build Event...
12. Double-click Code Builder
13. Implement the event as follows:

    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmit_ClickError
    
        Dim rsCustomers As ADODB.Recordset
        
        Set rsCustomers = New ADODB.Recordset
        rsCustomers.Open "Customers", _
                         CurrentProject.Connection, _
                         adOpenStatic, _
                         adLockOptimistic
        
        If IsNull(txtDrvLicNumber) Then
            MsgBox "You must enter the customer's driver's license number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
    	Exit Sub
        End If
        
        With rsCustomers
            If .Supports(adAddNew) Then
                .AddNew
                .Fields("DrvLicNumber").Value = txtDrvLicNumber
                .Fields("FirstName").Value = txtFirstName
                .Fields("LastName").Value = txtLastName
                .Fields("Address").Value = txtAddress
                .Fields("City").Value = txtCity
                .Fields("State").Value = txtState
                .Fields("ZIPCode").Value = txtZIPCode
                .Fields("Notes").Value = txtNotes
                .Update
            End If
        End With
        
        MsgBox "The customer's record has been created.", _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
        
        cmdReset_Click
        rsCustomers.Close
        Set rsCustomers = Nothing
    
        Exit Sub
        
    cmdSubmit_ClickError:
        MsgBox "An error occurred when trying to create the customer. " & _
               "Please report the error as follows." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Resume Next
    End Sub

14. In the Object combo box, select cmdClose
15. 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

16. Return to Microsoft Access
17. Close the NewCustomer form
18. When asked whether you want to save, click Yes
19. In the Navigation Pane, double-click NewCustomer
20. Create the following records so that, after creating each row, click the Submit button:
     

    Driver's Lic. #	First Name	Last Name	Address	City	State	ZIP Code
    P244-957-279	William	Post	808 Lennard Rd	Takoma Park	MD	20910
    182-37-4059	Myriam	Angel	4206 L St NW	Washington	DC	20004
    C930-240-057	Justine	Chisohlm	11366 Sansuit Drv	Rockville	MD	20853
    138-42-059	Ann	Hill	8522 Glenton Ave	Alexandria	VA

21. Close the NewCustomer form

Besides its own means of performing data entry, ADO allows you to use SQL. As you may know from the rules of the SQL, before performing data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data each of column. Before performing data entry, you must make sure that the table exists. Otherwise, you would receive a 3192 error:

Other than that, to perform data entry using SQL, you start with the INSERT combined with the VALUES keywords. The statement uses the following syntax:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify that you are entering data in the table using the INTO keyword between the INSERT keyword and the TableName factor. This is done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

If the data type of a column is a string type, include its value between double-quotes if you are using the DoCmd.RunSQL() method of Microsoft Access or you should include it in single-quotes if you are using ADO. For example, a shelf number can be specified as "HHR-604" for DoCmd.RunSQL() or 'HHR-604' for ADO and a middle initial can be given as "D" for Microsoft Access or 'D' for ADO.

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

1. On the Ribbon, click Create
2. In the Forms section, click Form Design
3. Save the table as Management
4. From the Controls section of the Ribbon, click the Button and click the form. If the wizard starts, click Cancel
5. Using the Properties window, change the following characteristics of the button:
   Name: cmdCreateRentalRates
   Caption: Create Rental Rates
6. Right-click the button and click Build Event...
7. Double-click Code Builder
8. Implement the event as follows:

   Private Sub cmdCreateValues_Click()
       Dim dbConnection As New ADODB.Connection
       
       Set dbConnection = CurrentProject.Connection
       
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Economy', 34.95, 28.75, 24.95, 24.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Compact', 38.95, 32.75, 28.95, 28.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Standard', 45.95, 39.75, 35.95, 34.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Full Size', 50.00, 45.00, 42.55, 38.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Mini Van', 55.00, 50.00, 44.95, 42.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('SUV', 56.95, 52.95, 44.95, 42.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Truck', 62.95, 52.75, 46.95, 44.95);"
       dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                           "VALUES('Grand Van', 69.95, 64.75, 52.75, 49.95);"
                           
       dbConnection.Close
       Set dbConnection = Nothing
   End Sub

9. Return to Microsoft Access
10. Swith the form to Form View
11. Click the Create Rental Rates button
12. Close the form
13. When asked whether you want to save, click No

A default value allows a column to use a value that is supposed to be common to most cells of a particular column. The default value can be set as a constant value or it can use a function that would adapt to the time the value is needed.

To sepecify the default value of a column, after its name and its data type, type DEFAULT followed by the desired default value. If the column is a numeric type, provide its value as the number. If the field is character or string-based, include its value in single-quotes. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim conDatabase As ADODB.Connection
    
    Set conDatabase = CurrentProject.AccessConnection
    conDatabase.Execute "CREATE TABLE Employees" & _
                 "(" & _
                 "  EmplNumber TEXT(6)," & _
                 "  HourlySalary Currency default 12.50," & _
                 "  FirstName Text(20)," & _
                 "  LastName Text(20)," & _
                 "  Address varchar(100)," & _
                 "  City VARCHAR(40)," & _
                 "  State char(2)," & _
                 "  ZIPCode varchar(20)," & _
                 "  Country varchar(50) DEFAULT 'USA'" & _
                 ");"
End Sub

Although the SQL as a language supports default values, Microsoft Access SQL does not allow a default value in a statement executed by the DoCmd object. If you try, you would receive an error. For example, the following code will produce a 3290 error (Syntax Error in CREATE TABLE Statement):

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE Employees" & _
                 "(" & _
                 "  EmplNumber TEXT(6)," & _
                 "  HourlySalary Currency default 12.50," & _
                 "  FirstName Text(20)," & _
                 "  LastName Text(20)," & _
                 "  Address varchar(100)," & _
                 "  City VARCHAR(40)," & _
                 "  State char(2)," & _
                 "  ZIPCode varchar(20)," & _
                 "  Country varchar(50) DEFAULT 'USA'" & _
                 ");"
End Sub