ODBC vs OLEDB
Typically, software applications are written in a specific programming language (such as Java, C#, etc.), while databases accept queries in some other database specific language (such as SQL). Therefore, when a software application needs to access data in a database, an interface that can translate languages to each other (application and database) is required. Otherwise, application programmers need to learn and incorporate database specific languages within their applications. ODBC (Open Database Connectivity) and OLEDB (Object Linking and Embedding, Database) are two interfaces that solve this specific problem. ODBC is a platform, language and operating system independent interface that can be used for this purpose. OLEDB is the successor to ODBC.
What is ODBC?
ODBC is an interface to access database management systems (DBMS). ODBC was developed by SQL Access Group in 1992 at a time there were no standard medium to communicate between a database and an application. It does not depend on a specific programming language or a database system or an operating system. Programmers can use ODBC interface to write applications that can query data from any database, regardless of the environment it is running on or the type of DBMS it uses.
Because ODBC driver acts as a translator between the application and the database, ODBC is able to achieve the language and platform independence. This means that the application is relieved of the burden of knowing the database specific language. Instead it will only know and use the ODBS syntax and the driver will translate the query to the database in a language it can understand. Then, the results are returned in a format that can be understood by the application. ODBC software API can be used with both relational and non relational database systems. Another major advantage of having ODBC as a universal middleware between an application and a database is that every time the database specification changes, the software does not need to be updated. Only an update to the ODBC driver would be sufficient.
What is OLEDB?
OLEDB is a data API developed by Microsoft. It allows to access data from a large range of data sources. It is implemented using Microsoft’s COM (Component Object Mode). OLEDB is considered to be a successor to ODBC, and it can handle data sources in a much higher level compared to ODBC. In essence, OLEDB extends the ODBC features to non-relational databases (e.g. object databases and spreadsheets). That means, OLEDB can be used with databases that does not use SQL. OLEDB was developed as a part of the Microsoft Data Access Components (MDAC).
What is the difference between ODBC and OLEDB?
If the programmer is unfamiliar with COM, then ODBC is the better option. But, ODBC is only good for relational databases, while OLEDB is suitable for both relational and non-relational databases. If the database does not support OLE (non-OLE environments) then ODBC is the best choice. If the environment is non-SQL, then you have to use OLEDB (because ODBC works only with SQL). Similarly, if interoperable database components are required, then OLEDB needs to be used instead of ODBC. However, for 16-bit data accessing ODBC is the only option (OLEDB does not support 16-bit). Finally, OLEDB is the best choice for connecting to multiple databases at once (ODBC can connect to only one database at a time).