Reports in separate DB

  • Thread starter Thread starter JF
  • Start date Start date
J

JF

Hello,

I have an access application with multiple reports that users can customize
or use as is. However, I would now like to separate these reports out to a
new DB but still allow the access app in the original DB to have access to
them. There are a couple of reasons for this. One is that users tend to
mistakenly overwrite the main mdb when upgrading, etc and therefore they
lose their custom reports. Another is, the VB code is locked in the main
mdb, and some pre-made reports use VB which means the users can't create
custom reports out of them. Unlocking the VB code is not an option.

In any case, is there an elegant way to separate reports out to a different
DB than where all the data is? The only way I can think of is, through
code, importing the selected report (a list of the reports in the report
MDB is easily created) to the main DB in a "tempReport". This "tempReport"
is then used for viewing or for any user changes and saved back to the
report DB if the user decides to customize. However, a problem I see with
this is I don't think I'll be able to create the "tempReport" if the report
has VB code in it due to the locking.

Another idea I somewhat researched but need more direction is having the
reports DB be a code reference to the main DB. Then, I can create a module
in the reports DB that would handle all the requests from the main db via
function calls (openReport, saveReport). That seems problematic though
because I'm not sure that the report can be opened within the context of the
report DB if there's no data within it. Also, saving a report seems like it
would be a real problem.

Has anyone tried this? Are there any other suggestions?

Regards,

JF
 
JF said:
I have an access application with multiple reports that users can customize
or use as is. However, I would now like to separate these reports out to a
new DB but still allow the access app in the original DB to have access to
them. There are a couple of reasons for this. One is that users tend to
mistakenly overwrite the main mdb when upgrading, etc and therefore they
lose their custom reports. Another is, the VB code is locked in the main
mdb, and some pre-made reports use VB which means the users can't create
custom reports out of them. Unlocking the VB code is not an option.

In any case, is there an elegant way to separate reports out to a different
DB than where all the data is? The only way I can think of is, through
code, importing the selected report (a list of the reports in the report
MDB is easily created) to the main DB in a "tempReport". This "tempReport"
is then used for viewing or for any user changes and saved back to the
report DB if the user decides to customize. However, a problem I see with
this is I don't think I'll be able to create the "tempReport" if the report
has VB code in it due to the locking.

Another idea I somewhat researched but need more direction is having the
reports DB be a code reference to the main DB. Then, I can create a module
in the reports DB that would handle all the requests from the main db via
function calls (openReport, saveReport). That seems problematic though
because I'm not sure that the report can be opened within the context of the
report DB if there's no data within it. Also, saving a report seems like it
would be a real problem.
Click to expand...


Making the reports mdb file a library reference in the main
mdb is a good way to do this. One major difficulty is how
the reports are tied to their record source. The obvious
approach is for the reports mdb to link to all appropriate
tables and put the queries that are used in reports local in
the reports db. A somewhat messy alternative to linking the
tables is to modify the reports' record source query to use
the IN clause with the path back to the db with the tables.
 
Thanks for the response.

I figured I would have to do something like that with linking the tables.
That shouldn't be a problem.

However, what if after opening the report within the main app, the user
wants to alter the opened report and then save the changes back to the
reports db (under a different report name)? I guess I'm confused as to
where exactly the opened report is "residing". It seems to me that it would
reside in the main db and so using any kind of system save command (Ex.
DoCmd "save") would save it in the main db. Before I do the work on this, I
want to make sure that through code I can easily save the altered report to
the reports db. Additionally, the user is allowed to create new reports.
After a new report is made, if it is closed down, a message box
automatically pops us asking if the user wants to save. I would need some
way to either intercept this or disable it so I can save the report to the
reports db and not the main db. Does this seem possible?

Thanks,

-JF
 
JF said:
I figured I would have to do something like that with linking the tables.
That shouldn't be a problem.

