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

• 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 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.

Example

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Product Support Services (PSS) Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

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.
  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, click the Search button in Help and type:

   DAO