Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

490

The Connection must be opened before it can be used. The following statement is the easiest

way to open an ADO Connection:

cnn.Open CurrentProject.Connection

In this case, the Connection connects to the current database. As you’ll soon see, a

Connection object requires a number of properties to be set before it can successfully open, but

opening a Connection on the current database’s Connection property provides all those set-

tings. CurrentProject.Connection is actually a long string (specifically, a connection string)

that includes all the information needed about the current database. A typical Connection prop-

erty setting is as follows:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;

Data Source=C:\Data\Chapter13.accdb;

Mode=Share Deny None;Extended Properties=““;

Jet OLEDB:System database=C:\...\Access\System.mdw;

Jet OLEDB:Registry Path=...\Access Connectivity Engine;

Jet OLEDB:Database Password=““;

Jet OLEDB:Engine Type=6;

Jet OLEDB:Database Locking Mode=1;

Jet OLEDB:Global Partial Bulk Ops=2;

Jet OLEDB:Global Bulk Transactions=1;

Jet OLEDB:New Database Password=““;

Jet OLEDB:Create System Database=False;

Jet OLEDB:Encrypt Database=False;

Jet OLEDB:Don’t Copy Locale on Compact=False;

Jet OLEDB:Compact Without Replica Repair=False;

Jet OLEDB:SFP=False;

Jet OLEDB:Support Complex Data=True

Note
Line breaks have been added above for clarity, and some lines have been shortened.

This is actually considerably more than the Connection object actually needs, but Microsoft

wanted to make sure nothing was missing.

Notice the Data Source portion of the ConnectionString property. This is the part that

points to a specific .accdb file. Changing this path means the Connection object can open vir-

tually any Access database as long as the path is valid and terminates at an .accdb file.

The following procedure opens a Connection against the current database, prints the

Connection object’s Provider property, and then closes and discards the Connection

object:

Public Sub OpenConnection()

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection

cnn.Open CurrentProject.Connection

‘ Connection is open