However, what if after opening the report within the main app, the user
wants to alter the opened report and then save the changes back to the
reports db (under a different report name)? I guess I'm confused as to
where exactly the opened report is "residing". It seems to me that it would
reside in the main db and so using any kind of system save command (Ex.
DoCmd "save") would save it in the main db. Before I do the work on this, I
want to make sure that through code I can easily save the altered report to
the reports db. Additionally, the user is allowed to create new reports.
After a new report is made, if it is closed down, a message box
automatically pops us asking if the user wants to save. I would need some
way to either intercept this or disable it so I can save the report to the
reports db and not the main db. Does this seem possible?
Click to expand...


Gee, I really don't lnow. I would never operate in design
mode on two different mdb files from a single front end. I
believe that might have been possible in an older version,
but not since the A2K. I would instruct the users to open
the report mdb separately if they were going to mucking
about with design operations.

But don't take my word for it, set a couple of simple test
mdb files and try it out.
--
Marsh
MVP [MS Access]


 
I gave it a try with two sample mdbs and sure enough it worked as I had
hoped. If the report was opened via the function that references reports
from the reports DB, then if I make changes to the opened report and save
them, the report in the reports db is saved.

The only problems I've found with this method is the reference to the
reports db is an absolute path. This is an issue since the user can choose
to install the app wherever they want so I can't guarantee the location of
the reports db.

Also, I need to get the "collection" of reports in the reports DB to display
in the main db. I can't, for example, use CurrentProject.AllReports even if
that code is in the reports db since the main app will always be the current
project. Any other suggestions?


Marshall Barton said:
JF said:
I figured I would have to do something like that with linking the tables.
That shouldn't be a problem.

However, what if after opening the report within the main app, the user
wants to alter the opened report and then save the changes back to the
reports db (under a different report name)? I guess I'm confused as to
where exactly the opened report is "residing". It seems to me that it would
reside in the main db and so using any kind of system save command (Ex.
DoCmd "save") would save it in the main db. Before I do the work on this, I
want to make sure that through code I can easily save the altered report to
the reports db. Additionally, the user is allowed to create new reports.
After a new report is made, if it is closed down, a message box
automatically pops us asking if the user wants to save. I would need some
way to either intercept this or disable it so I can save the report to the
reports db and not the main db. Does this seem possible?
Click to expand...


Gee, I really don't lnow. I would never operate in design
mode on two different mdb files from a single front end. I
believe that might have been possible in an older version,
but not since the A2K. I would instruct the users to open
the report mdb separately if they were going to mucking
about with design operations.

But don't take my word for it, set a couple of simple test
mdb files and try it out.
--
Marsh
MVP [MS Access]


to
a access
to of
the like
it
Click to expand...
Click to expand...
 
JF said:
I gave it a try with two sample mdbs and sure enough it worked as I had
hoped. If the report was opened via the function that references reports
from the reports DB, then if I make changes to the opened report and save
them, the report in the reports db is saved.
Click to expand...

Well. If it works, it works, but I am rather surprised.
What version are you using? I really didn't think the VBE
could keep track of multiple source files. Are you sure
that the changes were saved and not that it didn't just dump
the changes? I'd try this out myself, but. what with other
work, the holidays, etc, I don't really have the time to
delve into such a complex arrangement.


The only problems I've found with this method is the reference to the
reports db is an absolute path. This is an issue since the user can choose
to install the app wherever they want so I can't guarantee the location of
the reports db.
Click to expand...

Yes, that can be a problem. Is there any relationship
between the path to the front end db and the path to
report's mdb? If so, you could get the front end's path
using CurrentProject.Path and tack on the relative path of
the reports mdb.

But, this is really an installation problem since you will
have to set the path in the References collection before you
can open the front end db. Remember that one bad reference
usually messes up the others.

Also, I need to get the "collection" of reports in the reports DB to display
in the main db. I can't, for example, use CurrentProject.AllReports even if
that code is in the reports db since the main app will always be the current
project. Any other suggestions?
Click to expand...

Once you have the Reference straightened out, you can get
the path from there to use in your code:

Dim rdb As Database
Dim doc As Document
dim reportspath as string

rdbpath = Application.References("<refname>").FullPath
Set rdb = OpenDatabase(rdbpath)
For Each doc In rdb.Containers("Reports").Documents
Debug.Print doc.Name
Next doc
Set rdb = Nothing



 
Thanks for all your help Marshall.

