Copy of Database for Reports Only

[dkernan]

I am using Access 2K. Currently, we have a back-end .mdb that is
almost constantly in use, and some very complex reports that are
pulled via stand-alone form. Currently the reports are taking at
least 20 minutes to run, which is too long for a manager to wait. the
reports take 30 seconds if the .mdb is copied to the desktop and under
two minutes when no one else is in the system (no manager will stay
that late in the night for reports.)

What I want to do is have a copy of the database created every hour
and to have the manager reports pull data from the copy where there
will be significantly less activity. From reading the posts, it's
very easy to create a copy when the database isn't in use, but it's
always in use, so I need to automatically create the copy of the
database (or the three tables used for reports) every hour while the
back-end .mdb is in use.

I'm fairly new to Access and am better at usually don't do anything
more than write reports and queries in VBA, so your help is very much
appreciated and necessary.

Thanks!

-D. Kernan

 

[Guest]

Replication is what I consider to be the best-kept Access secret! With
replication you essentially have a copy of your database and with replication
manager you can set up almost continuous synchronization. I also have some
very complex queries/reports that I run from a database with the tables
linked to my locally replicated database. The reports run instantly whereas
on the network it usually results in my being booted out.

The problem you will have is finding someone that even knows about
replication; I am a completely self taught Access enthusiast and stumbled
onto replication 7 year ago and have been using it every since. The CG
network computers I have my Work Order DB on; I have no administrative
privileges and it takes an act of congress to make any changes. So with
replication all I do is sync to my USB Hard or Flash Drive DB. Then I use my
laptop and the database is 100% as functional as the Network DB but speed is
never an issue. You can also have a replica on your networked workstation HD
and the results are the same.
Best thing is that replication is all part of the MS Access package so no
extra cost or software to buy!

I will not have access to my home computer for at least a month so if you
have any questions please contact me on my alphanumeric pager 7132001003 at
usamobility dot net. leave a call back number or brief e-mail.

 

[Tony Toews [MVP]]

I am using Access 2K. Currently, we have a back-end .mdb that is
almost constantly in use, and some very complex reports that are
pulled via stand-alone form. Currently the reports are taking at
least 20 minutes to run, which is too long for a manager to wait.

Do your reports have subreports on them? If so that is your
performance problem.

Performance is worse after splitting - My personal experience
aka How to speed up complex forms and reports with many records each
with subreports.
http://www.granite.ab.ca/access/splitapp/performance.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[dkernan]

No there are no sub-forms. It's actually 2 small tables and one huge
table of 200+ fields. All of the reports are nested queries and some
go 8 levels deep. They're just complex reports.

i'm not in control of the back-end and wouldn't want to replicate it
without the admin's permission, but what I really want to do is just
copy the file while it's in use and use that copy for the reports. A
lot of people are saying that the copy is then unstable, but would
that matter if the copy is only being used for reports?

Dan