ADO is the most popular and well-known of Microsoft’s Data Access API’s. ADO is, like OLEDB, a COM object model for accessing, retrieving and updating data. Unlike OLE DB, however, ADO objects expose OLE Automation interfaces, allowing them to be accessed not only from C++, but also languages such as Visual Basic, and even from scripting languages like VBScript on ASP pages. ADO is also the easiest of the API’s to program against, as it provides a great deal of flexibility in how to do many operations, and the object model is relatively simple.
Behind the scenes, the ADO objects act as an OLE DB consumer. When you use ADO in your application, you are using OLE DB implicitly. ADO applications therefore must specify what OLE DB provider they wish to use. ADO uses the concept of connection strings similar to ODBC. The connection strings created through OLE DB data links can be used in ADO.
As with any OLE DB consumer, ADO can load any provider it wishes (although some providers may not support interfaces which ADO requires). This means that ADO can access data not only through data source specific providers, but also through ODBC drivers via MSDASQL. In fact, if in your ADO connection string you only specify what DSN to use, you will by default load the MSDASQL provider and connect to the data source specified in the DSN. For example, your connection string could look like:
Remember, however, that going through MSDASQL to reach a data source for which an OLE DB provider exists is not the best solution. Therefore, for instance, if you are using ADO to talk to SQL Server you should not specify a DSN in the connection string as this implies you want to load MSDASQL, when in fact you should be using the SQL Server OLE DB provider.
The following diagram shows the basic flow of an application connecting to SQL Server via ADO: