Opening a report in a different access database

[AndrewDB]

I am using an Access database to fill in and print claim forms. The claim
form itself is embedded as a bitmap to the background of the report. This has
made the Access application file very big (177Mb). I need to reduce the size
of the application file. I want to place these reports in a different Access
file to the application and then open them in preview using VBA from the
application file. I can transfer the data but can't get the reports to open
in preview.

 

[AndrewDB]

Hi Bonnie
Thanx for your response. I did try that initially but had problems with the
registration between the form image and database fields. Each time the report
was opened the fields did not correspond to the field labels contained in the
background image by varying amounts. Embedding the image gave me the accuracy
I need each time. So back to my problem, can you assist?

Regards

 

[Clifford Bass]

Hi Andrew,

177 MB, while big, is not very big; the maximum size of an Access
database is 2 GB. However, to do what you want you should use queries and
linked tables in your reports database so that you do not actually need to
copy any data. To open them from the original database place this in a
regular module:

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

Private m_appAccess As Access.Application

Public Sub PreviewARemoteReport(ByVal strReportName As String)

If m_appAccess Is Nothing Then
Set m_appAccess = New Access.Application
End If
With m_appAccess
If .CurrentProject.FullName = vbNullString Then
' Database not open
.OpenCurrentDatabase "C:\Temp\Reports Database.mdb"
.Visible = True
Else
' Database already open
' Make sure the report is closed so it will use up-to-date
information
.DoCmd.Close acReport, strReportName, acSaveNo
' Toggle visibility twice to make the database come to the front
.Visible = False
.Visible = True
End If
.DoCmd.OpenReport strReportName, acViewPreview
End With

End Sub

Public Sub CloseRemoteDatabase()

If Not m_appAccess Is Nothing Then
If m_appAccess.CurrentProject.FullName <> vbNullString Then
m_appAccess.CloseCurrentDatabase
End If
Set m_appAccess = Nothing
End If

End Sub

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

Fix the remote data base name. Then when you want to open a report do:

PreviewARemoteReport "rptMyReport"

This will fire up another copy of Access and open the specified report.
Or, if a previously used second copy is still running, it will use it.

Before you or your users exit out of Access make sure the following is
run:

CloseRemoteDatabase

Hope this helps,

Clifford Bass

 

[AndrewDB]

Thanx Bonnie and Clifford. I really appreciate the support.

Andrew

 

[Clifford Bass]

You are welcome Andrew!

Clifford Bass