Microsoft Access Database Development With VBA

ADO and Record Sets

 

Introduction to Record Sets in ADO

 

OverviewOverview

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.

To use a record set, you have many options.

Practical LearningPractical Learning: Introducing ADO's Record Sets

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

Creating a Record Set

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

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

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

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

The Connection of 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

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

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

The Tables of an ADO Recordset

 

Introduction

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

Table Maintenance

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.

Operations on a Record Set

 

Introduction to Fields

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

Navigating Among Records

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.

Practical LearningPractical Learning: Navigating Among Records of a Record Set

  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

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

Practical LearningPractical Learning: Adding a Row to a Record Set

  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

Data Entry Using SQL

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:

Error 3192

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

Practical LearningPractical Learning: Adding a Row Using SQL

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

Assistance With Data Entry: A Default Value for a Field

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

 

 
 

Record Selection in ADO

 

Introduction

a

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 LearningPractical Learning: Selecting a Value Using a Record Set

  1. In the Navigation Pane, right-click NewRentalOrder and click Design View
  2. On the form, double-click the Tag Number text box
  3. In the Properties window, click Event and double-click On Lost Focus...
  4. Click its ellipsis button
  5. Implement the event as follows:
    Private Sub txtTagNumber_LostFocus()
    On Error GoTo txtTagNumber_LostFocusError
    
        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_LostFocusError:
        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
    
    
  6. Return to Microsoft Access
  7. Close the NewRentalOrder form
  8. When asked whether you want to save, click Yes
  9. In the Navigation Pane, right-click UpdateRentalOrder and click Design View
  10. On the form, double-click the Mileage End text box
  11. On the Properties window, click Event and double-click On Lost Focus
  12. Click its ellipsis button and 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
  13. In the Object combo box, select cmdFind
  14. Implement the Click event as follows:
    Private Sub cmdFind_Click()
    On Error GoTo cmdFind_ClickError
    
        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 RentalOrderID = " & CLng(Me.txtReceiptNumber), _
                            CurrentProject.Connection, _
                            adOpenStatic, _
                            adLockOptimistic, _
                            adCmdText
        
        If IsNull(txtReceiptNumber) Then
            Exit Sub
        Else
            With rsRentalOrders
                txtEmployeeNumber = .Fields("EmployeeNumber").Value
                txtDrvLicNumber = .Fields("DrvLicNumber").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
                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("FullName").Value
                        Exit Do
                    End If
                    .MoveNext
                Loop
            End With
        
            rsCustomers.Open "SELECT ALL * FROM Customers " & _
                             "WHERE DrvLicNumber = '" & txtDrvLicNumber & "'", _
                             CodeProject.Connection, _
                             adOpenStatic, _
                             adLockOptimistic, _
                             adCmdText
            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
                        Exit Do
                    End If
                    .MoveNext
                Loop While Not .EOF
            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_ClickError:
        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
    
    
  15. In the Navigation Pane, double-click the RentalRates form
     
    Bethesda Car Rental - Rental Rates
  16. In the Navigation Pane, double-click NewRentalOrder
  17. Create a record with the following values, ignoring any information that is not given:
     
    Employee #: 28005
    Drv. Lic #: P244-957-279
    Tag Number: 4FP208
    Condition: Excellent
    Tank Level: Full
    Mileage Start: 10407
    Start Date: 02/10/2014
    Rate Applied: 56.95
    Order Status: Rented - Car On Road
    Notes: The customer plans to rent the car for the whole week.
     
  18. Click the Submit button
     
    Bethesda Car Rental - New Rental Order
  19. Create another record with the following values and ignore any iniformation that is not given:
     
    Employee #: 62094
    Drv. Lic #: 182-37-4059
    Tag Number: 8CT208
    Condition: Driveable
    Tank Level: 1/4 Empty
    Mileage Start: 20268
    Start Date: 02/14/2014
    Rate Applied: 44.95
    Order Status: Rented - Car On Road
  20. Click the Submit button
  21. Create another record with the following values and ignore any iniformation that is not given:
     
    Employee #: 94286
    Drv. Lic. #: C930-240-057
    Tag Number: 1CH249
    Condition: Excellent
    Tank Level: Half
    Mileage Start: 6422
    Start Date: 03/08/2014
    Rate Applied: 55.00
    Order Status: Rented - Car On Road
  22. Click the Submit button
  23. Close the NewRentalOrder form
  24. Close the RentalRates form

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 LearningPractical Learning: Updating a Record

  1. In the Navigation Pane, right-click UpdateRentalOrder 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 "SELECT * FROM RentalOrders " & _
        			"WHERE RentalOrderID = " & 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("DrvLicNumber").Value = txtDrvLicNumber
            .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_ClickError:
        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
  5. Close Microsoft Visual Basic and return to Microsoft Access
  6. Close the UpdateRentalOrder form
  7. When asked whether you want to save, click Yes
  8. In the Navigation Pane, double-click UpdateRentalOrder
  9. In the Receipt # text box, type 100002
  10. Click Find
  11. Change the following values, ignoring any information that is not given:
     
    Mileage End: 20416
    End Date: 02/17/2014
    Total Days: 2
    Order Status: Car Returned/Order Complete

  12. Click the Submit button
     
    Bethesda Car Rental - Updating a Rental Order
  13. In the Receipt # text box, type 100001
  14. Click Find
  15. Change the values as follows:
     
    Mileage End: 10968
    End Date: 02/15/2014
    Total Days: 5
    Order Status: Car Returned/Order Complete
  16. Click the Submit button
  17. In the Receipt # text box, type 100003
  18. Click Find
  19. Create another record with the following values and ignore any iniformation that is not given:
     
    Mileage End: 6829
    End Date: 03/11/2014
    Total Days: 3
    Order Status: Car Returned/Order Complete
  20. Click the Submit button
  21. Close the UpdateRentalOrder form

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

Previous Copyright © 2005-2013, FunctionX, Inc. Next