FILE: MDACCON.EXE Using Connection Strings with ODBC/OLEDB/ADO/RDS (193332)

MORE INFORMATION

The following file is available for download from the Microsoft Download Center:

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The following code was written using ActiveX Data Objects (ADO). However, it could be just as easily used in the Remote Data Service. If you remove the "PROVIDER=...;" syntax, the same connection string can be used in ODBC, MFC ODBC, Data Access Objects (DAO), DAO with ODBCDirect, or Remote Data Objects (RDO).

Within the Mdaccon.exe file, the projects ADOVB, ADOVC, and ADOVJ demonstrate this same sample code in Visual Basic, Visual C++ and Visual J++ respectively. OLEDBCON demonstrates opening the same connections in an OLE DB Consumer Application.

Connection Strings for ODBC and OLE DB

Applications built using ODBC, MFC ODBC, Data Access Objects (DAO), and Remote Data Objects (RDO) can use connection strings to invoke a specific ODBC Driver to return data from an underlying datastore. Applications using OLE DB, ActiveX Data Objects (ADO), and the Remote Data Service (RDS) can use connection strings to invoke a specific OLE DB Provider to return data, or use the OLE DB Provider for ODBC Drivers to invoke a ODBC Driver as well. The syntax for connection strings between ODBC Drivers and OLE DB Providers is similar, but there are differences in syntax depending upon the underlying datastore, (for example, Microsoft Access, Oracle, SQL Server, and so forth).

Mdaccon.exe uses a minimal subset of sample applications to demonstrate connection strings. Three ADO samples (one each in C++, VBA, and Java) demonstrate both ODBC Driver and OLE DB Provider connection strings. The ODBC Driver Connection string used in the ADO sample is identical to what you would use whether using ODBC, MFC ODBC, DAO, RDO, or RDS. The syntax in ADO for an OLE DB Provider is specific to ADO and RDS. Finally, a fourth sample application in C++ demonstrates the use of OLE DB Provider Connection strings for an OLE DB Consumer application.

Connection Strings for ODBC Drivers

ODBC Connection strings typically refer to a Data Source Name (DSN) that is defined in the ODBC Administrator (found in the Control Panel). The ODBC Administrator lets you define one of three types of DSNs:

User DSN: DSNs defined for the currently logged user only!
File DSN: DSNs stored in a file (independent of any given user).
System DSN: DSNs shared by all users and services (on Windows NT).

A DSN is simply a convenient way to store connection information that would otherwise have to be specified in a connection string. However, you can use what is called a DSN-less connection string and explicitly enumerate all of the connection information in your application independent of the ODBC Administrator. The Mdaccon.exe file and the code samples that follow later in this article, demonstrate both DSN (User or System) and DSN-less connection strings.

The syntax for a DSN, System DSN or File DSN follows:

{DSN=name|FileDSN=filename};[Database=database;]uid=userid;pwd=password

When using a DSN, the driver, server/data source, and database may have already been specified by the ODBC Administrator and do not usually need to be specified in the connection string. You can specify a database parameter in addition to a DSN to connect to a different database.

It is always a good idea to include the database parameter when you use a DSN. This ensures that you connect to the proper database because another user may have changed the default database since you last checked the DSN definition. Syntactically, there is no difference between a DSN and System DSN.

Syntax without a DSN (DSN-less connection) follows:

driver=drivername;Server=servername;Database=database; uid=userid;pwd=password

With Windows NT and Windows 95 operating systems, in the ODBC Administrator's System DSN dialog box, Data Sources and their (ODBC) Drivers are enumerated.

For developers using OLE DB, ADO, or RDS, this connection string syntax is valid, although you may want to add the PROVIDER= clause, as follows, to indicate that you want to go through the OLE DB Provider for ODBC Drivers:

Provider=MSDASQL;...

Because the MSDASQL provider is the default OLE DB provider (for ODBC) for ADO, you often see connection strings that omit the provider parameter. It is good practice to include the provider parameter explicitly to avoid confusion.

Using a DSN Created in the ODBC Driver Manager to an ODBC Driver

