Working with ADO

ActiveX Data Objects (ADO) is an object model that enables you to access data stored in a variety of database formats. This allows you to use a single object model for all your databases. In this section I present a simple example that uses ADO to retrieve data from an Access database.

ADO programming is a very complex topic. If you need to access external data in your Excel application, invest in one or more books that cover this topic in detail. This example is here so you can get a feel for how it works.

The following example retrieves data from an Access database named budget.mdb. This database contains one table named Budget, which has seven fields. This example retrieves the data in which the Item field contains the text "Lease" and the Division field contains the text "N. America." The qualifying data is stored in a Recordset object, and the data is then transferred to a worksheet (see Figure 23-8).

Figure 23-8:

Retrieve data from an Access database.

w

simple ado example.xls

UtM

A

B

c

D

E

F

&

1

DIVISION

DEPARTMENT

CATEGORY

ITEM

MUN I H

BUDGET

ACTUAL

2

N. America

Data Processing

Faci

ty

Lease

Jan

34EO

¿B®

--

3

N. America

Human Resources

Faci

ty

Lease

Jan

IBSS

38®

4

N. America

Accounting

Faci

ty

Lease

Jan

3898

29X3

5

N. America

Training

Faci

ty

Lease

Jarf

3185

3545

6

N. America

Security

Faci

ty

Lease

3368

4120

7

N. America

R&D

Faci

ty

Lease

Jan

392È.

3432

8

N. America

Operations

Faci

ty

Lease

Jan

3329

3715

9

N. America

Shipping

Faci

ty

Lease

Jan

MR

28S2

10

N. America

Sales

Faci

ty

Lease

Jan

S2Ï2

2087

11

N. America

Advertising

Faci

ty

Lease

Jan

■a®

3580

12

N. America

Public Relations

Faci

ty

Lease

Jan

4gl e

43M

13

N. America

Data Processing

Faci

ty

Lease

Feb

4440

4357

14

N. America

Human Resources

Faci

ty

Lease

Fob

4210

3190

15

N. America

Accounting

Faci

ty

Lease

Feb

3800

3058

16

N. America

Training

Faci

ty

Lease

Feb

4468

3759

17

N. America

Security

Faci

ty

Lease

Feb

3499

3568

18

N. America

R&D

Faci

ty

Lease

Feb

3394

4190

19

N. America

Operations

Faci

ty

Lease

Feb

4187

3074

Cihinnirin

FaHIHw

I ÜO--11

Füh

TIR7I1

V

H

► n \ Sheet Description /

J<

III!

>

' 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.mdb"

' Open the connection

Set Connection = New ADODB.Connection Cnct = "Provider=Microsoft.Jet.OLEDB.4.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'" .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, End With

Set Recordset = Nothing

Connection.Close

Set Connection = Nothing

0).CopyFromRecordset Recordset

Unlike the other examples in this chapter, this procedure uses early binding. Therefore, it requires a reference to the Microsoft ActiveX Data Objects 2.0 Library. In the VBE, use ToolsOReferences to create this reference.

This example, along with the Access database, is available from this book's Web site.

Part VII

The Part of Tens

0 0

Post a comment