XL: How To Retrieve a QueryDef from MS Access Using DAO

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

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

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.

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

   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

REFERENCES

For more information about data access objects, click the Index tab in Microsoft Excel Help and type:

   DAO