Work Magic with ADO

I feel it is important to learn how to use MS Query. I've seen numerous VBA applications that were created to retrieve data from a database into Excel that could have been done using MS Query alone. There's no sense in writing custom code to work with a database if you can achieve the same results with MS Query.

For those times when MS Query won't cut it, you can turn to ADO. ADO is a set of objects that you can use to interact programmatically with a database. The ADO object model is shown in Figure 16.16.

Figure 16.16

The ADO Object Model

ADO Object Model - Microsoft internet Explorer

File Edfc View Favorites Tools Help Lhf;s M //

[S ADO 2.8 API Reference F="l A

ADO Object Model

The following figures show the ADO objects and their collections. Click an object or collection for more information.

[connection|

U Errors |_jj

Error |

LJ Properties |—1~

Property |

| Command |

LJ Parameters |—[

Parameter |

L| Properties |_

Property |

[ Recordset |

|-| Fields |—f

Field

Lj Properties |—

Property j

j Record |

Lj Fields |—f

Fiefd |

| Stream |

v

li

J My Computer

Now, I must tell you that ADO is a much bigger topic than can possibly be covered in one chapter. In fact, entire books have been written on the use of ADO. The content presented here should be enough to get you off to a good start. In particular, you'll find examples that show you how to open a connection to a data source, retrieve data from a data source, and copy it to the worksheet, as well as run action queries against the data source that insert new records or modify existing records.

The first thing you need to do to use ADO is set a reference to the ADO object library. Select Tools ^ References from within the VBE. As you can see in Figure 16.17, there will likely be a few versions of ADO present on you computer. You should choose a version that you know will be installed on your user's computer. If you have a compelling reason to use a newer version of ADO that isn't installed on your users' computers, your users will need to install the required version of ADO before using your application. ADO version 2.1 was distributed with Office 2000 whereas version 2.5 was distributed with Office XP and Office 2003.

Figure 16.17

You will probably have many versions of ADO available on your PC.

References - VBAPro ject

Avaiabte References:

! ! Microsoft ActiveMovie Control

□ Microsoft ActiveX Data Oblects (Multi-dimeiisiorwi) 2. y Microsoft ActiveX Data Objects 2.0 Library

L I Microsoft ActiveX Data Objects 2. i Library

□ Microsoft ActiveX Data Cblects 2,5 Library

□ Microsoft ActiveX Data Objects 2.6 Library

□ Microsoft ActiveX Data Objects 2,7 Library

□ Microsoft ActiveX Data Oblects Recordset 2,7 Library D Microsoft ActiveX Plugin

□ Microsoft Add-]n Designer

IJ Microsoft ADO Ext, 2.7 for DDL and Security

□ Microsoft Agent Control 2.0 Li Microsoft Ageni Server 2.0

FI Microsoft Aoent Server Extensions 2.0

Priority

Microsoft Office 11,0 Object Library-

Location: C:\Program Fies\,Common Ftes\Mcrosoft 5hared\OFFJCEl l\M Language: Standard

ADO has a very flexible object model. For many tasks, you could go about completing them multiple ways. For this reason, it can be difficult to understand how to use ADO at first. I guarantee that you'll see various ways for completing tasks with ADO if you look at online help, other developer books, and so on. My advice to you is to keep in mind that ADO is meant to be flexible—don't get too hung up or surprised when you see people use different methods for performing various actions with ADO.

In general, you need to know three main objects to begin using ADO: Command, Connection, and Recordset. Many times the Connection object is the first object you need to worry about. A Connection object contains properties and methods that allow you to manage the details associated with a unique connection to a data source. I say data source rather than database because ADO allows you to connect to other stores of data besides databases.

The Command object contains properties and methods that allow you to create or modify a statement that will be sent to the data source defined by the Command's associated Connection object. Command objects are useful for executing update and insert SQL statements and stored procedures, and for setting parameters for a parameter query.

A Recordset has the properties and methods you need to work with the result set retrieved from the data source. Recordset objects consist of records (rows) and fields (columns).

Make the Connection

As the top-level object of the ADO object library, you always need a Connection on which to operate in order to do anything else. You can obtain a Connection in two ways: you can explicitly create a Connection object, or you can implicitly create one when you're using one of the lower-level objects such as a Recordset by providing the object with the detail necessary to create a Connection. Table 16.2 provides a list of some key properties and methods of the Connection object.

Easily the most important part of creating a Connection object is specifying the ConnectionString, a property that contains essential details that let ADO know where the data source is located.

Table 16.2: Key Connection Object Properties and Methods

Property/Method

ConnectionString Property Provider Property

State Property

Close Method Execute Method Open Method

Description

Provides the details used to establish a connection to the data source.

Sets or returns the name of the provider used for the connection. A provider is a software component that operates between ADO and the data source.

Indicates whether the connection is open (adStateOpen) or closed (adStateClosed).

Closes the connection and any dependent objects.

Executes a SQL statement or stored procedure on the connection.

Opens a connection to the data source.

Listing 16.1 presents a simple example that creates a Connection to the Northwind database. This listing just creates a new Connection, and opens and then closes the connection.

Listing 16.1: A Simple Connection Example

Sub MakeConnectionExamp1e()

Dim conn As ADODB.Connection

On Error GoTo ErrHandler

Set conn = New ADODB.Connection conn.Provider = "Microsoft.Jet.0LEDB.4.0;" conn.ConnectionString = "Data Source=C:\Program Fi1es\" & _

"Microsoft 0ffice\0FFICE11\SAMPLES\northwind.mdb" conn.Open

If conn.State = adStateOpen Then MsgBox "Connected!", vbOKOnly conn.Close

Else

MsgBox "Not connected!", vbOKCancel End If

Set conn = Nothing Exit Sub

ErrHandler:

MsgBox "Could not connect to database. " & Err.Description, vbOKOnly

End Sub

I could have made this even easier, but I wanted to demonstrate two things that will be useful later on. First, I used the Provider property of the Connection object to specify the provider to use. A provider is generally a product-specific driver used by ADO to communicate with the database specified by the data source property embedded in the connection string. It's possible to embed the provider in the connection string. For example, I could have eliminated the statement that set the Provider property and assign the following value to the ConnectionString property.

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Fi1es\" & _ "Microsoft Office\OFFICE11\SAMPLES\northwind.mdb"

The second thing that I'd like to point out is the If.. .Then statement that compares the Connection State to the defined constant adStateOpen. For the purposes of the example, I could have simply displayed the "Connected!" message at this point. After the conn.Open statement, there are only two possible outcomes: either the connection is open or an error occurs. Because I've enabled error handling, if the line following the Open statement is executed, it's safe to assume that the connection is open. Nonetheless, I used the State property of the Connection object as a way to demonstrate that it exists and how you use it.

The State property is important because it's possible for a Connection to close without your knowledge. For example, perhaps a connection to a data source automatically closes or times out after a specified period of inactivity. Consequently, it's important that you check the State of a Connection before you use it and refresh (or reopen) the connection as needed.

Ready, Set, Query!

When retrieving data using ADO, the majority of your code will be operating on a Recordset object. Using the Recordset object, you'll specify the SQL statement you want to send to the data source, execute the query using the Open method, and then examine the results using various navigational properties and methods. The Recordset object is probably the largest of the ADO objects in terms of number of properties and methods. Table 16.3 lists some of the most commonly used properties and methods of the Recordset object.

Table 16.3: Key Recordset Object Properties and Methods property/method

ActiveConnection Property BOF/EOF Properties

CursorType Property

Fields Property

RecordCount Property Source Property

State Property

AddNew Method Close Method Delete Method

Description

Returns/sets the Connection with which the object is associated.

Positional indicators. BOF indicates that the current record position is before the first record. EOF indicates that the current record position is after the last record.

Indicates the type of cursor used. A cursor is a database object used to aid in record navigation and update records in a recordset. Can be one of the following: adOpenForwardOnly, adOpenKeyset, adOpenDynamic, or adOpenStatic. Not all providers support all types of cursors.

Returns a Fields object that is a collection of Field objects associated with a Recordset object.

Returns the number of records in the recordset.

Sets/returns the data source (a Command object, SQL statement, stored procedure, or table name) for the recordset.

Indicates whether the connection is open (adStateOpen) or closed (adStateClosed).

Creates a new record in an updateable recordset. Closes the recordset.

Deletes the current record or a group of records.

Table 16.3: Key Recordset Object Properties and Methods (continued)

Property/Method description

Moves the position of the current record.

Move Method

MoveFirst, MoveLast, MoveNext, or MovePrevious

Moves the current record to the first, last, next, or previous record.

Open Method

Opens a cursor on the recordset.

Requery Method Update Method

Updates the data by reexecuting the query on which the object is based. Saves any changes you make to the current record.

Listing 16.2 demonstrates the Recordset object by retrieving a list of employees from the North-wind database and copying the data onto a worksheet.

Listing 16.2: Using a Recordset to Execute and Display a Query

Sub RecordsetExamp1e()

Dim rst As ADODB.Recordset Dim sConn As String Dim sSQL As String Dim rg As Range

On Error GoTo ErrHandler

Set rg = ThisWorkbook.Worksheets(1).Range("a1")

' Create a new recordset object Set rst = New ADODB.Recordset

' Connection details - this is the kind of thing ' that you can use the Settings class for sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Fi1es\" & _ "Microsoft 0ffice\0FFICE11\SAMPLES\northwind.mdb"

' SQL statement to retrieve list of employees sSQL = "SELECT LastName, FirstName, Title FROM employees"

' Open the recordset rst.Open sSQL, sConn

' Copy recordset to the range rg.CopyFromRecordset rst

' Adjust column sizes rg.CurrentRegion.Columns.AutoFit

' Close the recordset rst.Close

' Clean up. Set rst = Nothing Set rg = Nothing Exit Sub

ErrHandler:

MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly End Sub

This procedure is a good example of the flexibility of the ADO object model. Rather than explicitly creating a Connection object, it's implicitly created when the recordset is opened. The Open method of the Recordset object allows you to pass a connection string as the second parameter. Using the connection string, ADO creates a Connection object behind the scenes and then opens the recordset against it.

Once the recordset is opened, you can use the CopyFromRecordset method of the Range object to automatically copy the data to the range. The output of the RecordsetExample procedure is shown in Figure 16.18.

The CopyFromRecordset method is handy when you want to copy data to a worksheet exactly as it appears in the recordset. Many times, however, you'll need to do things like rearrange the order of the fields or examine individual records before copying them to a worksheet. Listing 16.3 demonstrates how you can loop through a recordset and manually transfer data to a worksheet.

Figure 16.18

Output of the

RecordsetExample procedure

□ Microsoft EHC.el-Cltdilt.er te EH3I I|(I">K ^

- g g

Fils Edt

View Insert Format Tools Data Window

Help

. S x|

i-i

-_J [

Ü gjj (¡M I ^ ■'■,-th

^ÛTgÇS^

- ä^d

A1

-

fx Davolio

—^

, A I

B

r c I

UJ

1

iDavolio I

[Nancy_

Sales Representative

2

Fuller

Andrew

Vice President, Sales

3

Leverling

Janet

Sales Representative

A

Peacock

Margaret

Sales Representative

5

Buchanan

Si even

Sales Manager

a

Suyama

Michael

Sales Representative

?

King

Robert

Sales Representative

3

Catlahan

Laura

Inside Sales Coordinator

3

Dodsworth

Anne

Sales Representative

10

11

12

13

. U

V

H 1

► H \Sheetl /sheets / Sheet3 / 5heet4 / J<

>1

I Ready

_

Listing 16.3: Looping Through a Recordset

Sub LoopThroughRecordset(rst As ADODB.Recordset, rg As Range) Dim nColumnOffset As Integer Dim fld As ADODB.Field

' Use With...End With on rst to ' save typing & increase performance ' Downside - harder to read. With rst

' Loop until we hit the end of the ' recordset Do Until .EOF

' Loop through each field and retrieve it's value nColumnOffset = 0

For Each fld In .Fields rg.Offset(0, nColumnOffset).Value = fld.Value nColumnOffset = nColumnOffset + 1

Next

' Move down one row on the worksheet Set rg = rg.Offset(1, 0)

' Move to the next record .MoveNext

Loop

End With

End Sub

You can test the LoopThroughRecordset procedure by replacing the statement

' Copy recordset to the range rg.CopyFromRecordset rst in the RecordsetExample procedure shown in Listing 16.2 with the following statement:

' Copy recordset to the range LoopThroughRecordset rst, rg

After you do this, if you run the RecordsetExample procedure, it produces exactly the same output as it did using the CopyFromRecordset method (Figure 16.18). Of course, you now have unlimited control over the exact output because you can examine the records row by row, field by field.

As Listing 16.3 demonstrates, there are two key parts to setting up a loop to walk through the data in a recordset. The first part is setting up the Do.. .Loop statement to loop until the EOF property of the Recordset object is true. If you run a query that doesn't return any records, EOF will be true right from the get go, otherwise the recordset will be returned and the first record in the result set will be the current record. The second part is to make sure you advance the current record with each pass through the Do.. .Loop. You advance the current record by using the MoveNext method of the Recordset object. Without this statement the loop will repeat endlessly, so I like to add this statement immediately after creating the Do.. .Loop.

As an alternative to looping through the fields, you can also access individual fields. The Fields object is a collection of Field objects. As you have seen throughout the book, you can access a particular object in a collection using either its name or index. Consequently, if you were just interested in the LastName field, you could retrieve its value using one of the following statements.

' Retrieve last name by name sLastName = rst.Fie1ds("LastName").Va1ue

' Retrieve last name by field index sLastName = rst.Fie1ds(0).Va1ue

You can determine the index of a particular field by looking at the SQL statement used to retrieve the data. The first field mentioned after the SELECT statement will have an index of 0, the second field will have an index of 1, and so on.

It's Not Just about Retrieving

Reading data from a database is just half the story. You may also need to modify existing data or add new data to a database. Many times I see beginners open a table using the Recordset object, locate the record they want to change, and then make the desired change. Alternately, they will open up a table using the Recordset object and then add a new record on to the end of it. Although these methods work, they are extemely inefficient. It is much better to construct a SQL statement to do the job and then send the SQL statement to the data source and let the data source worry about making the changes. After all, managing data is what the data source is designed to do.

Queries that involve updating, inserting, and deleting records are commonly referred to as action queries. In order to execute action queries with ADO, you use the Command object. The Command object has a CommandText property. To execute a Command object you set the CommandText property equal to the SQL statement you wish to execute and then provide either a connection string or a Connection object to the Command's ActiveConnection property. Once you have set the Com-mandText and ActiveConnection properties you're ready to call Command's Excecute method. Table 16.4 lists the most commonly used Command properties and methods.

Listing 16.4 provides an example of using the Command object. The ActionQuery function executes the supplied action query (a SQL statement) against the supplied Connection object and returns the number of records that were affected by the query. TestActionQuery demonstrates how to use ActionQuery by adding a new record to the Catagories table in the Northwind database and then editing an existing record.

Table 16.4: Key Command Object Properties and Methods

Property/Method

ActiveConnection property CommandText property CommandType property

Description

Returns/sets the connection with which the object is associated. Returns/sets the text of the command to be issued to the data source.

Indicates the type of command. Generally the command type will be either adCmdText for SQL statements or adCmdStoredProc for stored procedures.

Execute method

Parameters property

CreateParameter method

Returns a collection of Parameter objects associated with the Command object.

Creates a new Parameter object with the specified properties. Executes the command against the data source.

Listing 16.4: Executing Action Queries

Sub TestActionQuery()

Dim conn As ADODB.Connection Dim IRecordsAffected As Long Dim sSQL As String

On Error GoTo ErrHandler

Set conn = New ADODB.Connection conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Program Fi1es\" & _ "Microsoft 0ffice\0FFICE11\SAMPLES\northwind.mdb" conn.Open

If conn.State = adStateOpen Then ' Add a new category sSQL = "INSERT INTO Categories" & _

"([CategoryName], [Description]) " & _ "VALUES ('Jerky', 'Beef jerky, turkey jerky, " & _ "and other tasty jerkies');" IRecordsAffected = ActionQuery(conn, sSQL) MsgBox "Added " & IRecordsAffected & " record(s).", vbOKOnly

' Edit an existing category sSQL = "UPDATE Categories SET [Description] = " & _

"'Prepared meats except for jerky' " & _ "WHERE [CategoryName]='Meat/Pou1try';" IRecordsAffected = ActionQuery(conn, sSQL)

MsgBox "Updated " & IRecordsAffected & " record(s).", vbOKOnly conn.Close End If

Set conn = Nothing Exit Sub

ErrHandler:

MsgBox "Could not connect to database. " & Err.Description, _ vbOKOnly

End Sub

'/ returns number of records affected Public Function ActionQuery(conn As ADODB.Connection, _ sSQL As String) As Long

Dim IRecordsAffected As Long Dim cmd As ADODB.Command

On Error GoTo ErrHandler

IRecordsAffected = 0

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = conn .CommandText = sSQL .CommandType = adCmdText .Execute IRecordsAffected End With

ExitPoint:

ActionQuery = IRecordsAffected Exit Function ErrHandler:

Debug.Print "ActionQuery error: " & Err.Description Resume ExitPoint End Function

0 0

Post a comment