Split out reports in a FE into its own DB?

[Rick Roberts]

I am developing a good size app in Access 2003 with a FE / BE configuration
with most of my users using a Run Time version of Access that I will include
in my install package.

Currently all the reports and associated report forms are defined in the FE
along with everything else. My thought is to separate out the report related
items into it’s own db for purposes of maintenance. I can foresee a lot of
changes to the report db while the changes to the rest of the objects would
be minimal (hopefully!).

Do any of you MVP’s out there have a thought on this? I am just now getting
my mind around Front End Updating. I have seen Tony ‘s web site about the
Auto FE Updater and I am intrigued.

From a programming/security point of view it’s simpler to keep it all in
one, however I am interested in quicker loading, ease of maintenance and
whatever else I haven't thought of.

If I do split out the reports then what would be the best way call them from
a switchboard type menu? Application.Run? I do a lot of VBA coding so I am
not afraid to try anything!

I have a lot of thoughts swimming around my head and I would truly
appreciate anyone comments/experience with this type of issue.

 

[Marshall Barton]

I am developing a good size app in Access 2003 with a FE / BE configuration
with most of my users using a Run Time version of Access that I will include
in my install package.

Currently all the reports and associated report forms are defined in the FE
along with everything else. My thought is to separate out the report related
items into it’s own db for purposes of maintenance. I can foresee a lot of
changes to the report db while the changes to the rest of the objects would
be minimal (hopefully!).

Do any of you MVP’s out there have a thought on this? I am just now getting
my mind around Front End Updating. I have seen Tony ‘s web site about the
Auto FE Updater and I am intrigued.

From a programming/security point of view it’s simpler to keep it all in
one, however I am interested in quicker loading, ease of maintenance and
whatever else I haven't thought of.

If I do split out the reports then what would be the best way call them from
a switchboard type menu? Application.Run? I do a lot of VBA coding so I am
not afraid to try anything!

I have a lot of thoughts swimming around my head and I would truly
appreciate anyone comments/experience with this type of issue.

I haven't tried this kind of thing in a long time, because I
didn't think it was worth it. However, I did get that
arrangement to work by referencing the reports mdb as a
library in the main front end mdb. Then you can create a
public function in the reports mdb that can open the
reports. The function wouldn't have to do anything beyond
passing arguments to the OpenReport method and some error
handling. The main report's code would only need a minor
change. E.g. instead of
DoCmd.OpenReport . . .
you would use:
MyOpenReport . . .

Some things the reports mdb must do is link to all the
tables the same way the main mdb does and contain all the
queries needed by the reports.

One big drawback is that the reports will not be able to
refer back to values in the main mdb. So if you do that
sort of thing, you will have to do some rework to get the
values to the reports.

It would have been nice if Access provided an Open method
for a form/report Document or AccessObject, but until it
does, AFAIK, OpenReport is the only way to open a
form/report using arguments such as View and WhereCondition.

 

[Rick Roberts]

Thanks for your response and I appreciate your feedback. I have spent some
time looking into this and I am still split on separating it out or not.
Part of me feels like it’s a good idea, part of me doesn't.

Given your expertise what would you do?

 

[Marshall Barton]

As I said before, in the situation I was dealing with, I
didn't think it was worth it. However, your situation
and/or your criteria of what it's worth is probably
different then mine, so there's no definitive answer.

I suggest that you pick your most difficult report (in terms
of its need for form controls, global variables and tricky
record source query and see what it takes to move it to a
separate mdb. Once you get a feel for that, you'll get a
better idea if you want to proceed with all your other
reports or not.

 

[Rick Roberts]

Thanks, I am in the process of doing exactly that.

I suggest that you pick your most difficult report (in terms
of its need for form controls, global variables and tricky
record source query and see what it takes to move it to a
separate mdb. Once you get a feel for that, you'll get a
better idea if you want to proceed with all your other
reports or not.
--
Marsh
MVP [MS Access]

Thanks for your response and I appreciate your feedback. I have spent some
time looking into this and I am still split on separating it out or not.
Part of me feels like it’s a good idea, part of me doesn't.

Given your expertise what would you do?