XL: How To Retrieve the Results of a Parameter Query Using DAO

ID: Q146607

The information in this article applies to:

Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

SUMMARY

In the versions of Microsoft Excel listed at the beginning of this article, you can use data access objects (DAO) in Microsoft Visual Basic for Applications to retrieve the results of a Parameter QueryDef from Microsoft Access. This article demonstrates how to do so.

MORE INFORMATION

A Parameter QueryDef is created in Microsoft Access and saved with the database file. It consists of a query, which waits for a parameter to be passed to it, and may or may not include criteria.

Retrieving the results of a QueryDef from Microsoft Access involves the following general process:

Establish a Database object.
Establish a QueryDef object.
Establish a Recordset Object.
Pass a parameter to the QueryDef.
Retrieve the Headers (if desired).
Retrieve the data from the table.

After the data is retrieved, you should close all the objects you opened by issuing .Close commands.

Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

NOTE: This macro utilizes the Northwind.mdb sample database that is installed with Microsoft Office Professional, versions 7.0 and 97. If you accepted the default options when you installed Office 7.0, the file is located in the C:\MSOffice\Access\Samples folder. If you accepted the default options when you installed Office 97, this file is located in the C:\Program Files\Microsoft Office\Office\Samples folder. If your Northwind database is located in a different folder, you must edit the code provided before you attempt to run it.

To use DAO with a Visual Basic for Applications macro, you must reference the DAO Object library. With a module sheet active, click References on the Tools menu. If you are using Microsoft Excel 97, select the "Microsoft DAO 3.5 Object Library" check box in the Available References list and click OK. If you are using Microsoft Excel 7.0, select the "Microsoft DAO 3.0 Object Library" check box in the Available References list and click OK.

Sub GetParameterQuery()
  'This sub will pass two parameters to QueryDef in the Northwind
  'database and place the results on Sheet1. The parameters are hard
  'coded in, but you can get the parameters set in any number of ways,
  'from an InputBox to placing the parameters in specific cells on a
  'worksheet.

  Dim Db As Database
  Dim Qd As QueryDef
  Dim Rs As Recordset
  Dim Ws As Object
  Dim i As Integer
  Dim FirstDate As String
  Dim LastDate As String
  Dim Path as String

  'Set the Path to the database. This line is useful because
  'if your database is in another location, you just need to change
  'it here and the Path Variable will be used throughout the code.
  Path = "c:\msoffice\access\samples\northwind.mdb"

  'Set Ws
  Set Ws = Sheets("Sheet1")

  'This set of code will activate Sheet1 and clear any existing data.
  'After clearing the data, it will select cell A1.
  Ws.Activate
  Range("A1").Activate
  Selection.CurrentRegion.Select
  Selection.ClearContents
  Range("A1").Select

  'Set the strings that will be passed as parameters. The strings are
  'hard coded, just for the sake of simplicity. But there are lots
  'of ways to set these variables.
  'NOTE: For Excel 97, change these years to 96.
  FirstDate = "1/1/94"
  LastDate = "12/31/94"

  'Set the Database, and RecordSet. This Table exists in the database.
  Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:= True)
  Set Qd = Db.QueryDefs("Employee Sales By Country")

  Qd.parameters("Beginning Date") = FirstDate
  Qd.parameters("Ending Date") = LastDate

  'Create a new Recordset from the Query based on the stored QueryDef.
  Set Rs = Qd.OpenRecordset()

  'This loop will collect the field names and place them in the first
  'row starting at "A1."
  For i = 0 To Rs.Fields.Count - 1
     Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
  Next

  'The next three lines will get the data from the recordset and copy
  'it into the Worksheet (Sheet1).
  Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
  Ws.Range("A2").CopyFromRecordset Rs

  'This next code set selects the data region and auto-fits the columns.
  Sheets("Sheet1").Select
  Range("A1").Select
  Selection.CurrentRegion.Select
  Selection.Columns.AutoFit
  Range("A1").Select

  Qd.Close
  Rs.Close
  Db.Close

End Sub

REFERENCES

For more information about Data Access Objects, from the Visual Basic Editor, click the Office Assistant, type "DAO," click Search, and then click to view "Data Access Objects Overview."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q176476
   TITLE     : OFF: Office Assistant Not Answering Visual Basic Questions

For more information about data access objects in Excel 7.0, click the Search button in Help and type:

DAO

Additional query words: 7.00 8.00 97 XL97

Keywords          : kbprg kbualink97 kbdta kbdtacode KbVBA 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999