OPENING A CONNECTION TO A DATABASE

You first need to open a connection to a database. As in DAO, creating a reference to a database is pretty straightforward in ADO.

Connecting to the Current Database

Connecting to the current database (or project, as is popular in Access 2000) is the easiest of all. To perform this task, you use the CurrentProject object, which is a property of the Application object. The CurrentProject object has a Connection property, which you assign to a Connection object. You can see this in the following code, which prints the ConnectionString property to the Immediate window:

Sub DisplayLocalConnection()

    Dim cnnLocal As ADODB.Connection

    Set cnnLocal = CurrentProject.Connection

    Debug.Print cnnLocal.ConnectionString

End Sub

Although connecting to the current database is pretty easy, connecting to another database takes a little more work.

Connecting to Another Database

The main work in creating a connection to another database comes from having to create the connection string. This code performs this task:

Sub DisplayAnotherConnection()

    Dim cnnNet As New ADODB.Connection
    cnnNet.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=C:BooksPwrPrg2000AppCDExamplesVideoDat.mdb"

    Debug.Print cnnNet.ConnectionString

    cnnNet.Close

End Sub

In this procedure, the connection string passed to the Open method of the Connection object consists of the Provider and Data Source. The Provider in this example is "Microsoft.Jet.OLEDB.4.0" (Jet).

You can also set the provider ahead of time by setting the Provider property:

Sub DisplayProviderAndSecuredDB()

    Dim cnnNet As New ADODB.Connection

    cnnNet.Provider = "Microsoft.Jet.OLEDB.4.0"

    cnnNet.Properties("Jet OLEDB:System database") = _
      "C:BooksPwrPrg2000AppCDExamplesChap06MySystem.mdw"

    cnnNet.Open _
      "Data Source=C:BooksPwrPrg2000AppCDExamplesVideoDat.mdb;UserId=
       Admin;Password=MyPW"

    Debug.Print cnnNet.ConnectionString

End Sub

This example also shows how to create a connection while specifying the system database and using a user ID and password.