Home

Introduction to the ADO Library

The Microsoft ActiveX Data Objects Library (ADO)

Introduction

Microsoft ActiveX Data Objects, also called ADO, is a library that was developed to allow programmers on various environments to create and manage Microsoft Access databases.

Practical Learning: Introducing ADO

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. In the File Name text box, type (replace Database1 with) Bethesda Car Rental1
  4. Click Create
  5. On the Ribbon, click File and click Options
  6. In the left list of the Access Options dialog box, click Current Database
  7. In the main list, click Overlapping Windows
  8. Click OK on the dialog box
  9. Click OK on the message box
  10. On the Ribbon, click File and click Close
  11. In the list of files, click Bathesda Car Rental1
  12. On the Ribbon, click Create and, in the Forms section, click Form Design
  13. Save the form as Switchboard
  14. Complete the design of the form as follows (we applied the Bodoni MT Black font with size 20 to both labels and the buttons):

    Bethesda Car Rental - Database Switchboard

    Control Name Caption
    Label Label   Tables
    Label Label   Records
    Line Line    
    Button Button cmdCategoriesTable Categories
    Button Button cmdCarsTable Cars
    Button Button cmdCategoriesRecords Categories
    Button Button cmdCarsRecords Cars
    Button Button cmdEmployeesTable Employees
    Button Button cmdEmployeesRecords Employees
    Button Button cmdCustomersRecords Customers
    Button Button cmdCustomersTable Customers
    Button Button cmdNewRentalOrder New Rental Order
    Button Button cmdRentalRatesTable Rental Rates
    Button Button cmdUpdateRentalOrder Update Rental Order
    Button Button cmdRentalOrdersTable Rental Orders
    Button Button cmdRentalRatesTable Rental Rates
    Button Button cmdClose Close
  15. Save the form

Using ADO

Although treated as one entity, Microsoft ActiveX Data Object (ADO) is really many libraries grouped under one name. To use ADO in your database, open the References dialog box, locate the latest version of Microsoft ActiveX Data Objects 6.1 library and click its check box:

References

Once you are ready, click OK.

To use an object of the ADO library, you will declare a variable for it. The classes in ADO are created in namespaces. The most fundmanetal namespace in ADO is named ADODB. Therefore, when declaring a variable in DAO, to indicate that its class belongs to that library, precede it with that namespace.

Practical Learning: Using ADO

  1. On the Ribbon, click Database Tools and click Visual Basic
  2. On the main menu of Microsoft Visual Basic, click Tools and click References...
  3. Scroll down in the Available References list box until you find Microsoft ActiveX Data Objects...
    Double-click the item with the highest version
  4. Click OK

    References

  5. Return to Microsoft Access

The Connection to a Database

Introduction

To use or access a database in the ADO library, you must first establish a connection. To support this, the ADO library provides a class named Connection. Before establishing a connection, declare a variable of type ADODB.Connection. Here is an example:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
End Sub

Before using the connection, you must initialize the variable. This is done using the New and the Set operators. This can be done as follows:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
End Sub

A faster alternative is to apply the New operator when declaring the variable. Here is an example:

Private Sub cmdConnector_Click()
    Dim connector As New ADODB.Connection
End Sub

After using the Connection variable, to release the resources it was using, assign Nothing to it. Here is an example:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection

    Set connector = Nothing
End Sub

Opening a Connection

After declaring and initializing the Connection object, you can open the connection. To support this, the Connection class is equipped with a method named Open. The syntax of the Connection.Open() method is:

Connection.Open ConnectionString, UserID, Password, Options

The first argument to this method is called a connection string.

Introduction to the Connection String

A connection string is text made of various sections separated by semi-colons. Each section is created as a Key=Value expression. Based on this, a connection string uses the following formula:

Key1=Value1;Key2=Value2;Key_n=Value_n;

The Data Provider of a Database

The first part of the connection string is called the provider. It specifies the platform used to handle the database. To specify it, assign the desired name to the Provider key. This would be done as:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider=

    Set connector = Nothing
End Sub

