ACC95: Incorrect GetAccessReportList in "Building Applications"
ID: Q150058
|
The information in this article applies to:
-
Microsoft Access 7.0
-
Microsoft Excel for Windows, 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:
- Start Microsoft Excel (version 5.0 or later).
- On the Insert menu, select Macro, and then click Module to create a new
module within the workbook.
- 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
- On the Insert menu, select Macro, and then click Dialog to create a new
dialog within the workbook.
- 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.)
- Use the List Box button to draw a list box inside the Dialog frame.
- Using the right mouse button, click OK within the Dialog frame, and
then click Format Object on the menu that appears.
- Click to clear the Dismiss check box, and then click OK.
- Using the right mouse button, click OK within the Dialog frame, and
then click Assign Macro on the menu that appears.
- Select GetAccessReportList and click OK.
- Click the Sheet1 tab to activate a worksheet within the workbook.
- On the Tools menu, click Macro.
- Select Main and click Run. Your custom dialog box should appear.
- 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:
Q147816 ACC: Using Microsoft Access as an OLE Automation Server
Additional query words:
Keywords : kberrmsg kbole IntpOlea
Version : WINDOWS:5.0,7.0
Platform : WINDOWS
Issue type :