Take the 2-minute tour ×

I was reading a MS Excel help article about pivotcache and wonder what they mean by OLE DB and ODBC sources

...You should use the CommandText property instead of the SQL property, which now exists primarily for compatibility with earlier versions of Microsoft Excel. If you use both properties, the CommandText property’s value takes precedence.

For OLE DB sources, the CommandType property describes the value of the CommandText property.

For ODBC sources, the CommandText property functions exactly like the SQL property, and setting the property causes the data to be refreshed...

I really appreciate your short answers.

share|improve this question

7 Answers 7

up vote 45 down vote accepted

According to this book (excellent diagram here), he says precisely what MOZILLA said.

(directly from page 7 of that book)

  • ODBC provides access only to relational databases
  • OLE DB provides the following features
    • Access to data regardless of its format or location
    • Full access to ODBC data sources and ODBC drivers
  • So it would seem that OLE DB interacts with SQL-based datasources THRU the ODBC driver layer.

    alt text

    I'm not 100% sure this image is correct. The two connections I'm not certain about are ADO.NET thru ADO C-api, and OLE DB thru ODBC to SQL-based data source (because in this diagram the author doesn't put OLE DB's access thru ODBC, which I believe is a mistake).

    share|improve this answer
    If OLE DB uses ODBC to connect to SQL data sources, then any SQL data source which is supported by OLE DB would have to be supported by ODBC, however this is not the case - the original diagram must have been correct (and not this one). –  Danny Varod Jul 18 '11 at 15:52
    Actually sometimes OLE DB wraps the ODBC driver, sometimes it doesn't. See here –  bobobobo Apr 11 '12 at 2:42
    This entry jamesmccaffrey.wordpress.com/2006/05/02/odbc-vs-ole-db says that for SQL DS, OLEDB goes thru ODBC. –  Hernán Mar 27 '13 at 18:18
    @DannyVarod Isn't that the wrong way around? Did you mean, "If OLE DB uses ODBC to connect to SQL data sources, any SQL data source which is supported by ODBC must be supported by OLE DB"? –  Asad Nov 7 '13 at 20:59
    @DannyVarod Ah, never mind. I missed the critical qualifier in "any SQL data source which is supported by OLE DB would ...". I was talking about the fact that since OLE DB supports non-RDBMS data sources, it is entirely possible for the unfiltered set of data sources supported by OLE DB to be a superset of the ones supported by ODBC. –  Asad Nov 9 '13 at 3:05

    Here's my understanding (non-authoritative):

    ODBC is a technology-agnostic open standard supported by most software vendors. OLEDB is a technology-specific Microsoft's API from the COM-era (COM was a component and interoperability technology before .NET)

    At some point various datasouce vendors (e.g. Oracle etc.), willing to be compatible with Microsoft data consumers, developed OLEDB providers for their products, but for the most part OLEDB remains a Microsoft-only standard. Now, most Microsoft data sources allow both ODBC and OLEDB access, mainly for compatibility with legacy ODBC data consumers. Also, there exists OLEDB provider (wrapper) for ODBC which allows one to use OLEDB to access ODBC data sources if one so wishes.

    In terms of the features OLEDB is substantially richer than ODBC but suffers from one-ring-to-rule-them-all syndrome (overly generic, overcomplicated, non-opinionated).

    In non-Microsoft world ODBC-based data providers and clients are widely used and not going anywhere.

    Inside Microsoft bubble OLEDB is being phased out in favor of native .NET APIs build on top of whatever the native transport layer for that data source is (e.g. TDS for MS SQL Server).

    share|improve this answer

    ODBC:- Only for relational databases (Sql Server, Oracle etc)

    OLE DB:- For both relational and non-relational databases. (Oracle, Sql-Server, Excel, raw files, etc)

    share|improve this answer
    Is this not correct? –  MOZILLA Apr 24 '09 at 13:58
    It is correct. I don't know how you got -1 for this. –  bobobobo Jul 13 '09 at 15:00
    Upvoted 1.5 years later since someone dinked you. –  skaz Mar 10 '11 at 16:43
    Wrong, both can talk to non-relational stores depending on the drivers. –  Andy Dent Aug 11 '11 at 15:10

    On a very basic level those are just different APIs for the different data sources (i.e. databases). OLE DB is newer and arguably better.

    1. http://en.wikipedia.org/wiki/OLE_DB
    2. http://en.wikipedia.org/wiki/Open_Database_Connectivity

    I.e. you could connect to the same database using an ODBC driver or OLE DB driver. The difference in the database behaviour in those cases is what your book refers to.

    share|improve this answer
    As with many IT related subjects, things have almost come full circle. SQL 2012 was the last version to support OLE DB Native provider and applications should now switch to back ODBC. like the "olden days" of SQL Server technet.microsoft.com/en-us/library/hh967418.aspx –  Chris Wood Jun 4 at 14:55
    "OLE DB is newer and arguably better" this may have been true in 2008 but not in 2014. –  Michael David Watson Jun 20 at 21:04

    I'm not sure of all the details, but my understanding is that OLE DB and ODBC are two APIs that are available for connecting to various types of databases without having to deal with all the implementation specific details of each. According to the Wikipedia article on OLE DB, OLE DB is Microsoft's successor to ODBC, and provides some features that you might not be able to do with ODBC such as accessing spreadsheets as database sources.

    share|improve this answer

    Both are data providers (API that your code will use to talk to a data source). Oledb which was introduced in 1998 was meant to be a replacement for ODBC (introduced in 1992)

    share|improve this answer

    At Microsoft website, it shows that native OLEDB provider is applied to SQL server directly and another OLEDB provider called OLEDB Provider for ODBC to access other Database, such as Sysbase, DB2 etc. There are different kinds of component under OLEDB Provider. Refer to :http://msdn.microsoft.com/en-us/library/ms188721(v=SQL.105).aspx

    share|improve this answer

    Your Answer


