Microsoft Office 2000/Visual Basic Programmer's Guide

Using ADO Connection Strings

When you use ADO, you can often specify property settings several different ways: by setting built-in properties and arguments, by setting properties that use an object's Properties collection, or by passing in the value as part of a connection string. The basic format of a connection string is a series of keyword and value pairs separated by semicolons. The keyword is separated from the value by an equals sign (=), as shown in the following format:

keyword1=value;keyword2=value;keyword3=value

The values passed for connection string keywords must be formatted as strings and can contain spaces within the value. For example, the following is a valid keyword and value pair:

Jet OLE DB:Database Password=My Password

However, if the value contains single-quote ('), double-quote ("), or semicolon (;) characters, the value must be surrounded with either double quote or single quote characters, as shown in the following example for the password My's Password:

Jet OLE DB:Database Password="My's Password"

If the value contains both single- and double-quote characters, the quote character used to surround the entire value must be doubled each time it occurs within the value string, as shown in the following example for the password My's"Password:

Jet OLE DB:Database Password="My's""Password"

The following code fragment shows a specific example that passes a connection string to the ConnectionString argument of the Open method of the Connection object to specify the OLE DB provider to use, the database to open, and the workgroup information file (system database) to use for security accounts:

cnnDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDb.mdb;" & _
   "Jet OLE DB:System Database=C:\MySystemDB.mdw"

You can also set the same properties and values from the previous example by using the ADO Provider property and Properties collection, as shown in the following code fragment:

With cnnDB
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Properties("Jet OLE DB:System Database") = "C:\MySystemDB.mdw"
   .Open "C:\MyDb.mdb"
End With

Each method has equivalent results, so you can use whatever method you prefer. Most of the code examples in this chapter use the latter method to avoid formatting problems with long connection strings and to make it easier to see which properties are being set. However, some ADO component object models, such as the Microsoft ActiveX Data Objects Extensions for DDL and Security 2.1 (ADOX) and the Microsoft Jet and Replication Objects 2.1 (JRO) object libraries, require you to use connection strings to set certain properties. For example, ADOX doesn't have a Provider property, so if you aren't working with a Connection object that was previously opened by using ADO 2.1, you must specify the provider by using the Provider= keyword in a connection string. Similarly, the JRO CompactDatabase method requires you to pass connection strings for its SourceConnection and DestConnection arguments.