ODBC vs ADO
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 OLE DB (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. OLE DB is a successor to ODBC. ADO is a wrapper for OLE DB.
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, when 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 ADO?
ADO is a collection of COM (Component Object Mode) objects that act as an interface for accessing data in data sources. ADO was developed in 1996 by Microsoft as a part of the Microsoft Data Access Components (MDAC). ADO forms a middleware layer between applications written in some programming language and OLE DB (a data API developed by Microsoft and the successor to ODBC). Programmers can use ADO to access data without knowing the underlying implementation details of the database. Although you are not required to know any SQL to use ADO, you can certainly execute SQL statements using it.
What is the difference between ODBC and ADO?
ODBC is an open interface, which can be used by any application to communicate with any database system, while ADO is a wrapper around OLE DB (which is the successor to ODBC). 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 ADO (because ODBC works only with SQL). If interoperable database components are required, then ADO needs to be used instead of ODBC. However, for 16-bit data accessing ODBC is the only option (ADO does not support 16-bit). Finally, ADO is the best choice for connecting to multiple databases at once (ODBC can connect to only one database at a time).