The following three code lines demonstrate connecting to ODBC DataSources that have been, respectively, created for Microsoft Access, SQL Server and Oracle. The Access DSN is OLE_DB_Nwind_Jet, created by the Data Access Software Development Kit (SDK) for the Nwind.mdb Microsoft Access database. The SQL Server DSN is LocalServer, created by SQL Server when installed on a computer. The Oracle DSN is dseOracleDSN, created in this case manually on a computer running Oracle client utilities.

   ' Access ODBC Driver via DSN
   con1.Open "PROVIDER=MSDASQL;" & _
             "DSN=OLE_DB_NWind_Jet;" & _
             "UID=admin;PWD=;"

   ' Oracle ODBC Driver via DSN
   con1.Open "PROVIDER=MSDASQL;" & _
             "DSN=dseOracle;" & _
             "UID=demo;PWD=demo;"

   ' SQL Server ODBC Driver via DSN
   con1.Open "PROVIDER=MSDASQL;" & _
             "DSN=LocalServer;DATABASE=pubs;" & _
             "UID=<username>;PWD=<strong password>;"

For non-ADO/RDS/OLEDB Developers, you would remove the PROVIDER=...; syntax and have a connection string that is perfectly valid for ODBC, MFC ODBC, ODBCDirect and RDO developers.

For ADO/RDS Developers, the PROVIDER=MSDASQL; syntax is optional. By default, ADO and RDS use the OLE DB Provider for ODBC. However, it is good practice to specifically enumerate your provider. Also for ADO and RDS Developers, there is an alternate form of syntax for listing the Data Source Name (DSN), User ID (UID) and Password (PWD). This syntax is valid regardless of underlying provider or driver as it is supported by ADO/RDS.

For all three ODBC Drivers, an alternate form of syntax could be used to specify the Data Source, the User ID, and Password, as shown here for the Microsoft Access ODBC Driver:

   Con1.Open "PROVIDER=MSDASQL;" & _

             "DATA SOURCE=OLE_DB_NWind_Jet;" & _
             "USER ID=admin;PASSWORD=;"

The use of "Data Source", "User ID", and "Password" is syntax specific to ADO and is not viable for any application going directly to ODBC, that is, without the OLE DB Provider for ODBC. "Data Source" is equivalent to "DSN", "User ID" to "UID", and "Password" to "PWD", but only for applications building connection strings through ADO and RDS. This syntax is also valid for the DSN-Less and Native Provider discussion that follow.

There is one more OLE DB/ADO/RDS specific clause that could be used, in this case with the SQL Server Connection string. "INITIAL CATALOG=" is functionally equivalent to "DATABASE=". However, this syntax is only supported if the underlying OLE DB Provider supports this syntax. This is shown in the following code example for the Microsoft Access ODBC Driver:

   Con1.Open "PROVIDER=MSDASQL;" & _

             "DATA SOURCE=LocalServer;INITIAL CATALOG=pubs;" & _
             "USER ID=<username>;PASSWORD=<strong password>;"

Using a DSN-Less Connection to an ODBC Driver

In the following example, notice that SQL Server and Oracle both have Server= parameters but Microsoft Access uses DBQ= to specify a database. SQL Server also specifies an initial catalog to open on the server with the DATABASE= clause.

   '  Access ODBC Driver via DSN-Less
   con1.Open "PROVIDER=MSDASQL;" & _

             "DRIVER={Microsoft Access Driver (*.mdb)};" & _
             "DBQ=C:\...\NWind.mdb;" & _
             "UID=admin;PWD=;"

   '  Oracle ODBC Driver
   con1.Open "PROVIDER=MSDASQL;" & _

             "DRIVER={Microsoft ODBC for Oracle};" & _
             "SERVER=MyOracleServer;" & _
             "UID=demo;PWD=demo;"

   '  SQL Server ODBC Driver
   con1.Open "PROVIDER=MSDASQL;" & _

             "DRIVER={SQL Server};" & _
             "SERVER=MySQLServer;DATABASE=pubs;" & _
             "UID=<username>;PWD=<strong password>;"

