The Connection object establishes a connection to a data source. It allows your application to pass client information, such as username and password, to the database for validation.

To use ADO to establish a connection to a database

  1. Set a reference to the ADO Object Library.

  2. Declare a Connection object.

  3. Specify an OLE DB data provider.

  4. Pass connection information.

Once you have completed these steps, you will be ready to establish a connection using the Open method.

Setting a Reference to ADO

Before you can use ADO in your Visual Basic application, you must first set a reference to the Microsoft ActiveX Data Objects 2.0 Library.

To create a reference to the ADO Object Library

  1. On the Project menu, click References.

  2. Select Microsoft ActiveX Data Objects 2.0 Library, and then click OK.

Declaring a Connection Object

Once you have made a reference to the ADO object library, you can declare a Connection object in your application. Using the Connection object, you can then create a Command object or Recordset object.

Note  Unless you are using both ADO and DAO in the same application, you do not need to use the prefix "ADODB" before ADO data types.

The following example code declares and instantiate a new Connection object:

Dim cnStateUBookstore As Connection
Set cnStateUBookstore = New Connection

Specifying a Data Provider

Once you have instantiated a Connection object, you must specify an OLE DB data source provider. You do this by setting the Provider property.

The following example code specifies the Microsoft SQL Server OLE DB data provider:

cnStateUBookstore.Provider = "SQLOLEDB"

Note  For a complete listing of ADO Connection Object properties, read the article "Connection Object (ADO)" in ADO Help.

Passing Connection Information

The final step before establishing a connection to a data source is to specify the connection information. You do this by setting the Connection object's ConnectionString property. Connection string arguments are provider specific, are passed directly to the provider, and are not processed by ADO.

For more information about connection string syntax, read the article "ConnectionString Property (ADO)" in ADO Help.

The following connection string arguments are used with the SQL Server OLE DB provider.

Connection argument Description
User ID Valid user name
Password Valid user password
Data Source Name of the remote server
Initial Catalog Database name in the external data source

The following example code specifies a Microsoft SQL Server data provider and supplies connection information in the ConnectionString property:

With cnStateUBookstore
    .Provider = "SQLOLEDB"
    .ConnectionString = "User ID=sa;Password=;" & _
                        "Data Source=MSERIES1;" & _
                        "Initial Catalog=StateUBookstore"
End With

Note  In the above example code, the connection string argument Password=; used to signify a blank password is redundant since this is the default value for this argument.

The following table describes some of the OLE DB providers currently available.

OLE DB data provider Description
SQLOLEDB OLE DB provider for Microsoft SQL Server
MSDASQL OLE DB provider for ODBC
Microsoft.Jet.OLEDB.3.51 OLE DB provider for Microsoft Jet
MSIDXS OLE DB provider for Microsoft Index Server
ADSDSOObject OLE DB provider for Microsoft Active Directory Service
MSDAORA OLE DB provider for Oracle

For more information about specific OLE DB data providers, read the article "Using OLE DB Providers with ADO and RDS" in Platform SDK Help.