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

ID: Q147739

The information in this article applies to:

• 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

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

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

REFERENCES

For more information about Data Access Object, from the Visual Basic Editor, click the Office Assistant, type "DAO," click Search, and then click to view "Data Access Objects Overview."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q176476
   TITLE     : OFF: Office Assistant Not Answering Visual Basic Questions

   DAO

Keywords          : kbprg kbdta kbdtacode KbVBA 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999