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.

ADO programming is a very complex topic. If you have a 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 example in Listing 20-6 retrieves data from an Access database named budget.mdb. This database contains one table (named Budget), and the table 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 20-11).

Ejsimple ado example.x

s

I0E

A

B

c

D

E

F

G

1

DIVISION

DEPARTMENT

CATEGORY

ITEM

VI0 NTH

BUDGET

ACTUAL

I

2

N. America

Data Processing

Facility

Lease

Jan

3450

2631

3

N. America

Human Resources

Facility

Lease

Jan

4353

3875

_

4

N. America

Accounting

Facility

Lease

Jan

3898

2979

5

N. America

Training

Facility

Lease

Jan

3185

3545

6

N. America

Security

Facility

Lease

Jan

3368

4120

7

N. America

R&D

Facility

Lease

Jan

3926

3432

8

N. America

Operations

Facility

Lease

Jan

3329

3715

9

N. America

Shipping

Facility

Lease

Jan

4095

2892

10

N. America

Sales

Facility

Lease

Jan

3242

2687

11

N. America

Advertising

Facility

Lease

Jan

3933

3580

12

N. America

Public Relations

Facility

Lease

Jan

4316

4328

13

N. America

Data Processing

Facility

Lease

~eb

4440

4357

14

N. America

Human Resources

Facility

Lease

=eb

4210

3196

15

N. America

Accounting

Facility

Lease

"eb

2860

3658

16

N. America

Training

Facility

Lease

-eb

4468

3759

11

N. America

Security

Facility

Lease

"eb

3499

3568

16

N. America

R&D

Facility

Lease

-eb

3394

4196

19

N. America

Operations

Facility

Lease

=eb

4187

3074

>~tT|\~5heetI /~Descrip

S 7~

¡■UH_

_i zi:

±Jil

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

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

Listing 20-6: Using ADO to Retrieve Data from an Access File

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.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, 0).CopyFromRecordset Recordset End With

Set Recordset = Nothing Connection.Close Set Connection = Nothing End Sub

This example,along with the Access database,is available on the companion CD-ROM.

0 0

Responses

  • venera
    What is facility of ado in vba?
    2 years ago

Post a comment