Creating a Query Table from Microsoft Access Data

If you want to work in Excel with data that comes from external data sources and you know that the data you'll be working with often undergoes changes, you may want to create a query table. A query table is a special table in an Excel worksheet that is connected to an external data source, such as a Microsoft Access database, SQL Server, web page, or text file. To retrieve the most up-to-date information, the user can easily refresh the query table. Microsoft Excel offers a special menu option for obtaining data from external data sources: simply choose Data | Import External Data and select New Database Query. By querying an external database, you can bring in data that fit your requirements exactly. For example, instead of bringing in all product information into your spreadsheet for a review, you may want to specify criteria that the data must meet prior to retrieval. Therefore, instead of bringing in all the products from an Access table, you may want to retrieve only products with a unit price greater than $20.

In VBA, you can use the QueryTable object to access external data. Each QueryTable object represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database. To create a query programmatically, use the Add method of the QueryTables collection object. This method requires three arguments. The example procedure at the end of this section uses the following statement to create a query table on the active sheet:

Set myQryTable = ActiveSheet.QueryTables.Add(strConn, Dest, strSQL)

strConn is a variable that provides value for the first argument—Connection. This is a required argument of the Variant data type that specifies the data source for the query table.

Dest is a variable that provides value for the second argument—Destination. This is a required argument of the Range data type that specifies to the cell where the resulting query table will be placed.

strSQL is a variable that provides value for the third argument—SQL. This is a required argument of the String data type and defines the data to be returned by the query.

When you create a query using the Add method, the query isn't run until you call the Refresh method. This method accepts one argument—BackgroundQuery. This is an optional argument of the Variant data type that allows you to determine whether to return control to the procedure when a database connection has been established and the query has been submitted (True) or to return control to the procedure after the query has been run and all the data has been retrieved into the worksheet (False).

The CreateQueryTable procedure that follows only retrieves from the Northwind database's Products table those products whose UnitPrice field is greater than 20. Notice that the control is returned to the procedure only after all the relevant records have been fetched. The RefreshStyle method determines how data is inserted into the worksheet. The following constants can be used:

■ xlOverwriteCells—Existing cells are overwritten with the incoming data.

■ xlInsertDeleteCells—Cells are inserted or deleted to accommodate the incoming data.

■ xllnsertEntireRows—Entire rows are inserted to accommodate incoming data.

Sub CreateQueryTable()

Dim myQryTable As Object Dim myDb As String Dim strConn As String Dim Dest As Range Dim strSQL As String myDb = "C:\Program Files\Microsoft Office\Office\" _

& "Samples\Northwind.mdb" strConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=" & myDb & ";" Set Dest = Worksheets(1).Range("A1") strSQL = "SELECT * FROM Products WHERE UnitPrice>20" Set myQryTable = ActiveSheet.QueryTables.Add(strConn, _

With myQryTable

.RefreshStyle = xlInsertEntireRows .Refresh False End With End Sub

l^j Chapl5.xls

HBO]

A

B

C 1

D E

F

G :

a

Pre duttlD

-V '1 I' if' .1 n ;

Sn|> plier ID ..i'.'!'] lyll OiianliryPeiUiiit

UnitPiíce

Unitsln Stock

2

4

Chef Anton's Cajun Seasoning

2

2 40 - 6 ozjars

22

53

3

5

Cbet Anton's Gumbo Mis

2

2 35 boxes

21.35

0

1

6

Grandma's Boysenberry Spread

3

2 12 - e oz jars

25

120

5

7

Unclo Bob's Organic Dried Peara

3

7 12- 1 lb fikge.

3D

15

S

a

Northwoods Cranberry Sauce

3

2 12 -12 02 jara

AO

e

7

9

in i-h <i nil h Niku

4

e 1B-5G0 gpkgs

97

29

8

IDIIkJura__

4

0 12 -200 ml jars

■31

31

9

11

Queso Cóbrales

s'

411 kfl pktl-

21

22

10

12

Queso Manchego La Pastora

S

4 10- ECO gpkgs

3a

8G

' 1

14

Tofu

Gl

7 40- 100 jpkgs.

23.25

35

' 1

17

Alice Mutton

r

5 2D - 1 kg sins

39

0

13

IB

Camarvon Tigers

H

S 1E kg pkg.

52.5

12

J

2D

Sir Rodney's Marrnslade

S 3 3D gïfi boxes

81

¡0

zJ

H J

> nfiiheetl / SheeQ /

Ml

Mi

Figure 15-17: Data from an external data source, such as a Microsoft Access database, can be analyzed in an Excel worksheet using the QueryTable object.

0 0

Post a comment