Microsoft OLE DB Provider for ODBC

Office 2013

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 increasingly 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.

ODBC drivers are available for every major DBMS in use today, including Microsoft SQL Server, Microsoft Access (Microsoft Jet database engine), and Microsoft FoxPro, in addition to non-Microsoft database products such as Oracle.

The Microsoft ODBC Provider, however, allows ADO to connect to any ODBC data source. 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 all provider-dependent ADO properties and methods are supported.

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.

A typical connection string for this provider is:

 
"Provider=MSDASQL;DSN=dsnName;UID=userName;PWD=userPassword;" 

The string consists of these keywords:

Keyword

Description

Provider

Specifies the OLE DB Provider for ODBC.

DSN

Specifies the data source name.

UID

Specifies the user name.

PWD

Specifies the user password.

URL

Specifies the URL of a file or directory published in a Web folder.

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. Use 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 Data Source Administrator in the Windows Control Panel. In Microsoft Windows 2000, the ODBC Administrator is located under Administrative Tools. In previous versions of Windows, the ODBC Administrator icon is named 32-bit ODBC or simply ODBC.

As an alternative to setting a DSN, you can specify the ODBC driver (DRIVER=), such as "SQL Server;" 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 and password parameters.

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. 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 in the event that another user changed the default database parameter since you last checked the DSN definition.

The OLE DB provider for ODBC adds several properties to the Properties collection of the Connection object. The following table lists these properties with the corresponding OLE DB property name in parentheses.

Property Name

Description

Accessible Procedures
(KAGPROP_ACCESSIBLEPROCEDURES)

Indicates whether the user has access to stored procedures.

Accessible Tables
(KAGPROP_ACCESSIBLETABLES)

Indicates whether the user has permission to execute SELECT statements against the database tables.

Active Statements
(KAGPROP_ACTIVESTATEMENTS)

Indicates the number of handles an ODBC driver can support on a connection.

Driver Name
(KAGPROP_DRIVERNAME)

Indicates the file name of the ODBC driver.

Driver ODBC Version
(KAGPROP_DRIVERODBCVER)

Indicates the version of ODBC that this driver supports.

File Usage
(KAGPROP_FILEUSAGE)

Indicates how the driver treats a file in a data source; as a table or as a catalog.

Like Escape Clause
(KAGPROP_LIKEESCAPECLAUSE)

Indicates whether the driver supports the definition and use of an escape character for the percent character (%) and underline character (_) in the LIKE predicate of a WHERE clause.

Max Columns in Group By
(KAGPROP_MAXCOLUMNSINGROUPBY)

Indicates the maximum number of columns that can be listed in the GROUP BY clause of a SELECT statement.

Max Columns in Index
(KAGPROP_MAXCOLUMNSININDEX)

Indicates the maximum number of columns that can be included in an index.

Max Columns in Order By
(KAGPROP_MAXCOLUMNSINORDERBY)

Indicates the maximum number of columns that can be listed in the ORDER BY clause of a SELECT statement.

Max Columns in Select
(KAGPROP_MAXCOLUMNSINSELECT)

Indicates the maximum number of columns that can be listed in the SELECT portion of a SELECT statement.

Max Columns in Table
(KAGPROP_MAXCOLUMNSINTABLE)

Indicates the maximum number of columns allowed in a table.

Numeric Functions
(KAGPROP_NUMERICFUNCTIONS)

Indicates which numeric functions are supported by the ODBC driver. For a listing of function names and the associated values used in this bitmask, see Appendix E: Scalar Functions in the ODBC documentation.

Outer Join Capabilities
(KAGPROP_OJCAPABILITY)

Indicates the types of OUTER JOINs supported by the provider.

Outer Joins
(KAGPROP_OUTERJOINS)

Indicates whether the provider supports OUTER JOINs.

Special Characters
(KAGPROP_SPECIALCHARACTERS)

Indicates which characters have special meaning for the ODBC driver.

Stored Procedures
(KAGPROP_PROCEDURES)

Indicates whether stored procedures are available for use with this ODBC driver.

