Opening report in a different mdb

[JimP]

All of my objects are in one mdb, except a report format which is in a
different mdb.

Can I open (reference) the report object in the other mdb to open the
report?

 

[Marshall Barton]

All of my objects are in one mdb, except a report format which is in a
different mdb.

Can I open (reference) the report object in the other mdb to open the
report?

No, not directly.

Setting a ***reference*** to another Access file makes that
file a library to the file with the reference. Having a
library file allows you to use its public/global variables
and its public Function and Sub procedures. Using libraries
is a powerful, but potentially complex approach to solving a
problem and not to be done lightly.

For your report, all that stuff means that the library file
would have to have a farily simple procedure to open the
report. Doing that is not sufficient to resolving the issue
of which file contains the report's data records. If the
records are also in (or linked from) the library file, you
do not need anything else. But if the report's data records
are in the referencing file, then you need to do something
to tell the report where/how to get to the data tables. If
the report's record source is a query, the query would have
to be modified/replaced.

OTOH, if the other file contains both the report and the
report's data table (or links to the tables), then you could
use Automation to run the other database and its report
without making it a library. This would at least avoid the
maintenance issues of setting a reference to another file.

 

[JimP]

Thank you,

All objects (i.e. data, queries, etc.) except the "report" are in the are in
the host (referencing?) mdb. Can you offer a specific recommendation to run
the report? Or, is my best option to import the report into the host mdb,
open it, and then delete it?

 

[Marshall Barton]

It is way easier in the short term to import the report, but
because of maintenance issues, I hate to duplicate anything.

If you really want to pursue the library approach, then I
need to know more about the report and any subreports'
record source tables/queries. It would be a lot easier if
the report's mdb file could just link to the main mdb's
tables, but if the report is used on other tables in the
library mdb, then we'll have to figure out a way to change
the record source on the fly.

 

[Daniel Pineault]

I'm not sure if this is what you are looking for but you could do something
along the lines of:

Dim obj As New Access.Application
obj.OpenCurrentDatabase ("YourDatabaseNameAndFullPath.mdb")
obj.Visible
obj.DoCmd.OpenReport "YourReportName", acViewPreview
obj.CloseCurrentDatabase
Set obj = Nothing
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[JimP]

Here's some background.

There is one mdb that stores all objects except reports (the data itself
originates in a SQL Server db via linked tables). There is another mdb that
only contains reports. Reports can get quite large because of company logos,
such that a long time ago it was decided to place the reports in a separate
mdb.

Individual company's that use this service currently import their selected
reports into the mdb containing the data objects etc (this amounts to 20-30
reports out of several thousand). This works except that frequent updates to
the mdb's requires re-importing the reports (i.e. overhead).

It's possible that the (2) mdb's could be combined, but it could push the
(2) gb limit over time. I was hoping for a solution that would not require
physically importing the reports

Thanks,
Jim

It is way easier in the short term to import the report, but
because of maintenance issues, I hate to duplicate anything.

If you really want to pursue the library approach, then I
need to know more about the report and any subreports'
record source tables/queries. It would be a lot easier if
the report's mdb file could just link to the main mdb's
tables, but if the report is used on other tables in the
library mdb, then we'll have to figure out a way to change
the record source on the fly.
--
Marsh
MVP [MS Access]

All objects (i.e. data, queries, etc.) except the "report" are in the are
in
the host (referencing?) mdb. Can you offer a specific recommendation to
run
the report? Or, is my best option to import the report into the host mdb,
open it, and then delete it?


"Marshall Barton" wrote

 

[Marshall Barton]

Does that mean that all the reports use the same (linked?)
tables and the tables are in the same mdb as the reports?

If so then it's easy to run the report from your front end
mdb. If the reports' tables can be in various different
places, then we need to find a way to tell the reports where
their data resides.

 

[JimP]

All reports do use the same linked tables (500 tables and 1,500 queries).
All tables and queries reside in one mdb. All reports reside in another mdb.

