ADO Connection Strings

The connection string is essential for connecting your ADO database applications to SQL Server. (See also, "Mastering ADO" and "Using a .UDL File from ADO").

These connection-string examples illustrate the seven most common connection scenarios.

7. DSN Connection

DSN=MyDSN;UID=MyID;PWD=MyPwd

This Data Source Name (DSN) connection string uses the DSN keyword to identify the existing DSN, MyDSN. The UID and PWD keywords pass in authentication information.

6. DSN-less Connection

DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=MyID;PWD=MyPwd

The DRIVER keyword's {SQL Server} value tells the connection to use the Microsoft SQL Server ODBC driver.

5. Mixed-Security Connection

PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=pubs;" _  & "UID=MyID;PWD=MyPWD

The PROVIDER keyword tells ADO to use the SQLOLEDB provider. If you omit this keyword, the provider defaults to MSDASQL (the OLE DB provider for ODBC).

4. Windows-Authentication Connection

PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=pubs;" _  & "TRUSTED_CONNECTION=YES

Here, the DATABASE keyword sets Pubs as the default database. The TRUSTED_CONNECTION keyword specifies Windows authentication, the most secure way to connect to SQL Server.

3. OLE DB Provider Connection

PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=pubs;" _  & "TRUSTED_CONNECTION=YES; NETWORK LIBRARY=dbmsrpcn

When you use the NETWORK LIBRARY keyword, your application can dynamically choose the network library to use. Valid network library values are dbnmpntw, dbmssocn, dbmsspxn, dbmsvinn, and dbmsrpcn.

2. UDL File Connection

FILE NAME=c:MyAppPathMyUDL.udl

The FILE NAME= keyword precedes the absolute path and name of the Universal Data Link (UDL) file that stores SQL Server connection information.

1. Data Link Dialog Box Connection

Dim cn as New ADODB.ConnectionDim dl As New MSDASC.DataLinksSet cn = dl.PromptNew

The Data Link object's PromptNew method displays a dialog box that lets your application prompt for OLE DB connection properties, then dynamically build the string.