Connection Object

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:

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 Open method of the Connection object is used to open the connection. This method needs one primary setting - the connection string. 

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:

  1. Data Link File
    A data link file contains the connection string information. To create a data link file:
    1. Create an empty text file with "udl" as the extension.
    2. Locate the file in Windows Explorer.
    3. Right-click on the file, and choose "Properties".
    4. Set the properties.

    myConnection.Open "File Name=c:\pathname\filename.udl"

  2. Data Source Name (DSN)
    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:
    1. Open "Control Panel".
    2. Open "Administrative Tools".
    3. Open the "Data Sources (ODBC)" applet
    4. Click on the "System DNS" tab
    5. Click on the "Add" button
    6. 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