So, to answer your question the tables and reports do not reside in the same
mdb - but there are only (2) mdb's involved.

Does that mean that all the reports use the same (linked?)
tables and the tables are in the same mdb as the reports?

If so then it's easy to run the report from your front end
mdb. If the reports' tables can be in various different
places, then we need to find a way to tell the reports where
their data resides.
--
Marsh
MVP [MS Access]

There is one mdb that stores all objects except reports (the data itself
originates in a SQL Server db via linked tables). There is another mdb
that
only contains reports. Reports can get quite large because of company
logos,
such that a long time ago it was decided to place the reports in a
separate
mdb.

Individual company's that use this service currently import their selected
reports into the mdb containing the data objects etc (this amounts to
20-30
reports out of several thousand). This works except that frequent updates
to
the mdb's requires re-importing the reports (i.e. overhead).

It's possible that the (2) mdb's could be combined, but it could push the
(2) gb limit over time. I was hoping for a solution that would not require
physically importing the reports


"Marshall Barton" wrote

 

[Marshall Barton]

But where are the form's that are used to specify the
report's criteria and call the reports? I was under the
impression that they were in a third mdb file.

Actually, it may not make any difference. The important
thing is that the reports know exactly where their data
resides. **IF** the report's mdb has linked tables to the
tables in the data mdb, then the report's mdb file can be
opened and the reports can be run manually from the db
window.

If I have understood what you are saying, after you set the
a Reference to the reports mdb, all you need to do is create
a public procedure in a standard module in the reports' mdb
file something like:

Public Sub OpenRemoteReport(ReportName As Variant, _
Optional View As AcView, _
Optional FilterName As Variant, _
Optional WhereCondition As Variant, _
Optional WindowMode As AcWindowMode, _
Optional OpenArgs As Variant)

DoCmd.OpenReport ReportName, View, FilterName, _
WhereCondition, WindowMode, OpenArgs
End Sub

Then your forms can use buttons or whatever with code that
calls the above procedure instead of using DoCmd.OpenReport

 

[JimP]

Thanks for your help, but I'm not optimistic about this.

1. (Mdb1) The forms, queries, linked tables, macros, modules are in one mdb
(there is a menu heirarchy in this mdb that allows users to select reports
and enter report parameters)
2. (Mdb2) Only the reports are in the 2nd mdb (it is like a report catalog.
users have manually imported selected reports into Mdb1 above and then run
them from there)

I was hoping to find a way to select a report in Mdb1 and then somehow open
it (even though the actual report resides in Mdb2). All objects used by the
report are in Mdb1

My alternate plan is to simply import reports into Mdb1 from Mdb2 on the fly
(when selected, if they do not exist in Mdb1, then they are imported,
otherwise the import is skipped). This way all objects are in a single mdb.

But where are the form's that are used to specify the
report's criteria and call the reports? I was under the
impression that they were in a third mdb file.

Actually, it may not make any difference. The important
thing is that the reports know exactly where their data
resides. **IF** the report's mdb has linked tables to the
tables in the data mdb, then the report's mdb file can be
opened and the reports can be run manually from the db
window.

If I have understood what you are saying, after you set the
a Reference to the reports mdb, all you need to do is create
a public procedure in a standard module in the reports' mdb
file something like:

Public Sub OpenRemoteReport(ReportName As Variant, _
Optional View As AcView, _
Optional FilterName As Variant, _
Optional WhereCondition As Variant, _
Optional WindowMode As AcWindowMode, _
Optional OpenArgs As Variant)

DoCmd.OpenReport ReportName, View, FilterName, _
WhereCondition, WindowMode, OpenArgs
End Sub

Then your forms can use buttons or whatever with code that
calls the above procedure instead of using DoCmd.OpenReport
--
Marsh
MVP [MS Access]

All reports do use the same linked tables (500 tables and 1,500 queries).
All tables and queries reside in one mdb. All reports reside in another
mdb.

So, to answer your question the tables and reports do not reside in the
same
mdb - but there are only (2) mdb's involved.