String Functions
(KAGPROP_STRINGFUNCTIONS)

Indicates which string functions are supported by the ODBC driver. For a listing of function names and the associated values used in this bitmask, see Appendix E: Scalar Functions in the ODBC documentation.

System Functions
(KAGPROP_SYSTEMFUNCTIONS)

Indicates which system functions are supported by the ODBC driver. For a listing of function names and the associated values used in this bitmask, see Appendix E: Scalar Functions in the ODBC documentation.

Time/Date Functions
(KAGPROP_TIMEDATEFUNCTIONS)

Indicates which time and date functions are supported by the ODBC driver. For a listing of function names and the associated values used in this bitmask, see Appendix E: Scalar Functions in the ODBC documentation.

SQL Grammar Support
(KAGPROP_ODBCSQLCONFORMANCE)

Indicates the SQL grammar that the ODBC driver supports.

The OLE DB provider for ODBC adds several properties to the Properties collection of the Recordset and Command objects. The following table lists these properties with the corresponding OLE DB property name in parentheses.

Property Name

Description

Query Based Updates/Deletes/Inserts
(KAGPROP_QUERYBASEDUPDATES)

Indicates whether updates, deletions, and insertions can be performed using SQL queries.

ODBC Concurrency Type
(KAGPROP_CONCURRENCY)

Indicates the method used to reduce potential problems caused by two users attempting to access the same data from the data source simultaneously.

BLOB accessibility on Forward-Only cursor
(KAGPROP_BLOBSONFOCURSOR)

Indicates whether BLOB Fields can be accessed when using a forward-only cursor.

Include SQL_FLOAT, SQL_DOUBLE, and SQL_REAL in QBU WHERE clauses
(KAGPROP_INCLUDENONEXACT)

Indicates whether SQL_FLOAT, SQL_DOUBLE, and SQL_REAL values can be included in a QBU WHERE clause.

Position on the last row after insert
(KAGPROP_POSITIONONNEWROW)

Indicates that after a new record has been inserted in a table, the last row in the table will be come the current row.

IRowsetChangeExtInfo
(KAGPROP_IROWSETCHANGEEXTINFO)

Indicates whether the IRowsetChange interface provides extended information support.

ODBC Cursor Type
(KAGPROP_CURSOR)

Indicates the type of cursor used by the Recordset.

Generate a Rowset that can be marshaled
(KAGPROP_MARSHALLABLE)

Indicates that the ODBC driver generates a recordset that can be marshaled

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

ODBC provides 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, passes in a string with this syntax:


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

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 will 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 
                      ( ?, ? ) }"
                    

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:

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

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

read/write

not available

read-only

read-only

PageSize

read/write

read/write

read/write

read/write

RecordCount

read/write

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

Cancel

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.

The Microsoft OLE DB Provider for ODBC inserts several dynamic properties into the Properties collection of the unopened Connection, Recordset, and Command objects.

The tables below are a cross-index of the ADO and OLE DB names for each dynamic property. The OLE DB Programmer's Reference refers to an ADO property name by the term, "Description." You can find more information about these properties in the OLE DB Programmer's Reference. Search for the OLE DB property name in the Index or see Appendix C: OLE DB Properties.

The following properties are added to the Connection object's Properties collection.

ADO Property Name

OLE DB Property Name

Active Sessions

DBPROP_ACTIVESESSIONS

Asynchable Abort

DBPROP_ASYNCTXNABORT

Asynchable Commit

DBPROP_ASYNCTNXCOMMIT

Autocommit Isolation Levels

DBPROP_SESS_AUTOCOMMITISOLEVELS

Catalog Location

DBPROP_CATALOGLOCATION

Catalog Term

DBPROP_CATALOGTERM

Column Definition

DBPROP_COLUMNDEFINITION

Connect Timeout

DBPROP_INIT_TIMEOUT

Current Catalog

DBPROP_CURRENTCATALOG

Data Source

DBPROP_INIT_DATASOURCE

Data Source Name

