what is the difference between OLE DB and ODBC data sources?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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)

Answer 4

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 most part OLEDB remains Microsoft standard. Now, most Microsoft data sources allow both ODBC and OLEDB access, the first primarily for backward compatibility with legacy 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 featureset 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 still widely used.

In Microsoft world 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).