Open a report in one database from another

[Renee]

I have two database, in one of them I have a report that I have created, now
I would like to be able to view this report in my second database. Is this
possible?

 

[Dave Bolt]

Two solutions come immediately to mind.
1) Import the report to the second database and run it from there.
2) Separate the front and backend parts of the databases then you can attach
the data you want to the report you want.
I assume that the tables/queries in both databases are compatible with the
report you want to run.
Regards
Dave

 

[Marshall Barton]

I have two database, in one of them I have a report that I have created, now
I would like to be able to view this report in my second database. Is this
possible?

Two more ways are to Reference the report database as a
library in the second db. Then add a public procedure in a
standard module in the report db. Code the procedure to
open the report. The tricky part is to link the data to the
report.

Another way is to use Automation to open the second db in
its own Access window and do whatever you want. If the data
is not available in the report's db, you have to play some
games to get the report's data.

If the data is in the second db, I think you might want to
seriously consider just importing the report into the second
db and be done with it.

 

[Renee]

So, the information on the report from the first database is not in the
second database. Does this pose a problem? I am new to using modules and/or
codes so if that is what I need to do can you please give me a little more
information on how to set that up?
Thank You

 

[Marshall Barton]

This entire situation creates a problem and it get
complicated,. definitely not something for beginners. The
only easy answer is to import the report into the db with
the data.

To get a report in one db to present data from another db
requires the report's record source query to know where the
data resides. This means the report must be modified so it
can set it's record source depending on how it is opened.
This can be done by using the OpenReport method's OpenArgs
to pass enough information for the report to figure it out
(probably best to provide the query's entire SQL statement.
BUT, if you do that for the other db, then you have to add
more code to make the report work correctly in its own db.
Are you sure you want to take oll of that on when you are
"new to using modules and/or codes"?

 

[Renee]

Yes I am sure I want to do this as it is the only way that I can see this
working for me. We have looked into other options and nothing seems to work
without seriously effecting our current data. If you can help me through
setting up the codes I am sure I will get it to work.
Is it possible to use this code that I found:
http://www.mvps.org/access/reports/rpt0012.htm

This entire situation creates a problem and it get
complicated,. definitely not something for beginners. The
only easy answer is to import the report into the db with
the data.

To get a report in one db to present data from another db
requires the report's record source query to know where the
data resides. This means the report must be modified so it
can set it's record source depending on how it is opened.
This can be done by using the OpenReport method's OpenArgs
to pass enough information for the report to figure it out
(probably best to provide the query's entire SQL statement.
BUT, if you do that for the other db, then you have to add
more code to make the report work correctly in its own db.
Are you sure you want to take oll of that on when you are
"new to using modules and/or codes"?
--
Marsh
MVP [MS Access]

So, the information on the report from the first database is not in the
second database. Does this pose a problem? I am new to using modules and/or
codes so if that is what I need to do can you please give me a little more
information on how to set that up?

 

[Marshall Barton]

I fail to see how copying a report into another front end
mdb can affect the data in either database. The reason for
not duplicating a report is primarily one of maintaining the
report (i.e. the need to make modifications twice).

If you feel you must, then first create a public procedure
in a standard module in the report's mdb. A rough outline
of the procedure would be like this air code:

Public Sub MyOpenReport(strReport As String, _
intView As Integer, _
strQuery As String)

DoCmd.OpenReport strReport, intView, _
OpenArgs:= strQuery
End Sub

Then modify the report's Open event procedure to include:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Me.OpenArgs
End If

Back in the other mdb, set a Reference to the report's mdb
file. (Don't forget that this means you must have a copy of
both front ends on every machine.)

Now, you can use code like this to open the report:

strSQL = "SELECT . . . " _
& "FROM table IN """ & CurrentDb.Name & """" _
& "WHERE . . ."
MyOpenReport "name of report", acViewPreview, strSQL

If you are using a query that Joins multiple tables for the
report's record source, use this syntax:
strSQL = "SELECT . . . " _
& "FROM table1 INNER JOIN table2 " _
& " ON table1.pk = table2.fk " _
& "IN """ & CurrentDb.Name & """" _
& "WHERE . . ."

You really need to work to understand all this so you can
deal with the problems that will inevitably arise.

If the report uses subreports, this will quickly spin out of
control. I do not even want to contemplate the issues this
will raise.