Introduction to the ADO Library
|
|
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
| Control | Name | Caption | |
| Label |
 |
Tables | |
| Label |
|
Records | |
| Line |
 |
||
| Button |
 |
cmdCategoriesTable | Categories |
| Button |
|
cmdCarsTable | Cars |
| Button |
|
cmdCategoriesRecords | Categories |
| Button |
|
cmdCarsRecords | Cars |
| Button |
|
cmdEmployeesTable | Employees |
| Button |
|
cmdEmployeesRecords | Employees |
| Button |
|
cmdCustomersRecords | Customers |
| Button |
|
cmdCustomersTable | Customers |
| Button |
|
cmdNewRentalOrder | New Rental Order |
| Button |
|
cmdRentalRatesTable | Rental Rates |
| Button |
|
cmdUpdateRentalOrder | Update Rental Order |
| Button |
|
cmdRentalOrdersTable | Rental Orders |
| Button |
|
cmdRentalRatesTable | Rental Rates |
| Button |
|
cmdClose | Close |
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:
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
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.
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
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 SubPrivate 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 SubPrivate 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
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:
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
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 SubPrivate 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
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
and click the form and click the form and click the form and click the form
Private Sub cmdClose_Click()
DoCmd.Close
End Sub|
|
||
| Previous | Copyright © 2000-2022, FunctionX, Inc. | Next |
|
|
||