Microsoft OLE DB Provider for ODBC

To an ADO or RDS programmer, an ideal world would be one in which every data source exposes an OLE DB interface, so that ADO could call directly into the data source. Although more and more database vendors are implementing OLE DB interfaces, some data sources are not yet exposed this way. However, virtually all DBMS systems in use today can be accessed through ODBC.

The Microsoft^® ODBC Provider allows ADO to connect to any ODBC data source. ODBC drivers are available for every major DBMS in use today, including Microsoft^® SQL Server™, Microsoft Access (Microsoft Jet database engine), and Microsoft FoxPro^®, as well as non-Microsoft database products such as Oracle. The provider is free-threaded and unicode enabled.

The provider supports transactions, although different DBMS engines offer different types of transaction support. For example, Microsoft Access supports nested transactions, up to five levels deep.

This is the default provider for ADO and, when used with Microsoft SQL Server 6.5, all provider-dependent ADO properties and methods are supported, except as noted in the ADO language reference topics.

Connection String Parameters

To connect to this provider, set the Provider= argument of the ConnectionString property to:

MSDASQL

Reading the Provider property will return this string as well.

Because this is the default provider for ADO, if you omit the Provider= parameter from the connection string, ADO will attempt to establish a connection to this provider.

The provider does not support any specific connection parameters in addition to those defined by ADO. However, the provider will pass any non-ADO connection parameters to the ODBC driver manager.

Because you can omit the Provider parameter, you can therefore compose an ADO connection string that is identical to an ODBC connection string for the same data source, using the same parameter names (DRIVER=, DATABASE=, DSN=, and so on), values, and syntax as you would when composing an ODBC connection string. You can connect with or without a predefined data source name (DSN) or FileDSN.

Syntax with a DSN or FileDSN:

"[Provider=MSDASQL;] { DSN=name | FileDSN=filename } ; [DATABASE=database;] UID=user; PWD=password"

Syntax without a DSN (DSN-less connection):

"[Provider=MSDASQL;] DRIVER=driver; SERVER=server; DATABASE=database; UID=user; PWD=password"

If you use a DSN or FileDSN, it must be defined through the ODBC Administrator in the Windows Control Panel. As an alternative to setting a DSN, you can specify the ODBC driver (DRIVER=), such as "SQLServer," the server name (SERVER=), and the database name (DATABASE=).

You can also specify a user account name (UID=), and the password for the user account (PWD=) in the ODBC-specific parameters or in the standard ADO-defined User ID and Password parameters. If you include both the ADO and the ODBC-specific parameters for these values, the ADO parameters take precedence.

Although a DSN definition already specifies a database, you can specify a DATABASE parameter in addition to a DSN to connect to a different database. This also changes the DSN definition to include the specified database. It is a good idea to always include the DATABASE parameter when you use a DSN. This will ensure that you connect to the proper database because another user may have changed the default database parameter since you last checked the DSN definition.

Command Text

How you use the Command object largely depends on the data source, and what type of query or command statement it will accept.

ODBC does provide a specific syntax for calling stored procedures. For the CommandText property of a Command object, the CommandText argument to the Execute method on a Connection object, or the Source argument to the Open method on a Recordset object, pass in a string having this syntax:

"{ [ ? = ] call procedure [ ( ? [, ? [ , … ]] ) ] }"

Where each ? references an object in the Parameters collection. The first ? references Parameters(0), the next ? references Parameters(1), and so on.

The parameter references are optional and depend on the structure of the stored procedure. If you want to call a stored procedure that defines no parameters, your string would look like this:

"{ call procedure }"

If you have two query parameters, your string would look like this:

"{ call procedure ( ?, ? ) }"

If the stored procedure wants to return a value, the return value is treated as another parameter. If you have no query parameters but you do have a return value, your string would look like this:

"{ ? = call procedure }"

Finally, if you have a return value and two query parameters, your string would look like this:

"{ ? = call procedure ( ?, ? ) }"

Recordset Behavior

The following tables list the standard ADO methods and properties available on a Recordset object opened with this provider.

For more detailed information about Recordset behavior for your provider configuration, run the Supports method and enumerate the Properties collection of the Recordset to determine whether provider-specific dynamic properties are present.

Availability of standard ADO Recordset properties:

The AbsolutePosition and AbsolutePage properties are write-only when ADO is used with version 1.0 of the Microsoft OLE DB Provider for ODBC.

Availability of standard ADO Recordset methods:

*Not supported for Microsoft Access databases.

See Also   For specific implementation details and functional information about the Microsoft OLE DB Provider for ODBC, consult the documentation for Microsoft OLE DB Provider for ODBC and the Microsoft OLE DB Programmer's Reference, available in the Data Access SDK. You can also see the Data Access Web page at http://www.microsoft.com/data.