Intro to ADO

Post date: Aug 13, 2015 3:56:07 PM

ADO is an abbreviation for ActiveX Data Object. ADO was created and maintained by Microsoft to provide a standard programming interface to various databases. This allows coders to learn one way to work with databases no matter which database they are working with. So it does not matter if we are working with MS Access, MS SQL Server, Oracle, IBM’s DB2, text files, CSV files or even LDAP. ADO provides a single way to program for all of them.

The most common use for ADO is to read records from a database table. While ADO can do much more than that, understanding how to use ADO to read databases provides us with the foundation to explore all other ADO uses. Using ADO to read databases involves a few simple steps:

• 1. Create a Connection Object for a database brand (such as: MS Access, MS SQL Server, DB2, Oracle, etc.)

  2. Using the Connection Object open a connection to a specific database (such as MS Access’ demo database Northwind.accdb)

  3. Create a Recordset Object that can hold retrieved database table records

  4. Create an SQL Select Statement to specify which records from which database table to get (such as all Employees from Northwind’s Employees 2003 table)

  5. Using the Connection Object, Recordset Object and SQL Select Statement, open a record set and retrieve records

  6. Put the Recordset’s records where we want them (such as an Excel table).

  7. Close the Recordset Object

  8. Close the Connection Object

While that may sound complicated, trust me, with a little practice that explanation will sound more complicated than it is. Below is code to show what those steps actually look like. In this example we are using MS Access’ demo database Northwind.accdb

Connection Strings

Connection strings are perhaps the most confusing aspect of ADO. I find it easiest to think of connection strings as our Connection Object’s “Open” function’s named parameters. In this example, the two named parameters are Provider and Data Source.

Provider tells ADO which program on our PC knows how to communicate with our database brand. In this example we are using a specific version of Microsoft’s ACE OLEDB provider. Microsoft’s ACE OLEDB provider comes with MS Windows and knows how to communicate with several Microsoft data stores including MS Access and Excel. For more on providers see: https://en.wikipedia.org/wiki/ADO.NET_data_provider.

Data Source tells ADO where our database resides. In this example the Northwind database is on my C drive’s root directory.

Unfortunately, connection string parameter names can change from database to database. Fortunately there is a website dedicated to providing connection string examples for each database brand. See: http://www.connectionstrings.com. And the good news is most people only work with one or two database brands so for them, they only need to know one or two connection strings.

Working with Recordsets

Common methods for working with Recordsets include:

Field at a time: http://msdn.microsoft.com/en-us/library/office/ff197799.aspx

GetRows http://msdn.microsoft.com/en-us/library/windows/desktop/ms677500(v=vs.85).aspx

CopyFromRecordset http://msdn.microsoft.com/en-us/library/office/aa223845(v=office.11).aspx

QueryTable: http://msdn.microsoft.com/en-us/library/office/aa174289(v=office.11).aspx