Although there are various providers in the industry, there are two primary providers used in ADO. One of them is Microsoft SQL Server and it is represented by SQLOLEDB. Therefore, if your database will reside on a Microsoft SQL Server, you can specify the provider as follows:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider=SQLOEDB"

    Set connector = Nothing
End Sub

The most common provider we will use is the Microsoft JET database engine. It can represented as Microsoft.JET.OLEDB.4.0 (case insensitive). You can use it to specify the provider as follows:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider=Microsoft.Jet.OLEDB.4.0"

    Set connector = Nothing
End Sub

You can also include the name of the provider as its own string. To do that, you can include it in single-quotes:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider='Microsoft.JET.OLEDB.4.0'"

    Set connector = Nothing
End Sub

You can also specify the provider as Microsoft.ACE.OLEDB.12.0.

Introduction to the Data Source of a Database

The second part of the connection string is referred to as the data source. It is represented by a key named Data Source. To specify it, you can assign the name of the database with its extension to the Data Source factor. Here is an example:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=Example.accdb"

    Set connector = Nothing
End SubP

If you provide (only) the name of the database, the database engine would look for it in the same folder as the application that called it. In the above example, the database engine may look for it in the My Documents folder. If you want to refer to a database that is outside of the folder of the application that called this method, provide a complete path of the database. This can consist of the drive, the folder(s), and the name of the database with its extension. Here is an example:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=C:\Exercises\Example.accdb"

    Set connector = Nothing
End Sub

If you are referring to a contemporary or later database, specify the extension as .accdb. If you are referring to a database compatible with previous versions of Microsoft Access, specify the extension as .mdb.

To be safer, you can include the data source in single-quotes:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    connector.Open "Provider='Microsoft.ACE.OLEDB.12.0';'Data Source=Example.accdb'"

    Set connector = Nothing
End Sub

Instead of directly passing a string to the Open() method, you can first declare a String variable, initialize it with the necessary provider/data source, and then pass that string variable to the Create() method. Here is an example:

Private Sub cmdConnector_Click()
    Dim strConnection As String
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    strConnection = "Provider='Microsoft.ACE.OLEDB.12.0';'Data Source=Example.accdb'"
    connector.Open strConnection

    Set connector = Nothing
End Sub

Of course, you can create the string using any of the appropriate techniques you want. Here is an example:

Private Sub cmdConnector_Click()
    Dim strConnection As String
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    strConnection = "Provider='Microsoft.ACE.OLEDB.12.0';"
    strConnection = strConnection & "'Data Source=Example.accdb'"
    connector.Open strConnection

    Set connector = Nothing
End Sub

Using an ADO Connection

A Connection to the Current Database

The connection allows you to create a connection to either the database you are working on or another database that may be currently closed. You must know the path to the database you want to access. In some cases, you may simply want to connect to the current database. To support this, the CurrentProject property of the Application object is equipped with a property named Connection. This gives you a convenient access to the connection of the currently opened database. Here is an example of invoking it:

Private Sub cmdCurrentConnection_Click()
    Dim conCurrent As ADODB.Connection
    
    Set conCurrent = Application.CurrentProject.Connection
End Sub

Remember, after using a connection, you should release the resources it was using. Here is an example:

Private Sub cmdCurrentConnection_Click()
    Dim conCurrent As ADODB.Connection
    
    Set conCurrent = Application.CurrentProject.Connection

    Set conCurrent = Nothing
End Sub

When this code executes, it identifies the connection to the current database and stores it in a declared variable.

Executing a SQL Statement

After creating a connection to a database, the next step is usually to specify what you want to do on the database. One of the most usual operations you can perform is to submit a SQL statement to it (the connection). This is also equivalent to executing the statement.

To let you execute a statement, the Connection class is equipped with a method named Execute. Its syntax is:

Execute CommandText, RecordsAffected, Options

The first argument, CommandText, can be a table, a query, a SQL statement, etc. The second and the third arguments are optional. Here is an example:

