ADO: ActiveX Data Objects

Choosing a Data Provider

As of this writing, Microsoft supplies ten OLE DB providers with ADO. These providers are listed in Table 4-2. Other companies supply their own OLE DB providers. In this book, I will focus on the OLE DB provider for ODBC drivers and the OLE DB providers for SQL Server and Microsoft Access.

Table 4-2. Available Microsoft OLE DB Providers

Provider	Value
Microsoft OLE DB provider for ODBC	MSDASQL.1
Microsoft OLE DB provider for Microsoft Indexing Service	MSIDXS
Microsoft OLE DB provider for Microsoft Active Directory Service	ADSDSOObject
Microsoft OLE DB provider for Microsoft Jet	Microsoft.Jet.OLEDB.4.0
Microsoft OLE DB provider for SQL Server	SQLOLEDB
Microsoft OLE DB provider for Oracle	MSDAORA
Microsoft OLE DB provider for Internet Publishing	MSDAIPP.DSO
Microsoft Data Shaping Service for OLE DB (ADO Service Provider)	MSDataShape
Microsoft OLE DB Persistence Provider (ADO Service Provider)	MSPersist
Microsoft OLE DB Remoting Provider (ADO Service Provider)	MS Remote

The OLE DB provider for ODBC, supplied by Microsoft, is probably the most popular type of data source used today and is the default data provider of ADO. In other words, if you do not specify a data provider before opening a connection, ADO will assume you wish to use the ODBC OLE DB data provider. The ODBC OLE DB provider allows ADO to access any data source that has an ODBC-compliant driver, including, among others, flat files, Microsoft SQL Server, Microsoft Access, Microsoft FoxPro, Paradox, dBase, Oracle databases, and Microsoft Excel worksheets.

To explicitly choose the OLE DB provider for ODBC, set the value of the Provider argument in the ConnectionString of the Connection object to MSDASQL.1, as shown in Example 4-10. Additionally, this example illustrates the use of the ConnectionString to include arguments for usernames (UID) and passwords (PWD) with Connection objects con1 and con2.

Example 4-10. Specifying the Microsoft OLE DB Provider for ODBC Drivers

Dim con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim con3 As ADODB.Connection
Dim con4 As ADODB.Connection

Set con1 = New ADODB.Connection
Set con2 = New ADODB.Connection
Set con3 = New ADODB.Connection
Set con4 = New ADODB.Connection

' connect without using a DSN (Data Source Name)
con1.Open "Provider=MSDASQL.1; " _
        & "DRIVER={SQL Server}; " _
        & "Database=Northwind; " _
        & "Server=JROFF-NTLT; " _
        & "UID=sa; " _
        & "PWD="
    
' connect using a DSN and use the default provider
con2.Open "DSN=BiblioDSN; " _
        & "UID=BigBear; " _
        & "PWD=1810"
    
' connect using a DSN and specify the provider
con3.Open "Provider=MSDASQL.1; " _
        & "DSN=BiblioDSN; " _
        & "UID=Jason; " _
        & "PWD=1810; " _
        
' connect using a File DSN and specify the provider
con4.Open "Provider=MSDASQL.1; " _
        & "FileDSN=C:\Program Files\Common Files\ODBC\" _
                   & "Data Sources\BiblioDSN.dsn"

' close all connections
con1.Close
con2.Close
con3.Close
con4.Close

' clean up all connections
Set con1 = Nothing
Set con2 = Nothing
Set con3 = Nothing
Set con4 = Nothing

Data providers offer their own options for the syntax of the ConnectionString argument and property of the Connection object. The OLE DB provider for ODBC drivers is no exception. There are two ways to access a data source with this data provider through the connection string -- the first with a DSN, the second without.

The following can be used to correctly specify a DSN:

"Provider=MSDASQL.1; DSN=dsn_name; [DATABASE=database_name]; " _
& "UID=user_name; PWD=password"

  "Provider=MSDASQL.1; FileDSN=dsn_file; [DATABASE=database_name]; " _
& "UID=user_name; PWD=password"

As you can see, either a DSN name or a DSN filename can be given. The Provider argument is shown in both of these examples, but it is optional, since the OLE DB provider for ODBC drivers is the default data provider for ADO.

The DATABASE argument is optional. It refers to the name of the database to be used with the DSN, although one is already provided within the DSN itself. The DSN must be specified in the ODBC applet in the Windows Control Panel. Using the DATABASE argument in a DSN connection string actually alters the DSN definition, so it is important to use it whenever you can to ensure that you are getting the database that you need, in case someone else has altered the DSN definition.

An alternative syntax for an ODBC drivers data-provider connection string, a DSN-less connection, is as follows:

"Provider=MSDASQL.1; DRIVER=driver; SERVER=server; " _
& "DATABASE=database; UID=user_name; PWD=password;"

Connections of the preceding types do not need to include the Provider argument, because the OLE DB data provider for ODBC drivers is assumed to be the default. The DRIVER argument refers to the actual data-source driver for the connection. The SERVER argument refers to the name of the server chosen as the data source, and the DATABASE argument refers to the database name within the chosen server. See also the portion of code that opens the con1 Connection object in Example 4-10.