Search code examples
odbcoledb

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

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.

Solution

  • According to ADO: ActiveX Data Objects, a book by Jason T. Roff, published by O'Reilly Media in 2001 (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).