Working with ADO

ADO (ActiveX Data Objects) is an object model that enables you to access data stored in a variety of database formats. Importantly, this methodology allows you to use a single object model for all your databases. This is currently the preferred data access methodology and should not be confused with DAO (Data Access Objects).

This section presents a simple example that uses ADO to retrieve data from an Access database.

Note  

ADO programming is a very complex topic. If you need to access external data in your Excel application, you'll probably want to invest in one or more books that cover this topic in detail.

The ADO_Demo example retrieves data from an Access 2007 database named  budget data.accdb . This database contains one table (named Budget). This example retrieves the data in which the Item field contains the text Lease , the Division field contains the text N. America, and the Year field contains 2006. The qualifying data is stored in a Recordset object, and the data is then transferred to a worksheet (see Figure 20-15).

Figure 20-15: This data was retrieved from an Access database.

 Sub ADO_Demo() '   This demo requires a reference to '   the Microsoft ActiveX Data Objects 2.x Library     Dim DBFullName As String     Dim Cnct As String, Src As String     Dim Connection As ADODB.Connection     Dim Recordset As ADODB.Recordset     Dim Col As Integer     Cells.Clear '   Database information     DBFullName = ThisWorkbook.Path & "\budget data.accdb" '   Open the connection     Set Connection = New ADODB.Connection     Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"     Cnct = Cnct & "Data Source=" & DBFullName & ";"     Connection.Open ConnectionString:=Cnct '   Create RecordSet     Set Recordset = New ADODB.Recordset     With Recordset '       Filter         Src = "SELECT * FROM Budget WHERE Item = 'Lease' "         Src = Src & "and Division = 'N. America' "         Src = Src & "and Year = '2006'"         .Open Source:=Src, ActiveConnection:=Connection '       Write the field names         For Col = 0 To Recordset.Fields.Count - 1            Range("A1").Offset(0, Col).Value = _              Recordset.Fields(Col).Name         Next '       Write the recordset         Range("A1").Offset(1, 0).CopyFromRecordset Recordset     End With     Set Recordset = Nothing     Connection.Close     Set Connection = Nothing End Sub 

CD-ROM  

This example (named  simple ado example.xlsm ), along with the Access database file (named  budget data.accdb ), is available on the companion CD-ROM.