Running a Microsoft Access Query

The two example procedures that follow demonstrate how to run Microsoft Access queries from within an Excel VBA procedure. The most popular types of queries that are executed in the Access user interface are Select and Parameter queries. Both example procedures use the CopyFrom-Recordset method of the Range object to place the data returned by the query into an Excel worksheet. The connection with the database is established via the ADO.

The ADOX Object Library (see Figure 15-4 at the beginning of this chapter) gives you access to the database structure, security, and procedures that are stored in the database. The top object in this library is the Catalog object that represents the entire database. This object contains such database elements as tables, fields, indexes, views, and stored procedures. Using the Create method of the Catalog object, you can create a new database as follows:

Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog cat.Create "Provider=Microsoft.Jet.0LEDB.4.0;" &_ "Data Source=C:\ExcelDump2.mdb;"

The above example illustrates how you would create a new database using the ActiveX Data Objects. Recall that earlier in this chapter you created a new database called NewDB_DA by using the Data Access Objects.

The example procedure RunAccessQuery begins by creating an object variable cat that points to the Catalog object. Next, the ActiveConnection property of the Catalog object defines the method of establishing the connection to the database:

Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "Data Source=" & dbPath

The Command object in the ADODB Object Library (see Figure 15-3 at the beginning of this chapter) specifies the command that you want to execute in order to obtain data from the data source. Our procedure attempts to access a specific query in a database.

Set cmd = cat.Views(strQryName).Command

The Views collection, which is a part of the ADOX Object Library, contains all View objects of a specific catalog. View is a filtered set of records or a virtual table created from other tables or views. After gaining access to the required query in the database, you can run the query in the following way:

Set rst = cmd.Execute

The Execute method of the Command object allows you to activate a specific query, an SQL statement, or a stored procedure. The returned set of records is then assigned to the object variable of the type Recordset using the Set keyword. After creating the set of records, these records are placed in an Excel worksheet using the CopyFromRecordset method.

Running a Select Query

Sub RunAccessQuery(strQryName As String) ' prior to running this procedure you must set up ' references to the required object libraries

Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Dim i As Integer Dim dbPath As String dbPath = "C:\Program Files\Microsoft Office\" _ & "Office\Samples\Northwind.mdb"

Set cat = New ADOX.Catalog cat.ActiveConnection = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & dbPath Set cmd = cat.Views(strQryName).Command

Set rst = cmd.Execute Sheets(2).Select

Next

With ActiveSheet

.Range("A2").CopyFromRecordset rst .Range(Cells(1, 1), _

Cells(1, rst.Fields.Count)).Font.Bold = True .Range("A1").Select End With

Selection.CurrentRegion.Columns.AutoFit rst.Close

Set cmd = Nothing

Set cat = Nothing

End Sub

To run the above sub procedure, type the following statement in the Immediate window and press Enter:

RunAccessQuery("Current Product List")

Çt Chopl 5nli

i A 1 B

1

iProductlD IPiniliictN.itue

3

3 Aniseed Symp

3

40 Eoslon Crab Meat

4

GQ Camembert Pierrot

5

13 Carnarvon Tigers

6

1 Chai

y

21 Chang

s

Chartreuse varie

9

4 Chef Anton's Cajun Seasoning

ID

43 Chocalade

li

33 Cole de Blaye

12

58 Escargots de Bourgogne

13

52 Rio Ml*

14

71 Fîsstemysost

15

33 Geitost

IG

15 GenBn Shcuju

17

5S Gnocctrl dl rrtmna Alice

18

31 Gorgonzola Telino

H 4

S Grandma's Ëoysanberry Spread ( n:\ 5hestl \shccl2/;5l-|< | | r | f |

Figure 15-11: The results of running an Access query from an Excel VBA procedure are placed in a worksheet.

Figure 15-11: The results of running an Access query from an Excel VBA procedure are placed in a worksheet.

Running a Parameter Query

You can run a Microsoft Access parameter query and place the resulting data in a Microsoft Excel spreadsheet. For example, the RunAccessParam-Query procedure runs the Employee Sales by Country parameter query in the Microsoft Access database and retrieves the records for the period beginning 7/1/96 and ending 7/30/96. The Employee Sales by Country query requires two parameters that define the beginning and ending date.

These parameters should be defined using the Parameters collection of the Command object:

cmd.Parameters("[Beginning Date]") = StartDate cmd.Parameters("[Ending Date]") = EndDate

After setting up the parameters, the query can be executed using the following statement:

Set rst = cmd.Execute

The set of records returned by this query is assigned to the object variable of type Recordset and then copied to a worksheet using the CopyFrom-Recordset method (see more information on using this method later in this chapter).

Sub RunAccessParamQuery()

' prior to running this procedure you must set up ' references to the required object libraries

Dim cat As ADOX.Catalog Dim cmd As ADODB.Command Dim rst As ADODB.Recordset Dim i As Integer Dim dbPath As String Dim StartDate As String Dim EndDate As String dbPath = "C:\Program Files\Microsoft Office\" _

& "Office\Samples\Northwind.mdb" StartDate = "7/1/96" EndDate = "7/31/96"

Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" &_ "Data Source=" & dbPath

Set cmd = cat.Procedures("Employee Sales by Country").Command cmd.Parameters("[Beginning Date]") = StartDate cmd.Parameters("[Ending Date]") = EndDate

Set rst = cmd.Execute Sheets(1).Select

Next

With ActiveSheet

.Range("A2").CopyFromRecordset rst

.Range(Cells(1, 1), Cells(1, rst.Fields.Count)).Font. _

Bold = True .Range("A1").Select End With

Selection.CurrentRegion.Columns.AutoFit rst.Close

Set cmd = Nothing

Set cat = Nothing

End Sub

0 0

Post a comment