DBPROP_DATASOURCENAME

Data Source Object Threading Model

DBPROP_DSOTHREADMODEL

DBMS Name

DBPROP_DBMSNAME

DBMS Version

DBPROP_DBMSVER

Extended Properties

DBPROP_INIT_PROVIDERSTRING

GROUP BY Support

DBPROP_GROUPBY

Heterogeneous Table Support

DBPROP_HETEROGENEOUSTABLES

Identifier Case Sensitivity

DBPROP_IDENTIFIERCASE

Initial Catalog

DBPROP_INIT_CATALOG

Isolation Levels

DBPROP_SUPPORTEDTXNISOLEVELS

Isolation Retention

DBPROP_SUPPORTEDTXNISORETAIN

Locale Identifier

DBPROP_INIT_LCID

Location

DBPROP_INIT_LOCATION

Maximum Index Size

DBPROP_MAXINDEXSIZE

Maximum Row Size

DBPROP_MAXROWSIZE

Maximum Row Size Includes BLOB

DBPROP_MAXROWSIZEINCLUDESBLOB

Maximum Tables in SELECT

DBPROP_MAXTABLESINSELECT

Mode

DBPROP_INIT_MODE

Multiple Parameter Sets

DBPROP_MULTIPLEPARAMSETS

Multiple Results

DBPROP_MULTIPLERESULTS

Multiple Storage Objects

DBPROP_MULTIPLESTORAGEOBJECTS

Multi-Table Update

DBPROP_MULTITABLEUPDATE

NULL Collation Order

DBPROP_NULLCOLLATION

NULL Concatenation Behavior

DBPROP_CONCATNULLBEHAVIOR

OLE DB Services

DBPROP_INIT_OLEDBSERVICES

OLE DB Version

DBPROP_PROVIDEROLEDBVER

OLE Object Support

DBPROP_OLEOBJECTS

Open Rowset Support

DBPROP_OPENROWSETSUPPORT

ORDER BY Columns in Select List

DBPROP_ORDERBYCOLUMNSINSELECT

Output Parameter Availability

DBPROP_OUTPUTPARAMETERAVAILABILITY

Password

DBPROP_AUTH_PASSWORD

Pass By Ref Accessors

DBPROP_BYREFACCESSORS

Persist Security Info

DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO

Persistent ID Type

DBPROP_PERSISTENTIDTYPE

Prepare Abort Behavior

DBPROP_PREPAREABORTBEHAVIOR

Prepare Commit Behavior

DBPROP_PREPARECOMMITBEHAVIOR

Procedure Term

DBPROP_PROCEDURETERM

Prompt

DBPROP_INIT_PROMPT

Provider Friendly Name

DBPROP_PROVIDERFRIENDLYNAME

Provider Name

DBPROP_PROVIDERFILENAME

Provider Version

DBPROP_PROVIDERVER

Read-Only Data Source

DBPROP_DATASOURCEREADONLY

Rowset Conversions on Command

DBPROP_ROWSETCONVERSIONSONCOMMAND

Schema Term

DBPROP_SCHEMATERM

Schema Usage

DBPROP_SCHEMAUSAGE

SQL Support

DBPROP_SQLSUPPORT

Structured Storage

DBPROP_STRUCTUREDSTORAGE

Subquery Support

DBPROP_SUBQUERIES

Table Term

DBPROP_TABLETERM

Transaction DDL

DBPROP_SUPPORTEDTXNDDL

User ID

DBPROP_AUTH_USERID

User Name

DBPROP_USERNAME

Window Handle

DBPROP_INIT_HWND

The following properties are added to the Recordset object's Properties collection.

ADO Property Name

OLE DB Property Name

Access Order

DBPROP_ACCESSORDER

Blocking Storage Objects

DBPROP_BLOCKINGSTORAGEOBJECTS

Bookmark Type

DBPROP_BOOKMARKTYPE

Bookmarkable

DBPROP_IROWSETLOCATE

Change Inserted Rows

DBPROP_CHANGEINSERTEDROWS

Column Privileges