Yea, I'm sure the changes were saved and in the correct DB. I changed the
title and everything and then when I go into the reports db, sure enough the
changes stuck.

As far as the references go, the reports DB will be in the same directory as
the main DB - which is chosen by the user at installation. I didn't even
realize there was a "references" collection. Could I add the reports DB
reference in my startup routine? Obviously, it would execute every time.
Are there any problems that could arise from doing this?

That code is exactly what I was looking for. I was playing around with the
"containers" collection but I couldn't find the documentation for how to get
reports from it. On a side note, I think I will be moving queries over to
this new DB. Do I use "QueryDefs" to get the queries from the collection?
I suppose I can just experiment.

Thanks again for your help and Happy Holidays!
 
JF said:
Yea, I'm sure the changes were saved and in the correct DB. I changed the
title and everything and then when I go into the reports db, sure enough the
changes stuck.
Click to expand...

The mind boggles at the effort the Access developers went
through to deal with all the ecomplexities involved with
this situation.

As far as the references go, the reports DB will be in the same directory as
the main DB - which is chosen by the user at installation. I didn't even
realize there was a "references" collection. Could I add the reports DB
reference in my startup routine? Obviously, it would execute every time.
Are there any problems that could arise from doing this?
Click to expand...

No, you can't. The references must be straightened out
before you attempt to run any code in the mdb (not 100%
true, but extrordinaily difficult to work through the
complexities). If I were doing this, the first thing I
would set up is an install mdb file that manipulated the
front end into its destination directory, squared away its
references, reestablish its table links and maybe do a
little house keeping.

That code is exactly what I was looking for. I was playing around with the
"containers" collection but I couldn't find the documentation for how to get
reports from it. On a side note, I think I will be moving queries over to
this new DB. Do I use "QueryDefs" to get the queries from the collection?
I suppose I can just experiment.
Click to expand...

Yes, you'd better experiment. You can certainly import the
queries to get started, but, for updates, you'll have to
work out the issues with either updating each querydef's
properties (probably just the SQL property) or deleting
obsolete objects and using TransferDatabase or CopyObject to
copy in the new versions.
--
Marsh
MVP [MS Access]


 
Hi Marshal,

I'm not sure if you still are following this thread but I am having a
problem with the code below. It works fine for retrieving the list of
reports in the reports db. However, when I exit the main application, the
MSACCESS.EXE executable still remains in the task list. Also, the lock
files (.ldb) for both dbs have not been deleted. This only happens when I
go to the report form and execute the code that opens a reference to the
reports db. I explicitly clean up the reference by using rdb.Close and Set
rdb = Nothing but the problem persists. Note that the location of this code
is actually in a module in the reports db (not in a main db module) if that
makes any difference.

-JF
 
JF said:
Hi Marshal,

I'm not sure if you still are following this thread but I am having a
problem with the code below. It works fine for retrieving the list of
reports in the reports db. However, when I exit the main application, the
MSACCESS.EXE executable still remains in the task list. Also, the lock
files (.ldb) for both dbs have not been deleted. This only happens when I
go to the report form and execute the code that opens a reference to the
reports db. I explicitly clean up the reference by using rdb.Close and Set
rdb = Nothing but the problem persists. Note that the location of this code
is actually in a module in the reports db (not in a main db module) if that
makes any difference.

-JF
Click to expand...

The db may not be closing because you forgot to Close the
database that you opened (as opposed to just referencing an
existing db object).

Does the reports mdb actually have a reference to itself?
If so, it shouldn't. The round about technique of
determining the path to the reports mdb is only needed in
the main db.

You're running that code in the reports mdb??? I'm not sure
what sense that makes, but, regardless of all that, there's
no need to use OpenDatabase.

All that is unnecessary if the code is in the reports mdb.
While your code doesn't even "know" what CurrentDb is (it
depends on which mdb you started on the desktop), it can use
CodeDb to avoid the whole issue.

Dim rdb As Database
Dim doc As Document
Set rdb = CodeDb()
For Each doc In rdb.Containers("Reports").Documents
Debug.Print doc.Name
Next doc
Set rdb = Nothing
 
