ID: Q193332
The information in this article applies to:
Mdaccon.exe is a sample that that demonstrates the use of ODBC Driver and OLE DB Provider connection strings. Four separate projects, one each in C++, Visual Basic for Applications, and Java, as well as a C++ OLE DB Consumer Application, demonstrate the wide variety and specific use of connection strings for commonly used drivers and providers. Additional discussion is included on the following topics:
The following file is available for download from the Microsoft Software Library:
~ Mdaccon.exe (size: 110938 bytes)
For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q119591
TITLE : How to Obtain Microsoft Support Files from Online Services
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.
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.
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:
The syntax for a DSN, System DSN or File DSN follows:
{DSN=name|FileDSN=filename};[Database=database;]uid=userid;pwd=password
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
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;...
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=sa;PWD=;"
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=;"
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=sa;PASSWORD=;"
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=sa;PWD=;"
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.
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=sa;PASSWORD=;"
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=sa;PWD=;"
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.
Data Access SDK; search on: "Connection String Syntax"; "Properties Table".
Microsoft Developer Network: search on: "Using OLE DB Providers with ADO"
The white paper "OLE DB for the ODBC Programmer" contains a useful discussion for creating an OLE DB Consumer Application that was the basis for the OLE DB sample used with this article. You can download this white paper from the following location:
http://www.microsoft.com/data/oledb/prodinfo/wpapers/oledb4odbc.htm
"Setting Connection String Parameters in DAO", white paper, by Joel Gilman. This white paper can be found at the following Web URL:
http://www.microsoft.com/accessdev/articles/daoconst.htm
Additional query words: kbDSupport kbdse odbc oledb ado rds connection string kbDAO300 kbDAO350 kbODBC kbADO kbOLEDB kbMDAC kbRDS kbSDKDataAc kbSDKODBC kbSDKOLEDB kbDatabase
Technology : odbc
Version : WINDOWS:1.0,1.1,1.5,2.0,2.1,3.0,3.5,3.51
Platform : WINDOWS
Issue type : kbinfoLast Reviewed: October 1, 1998