Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

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.

Категории