database changes and importing data

valmatic
valmatic used Ask the Experts™
on
Hi.  In the past I've made all of my changes in a copy of my production db and once it's working the way I want, I change the names on the existing object being changed and I copy objects over to the production db manually after hours.  Any changes to the tables are duplicated manually so I make sure I don't affect the data.  Generally it works but sometimes the production database gets hosed because maybe I forgot something on the transfer or some other detail and I end up restoring from a saved copy and trying to figure out what happened.  There has to be an easier way though.  

What is the best way to make multiple changes to an access database that may take days to fnish, without affecting data in a production environment?  

Example - I have a database that has been running for years.  I'm asked to include new report data that is not being tracked anywhere.  I have to add fields to tables, change queries, forms, reports, afected macros, etc...  What's the best way to do so so users don't see any lapse in functionality?


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
If you were to create  a master database and the remote databases can be replicas. You can make changes to the "master" database an test until you are satified it is working as designed. Then perform a replication sync to update the remote database replicas.

Author

Commented:
HI.  Are you talking about splitting my database or setting up some sort of synch between the users' copy and the master?  
I recommend a split database, particularly if you are having data being changed to a central location by multiple users. That way you can add new queries, forms, etc to the front end and simply distribute new copies of the resulting application to your users without having to change any shared files. If you also need to make changes to the back end with the data, you can use the replica technique tbarkldull mentioned to update your table structure after hours. That would indeed be a synch between your development copy and the production copy.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
That makes sense, mostly.  The back end setup though...  Would it simply be a linked table setup for all tables?  I'd make my changes to the master and when done, go into the copy and tell it to synch?  Is there a way to push a synch from the master?  I've only ever used a pull method.

Commented:
The sync is a copy (replica) of the Master. Replication allows copying and distributing updates and is a push method

Author

Commented:
OK, I've just read a little on replication in Access.  Current database has password security written into it.  Is it true replication does not allow password driven access?    

Author

Commented:
not completely what i wanted

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial