open report from external Database

R

Ron Weiner

If you simply want to Print the report than it is pretty easy. If you
need/want to print preview then it becomes a little more complex. Basically
what you want to do is to start another instance of Access running the other
database, and use that instance to print the report. It is important to
make sure that whenever you create a new instance of anything that you
properly dispose of it when you are done. Otherwise that instance will
continue to live in memory until the computer is restarted or crashes due to
low resources. Here is some code I have used in the past to print reports
from an outside database.

Public Sub OpenExtRpt(strExtDbFileSpec As String, strReportName As String, _
Optional acView As Integer = acViewNormal, _
Optional strFilter As String = "", _
Optional strWhere As String = "")

Dim o As Access.Application
On Error GoTo Err_OpenExtRpt
Set o = New Access.Application
o_OpenCurrentDatabase strExtDbFileSpec
If acView = acViewNormal Then
'Print to Printer
o.DoCmd.OpenReport strReportName, acView, strFilter, strWhere
Else
' Print Preview - Cheat Make A snapshot - But No Filter - No Where
o.DoCmd.OutputTo acOutputReport, strReportName, acFormatSNP,
"c:\temp.snp", True
End If
Err_OpenExtRptOut:
o.CloseCurrentDatabase
Set o = Nothing
Exit Sub
Err_OpenExtRpt:
MsgBox "Error " & Err.Number & " - " & Err.Description
GoTo Err_OpenExtRptOut
End Sub

Notice that this code creates a new instance of access
opens the database that contains the report
Prints the report to the printer
or
Creates a SnapShot and opens the Snapshot Viewer
then promptly Closes the database
and frees all resources

There is no reason you could not open the report in print preview mode but
it would all disappear as soon as the code got to the o.CloseCurrentDatabase
statement. If you absolutely need preview and a filter or a where condition
for the report than you need to create the Access Application object OUTSIDE
of this sub and pass it to the sub or make its scope global so this sub can
see it. Then you also have to come up with a methodology for closing and
destroying this object when the user is no longer interested in the preview.
 
O

Ofer Cohen

Create a reference from MDB1 to MDB2, as followed:

1. Open MDB2
2. Create a function in a module that open the report
Function RunMyReport()
docmd.OpenReport "ReportName"
End Function
3. Close MDB2

4. Open MDB1
5. Press Ctrl+G to open code editor
6. In the menu bar select Tools > reference
7. Browse and select MDB2
8. Call the function you created in stage 2

RunMyReport
======================
The reference from MDB1 to MDB2 will recognize all the functions that are in
MDB2 only, it wont recognize Forms/Reports/Macro's etc.
So to run any of them, you'll need to create a function in MDB2 that run any
of them, and then you can run the function.
 
Top