Contents
| | |
|
Microsoft OLE DB Provider for 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 1.5 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
MSDASQLReading 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 calling 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 on 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:
Property ForwardOnly Dynamic Keyset Static AbsolutePage not available not available read/write read/write AbsolutePosition not available not available read/write read/write ActiveConnection read/write read/write read/write read/write BOF read-only read-only read-only read-only Bookmark not available not available read/write read/write CacheSize read/write read/write read/write read/write CursorLocation read/write read/write read/write read/write CursorType read/write read/write read/write read/write EditMode read-only read-only read-only read-only EOF read-only read-only read-only read-only Filter read/write read/write read/write read/write LockType read/write read/write read/write read/write MarshalOptions read/write read/write read/write read/write MaxRecords read/write read/write read/write read/write PageCount not available not available read-only read-only PageSize read/write read/write read/write read/write RecordCount not available not available read-only read-only Source read/write read/write read/write read/write State read-only read-only read-only read-only Status read-only read-only read-only read-only
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:
Method ForwardOnly Dynamic Keyset Static AddNew Yes Yes Yes Yes CancelBatch Yes Yes Yes Yes CancelUpdate Yes Yes Yes Yes Clone No No Yes Yes Close Yes Yes Yes Yes Delete Yes Yes Yes Yes GetRows Yes Yes Yes Yes Move Yes Yes Yes Yes MoveFirst Yes Yes Yes Yes MoveLast No Yes Yes Yes MoveNext Yes Yes Yes Yes MovePrevious No Yes Yes Yes NextRecordset* Yes Yes Yes Yes Open Yes Yes Yes Yes Requery Yes Yes Yes Yes Resync No No Yes Yes Supports Yes Yes Yes Yes Update Yes Yes Yes Yes UpdateBatch Yes Yes Yes Yes
*Not supported for Microsoft Access databases.
For specific implementation details and functional information about the Microsoft OLE DB Provider for ODBC, consult the documents "Microsoft OLE DB Provider for ODBC" and the "Microsoft OLE DB Programmer's Reference," available in the OLE DB SDK, which you can review or download at Microsoft OLE DB Web page. The "Microsoft ODBC Programmer's Reference" is available on the Microsoft ODBC Web page.
Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.