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 (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.
Note
All routines shown in this chapter can be found in Chap06.mdb on the CD-ROM in the ExamplesChap06 folder.
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).
Note
The examples in this chapter will mainly use Jet as the service provider. Chapter 25, “Developing SQL Server Projects Using ADPs,” shows many examples of working with SQL Server and the connection string.
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.
3.17.157.6