Thanks again Marshall.

I think I figured out the problem. The main app has a list box that lists
all the current reports. The "Data Source" for this list box is a function
that builds the array of reports on the fly. This function is located in
the main db. However, with the new reports db, I just had the original
function call a function in the reports db that did all the list box work.
This function in the reports db was causing the problems. Even though I
made sure I was closing all references, I still had the problem. I moved
the work back to the main db but still had the problem. Finally, I made
sure I set every object reference to "Nothing", including even the "doc"
variable in the code below after each loop iteration. Now, everything works
fine.

As far as having the code in the reports db, I basically did that for
modularity. Since I basically have an "API" for opening, closing, deleting
reports, etc. I figured I would throw all code related to reports db in a
reports db module. Is there a better way to, say, open the reports then to
have an "openReport" function in the reports db which just calls a DoCmd?
 
JF said:
Thanks again Marshall.

I think I figured out the problem. The main app has a list box that lists
all the current reports. The "Data Source" for this list box is a function
that builds the array of reports on the fly. This function is located in
the main db. However, with the new reports db, I just had the original
function call a function in the reports db that did all the list box work.
This function in the reports db was causing the problems. Even though I
made sure I was closing all references, I still had the problem. I moved
the work back to the main db but still had the problem. Finally, I made
sure I set every object reference to "Nothing", including even the "doc"
variable in the code below after each loop iteration. Now, everything works
fine.
Click to expand...

Hmmm. That's one I haven't seen before. Thanks for the
update, I'll keep that in mind.

As far as having the code in the reports db, I basically did that for
modularity. Since I basically have an "API" for opening, closing, deleting
reports, etc. I figured I would throw all code related to reports db in a
reports db module.
Click to expand...

Sounds like a good reason to me.

I don't want to recommend something based on undocumented
knowledge, but you could use a query to retrieve all the
reports:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764

You could even use that in the main mdb (e.g. the RowSource
of a combo or list box) by using an IN phrase on the From
clause.

Is there a better way to, say, open the reports then to
have an "openReport" function in the reports db which just calls a DoCmd?
Click to expand...

No, AFAIK, short of using Automation, there is no other way
to open a report in a different mdb.
--
Marsh
MVP [MS Access]



 
Once again Marshall, I thank you for your help. I like using that query
much better even if it is undocumented. It seems cleaner and I don't seem
to have any problems with Access remaining open. I was never a big fan of
having a function for the List Box data source - I inherited this
application and that was the way it was so I simply followed suit at first.

I was hoping I could get your advice on two more things. First, although
not required for what I'm doing, I decided to throw all the user-defined
queries in this separate "reports db". It just seemed like a good way to
clean up the main db. Anyway, everything works just like the reports
(albeit, I had to modify the row source query a little) except for one
thing. The form that displays all the queries and reports has some code
that closes all open queries/reports when the form is unloaded. This was
easily handled when everthing was in the same db by using the AllReports and
AllQueries collections. Now, for reports, this is easy to handle by simply
using the Reports collections (iterating downwards) since it contains all
the open reports. However, there is not a similar collection for queries.
I tried iterating through the QueryDefs collection of the reports db and
calling Close on each QueryDef with no success. The funny thing is, I
searched on Google Groups and saw that you had actually answered a question
similar to this about a year and a half ago saying it wasn't possible. I'm
hoping you might have figured out a way since then. If not, no big deal, I
can just throw queries back in the main db.

The second issue is a little more problematic. After further testing, I was
wrong about everything working just fine. The one scenario that doesn't
work is opening a new report (or query) using:

DoCmd.RunCommand acCmdNewObjectReport

And then saving using the tool bar save button. It will always save in the
main db. I also tried using this:

Application.CreateReport "reports.mdb"

which takes a database for the first argument, but that again saves the
report in the main db despite the database argument.

