ADO

Intro to ADO

There are many ways to get data into Excel. Power Query is one way and is relatively new. For some tasks, Power Query is the best way. But for security and speed, the old way: VBA, SQL, and ADO can't be beat.


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 we work with or which platform we develop on. 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


Declare Variables

We need two objects: a Connection Object and a Recordset Object. Here we declare their variables as generic objects.

Dim oCN As Object 'Connection

Dim oRS As Object 'Recordset


Create Objects

Here we ask Windows to put two specific objects into our generic objects: an ADO Connection Object and an ADO Recordset Object

Set oCN = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


Open Connection

Here we tell ADO exactly which database brand we need to connect to and where the specific database we want to work with resides using a Connection String

oCN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Northwind 2003.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.


Open Recordset

With our connection object (oCN) open to our MS Access Northwind database, we can open our recordset object (oRS).

The SQL Select Statement instructs our database to send us all records and all fields from its Employees 2003 table.

For more on SQL see:

http://www.w3schools.com/sql/

oRS.Open "Select * From [Employees 2003]", oCN


Read Records

There are many ways to put our Recordset records into Excel ranges, tables, or VBA variables. In this example we are placing the recordset (oRS) into an Excel table (known to VBA as a ListObject).

Selection.Parent.ListObjects.Add( _

xlSrcQuery, oRS, Destination:=Selection).QueryTable.Refresh

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

Housekeeping

When we finish using our objects we should close them to free up memory and resources.

Once closed it is a good practice to release all memory from the variables by setting them to Nothing.

oRS.Close

oCN.Close


Set oRS = Nothing

Set oCN = Nothing