Method to refresh reports between databases

  • Thread starter Thread starter Michael Clifford
  • Start date Start date
M

Michael Clifford

I have two Access databases (Production and Test) and, due to the PITA
exclusive lock on report designs, need to stage the report objects back and
forth. I can identify which reports have been worked on recently (with a
query to the system table). The problem is automating the copy of revised
and/or new reports back and forth.

The Import and Export options don't work too well, since these (and their
corresponding methods) avoid name collisions.

I'm new to programming in Access and am having trouble figuring out where
should the code go, since this involves two databases. Also, how can I
programmatically handle the copy so that Report x copies from Test to
Production as Report x (NOT Reportx1) if the former is newer than the
latter? Any help is appreciated. Thanks in advance.

BTW, my apologies: I posted this to the multiuser section but had second
thoughts, that this section may be more appropriate.

Regards,

Michael Clifford
 
Help me understand why you are having a problem here: have you not split the
database into front and back ends? Are you not simply distributing a
complete new copy of the front end to the users with your changes, after
re-linking the tables to the production back end? There's no need, in that
case, to "stage objects"...

It's _vital_ for Access multiuser; it's appropriate even for single-user
standalone databases that you distribute to a user or users.

It's simple, easy, and avoids the problem you seem to be facing.

Larry Linson
Microsoft Access MVP
 
Larry,

This is a 3rd party commercial specialized database application that I have
to administer. It uses a back-end (SQL Server) and a front end of forms,
reports, and linked tables. People develop reports constantly, or rather
they try to, but can't save the report and form designs due to the exclusive
lock.

Moreover, this is a medical application and the interfaces to the medical
equipment are written in Access. I can't take down the entire hospital
enterprise every time that a report design needs to be saved. We've had to
create a second server to develop the reports, shut down the works, get an
exclusive lock, then "blast" the changes back to the server. This has been a
time-consuming manual process.

I'd like a routine that takes the most recent report designs on Development
and copies them back to Production. If there's a report by the same name, it
copies over it if it was modified before the one on Development. This
contravenes Access's behavior to append a number on to the name when a name
collision occurs.

As mentioned before, I'm puzzled where to park the code (in a separate VB
exe file?). Please advise. Thanks in advance.

Hope that clarifies.

Regards,

Michael Clifford
 
Back
Top