Private Sub cmdConnector_Click()
    Dim connector As ADODB.Connection
    Dim strConnection As String
    Dim strStatement As String
    
    strStatement = "Blah Blah Blah"

    Set connector = New ADODB.Connection
    strConnection = "Provider='Microsoft.ACE.OLEDB.12.0';"
    strConnection = strConnection & "Data Source='C:\Programs\Exercise1.accdb';"    

    connector.Open strConnection
    connector.Execute strStatement 

    Set connector = Nothing
End Sub

In this case, the strStatement string would be passed to the Execute() method of the Connection object that would execute it. Because the statement is created as a string and doesn't "belong" to Microsoft Access, it will not be checked until it must be executed. This means that it is (quite completely) your responsibility to formulate a valid statement. Microsoft Access cannot check, and will not assist you with, the validity of the statement, until the statement executes.

Closing a Connection

When using a connection, it consumes resources that other applications may need. Therefore, after using it, you should close it and free the resources it was using so they can be made available to the other parts of the computer.

To provide you with the ability to close a connection, the Connection class is equipped with a method named Close. Therefore, to close a connection, call its Close() method. Here is an example:

Private Sub cmdDataSource_Click()
    Dim connector As ADODB.Connection
    
    Set connector = New ADODB.Connection
    
    . . .

    connector.Close
    Set connector = Nothing
End Sub

The Tables of an ADO Connection

Creating a Table Using an ADO Connection

There are many types of operations you can perform on an ADO connection. As mentioned already, to perform an operation, create its SQL statement and pass it to the Execute() method of the Connection class. This means that the ADO's Connection class relies on the SQL to perform most of its database operations.

To create a table using the ADO's Connection class, formulate a SQL's CREATE TABLE ... statement and pass it to the Execute() method. Here is an example:

Private Sub cmdTable_Click()
    Dim conCurrent As ADODB.Connection
    
    Set conCurrent = Application.CurrentProject.Connection
    
    conCurrent.Execute "CREATE TABLE Customers(FirstName Text, " & _
                       "                       LastName Char);"
    
    MsgBox "A table named Customers has been created."
    conCurrent.Close
End Sub

Deleting a Table in ADO

Another type of operation you can perform using a database connection is to remove a table from the database. To delete a table, create a DROP TABLE ... SQL statement and execute it by calling the Execute() method of the ADODB.Connection class. Here is an example:

Private Sub cmdDeleteTable_Click()
    Dim conDepartments As ADODB.Connection
    Dim strSQL As String
    
    Set conDepartments = New ADODB.Connection
    conDepartments.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source='C:\My Documents\Exercise.accdb'"
    
    strSQL = "DROP TABLE Departments;"
    
    conDepartments.Execute strSQL
    
    MsgBox "The Departments table of the Exercise.accdb database has been deleted"
    
    Set conDepartments = Nothing
End Sub
 
 
 

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)," & _
	                "  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)," & _
                 "  Country varchar(50) DEFAULT 'USA'" & _
                 ");"
End Sub

