Anatomy of a Connection String

[Previous] [Next]

ADO is extremely flexible when it comes to connecting to databases and provides a variety of ways to build a connection string.

Using Data Links to Build Connection Strings

First let's spend a little time looking at how you create a connection to a database at design time, using Visual Basic 6. Whether you're using the DataEnvironment object, the DataView window, or the ADO Data Control (Microsoft ADO Data Control 6.0), you can access the same user interface, the Data Link Properties property sheet, to specify the OLE DB provider you want to use. Rather than including their own connection strings, each of these technologies uses the Data Link Properties property pages to let the user interactively build the connection strings.

Select the OLE DB provider on the Provider tab, as shown in Figure 3-1, and click Next to see the Connection property page, where you can specify the location of your database. You can even click the Test Connection button to test whether you can connect to the database you selected.

Perhaps the best way to learn about using connection strings is to create one. From a Visual Basic 6 project, simply reference the Microsoft OLE DB Service Component 1.0 Type Library and write code such as the following:

 Dim objDataLink As MSDASC.DataLinks Dim strConn As String Set objDataLink = New MSDASC.DataLinks strConn = objDataLink.PromptNew MsgBox "The connection string you created is:" & _ vbCrLf & strConn 

When you run this code, it will display the same Data Link Properties property sheet interface used within Visual Basic 6. Once you click on the OK button for that set of property pages, you'll see a message box showing the connection string that you built. You can use the connection string returned from this simple snippet of code to learn how to build connection strings on your own. Another way of learning from existing connection strings is to pass a Connection object into the PromptEdit method on the DataLinks object, which will display the Data Link Properties property sheet with the properties for that Connection object. You can then edit that connection string.

NOTE

---

You can display the Data Link Properties user interface in your applications if you distribute the Mdac_typ.exe file with your application to include the Microsoft Data Access Components.

A connection string consists of a series of name-value pairs delimited by semicolons:

 strConn = "Setting1=Value1;Setting2=Value2;..." 

Figure 3-1 Select your OLE DB provider on the Data Link Properties property sheet.

Using Data Link Files

In version 2.0 of the Microsoft Data Access Components (MDAC), OLE DB provided functionality similar to that of ODBC DSN (data source name) through a feature called data links. This feature saves connection string information to a file with a .udl extension. Prior to MDAC 2.5, you could create a data link file by right-clicking in a folder or on the Desktop and selecting Microsoft Data Link from the New submenu. Although the ability to create data link files this way was removed in MDAC 2.5, you can still create a file with a .udl extension and it will be associated with data links.

Once you've created a data link file, right-click on the file, select Properties, and you'll see a user interface that is the same as the one described in the previous section (as shown in Figure 3-1), only with a General tab added. You can view the contents of the file in a text editor such as Microsoft Notepad. To use this data link file in a connection string, you can use a name-value pair in the string, such as the following:

 "File Name=MyDataLink.udl;" 

If you do not specify a full path to the data link file, ADO will look in the current working directory for your application. You can also use relative paths in your connection string:

 "File Name=Data\MyDataLink.udl;" 

The OLE DB Provider For ODBC Drivers

ADO communicates with your database by means of an OLE DB provider, similar to how RDO communicates with your database through an ODBC driver. ADO 2.0 and later ship with native OLE DB providers for Access, SQL Server, and Oracle databases.

What if you don't have a native OLE DB provider to communicate with your database? As long as you have an ODBC driver that supports basic ODBC functionality, you should still be able to use ADO. The OLE DB Provider For ODBC Drivers makes ODBC API calls that ask the driver what functionality it supports. You might experience problems if the ODBC driver doesn't accurately state the functionality it supports.

In fact, the default OLE DB provider is the OLE DB Provider For ODBC Drivers. This provider acts as a translation layer between OLE DB and ODBC. In the connection string, this provider is listed as MSDASQL and is often referred to by its code name, Kagera. You can use this provider with an ODBC DSN—user, system, or file—or with a connection string that doesn't have a DSN. Here are some examples:

 "Provider=MSDASQL;DSN=MyUserDSN;UID=MyUID;PWD=MyPwd;" "Provider=MSDASQL;DSN=MySystemDSN;UID=MyUID;PWD=MyPwd;" "Provider=MSDASQL;FileDSN=MyFileDSN;UID=MyUID;PWD=MyPwd;" "Provider=MSDASQL;Driver={SQL Server};Server=MyServer; Database=MyDatabase;UID=MyUID;PWD=MyPwd;" 

My recommendation is to use a native OLE DB provider if one is available.

The OLE DB Provider for Access Databases

If you're connecting to an Access database you can use the OLE DB provider for Access databases, Microsoft Jet 4.0 OLE DB Provider. To use this provider, specify the provider name and version and the location of your database in the connection string as follows:

 "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Path\To\MyDatabase.MDB;" 

If you do not specify the entire path to your database, ADO will look for the database in your application's working path. You can also use relative paths. For example, if your database is in the Data subdirectory of your application, you could use the following connection string:

 "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Data\MyDatabase.MDB;" 

In the connection string, you can also specify the location of your system database, your database password, and a number of other options.

The OLE DB Provider For SQL Server Databases

If you're connecting to a SQL Server database, you can specify the native OLE DB provider, the location of your SQL Server, and the database you want to use, as well as security information:

 "Provider=SQLOLEDB;Data Source=MyServer; Initial Catalog=MyDatabase; User ID=MyUID;Password=MyPassword;" 

You can also specify the network library, packet size, and other attributes in the connection string.

The OLE DB Provider For Oracle Databases

Developers who want to use ADO with Oracle databases need to do a little more than just install ADO and build a connection string. Both the Microsoft OLE DB Provider For Oracle and the Microsoft ODBC Driver For Oracle communicate with Oracle's client components rather than directly with the Oracle database. In order to use ADO with Oracle, you have to install the appropriate version of the Oracle client utilities (SQL*Net) and create a database alias. Once you've done that, you can use a connection string such as this:

 "Provider=MSDAORA;Data Source=MyDatabaseAlias; User ID=MyUID;Password=MyPassword;" 

If you're looking to learn more about any of the above database provider options, see the documentation on these OLE DB providers in the Microsoft Data Access SDK, programmatically navigate through the Properties collection on the Connection object after specifying the provider, or examine the All tab on the Data Link Properties property sheet.