DBPROP_COLUMNRESTRICT

Column Set Notification

DBPROP_NOTIFYCOLUMNSET

Delay Storage Object Updates

DBPROP_DELAYSTORAGEOBJECTS

Fetch Backwards

DBPROP_CANFETCHBACKWARDS

Hold Rows

DBPROP_CANHOLDROWS

IAccessor

DBPROP_IAccessor

IColumnsInfo

DBPROP_IColumnsInfo

IColumnsRowset

DBPROP_IColumnsRowset

IConnectionPointContainer

DBPROP_IConnectionPointContainer

IConvertType

DBPROP_IConvertType

Immobile Rows

DBPROP_IMMOBILEROWS

IRowset

DBPROP_IRowset

IRowsetChange

DBPROP_IRowsetChange

IRowsetIdentity

DBPROP_IRowsetIdentity

IRowsetInfo

DBPROP_IRowsetInfo

IRowsetLocate

DBPROP_IRowsetLocate

IRowsetResynch

IRowsetUpdate

DBPROP_IRowsetUpdate

ISequentialStream

DBPROP_ISequentialStream

ISupportErrorInfo

DBPROP_ISupportErrorInfo

Literal Bookmarks

DBPROP_LITERALBOOKMARKS

Literal Row Identity

DBPROP_LITERALIDENTITY

Maximum Open Rows

DBPROP_MAXOPENROWS

Maximum Pending Rows

DBPROP_MAXPENDINGROWS

Maximum Rows

DBPROP_MAXROWS

Notification Granularity

DBPROP_NOTIFICATIONGRANULARITY

Notification Phases

DBPROP_NOTIFICATIONPHASES

Objects Transacted

DBPROP_TRANSACTEDOBJECT

Own Changes Visible

DBPROP_OWNUPDATEDELETE

Own Inserts Visible

DBPROP_OWNINSERT

Preserve on Abort

DBPROP_ABORTPRESERVE

Preserve on Commit

DBPROP_COMMITPRESERVE

Quick Restart

DBPROP_QUICKRESTART

Reentrant Events

DBPROP_REENTRANTEVENTS

Remove Deleted Rows

DBPROP_REMOVEDELETED

Report Multiple Changes

DBPROP_REPORTMULTIPLECHANGES

Return Pending Inserts

DBPROP_RETURNPENDINGINSERTS

Row Delete Notification

DBPROP_NOTIFYROWDELETE

Row First Change Notification

DBPROP_NOTIFYROWFIRSTCHANGE

Row Insert Notification

DBPROP_NOTIFYROWINSERT

Row Privileges

DBPROP_ROWRESTRICT

Row Resynchronization Notification

DBPROP_NOTIFYROWRESYNCH

Row Threading Model

DBPROP_ROWTHREADMODEL

Row Undo Change Notification

DBPROP_NOTIFYROWUNDOCHANGE

Row Undo Delete Notification

DBPROP_NOTIFYROWUNDODELETE

Row Undo Insert Notification

DBPROP_NOTIFYROWUNDOINSERT

Row Update Notification

DBPROP_NOTIFYROWUPDATE

Rowset Fetch Position Change Notification

DBPROP_NOTIFYROWSETFETCHPOSISIONCHANGE

Rowset Release Notification

DBPROP_NOTIFYROWSETRELEASE

Scroll Backwards

DBPROP_CANSCROLLBACKWARDS

Skip Deleted Bookmarks

DBPROP_BOOKMARKSKIPPED

Strong Row Identity

DBPROP_STRONGITDENTITY

Unique Rows

DBPROP_UNIQUEROWS

Updatability

DBPROP_UPDATABILITY

Use Bookmarks

DBPROP_BOOKMARKS

The following properties are added to the Command object's Properties collection.

ADO Property Name

OLE DB Property Name

Access Order

DBPROP_ACCESSORDER

Blocking Storage Objects

DBPROP_BLOCKINGSTORAGEOBJECTS

Bookmark Type

DBPROP_BOOKMARKTYPE

Bookmarkable

