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


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


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

5. Mixed-Security Connection

MyServer;DATABASE=pubs;" _

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

MyServer;DATABASE=pubs;" _

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

MyServer;DATABASE=pubs;" _

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:\MyAppPath\MyUDL.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.Connection
Dim dl As New MSDASC.DataLinks
Set 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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.