Practical Learning: Creating a Table in ADO

  1. On the Switchboard form, belows the Tables label, right-click the Categories button and click Build Event...
  2. In the Choose Builder dialog box, double-click Code Builder
  3. Create new procedures as follows:
    Private Sub CreateCategoriesTable()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "CREATE TABLE Categories" & _
                             "(" & _
                             "   Category TEXT(30) NOT NULL, " & _
                             "   Daily    Double   not null, " & _
                             "   Weekly   Double   not null, " & _
                             "   Monthly  Double   not null, " & _
                             "   Weekend  Double   not null, " & _
                        "   CONSTRAINT PK_Categories PRIMARY KEY(Category)" & _
                             ");"
        dbConnection.Close
        Set dbConnection = Nothing
        Application.RefreshDatabaseWindow
    End Sub
    
    Private Sub CreateEmployeesTable()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "CREATE TABLE Employees" & _
                             "(" & _
                             "   EmployeeNumber Text(20) not null, " & _
                             "   FirstName      String(25) null, " & _
                             "   LastName       Char(25) not null, " & _
                             "   Title          VarChar(50) null, " & _
                             "   Notes          Memo," & _
                       "   CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber)" & _
                             ");"
        dbConnection.Close
        Set dbConnection = Nothing
        Application.RefreshDatabaseWindow
    End Sub
  4. In the Object combo box, select cmdCarsTable and implement the event as follows:
    Private Sub cmdCarsTable_Click()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "CREATE TABLE Cars" & _
                             "(" & _
                             "   TagNumber    Text(20)    not null, " & _
                             "   Category     String(30)  not null, " & _
                             "   Make         Char(40)    not null, " & _
                             "   Model        VarChar(40) not null, " & _
                             "   Doors        BYTE, " & _
                             "   Passengers   Byte, " & _
                             "   Condition    Text(25) DEFAULT 'Unknown', " & _
                             "   DVDBDPlayer  Text(25) default 'None', " & _
                             "   Availability Text(25) DEFAULT 'Available', " & _
                             "   Notes        Note," & _
                             "   CONSTRAINT FK_Categories FOREIGN KEY(Category) " & _
                             "       REFERENCES Categories(Category)," & _
                             "   CONSTRAINT PK_Cars PRIMARY KEY(TagNumber)" & _
                             ");"
        dbConnection.Close
        Set dbConnection = Nothing
        Application.RefreshDatabaseWindow
    
        cmdCarsTable.Enabled = False
    End Sub
  5. In the Object combo box, select cmdRentalOrdersTable and implement the event as follows:
    Private Sub cmdRentalOrdersTable_Click()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "CREATE TABLE RentalOrders				   " & _
                             "(							   " & _
                             "   ReceiptNumber     Counter(100001, 1),		   " & _
                             "   EmployeeNumber    TEXT(20)  NOT NULL,		   " & _
                             "   CustomerFirstName Text(25),			   " & _
                             "   CustomerLastName  Text(25)  not null,		   " & _
                             "   CustomerAddress   Text(60), CustomerCity    Text(50), " & _
                             "   CustomerState     Text(40), CustomerZIPCode Text(20), " & _
                             "   TagNumber         Char(20), CarCondition VarChar(50), " & _
                             "   TankLevel         String(30),                         " & _
                             "   MileageStart      INTEGER,  MileageEnd   Integer,     " & _
                             "   TotalMileage      INT,				   " & _
                             "   StartDate         DATE,     EndDate      Date,        " & _
                             "   TotalDays         Integer, 			   " & _
                             "   RateApplied       Double,				   " & _
                             "   TaxRate           Double    Default      0.0750,      " & _
                             "   OrderStatus       Text(40)  Default      'Unknown',   " & _
                             "   Notes             LongText,			   " & _
                             "   CONSTRAINT FK_Employees Foreign Key(EmployeeNumber)   " & _
                             "      REFERENCES Employees(EmployeeNumber),		   " & _
                             "   CONSTRAINT FK_Cars Foreign Key(TagNumber)		   " & _
                             "      REFERENCES Cars(TagNumber),			   " & _
                             "   CONSTRAINT PK_RentalOrders PRIMARY KEY(ReceiptNumber) " & _
                             ");"
        dbConnection.Close
        Set dbConnection = Nothing
        Application.RefreshDatabaseWindow
    
        cmdRentalOrdersTable.Enabled = False
    End Sub
  6. Return to Microsoft Access and switch the form to Form View

    Bethesda Car Rental - Database Switchboard

  7. Close the Switchboard form
  8. When asked whether you want to save, click Yes

Data Entry Using SQL

As mentioned for table creation, ADO relies on the SQL for some of its operation. As a matter of fact, you can create records in SQL using the SQL although that's not the only way to perform data entry in ADO. If you use SQL, before performing data entry, make sure the table exists. Otherwise, you would receive a 3192 error:

Error 3192

Other than that, to perform data entry using SQL, you use the formula that includes INSERT...VALUES keywords introduced in Lesson 23. Here is an example:

