5.1.7. OleDB connections

<< Click to Display Table of Contents >>

Navigation:  5. Detailed description of the Actions > 5.1. General Parameters used in many actions >

5.1.7. OleDB connections

Typically, you won’t install simultaneously an ODBC driver and an OleDB drivers to connect to your database: it’s either one or the other. If you have the choice, you should favor the installation and the usage of the ODBC drivers (rather than the OleDB drivers).


You should favor ODBC over OleDB because:

1.For almost all databases, ODBC connections are much faster (around 3 times to 20 times faster) than the equivalent OleDB connection.


2.OleDB drivers are not as common as ODBC drivers. This means that you’ll have less choice when choosing a database backend.


3.OleDB drivers tend to be slower and with less options (For example, on February 2020, we know no OleDriver that supports the very important "DBPROP_IMultipleResults" option that allows to track the error status of “batch” operations).


4.The future of the whole OleDB technology is unclear: it may disappear soon.


More details:


Initially, the objective of OleDB was to supersede and to replace the older ODBC technique that was invented in 1991. When Microsoft introduced OleDB, Microsoft claimed that OleDB will run 100 times faster than ODBC in many common situations. Unfortunately, OleDB was a flop: Most of the database vendors ignored it and never produced any OleDB drivers. A few years later, Microsoft introduced ODBC v3 that is providing the same benefit as OleDB (i.e. fast INSERT speed and a database connection based on a simple Connection-String). ODBC v3 was (and still is) a great success. This success can be explained by the fact that ODBC v3 is retro-compatible with the old ODBC v1: i.e. If you have an application (such as Anatella) that is using ODBC v3, it can still access ODBC v1 drivers. This means that, with ODBC v3, you can access any tables stored in any database (whatever the ODBC version: v1,v2,v3).


The final position of Microsoft with regard to OleDB is ambiguous: At one point (in 2011), Microsoft declared that OleDB is obsolete and deprecated, but a few years later, in 2018, Microsoft declared OleDB un-deprecated? Anyway, because of the lack of support from most database vendors and the unclear status about the future of the technology, OleDB is mostly a dead technology right now.

One advantage of OleDB over ODBC is that OleDB is not optimized to communicate with a database: it can connect to many data “provider” that are not necesseraly databases. In theory, this means that you could imagine to use an OleDB driver to connect to some very exotic “OleDB data source/provider” (Anatella supports such a use case by providing a large set of OleDB connection options). In practice, I never saw an OleDB drivers used reliably for something else than a database connection.