How To Retrieve the Results of a Parameter Query Using DAO (146607)

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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