Private Sub cmdSubmit_Click()
   Dim dbConnection As New ADODB.Connection
    
    Set dbConnection = CurrentProject.Connection
    
    If IsNull(txtCustomerNumber) Then
        MsgBox "You must enter a customer number.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Exit Sub
    End If
    
    If IsNull(txtLastName) Then
        MsgBox "You must enter the customer's name.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Exit Sub
    End If
    
    dbConnection.Execute "INSERT INTO Customers(CustomerNumber, FirstName, LastName," & _
                         "                      Address, City, State, ZIPCode, Notes) " & _
                         "VALUES('" & txtCustomerNumber & "', '" & txtFirstName & "', '" & _
                         txtLastName & "', '" & txtAddress & "', '" & txtCity & "', '" & _
                         txtState & "', '" & txtZIPCode & "', '" & txtNotes & "');"
    
    dbConnection.Close
    Set dbConnection = Nothing
End Sub

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.

Practical Learning: Adding a Record Using SQL

  1. In the Navigation Pane, right-click Switchboard and click Design View
  2. Below the Tables label, right-click Categories and click Build Event...
  3. Add a procedure as follows:
    Private Sub CreateCategoriesTable()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "CREATE TABLE Categories" & _
                             "(" & _
                             "   Category TEXT(30) NOT NULL, " & _
                             "   Daily    Double   not null, " & _
                             "   Weekly   Double   not null, " & _
                             "   Monthly  Double   not null, " & _
                             "   Weekend  Double   not null," & _
                             "   CONSTRAINT PK_Categories PRIMARY KEY(Category)" & _
                             ");"
        dbConnection.Close
        Set dbConnection = Nothing
        Application.RefreshDatabaseWindow
    End Sub
    
    Private Sub CreateRentalRatesRecords()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Economy', 34.95, 28.75, 24.95, 24.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Compact', 38.95, 32.75, 28.95, 28.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Standard', 45.95, 39.75, 35.95, 34.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Full Size', 50.00, 45.00, 42.55, 38.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Mini Van', 55.00, 50.00, 44.95, 42.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('SUV', 56.95, 52.95, 44.95, 42.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Pickup Truck', 62.95, 52.75, 46.95, 44.95);"
        dbConnection.Execute "INSERT INTO Categories(Category, Daily, Weekly, Monthly, Weekend)" & _
                            "VALUES('Passenger Van', 69.95, 64.75, 52.75, 49.95);"
                            
        dbConnection.Close
        Set dbConnection = Nothing
    End Sub
    
    Private Sub cmdCategoriesTable_Click()
        CreateCategoriesTable
        CreateRentalRatesRecords
    
        cmdCategoriesTable.Enabled = False
    End Sub
  4. In the Object combo box, select cmdEmployeesTable and implement the event as follows:
    Private Sub CreateEmployeesTable()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "CREATE TABLE Employees" & _
                             "(" & _
                             "   EmployeeNumber Text(20) not null, " & _
                             "   FirstName      String(25) null, " & _
                             "   LastName       Char(25) not null, " & _
                             "   Title          VarChar(50) null, " & _
                             "   Notes          Memo," & _
                             "   CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber)" & _
                             ");"
        dbConnection.Close
        Set dbConnection = Nothing
        Application.RefreshDatabaseWindow
    End Sub
    
    Private Sub CreateEmployeesRecords()
        Dim dbConnection As New ADODB.Connection
        
        Set dbConnection = CurrentProject.Connection
        
        dbConnection.Execute "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title) VALUES('48058', 'Daniel', 'Keller', 'General Manager');"
        dbConnection.Execute "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title) VALUES('96350', 'Margareth', 'Gremore', 'Accounts Associates');"
        dbConnection.Execute "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title) VALUES('28005', 'Patrick', 'Starr', 'Accounts Associates');"
        dbConnection.Execute "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title) VALUES('27479', 'William', 'Schenke', 'Accounts Associates');"
        dbConnection.Execute "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title) VALUES('62094', 'Laura', 'Walters', 'Accounts Manager');"
        dbConnection.Execute "INSERT INTO Employees(EmployeeNumber, FirstName, LastName, Title) VALUES('94286', 'Sonia', 'Wallers', 'Accounts Associates');"
    
        dbConnection.Close
        Set dbConnection = Nothing
    End Sub
    
    Private Sub cmdEmployeesTable_Click()
        CreateEmployeesTable
        CreateEmployeesRecords
    
        cmdEmployeesTable.Enabled = False
    End Sub
  5. Return to Microsoft Access and switch the Switchboard form to Form View
  6. Below the Tables label, click Categories, Cars, Employees, and Rental Orders

    Bethesda Car Rental - Database Switchboard

  7. Close the Switchboard form
  8. When asked whether you want to save, click Yes
  9. In the Navigation Pane, right-click Cars and click Design View
  10. Right-click Notes and click Insert Rows
  11. Type Pictures and press Tab
  12. Set the Data Type to Attachment
  13. Save and close the table
  14. On the Ribbon, click Database Tools and click Relationships
  15. Position the items as you see fit. Here is an example:

    Bethesda Car Rental - Data Relationships

  16. To close the Relationships window, click the Close button on the Ribbon
  17. When asked whether you want to save, click Yes
  18. On the Ribbon, click Create and click Form Design
  19. Using the Properties window, change the following characteristics:
    Record Source: Categories
    Default View: Continuous Forms
    Navigation Buttons: No
    Min Max Buttons: Min Enabled
  20. Right-click the body of the form and click Form Header/Footer
  21. Save the form as Categories
  22. Design the form as follows:

    Bethesda Car Rental - Rental Rates

  23. Save and close the form
  24. On the Ribbon, click Create and, in the Forms section, click Form Design
  25. In the Property Sheet, change the following characteristics:
    Record Source: Employees
    Auto Center: Yes
  26. Save the form as Employees
  27. Design the form as follows:

    Bethesda Car Rental - Employees

  28. Right-click the Close button and click Build Event
  29. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  30. Save and close the form
  31. On the Ribbon, click Create and, in the Forms section, click Form Design
  32. Using the Properties window, change the Record Source to Cars
  33. Save the form as Cars
  34. In the Controls section of the Ribbon, click the Combo Box Combo Box and click the form
  35. In the first page of the wizard, make sure the first radio button is selected and click Next
  36. In the list of the second page of the wizard, click Table: Categories and click Next
  37. In the Available Fields list of the third page of the wizard, double-click Category and click Next
  38. In the 4th page of the wizard, click Next
  39. In the 5th page of the wizard, click Next
  40. In the 6th page of the wizard, click the arrow of the combo box and select Category
  41. Click Finish
  42. In the Controls section of the Ribbon, click the Combo Box Combo Box and click the form
  43. In the first page of the wizard, click the second radio button and click Next
  44. In the second page of the wizard, click the cell under Col1 and type Unknown
  45. Press the down arrow key and type Excellent
  46. Press the down arrow key and type Good Shape
  47. Press the down arrow key and type Needs Repair
  48. Press the down arrow key and type Must be Retired
  49. Click Next
  50. Click the arrow of the combo box and select Condition
  51. Click Finish
  52. In the Controls section of the Ribbon, click the Combo Box Combo Box and click the form
  53. In the first page of the wizard, click the second radio button and click Next
  54. In the second page of the wizard, click the cell under Col1 and type None
  55. Press the down arrow key and type BD Player
  56. Press the down arrow key and type DVD Player
  57. Press the down arrow key and type Both BD and DVD Players
  58. Click Next
  59. Click the arrow of the combo box and select DVDBDPlayer
  60. Click Finish
  61. In the Controls section of the Ribbon, click the Combo Box Combo Box and click the form
  62. In the first page of the wizard, click the second radio button and click Next
  63. In the second page of the wizard, click the cell under Col1 and type Other
  64. Press the down arrow key and type Rented
  65. Press the down arrow key and type Available
  66. Click Next
  67. Click the arrow of the combo box and select Availability
  68. Click Finish
  69. Complete the design of the form as follows:

    Bethesda Car Rental - Update Rental Order

  70. Right-click the Close button and click Build Event
  71. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub
  72. Close Microsoft Visual Basic and return to Microsoft Access
  73. Close Microsoft Access
  74. When asked whether you want to save, click Yes
 
 
   
 

Previous Copyright © 2000-2016, FunctionX, Inc. Next