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

User · 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

User · Answer

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

User · Answer

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

User · 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  See Distributed Queries on MSDN for more

User · Answer

ODBC works only for relational databases  it can t works with non-relational databases such as Ms Excel files  Where Olebd can do everything

User · Answer

ODBC and OLE DB are two competing data access technologies  Specifically regarding SQL Server  Microsoft has promoted both of them as their Preferred Future Direction - though at different times   ODBC  ODBC is an industry-wide standard interface for accessing table-like data  It was primarily developed for databases and presents data in collections of records  each of which is grouped into a collection of fields  Each field has its own data type suitable to the type of data it contains  Each database vendor  Microsoft  Oracle  Postgres       supplies an ODBC driver for their database    There are also ODBC drivers for objects which  though they are not database tables  are sufficiently similar that accessing data in the same way is useful  Examples are spreadsheets  CSV files and columnar reports   OLE DB  OLE DB is a Microsoft technology for access to data  Unlike ODBC it encompasses both table-like and non-table-like data such as email messages  web pages  Word documents and file directories  However  it is procedure-oriented rather than object-oriented and is regarded as a rather difficult interface with which to develop access to data sources  To overcome this  ADO was designed to be an object-oriented layer on top of OLE DB and to provide a simpler and higher-level     though still very powerful     way of working with it  ADO   s great advantage it that you can use it to manipulate properties which are specific to a given type of data source  just as easily as you can use it to access those properties which apply to all data source types  You are not restricted to some unsatisfactory lowest common denominator   While all databases have ODBC drivers  they don   t all have OLE DB drivers  There is however an interface available between OLE and ODBC which can be used if you want to access them in OLE DB-like fashion  This interface is called MSDASQL  Microsoft OLE DB provider for ODBC     SQL Server Data Access Technologies  Since SQL Server is  1  made by Microsoft  and  2  the Microsoft database platform  both ODBC and OLE DB are a natural fit for it   ODBC  Since all other database platforms had ODBC interfaces  Microsoft obviously had to provide one for SQL Server  In addition to this  DAO  the original default technology in Microsoft Access  uses ODBC as the standard way of talking to all external data sources  This made an ODBC interface a sine qua non  The version 6 ODBC driver for SQL Server  released with SQL Server 2000  is still around  Updated versions have been released to handle the new data types  connection technologies  encryption  HA DR etc  that have appeared with subsequent releases  As of 09 07 2018 the most recent release is v13 1    ODBC Driver for SQL Server     released on 23 03 2018   OLE DB  This is Microsoft   s own technology  which they were promoting strongly from about 2002     2005  along with its accompanying ADO layer  They were evidently hoping that it would become the data access technology of choice   They even made ADO the default method for accessing data in Access 2002 2003   However  it eventually became apparent that this was not going to happen for a number of reasons  such as    The world was not going to convert to Microsoft technologies and away from ODBC  DAO ODBC was faster than ADO OLE DB and was also thoroughly integrated into MS Access  so wasn   t going to die a natural death  New technologies that were being developed by Microsoft  specifically ADO NET   could also talk directly to ODBC  ADO NET could talk directly to OLE DB as well  thus leaving ADO in a backwater   but it was not  unlike ADO  solely dependent on it    For these reasons and others  Microsoft actually deprecated OLE DB as a data access technology for SQL Server releases after v11  SQL Server 2012   For a couple of years before this point  they had been producing and updating the SQL Server Native Client  which supported both ODBC and OLE DB technologies  In late 2012 however  they announced that they would be aligning with ODBC for native relational data access in SQL Server  and encouraged everybody else to do the same  They further stated that SQL Server releases after v11 SQL Server 2012 would actively not support OLE DB   This announcement provoked a storm of protest  People were at a loss to understand why MS was suddenly deprecating a technology that they had spent years getting them to commit to  In addition  SSAS SSRS and SSIS  which were MS-written applications intimately linked to SQL Server  were wholly or partly dependent on OLE DB  Yet another complaint was that OLE DB had certain desirable features which it seemed impossible to port back to ODBC     after all  OLE DB had many good points   In October 2017  Microsoft relented and officially un-deprecated OLE DB  They announced the imminent arrival of a new driver  MSOLEDBSQL  which would have the existing feature set of the Native Client 11 and would also introduce multi-subnet failover and TLS 1 2 support  The driver was released in March 2018

User · Answer

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   You can read more on both in Wikipedia    OLE DB ODBC   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

User · Answer

August  2011  Microsoft deprecates OLE DB  Microsoft is Aligning with ODBC for Native Relational Data Access       October  2017  Microsoft undeprecates OLE DB  Announcing the new release of OLE DB Driver for SQL Server

User · Answer

To know why M  invents OLEDB  you can t compare OLEDB with ODBC  Instead  you should compare OLEDB with DAO RDO  or ADO  The latter largely relies on SQL  However  OLEDB relies on COM  But ODBC is already there many years  so there s a OLEDB-ODBC bridges to remedy this  I think there s a big picture when M  invents OLEDB

User · Answer

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

User · 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

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

Examples related to odbc

Examples related to oledb