Microsoft KB Archive/146607

= How To Retrieve the Results of a Parameter Query Using DAO =

Article ID: 146607

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition

-



This article was previously published under Q146607



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

