Access Database Design and Programming, Second Edition

A Closer Look at Connection Strings

It seems fair to say that the most confusing aspect of using ADO is determining the correct connection string required to establish a connection to an OLE DB provider. Certainly, this is one of the first confusing aspects of ADO, if not the only one.

In the beginning, there was only one OLE DB provider — Microsoft OLE DB Provider for ODBC Drivers. This was a good way for Microsoft to introduce OLE DB, because it meant that any ODBC provider automatically became an OLE DB provider.

Today, the list of OLE DB providers has grown to include the following (and presumably there are more of which I am not aware):

• Microsoft OLE DB Simple Provider (a JavaBeans-related interface)

• Microsoft OLE DB Provider for ODBC Drivers (for Open Database Connectivity)

• Microsoft OLE DB Provider for Oracle (for Oracle databases)

• Microsoft Jet 3.51 OLE DB Provider (for Jet databases)

• Microsoft OLE DB Provider for SQL Server (for SQL Server databases)

• Microsoft OLE DB Provider for Directory Services (provides directory services, that is, logon, administration and replication services, for Windows NT Server networks)

Aside from the ODBC provider, the SQL Server provider is used most often in examples, so we will not do so here. On the PC side, I think that the most interesting OLE DB providers are the Jet provider and the ODBC provider, especially since the latter can be used to connect to such things as Excel spreadsheets and text documents. Accordingly, we will take a look at ...