Open a report in another database

[dday01]

Hi all,

I would like to open a report by clicking a button on a form in one access data base which opens a report from another access database. Is this possible? What code would I use to accomplish this?

All help is greatly appreciated.

Thanks,

D

 

[SgtJarrow]

I don't know where this code originated from, but I know it was from a tek-tips memeber...so they deserve the credit.

Place the code at the bottom of this post into a module. Then create the button you want and put the follwing in the OnClick event procedure:

Dim RetVal As Variant

RetVal = fOpenRemoteReport("Path to DB", "Name of Report)



' ************* Start Code ***************
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

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com