Home  |  About  |   Search   

What's New
Table Of Contents
Credits
Netiquette
10 Commandments 
Bugs
Tables
Queries
Forms
Reports
Modules
APIs
Strings
Date/Time
General
Downloads
Resources
Search
Feedback
mvps.org

RunCommand Constants

Terms of Use


 

Reports: Open a report in an external database

Author(s)
Dev Ashish

(Q)    How can I open reports present in an external database through Automation?

(A)    Access 97 gives us a new method, OpenCurrentDatabase, a member of the Application object.  The following code uses this method to get to a report in an external database.

'************ Code Start *************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiSetForegroundWindow Lib "user32" _
            Alias "SetForegroundWindow" _
            (ByVal hwnd As Long) _
            As Long

Private Declare Function apiShowWindow Lib "user32" _
            Alias "ShowWindow" _
            (ByVal hwnd As Long, _
            ByVal nCmdShow As Long) _
            As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_NORMAL = 1

Function fOpenRemoteReport(strMDB As String, _
                                        strReport As String, _
                                        Optional intView As Variant) _
                                        As Boolean
Dim objAccess As Access.Application
Dim lngRet As Long

    On Error GoTo fOpenRemoteReport_Err

    If IsMissing(intView) Then intView = acViewPreview

    If Len(Dir(strMDB)) > 0 Then
        Set objAccess = New Access.Application
        With objAccess
            lngRet = apiSetForegroundWindow(.hWndAccessApp)
            lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
            'the first call to ShowWindow doesn't seem to do anything
            lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
            .OpenCurrentDatabase strMDB
            .DoCmd.OpenReport strReport, intView
            Do While Len(.CurrentDb.Name) > 0
                DoEvents
            Loop
        End With
    End If
fOpenRemoteReport_Exit:
    On Error Resume Next
    objAccess.Quit
    Set objAccess = Nothing
    Exit Function
fOpenRemoteReport_Err:
    fOpenRemoteReport = False
    Select Case Err.Number
        Case 7866:
            'mdb is already exclusively opened
            MsgBox "The database you specified " & vbCrLf & strMDB & _
                vbCrLf & "is currently open in exclusive mode.  " & vbCrLf _
                & vbCrLf & "Please reopen in shared mode and try again", _
                vbExclamation + vbOKOnly, "Could not open database."
        Case 2103:
            'form doesn't exist
            MsgBox "The report '" & strReport & _
                        "' doesn't exist in the Database " _
                        & vbCrLf & strMDB, _
                        vbExclamation + vbOKOnly, "report not found"
        Case 7952:
            'user closed mdb
            fOpenRemoteReport = True
        Case Else:
            MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
                    vbCritical + vbOKOnly, "Runtime error"
    End Select
    Resume fOpenRemoteReport_Exit
End Function
'************ Code End *************

1998-2009, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer