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:\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.
