|
The Connection to a
Database
|
|
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 operator. You have two options.
You can initialize the variable after declaring it. In this case, precede
the variable with the Set operator. This would 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. This would be done
as follows:
Private Sub cmdConnector_Click()
Dim connector As ADODB.Connection
Set connector = New ADODB.Connection
Set connector = Nothing
End Sub
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 made of 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 Microsoft Access Office 2007
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 value of 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
|
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 that, after using a connection, you should
release the resources it was using:
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 named conCurrent.
|
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 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 SQL
statement. It can also be something else as we will see. 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 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 and 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
|
 Practical
Learning: Creating a Table in ADO
|
|
- On the Ribbon, click Create and, in the Forms section, click Form
Design
- From the Controls section of the Ribbon, click the Button
and click the form. If the wizard starts, click Cancel
- Using the Properties window, change the following characteristics of
the button:
Name: cmdCreateTables
Caption:
Create Tables
- Right-click the button and click Build Event...
- Double-click Code Builder
- Implement the event as follows:
Private Sub cmdCreateTables_Click()
Dim dbConnection As New ADODB.Connection
Set dbConnection = CurrentProject.Connection
dbConnection.Execute "CREATE TABLE RentalRates" & _
"(" & _
" Category Text(32), " & _
" Daily Double, " & _
" Weekly Double, " & _
" Monthly Double, " & _
" Weekend Double " & _
");"
dbConnection.Execute "CREATE TABLE Customers" & _
"(" & _
" CustomerNumber Counter(10001, 1), " & _
" DrvLicNumber Text(30), " & _
" FirstName Text(25), " & _
" LastName Text(25), " & _
" Address Text(60), " & _
" City Text(50), " & _
" State Text(40)," & _
" ZIPCode Text(20)," & _
" Notes Memo, " & _
" CONSTRAINT PK_Customers PRIMARY KEY(CustomerNumber)" & _
");"
dbConnection.Execute "CREATE TABLE RentalOrders" & _
"(" & _
" ReceiptNumber Counter(100001, 1), " & _
" EmployeeNumber Text(20), " & _
" DrvLicNumber Text(30), " & _
" TagNumber Text(20), " & _
" CarCondition Text(50), TankLevel Text(30), " & _
" MileageStart Integer, MileageEnd Integer, " & _
" TotalMileage Integer, " & _
" StartDate Date, EndDate Date, TotalDays Integer, " & _
" RateApplied Double, " & _
" TaxRate Double Default 0.0750, " & _
" OrderStatus Text(40) Default 'Unknown', " & _
" Notes Memo, " & _
" 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
End Sub
- Close Microsoft Visual Basic and return to Microsoft Access
- Switch the form to Form View
- Click the Create Tables button
- Close the form
- When asked whether you want to save the table, click No
- In the Navigation Pane, right-click Customers and click Design View
- Right-click Address under Field Name and click Insert Rows
- In the new empty box, type FullName
- Set its Data Type to Calculated
- In the Expression Builder, type LastName & ', ' & FirstName
- Click OK
- Save and close the Customers table
- On the Ribbon, click Create and, in the Forms section, click Form
Design
- Using the Properties window, change the following characteristics:
Record Source: RentalRates
Default View: Continuous Forms
Navigation Buttons: No
- Save the form as RentalRates
- Design the form as follows:
- Save and close the form
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
|
Practical
Learning: Ending the Lesson
|
|
|