I was thinking a possible solution would be to move all reports and queries
over to the reports db (via code triggered on certain events (unload of
form, refresh of list box, etc). Does this seem reasonable or even
possible? Any other suggestions?

Regards,

JF

Marshall Barton said:
JF said:
Thanks again Marshall.

I think I figured out the problem. The main app has a list box that lists
all the current reports. The "Data Source" for this list box is a function
that builds the array of reports on the fly. This function is located in
the main db. However, with the new reports db, I just had the original
function call a function in the reports db that did all the list box work.
This function in the reports db was causing the problems. Even though I
made sure I was closing all references, I still had the problem. I moved
the work back to the main db but still had the problem. Finally, I made
sure I set every object reference to "Nothing", including even the "doc"
variable in the code below after each loop iteration. Now, everything works
fine.
Click to expand...

Hmmm. That's one I haven't seen before. Thanks for the
update, I'll keep that in mind.

As far as having the code in the reports db, I basically did that for
modularity. Since I basically have an "API" for opening, closing, deleting
reports, etc. I figured I would throw all code related to reports db in a
reports db module.
Click to expand...

Sounds like a good reason to me.

I don't want to recommend something based on undocumented
knowledge, but you could use a query to retrieve all the
reports:

SELECT [Name]
FROM MSysObjects
WHERE [Type] = -32764

You could even use that in the main mdb (e.g. the RowSource
of a combo or list box) by using an IN phrase on the From
clause.

Is there a better way to, say, open the reports then to
have an "openReport" function in the reports db which just calls a DoCmd?
Click to expand...

No, AFAIK, short of using Automation, there is no other way
to open a report in a different mdb.
--
Marsh
MVP [MS Access]



when
I and
Set this
code
Click to expand...
Click to expand...
 
JF said:
Once again Marshall, I thank you for your help. I like using that query
much better even if it is undocumented. It seems cleaner and I don't seem
to have any problems with Access remaining open. I was never a big fan of
having a function for the List Box data source - I inherited this
application and that was the way it was so I simply followed suit at first.

I was hoping I could get your advice on two more things. First, although
not required for what I'm doing, I decided to throw all the user-defined
queries in this separate "reports db". It just seemed like a good way to
clean up the main db. Anyway, everything works just like the reports
(albeit, I had to modify the row source query a little) except for one
thing. The form that displays all the queries and reports has some code
that closes all open queries/reports when the form is unloaded. This was
easily handled when everthing was in the same db by using the AllReports and
AllQueries collections. Now, for reports, this is easy to handle by simply
using the Reports collections (iterating downwards) since it contains all
the open reports. However, there is not a similar collection for queries.
I tried iterating through the QueryDefs collection of the reports db and
calling Close on each QueryDef with no success. The funny thing is, I
searched on Google Groups and saw that you had actually answered a question
similar to this about a year and a half ago saying it wasn't possible. I'm
hoping you might have figured out a way since then. If not, no big deal, I
can just throw queries back in the main db.
Click to expand...

Well, I don't remember that earlier thread and I'm not
entirely sure what you mean by a query being open.
Probably, you just mean the query is displaying its data in
sheet view and, if so, then no I don't know of any way to
identify and close those. Clearly, I don't know everything,
so there may be some obscure (API?) stuff that might do it.

The second issue is a little more problematic. After further testing, I was
wrong about everything working just fine. The one scenario that doesn't
work is opening a new report (or query) using:

DoCmd.RunCommand acCmdNewObjectReport

And then saving using the tool bar save button. It will always save in the
main db. I also tried using this:

Application.CreateReport "reports.mdb"

which takes a database for the first argument, but that again saves the
report in the main db despite the database argument.
Click to expand...

That db argument is used to locate a template report. It
has nothing to do with where the new report is created.

Both DoCmd and the Application object are unaware of any
other databases that may be open and/or referenced.

Like I said else thread, short of using Automation, I don't
think this is possible.

I was thinking a possible solution would be to move all reports and queries
over to the reports db (via code triggered on certain events (unload of
form, refresh of list box, etc). Does this seem reasonable or even
possible? Any other suggestions?
Click to expand...

You're crawling further and further out on that limb, which
may very well break off just as you think you're getting
close to the end.

No, I don't think that's reasonable, but then I wouldn't
have allowed users to design objects in a library mdb from
the main mdb either. You may very well be blazing a new
trail here.
 
Back
Top