Access™ 2007 VBA Programmer's Reference

7.4. Rolling Your Own Connection String

The following is the output of the CurrentProject.Connection property for the Northwind 2007 sample database. (To shorten the file paths for this example, the database is moved to a different folder.)

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=C:\Databases\Northwind2007.accdb;
Mode=Share Deny None;
Extended Properties="";
Jet OLEDB:System database=C:\Databases\System.mdw;
Jet OLEDB:Registry Path=
     Software\Microsoft\Office\12.0\Access\Access Connectivity Engine;
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=6;
Jet OLEDB:Database Locking Mode=0;
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 there is one connection option listed that was not available in previous versions of Access. That's the Jet OLEDB:Support Complex Data=True option, which explicitly requires that the connection provider support the complex data in Access 2007 for Multi-valued and Attachment field types.

When creating a connection string, only the following five parameters need to be supplied:

• Provider (including version)

• User ID

• Password or database password (if applicable)

• Data source

• System database (if applicable)

In the case of an ADP, the requirements for ...