DBPROP_IROWSETLOCATE

Change Inserted Rows

DBPROP_CHANGEINSERTEDROWS

Column Privileges

DBPROP_COLUMNRESTRICT

Column Set Notification

DBPROP_NOTIFYCOLUMNSET

Delay Storage Object Updates

DBPROP_DELAYSTORAGEOBJECTS

Fetch Backwards

DBPROP_CANFETCHBACKWARDS

Hold Rows

DBPROP_CANHOLDROWS

IAccessor

DBPROP_IAccessor

IColumnsInfo

DBPROP_IColumnsInfo

IColumnsRowset

DBPROP_IColumnsRowset

IConnectionPointContainer

DBPROP_IConnectionPointContainer

IConvertType

DBPROP_IConvertType

Immobile Rows

DBPROP_IMMOBILEROWS

IRowset

DBPROP_IRowset

IRowsetChange

DBPROP_IRowsetChange

IRowsetIdentity

DBPROP_IRowsetIdentity

IRowsetInfo

DBPROP_IRowsetInfo

IRowsetLocate

DBPROP_IRowsetLocate

IRowsetResynch

IRowsetUpdate

DBPROP_IRowsetUpdate

ISequentialStream

DBPROP_ISequentialStream

ISupportErrorInfo

DBPROP_ISupportErrorInfo

Literal Bookmarks

DBPROP_LITERALBOOKMARKS

Literal Row Identity

DBPROP_LITERALIDENTITY

Maximum Open Rows

DBPROP_MAXOPENROWS

Maximum Pending Rows

DBPROP_MAXPENDINGROWS

Maximum Rows

DBPROP_MAXROWS

Notification Granularity

DBPROP_NOTIFICATIONGRANULARITY

Notification Phases

DBPROP_NOTIFICATIONPHASES

Objects Transacted

DBPROP_TRANSACTEDOBJECT

Own Changes Visible

DBPROP_OWNUPDATEDELETE

Own Inserts Visible

DBPROP_OWNINSERT

Preserve on Abort

DBPROP_ABORTPRESERVE

Preserve on Commit

DBPROP_COMMITPRESERVE

Quick Restart

DBPROP_QUICKRESTART

Reentrant Events

DBPROP_REENTRANTEVENTS

Remove Deleted Rows

DBPROP_REMOVEDELETED

Report Multiple Changes

DBPROP_REPORTMULTIPLECHANGES

Return Pending Inserts

DBPROP_RETURNPENDINGINSERTS

Row Delete Notification

DBPROP_NOTIFYROWDELETE

Row First Change Notification

DBPROP_NOTIFYROWFIRSTCHANGE

Row Insert Notification

DBPROP_NOTIFYROWINSERT

Row Privileges

DBPROP_ROWRESTRICT

Row Resynchronization Notification

DBPROP_NOTIFYROWRESYNCH

Row Threading Model

DBPROP_ROWTHREADMODEL

Row Undo Change Notification

DBPROP_NOTIFYROWUNDOCHANGE

Row Undo Delete Notification

DBPROP_NOTIFYROWUNDODELETE

Row Undo Insert Notification

DBPROP_NOTIFYROWUNDOINSERT

Row Update Notification

DBPROP_NOTIFYROWUPDATE

Rowset Fetch Position Change Notification

DBPROP_NOTIFYROWSETFETCHPOSITIONCHANGE

Rowset Release Notification

DBPROP_NOTIFYROWSETRELEASE

Scroll Backwards

DBPROP_CANSCROLLBACKWARDS

Skip Deleted Bookmarks

DBPROP_BOOKMARKSKIP

Strong Row Identity

DBPROP_STRONGIDENTITY

Updatability

DBPROP_UPDATABILITY

Use Bookmarks

DBPROP_BOOKMARKS

See AlsoFor details regarding specific implementation and functional information about the Microsoft OLE DB Provider for ODBC, consult the OLE DB Programmer's Guide or visit the Data Platform Developer Center.

Community Additions

ADD
Show:
© 2014 Microsoft