One of the most fundamental programming tasks is writing code that interacts with a database. This week I’ll demonstrate a basic method of retrieving data from an Access database and displaying it on an Excel worksheet. Being a programming column, of course, I’ll show how to do this programmatically. That said, if you haven’t already done so, be sure to look into Microsoft Query for bringing data into Microsoft Excel using the user interface.
Any time you interact with a database, you’ll need to use a special application programming interface (API) that allows you to communicate with the database. For most purposes, you can use one of the versions of ActiveX Data Objects, commonly referred to as ADO.
Prior to using ADO in your project however, you need to add a reference to it in the VBE. To do this, select Tools/References from the Visual Basic Editor menu and check the checkbox next to the latest version of ADO on your computer as illustrated in Figure 1.
Figure 1 - Adding a reference to Microsoft ActiveX Data Objects
Click to enlarge
Listing 1 shows how to connect to the Northwind sample database, execute a query, and copy the results on to a worksheet. Enter the following listing in the code module associated with Sheet1 (you could also insert a new module and enter it there).
Listing 1: This listing demonstrates basic data retrieval from an Access database.
'NOTE: Set a reference in the VBE to the latest version of
' the Microsoft ActiveX Data Objects Library on your computer.
Sub DatabaseExample()
Dim rst As ADODB.Recordset
Dim sConnection As String
Dim sSQL As String
Dim rg As Range
On Error GoTo ErrHandler
' This is the range that will receive the data.
Set rg = ThisWorkbook.Worksheets(1).Range("a1")
' The database connection string. Double-check the path
' to the Northwind database on your computer.
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\" & _
"OFFICE11\SAMPLES\northwind.mdb"
' The query to execute
sSQL = "SELECT LastName, FirstName, Title FROM employees"
' Create & Open the recordset
Set rst = New ADODB.Recordset
rst.Open sSQL, sConnection
' Copy to the range
rg.CopyFromRecordset rst
rg.CurrentRegion.Columns.AutoFit
' Close the recordset when you're done with it.
rst.Close
ExitPoint:
Set rst = Nothing
Set rg = Nothing
Exit Sub
ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
' resume at the ExitPoint label to clean up object variables
Resume ExitPoint
End Sub
Running listing 1 produces the results shown in Figure 2.
Figure 2: Success! - Click to enlarge
Querying a database programmatically using ADO generally involves four tasks. First, you need to define a connection string. A connection string is simply the details required by ADO to locate and connect to the desired database. While I’ve just defined a connection string in this example, it is also common to use the ADO Connection object to handle the details associated with connecting to a database.
Second, you need to build the SQL statement that will return the data that you’re interested in. ADO has a Command object that you can use to do this — or you could just define a SQL statement and store it in a string as I have in the example.
Third, you need to use the ADO Recordset object to execute the query (or command) against the connection and store the results.
Finally, once you have the results in a Recordset, you need to display them somehow. Using the CopyFromRecordset method associated with a Range object is the easiest way to go about this. In a future column I’ll demonstrate a technique that is a little more flexible.