As in the DSN examples, the Provider is specifically enumerated even though you could rely upon ADO's use of this particular provider by default.

The significant difference in each of the three connection strings is the DRIVER= syntax. While shared by all three code samples, the content in between the {} corresponds to the exact syntax of the name of an ODBC Driver registered in the ODBC Driver Manager.

The other main difference between each data source is the syntax used to specify the actual database being opened. For Microsoft Access the DBQ clause is used to provide a path to an actual Microsoft Access .mdb file. For SQL Server both the server name as well as the database to access within that server are specified. For Oracle a value that matches the name of a service specified in the SQL Easy Net utility is specified.

Using a Native OLE DB Provider

Note in the following that the Microsoft Access and Oracle OLE DB native providers need a different User ID and Password syntax than that used in any of the other connection strings.

   ' Access Provider
   con1.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;" & _

             "DATA SOURCE=C:\...\NWind.mdb;" & _
             "USER ID=admin;PASSWORD=;"

   '  Oracle Provider
   con1.Open "PROVIDER=MSDAORA;" & _

             "DATA SOURCE=MyOracleServer;" & _
             "USER ID=demo;PASSWORD=demo;"

   '  SQL Server Provider
   con1.Open "PROVIDER=SQLOLEDB;" & _

             "DATA SOURCE=MySQLServer;DATABASE=pubs;" & _
             "USER ID=<username>;PASSWORD=<strong password>;"

Once again the Provider= clause is used, but this time it refers to the ProgID of native OLE DB providers other than the OLE DB Provider for ODBC Drivers.

For Microsoft Access and Oracle, the ODBC syntax of DSN, UID, and PWD is not supported. However the SQL Server OLE DB Provider does support the use of this otherwise ODBC-specific syntax.

The DATA SOURCE= syntax is identical to the SERVER= and DBQ= syntax you see with DSN-less connection strings.

For both Microsoft Access and Oracle Native OLE DB Providers, the DSN=, UID=, and PWD=, syntax are not supported. However, the SQL Server OLE DB Provider recognizes this otherwise ODBC-Driver specific syntax, as shown in the following example:

    '  SQL Server Provider
    Con1.Open "PROVIDER=SQLOLEDB;" & _
              "DSN=LocalServer;DATABASE=pubs;" & _
              "UID=<username>;PWD=<strong password>;"

Connection Strings Within OLE DB Consumer Applications

The preceding code samples utilized ADO's connection object. However, what about for an OLE DB Consumer application? The equivalent OLE DB code would set the values of provider properties in order to make a connection. Specifically, equivalent OLE DB code for each of the preceding samples would reference one or more of the following OLE DB Properties:

   OLE DB Property               Description
   ----------------------------------------------------------------------

   DBPROP_INIT_DATASOURCE         This property is equivalent to DSN= when
                                  using the OLE DB provider for ODBC to go
                                  to an ODBC Datasource.  Otherwise, it
                                  names either the Server (Oracle, SQL
                                  Server Providers) or database file (Jet
                                  Provider) that is to be opened.

   DBPROP_AUTH_USERID             This property is equivalent to the UID=
                                  ODBC syntax.

   DBPROP_AUTH_PASSWORD           This property is equivalent to the PWD=
                                  ODBC Syntax.

   DBPROP_INIT_CATALOG            This property is equivalent to the
                                  DATABASE= ODBC Syntax.

   DBPROP_INIT_PROVIDERSTRING     This property lets you cheat and pass
                                  your existing ODBC Connection String
                                  to the OLE DB Provider for ODBC.
                                  Otherwise it is useful for provider
                                  specific connection information.

The Mdaccon.exe file contains a project, OLEDBCON, which demonstrates connecting to Microsoft Access, Oracle, and SQL Server through various combinations of these OLE DB Properties. Demonstrated first is the use of the native OLE DB provider for each Datasource, then various permutations going through the OLE DB Provider for ODBC to ODBC Drivers for each Datasource.
Microsoft Data Access Components (MDAC) version 2.6 and later do not contain the following Jet components: