Data Sources and Connections to a Database

The Data Source of an Application

Introduction

Although a database is primarily considered as a project that holds one or more lists of items, there can be other issues involved such as how the data would be made available to the users, what computer(s) would access the data, what types of users (physical or else) would access the database. A database is started as a computer file before being presented to the user(s). The database could reside in one computer and used by one person. A database can also be stored in one computer but accessed by different computers in a network. Another database can be created and stored in a server to be accessed through the Internet. These and other related scenarios should be dealt with to create and distribute an effective database.

You may plan to create a database that would be used by one person using one computer. As your job becomes more effective, you could be asked to create another database that would be accessed by different people. Regardless of why and how, after creating a database, you should have a way of making it available to those who would use it. To do this, you must create a data source. With Microsoft Access, you have two main options.

In Microsoft Access, a database is created as a computer file and it has a path, that is, where the database file is located. The path to a file is also known as its location. The path to a database, including its name, is also called the data source. In some of your database operations, you will be asked to provide a data source for your database. In this case, provide the complete path followed by the name of the database.

Using the path to a database is probably the easiest way to access it but this is more convenient if you are working in Microsoft Access. If you plan to access your database from another programming environment, then you should create an ODBC data source. To do this, in the Control Panel or the Administrative Tools, double-click Data Source (ODBC) to open the ODBC Data Source Administrator:

To proceed, click the Add button. This would launch a wizard. In the first page of the Create New Data Source wizard, click Microsoft Access Driver (*.mdb):

Click Finish. In the following screen, you would be asked to enter a name for the data source. You can enter the name in one or more words. The name would be used by the applications that need to access the database. This means that you should pay attention to the name you give. In the Description text box, you can enter a short sentence anyway you like. To specify the database that would be used, click Select and select an mdb database. Here is an example:

After selecting the necessary database, if you need to be authenticated in order to use the database (if the database is protected), click the Advanced button:

By default, a database is meant to allow anybody to use it. In this case, you can leave the Login Name and the Password empty. Otherwise, type the necessary credentials. After using the Set Advanced Options dialog box, click OK (or Cancel to keep it the way it previously was).

After entering the necessary information and selecting the desired database, you can click OK twice.

The Connection to a Database

Introduction

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 ADO library provides an object called Connection. Based on this, before establishing a connection, declare a variable of type ADODB.Connection and initialize it using the New operator. This would be done as follows:

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

Opening a Connection

After declaring and initializing the Connection object, you can then open the connection. To support this, the Connection object is equipped with the Open() method. 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.

The Connection String

The connection string is text made of various sections separated by semi-colons. Each section is made of a Key=Value expression. This means that 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 would start with:

Provider=Microsoft.JET.OLEDB.4.0

You can also include the name of the provider in single-quotes to delimit it. If you were working on an MSDE or a Microsoft SQL Server database, the provider would be SQLOLEDB.

The second part of the connection string specifies the data source. To provide this information, you can assign the path and name of the database to the Data Source attribute. Here is an example:

Provider='Microsoft.JET.OLEDB.4.0';Data Source='C:\Programs\Example1.mdb';"

The provider and the data source are the most common pieces of information. 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 different from the above done for a Microsoft Access database.

For the Connection.Open() method, the connection string is the only required argument. If you are working in a secure environment and the database requires authentication, then you should (must) provide some credentials. To do this, you must provide a user name as the UserID argument and possibly a password as the Password argument. The last argument allows you to indicate if you want the connection to return a value upon establishing the connection.

Here is an example of opening a connection

Instead of directly passing a connection string to the Open() method, you can first declare a String variable, initialize it with the connection string, and then pass it to the Open() method. Here is an example:

Private Sub cmdConnector_Click()
    Dim conConnector As ADODB.Connection
    Dim strConnection As String
    
    Set conConnector = New ADODB.Connection
    strConnection = "Provider='Microsoft.JET.OLEDB.4.0';"
    strConnection = strConnection & "Data Source='C:\Programs\Exercise1.mdb';"
    
    conConnector.Open strConnection
    
End Sub

If you (or someone else) had created an ODBC data source and want to use it to open the database, include its name (and the credentials) in the connection string. Here is an example:

Private Sub cmdDataSource_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    
    conConnector.Open "DSN=Exercise;UID=;PWD=;"

End Sub

Remember that, in this case, the ODBC data source must have been created.

A Connection to the Current Database

The connection object as defined above allows you to create a connection to either the database you are working on or another database that may be currently closed. As you can see from the above introduction, you must know the path to the database you want to access. In some cases, you may simply need to have your connection point 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

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 object is equipped with the Execute() method. 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 in future lessons. The second and the third arguments are optional.

Here is an example:

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

    Set conConnector = New ADODB.Connection
    strConnection = "Provider='Microsoft.JET.OLEDB.4.0';"
    strConnection = strConnection & "Data Source='C:\Programs\Exercise1.mdb';"    

    conConnector.Open strConnection
    conConnector.Execute strStatement 
    
End Sub

In this case, the strStatement string would be passed to the Execute() method of the Connection 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 completely your responsibility to formulate a valid statement. Microsoft Access cannot check, and will not assist you with, the validity of the statement.

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 object is equipped with the Close() method. After closing a connection, to free the resources it was using, assign Nothing to it. This would be done as follows:

Private Sub cmdDataSource_Click()
    Dim conConnector As ADODB.Connection
    
    Set conConnector = New ADODB.Connection
    
    conConnector.Open "DSN=Exercise;UID=;PWD=;"

    conConnector.Close
    Set conConnector = Nothing
End Sub