"Marshall Barton" wrote

 

[Marshall Barton]

Is there a reason why the reports mdb2 can not link to the
tables too?

If the reports are based on some queries in mdb1, then I
contend that those queries belong in mdb2 as well.

The approach/code I posted should work if the reports can
pass the test of being run manually in mdb2.

 

[JimP]

The problem is if all objects are in the same mdb (along with temp tables
for the report data), the 2 gb limit is occasionally exceeded. As it is, the
mdb that stores the reports will probably need to be split.

So, I think it is probably best left as is. Thanks for your help.

Is there a reason why the reports mdb2 can not link to the
tables too?

If the reports are based on some queries in mdb1, then I
contend that those queries belong in mdb2 as well.

The approach/code I posted should work if the reports can
pass the test of being run manually in mdb2.
--
Marsh
MVP [MS Access]

Thanks for your help, but I'm not optimistic about this.

1. (Mdb1) The forms, queries, linked tables, macros, modules are in one
mdb
(there is a menu heirarchy in this mdb that allows users to select reports
and enter report parameters)
2. (Mdb2) Only the reports are in the 2nd mdb (it is like a report
catalog.
users have manually imported selected reports into Mdb1 above and then run
them from there)

I was hoping to find a way to select a report in Mdb1 and then somehow
open
it (even though the actual report resides in Mdb2). All objects used by
the
report are in Mdb1

My alternate plan is to simply import reports into Mdb1 from Mdb2 on the
fly
(when selected, if they do not exist in Mdb1, then they are imported,
otherwise the import is skipped). This way all objects are in a single
mdb.


"Marshall Barton" wrote in message

 

[Marshall Barton]

Right, that's the problem we're trying to solve.

I don't understand why the reports' can not get their data
from the main mdb. I would think the reports can use linked
tables or the reports' record source can use queries with an
IN phrase to retrieve their data in the main mdb. Isn't
that the kind of things that are done by the folks when a
new report is created?

 

[JimP]

The reports that we have been referring to are "client" custom reports with
logos and features that other users probably would not use.

There are also another 1,000 or so reports that are for all users. Combining
all objects into a single mdb would exceed the 2 gb limit.

Right, that's the problem we're trying to solve.

I don't understand why the reports' can not get their data
from the main mdb. I would think the reports can use linked
tables or the reports' record source can use queries with an
IN phrase to retrieve their data in the main mdb. Isn't
that the kind of things that are done by the folks when a
new report is created?
--
Marsh
MVP [MS Access]

The problem is if all objects are in the same mdb (along with temp tables
for the report data), the 2 gb limit is occasionally exceeded. As it is,
the
mdb that stores the reports will probably need to be split.

So, I think it is probably best left as is. Thanks for your help.

 

[Marshall Barton]

The reports that we have been referring to are "client" custom reports with
logos and features that other users probably would not use.

You keep saying that, but I don't see how that changes the
problem we're trying to solve. The question at hand is how
can the report's get to their data in mdb1

There are also another 1,000 or so reports that are for all users. Combining
all objects into a single mdb would exceed the 2 gb limit.

So? Maybe you should put the custom report in mdb2 and
create an mdb3 for the common reports? Either way, it
doesn't change the problem and the question still remains
the same. How to get all the reports, regardless of where
they are located, connected to their data.

Certainly putting the reports in mdb1 will make all the data
and queries readily available because that's what you are
doing now. BUT, that means importing reports on an as
needed basis and that in turn causes bloat in mdb1. More
bloat will force more frequent Compact operations and that
requires a more rigorous backup procedure, especially since
you also have the data tables in mdb1.

That also raises the serious question of why you have the
data tables in mdb1. The tables should be in a separate
backend, data only mdb so the data is not involved when
forms and queries are modified/improved and the forms are
not impacted by data imports and maintenance. Having the
data tables in mdb1 also implies that all the users are
sharing and opening the same mdb, which is a known cause of
corruption.

This architecture strikes me as a maintenance nightmare with
a disaster lurking around every corner.