The Connection object is used to open a connection to the database. A connection string is used to supply the information for the connection. The connection string is a collection of name=value pairs separated by semi-colons. The information required varies depending on the database and type of provider:
The Open method of the Connection object is used to open the connection. This method needs one primary setting - the connection string.
Name Value for SQL Server Value for Access 2000 Provider (for OLE-DB) Driver (for ODBC)
SQLOLEDB {SQL Server}
Microsoft.Jet.OLEDB.4.0 {Microsoft Access Driver (*.mdb)}
Initial File Name Physical path and file name Physical path and file name Data Source (for OLE-DB) DBQ (for ODBC)
Physical path and file name Physical path and file name Initial Catalog Name of the database User ID username username Password password password Persist Security Info
(if using OLE-DB)a Boolean to determine if Windows remembers your password For testing local applications with MS Access, I usually use ODBC:
myConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\pathname\filename.mdb"
The connection string can be set as a property of the Connection object:
myConnection.ConnectionString = myConnectionString
The connection string can be passed as a parameter of the Open method:
myConnection.Open myConnectionString
The connection string can be stored elsewhere, and the Open method is told how to get it:
A data link file contains the connection string information. To create a data link file:
- Create an empty text file with "udl" as the extension.
- Locate the file in Windows Explorer.
- Right-click on the file, and choose "Properties".
- Set the properties.
myConnection.Open "File Name=c:\pathname\filename.udl"
A DSN also contains the connection string information, except the information is stored in the registry instead of a text file. To create a DSN:
- Open "Control Panel".
- Open "Administrative Tools".
- Open the "Data Sources (ODBC)" applet
- Click on the "System DNS" tab
- Click on the "Add" button
- Set the properties, paying particular attention to "Data Source Name"
myConnection.Open "DSN=Data Source Name"
To create a Connection object:
ASP Dim objCon Set objCon = Server.CreateObject("ADODB.Connection")
VB Dim objCon As ADODB.Connection Set objCon = New ADODB.Connection
VB .Net Dim objCon as New ADODB.Connection