Microsoft OLE DB Provider for ODBC

Applies to: Access 2013, 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.

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.

Typical Connection String

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.

Provider-Specific Connection Properties

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.

Provider-Specific Recordset and Command Properties

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

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

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:

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.

Dynamic Properties

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.

Connection Dynamic 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

Recordset Dynamic Properties

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

Command Dynamic Properties

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 also

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