To use or access a database, a user typically launches
it and opens the necessary object(s) from it. You too will need to access
a database but with code. To programmatically access a database using the
ADO library, you must first establish a connection. To support this, the
ADODB namespace provides a class named Connection. You can also
use ConnectionClass. To create a connection to a database, declare a variable of type ADODB.Connection
or ADODB.ConnectionClass and
initialize it using the New operator. This would be done as follows:

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

This method takes four arguments and all of them are
optional. In reality, the first argument must be defined in order to
establish as connection.

The Connection String

When establishing a connection to a database, you have
two alternatives, you can use the first argument to the Connection.Open()
method or you can separately create a connection string.

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

One of the expressions you can specify in the
connection string is the name of the provider. To do this, type Provider=
followed by the provider you are using. For most databases we will create
or use here, the provider will be Microsoft.JET.OLEDB.4.0. This
means that our connection string can start with:

It is important to note that the
content
of the connection string differs from one provider to another. If you were
working on a Microsoft SQL Server database, your connection string would
be different from the above done for a Microsoft JET database.

You can pass this connection string as the first (and
probably the only) argument to the method. Here is an example:

As mentioned earlier, the first argument is optional but you
must find a way to specify it. In effect, there is an alternative. To separately
support the connection string as its own object, the Connection class is
equipped with a property named ConnectionString, which is of type String.
To use it, declare a String variable, assign the connection string to that
variable, and assign that variable to the Connection.ConnectionString
property. You must do this prior to calling the Open() method. Here is an
example:

When creating your database, if you are working in a secure
environment and the database requires authentication, you may need to provide
login credentials, which include a username and a password. Normally, these
properties are mostly applied if you are working on a Microsoft SQL Server
database.

To specify the login credentials when accessing the
database, you can pass the second and the third arguments to the Open()
method of the Connection class.

Executing a SQL Statement

After creating a connection to a database, you can
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 the Execute() method. Its syntax is:

When it is called, the Execute() method of the Connection
class examines and executes its (first) argument, in this case strStatement.
If this method succeeds, it returns an object called a record set. We will
study record sets in future lessons.

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 close a connection, the Connection
class is equipped with the Close() method. This can be done as follows: