Microsoft KB Archive/147739

= How To Retrieve a QueryDef from MS Access Using DAO =

Article ID: 147739

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 Q147739



SUMMARY
In Microsoft Excel versions 97 and 7.0, you can use data access objects (DAO) in Visual Basic for Applications to retrieve the results of QueryDef from Microsoft Access. This article demonstrates how to do so.



MORE INFORMATION
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. This article uses the Northwind database included with Microsoft Office 95 Professional. If you selected the default options, the database is located in the \Msoffice\Access\Samples folder. If your Northwind database is located in a different folder, edit the provided code before running it.

In Microsoft Office 97, the default path for the Northwind database is \Program Files\Microsoft Office\Office\Samples\Northwind.mdb.

A QueryDef is created in Microsoft Access and saved with the database file. It consists of a query and may or may not include criteria.

NOTE: If you receive the "User-defined type not defined" error, activate a module sheet, click References on the Tools menu, and click Microsoft DAO 3.0 Object Library check box.

Retrieving the results of a QueryDef from Microsoft Access is a five step process as follows:
 * 1) Establish a Database object.
 * 2) Establish a QueryDef object.
 * 3) Establish a Recordset Object.
 * 4) Retrieve the Headers (if desired).
 * 5) Retrieve the data from the table.

After the data is retrieved, you should close all the objects that you opened by issuing .Close commands. Sub GetQueryDef 'This sub will get data from an Existing QueryDef in the Northwind 'database and place the data on sheet2.

Dim Db As Database Dim Qd As QueryDef Dim Rs As Recordset Dim Ws As Object Dim i As Integer 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. '     'If you're using Microsoft Office 97, the line should read: '     'Path = "C:\Program Files\Microsoft      'Office\Office\Samples\Northwind.mdb" '     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 Database and QueryDef. This QueryDef exists in the 'database. Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True, _        Exclusive:=False) Set Qd = Db.QueryDefs("Invoices")

'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

'This line simply sets the font to bold for the headers. Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold _ =True

'The next line will get the data from the recordset and copy it     'into the Worksheet (Sheet1).

Ws.Range("A2").CopyFromRecordset Rs

'This next code set will just select the data region and auto-fit '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

