ACC95: Incorrect GetAccessReportList in "Building Applications"

Last reviewed: August 28, 1997
Article ID: Q150058
The information in this article applies to:
  • Microsoft Access version 7.0
  • Microsoft Excel versions 5.0, 7.0

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

The GetAccessReportList procedure on page 288 of the "Building Applications with Microsoft Access for Windows 95" manual causes the error "Object doesn't support this property or method." (run-time error 438)

CAUSE

This procedure refers to a variable called "dbs" but fails to declare and set this variable. Also, the "and" condition on the first If statement refers to "mdb" but should refer to "mda." Finally, the text argument of the AddItem method should be specified as a named argument.

RESOLUTION

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access for Windows 95" manual.

The following steps show how to use the GetAccessReportList procedure in Microsoft Excel. The corrected GetAccessReportList procedure is included in these steps:

  1. Start Microsoft Excel (version 5.0 or later).

  2. On the Insert menu, select Macro, and then click Module to create a new module within the workbook.

  3. Type the following code in the module, which includes the corrected GetAccessReportList procedure:

    Option Explicit

    Dim objAccess As Object

           Sub GetAccessReportList()
             Dim dbs As Object
             Dim strDBName As String
             Dim intReport As Integer
    
             Set objAccess = CreateObject("Access.Application.7")
    
             strDBName = DialogSheets(1).EditBoxes(1).Text
             If Right$(strDBName, 4) <> ".mdb" And _
               Right$(strDBName, 4) <> ".mda" Then
               strDBName = strDBName & ".mdb"
             End If
    
             With objAccess
               .OpenCurrentDatabase (strDBName)
               Set dbs = .DBengine(0)(0)
               With dbs.Containers("Reports")
                 For intReport = 0 To .Documents.Count - 1
                   If Left$(.Documents(intReport).Name, 4) <> "~TMP" Then
                     DialogSheets(1).ListBoxes(1).AddItem Text:= _
                       .Documents(intReport).Name
                   End If
                 Next intReport
               End With
             End With
           End Sub
    
           Sub Main()
             DialogSheets(1).ListBoxes(1).RemoveAllItems
             DialogSheets(1).Show
           End Sub
    
    

  4. On the Insert menu, select Macro, and then click Dialog to create a new dialog within the workbook.

  5. Use the Edit Box button from the Forms toolbox to draw an edit box inside the Dialog frame. (After clicking the Edit Box button, you must click and drag inside the Dialog frame to draw the edit box.)

  6. Use the List Box button to draw a list box inside the Dialog frame.

  7. Using the right mouse button, click OK within the Dialog frame, and then click Format Object on the menu that appears.

  8. Click to clear the Dismiss check box, and then click OK.

  9. Using the right mouse button, click OK within the Dialog frame, and then click Assign Macro on the menu that appears.

  10. Select GetAccessReportList and click OK.

  11. Click the Sheet1 tab to activate a worksheet within the workbook.

  12. On the Tools menu, click Macro.

  13. Select Main and click Run. Your custom dialog box should appear.

  14. Type a valid path and database name in the edit box and click OK, for example:

             C:\Msoffice\Access\Samples\Northwind.mdb
    
    
A list of reports should appear in the list box. Click Cancel to close.

Alternative GetAccessReportList Procedure

Below is a slightly faster version of the GetAccessReportList procedure:

   Sub GetAccessReportList()
     Dim lstReports As list box
     Dim strDBName As String
     Dim intReport As Integer
     Dim docReports As Object
     Dim intCount As Integer
     Dim strDoc As String

     Set lstReports = DialogSheets(1).ListBoxes(1)
     Set objAccess = CreateObject("Access.Application.7")

     strDBName = DialogSheets(1).EditBoxes(1).Text
     If Right$(strDBName, 4) <> ".mdb" And _
       Right$(strDBName, 4) <> ".mda" Then
       strDBName = strDBName & ".mdb"
     End If

     objAccess.OpenCurrentDatabase strDBName
     With objAccess.DBengine(0)(0)
       Set docReports = .Containers("Reports").Documents
       intCount = docReports.Count - 1
       For intReport = 0 To intCount
         strDoc = docReports.Item(intReport).Name
         If Left$(strDoc, 4) <> "~TMP" Then
           lstReports.AddItem Text:=strDoc
         End If
       Next
     End With
   End Sub

REFERENCES

For more information about using Microsoft Access as an OLE automation server, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q147816
   TITLE     : ACC: Using Microsoft Access as an OLE Automation
               Server
Keywords          : AutoGnrl kberrmsg kbole IntpOleA
Technology        : kbole
Version           : 7.0
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbdocerr
Solution Type     : kbcode


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.