Database replicas change of  design master

Posted on 2009-04-27
Last Modified: 2012-05-06
I have a design master database seating on our company server. I have a replica database on laptop of our salesperson. Synchronization is done over VPN.
Inside company several users are connected to design master.
All this works ok.
Problem is if I want to make design changes on some forms or tables.

I want to copy design master on my hard disk make changes on the form.
After that I would for the short period of time I kick of users from design master on server to  import changed forms back on server design master. Then put back users on.

But as sun as copy design master to my hard disk it become new replica database and I am not allowed to do any changes relating to design.

This is big problem for me as maintenance of the databases is very complicated.
I need to kick of all users then do changes which sometimes take long time.
Is there any way to go around this problem and still keep this database setup and configuration (design master and replicas).
Any sugestions.

Question by:Taras
    LVL 11

    Expert Comment

    First I would certainly say stop having users working with the design master. No exceptions.

    You also say "I want to copy design master on my hard disk make changes on the form." From this I conclude that your design master includes forms and  code and queries and whatever. This is not a good idea.  The reason I say this is that every time a change is made to the design of anything in the design master, access makes entries in a system table to which you have no edit permissions at all. This table grows and grows and your application can wind up being very large, just because of the contents of the system table.

    IMHO the solution to this problem is to only ever replicate tables.

    So what you need is a split database, where the data resides in the "Back End", and forms, code, queries etc. are in the "Front End" which is linked to the back-end tables.
    Your observed that copying the design  master from A to B turns it into a regular replica. This is correct. Whenever a design master is moved (or copied) from the location in which it was created to any other location it automatically reverts to being a plain ordinary replica.  The way to get out of this bind it to copy a replica to some location and then open it up. On the Tools/Replication menu there is an option to 'Recover Design Master'. Select this option and the new copy you made will become the design master.

    Now that you have a design  master (only containing tables) you can leave it where it is, and you will only every use it to modify the design of tables. Such table changes would then be propagated to the working replicas via a one-way synchronization (from the master to the replica).

    Author Comment

    To give you short history of development conditions.

    Original database was developed and set up as one of many company shared databases on company server folder for 5-10 company employees.
    Access and processing speed was good. Regarding constant databases move as new servers were introduced we found that splitting database to front and back end will not bring much advantage and will make maintenance more demanding.
    We did not want to constantly update (reconnecting broken -changed links).
    We have kept most of databases as one file and just make new shortcuts on user machine from new locations.
    Then company wanted to gave 2 outside salesmen access to database. Access to database would be over VPN. At that point I tested option for splitting database and trying to share data with salesman, however the seed was very slow and this option was not viable.
    So at the end I come to replication.
    I replicated database made design master and gave replicas to outside users.
    As to users side they are happy and satisfied.
    Everything looks good and smooth.

    As my side I have problem with maintenance all the time.
     As I understand you properly you suggests to copy design master e.g. A  to my hard disk and then change it from replica to design master e.g. B do the changes on B.
    Then kick of my users from A and import changes from B  to A as exclusive user of A.
    LVL 11

    Accepted Solution

    The protocol you suggest would work (kick of my users from A and import changes from B  to A), but what I recommend is that you split the database. There are lots of very good reasons for doing this, and all of them are about preventing the loss of data on the one hand, and preventing the loss of coding effort on the other hand.

    The back end which has the tables is the one that needs to be replicated. Replication is all about synchronizing. What needs to be synchronized is the data.

    The front end is just a plain vanilla application that your users can get updates to any time you want. Just get the new version of the MBD/MDE to them - more on that below.

    You say "Regarding constant databases move as new servers were introduced we found that splitting database to front and back end will not bring much advantage and will make maintenance more demanding."

    I would strongly recommend that you reconsider that decision. Having your data and code/forms in a single file is not a good idea for several reasons.
    • First it means that all users must either all use the exact same file that's sitting on the LAN on a server, or they have to use replicas.
      • The first option is bad news because Access MDB files are very touchy about network conditions and are subject to the possibility of corruption, making them unusable and non-repairable after being damaged. If someone's dodgy network card corrupts the file then all users lose the program and the data.
      • The second option is bad news because replication is a bit of a pain to manage either manually or via code. Much more painful than table linking. Replication is also not perfect (at least in MS Access land) and there will be conflicts between replicas that will need to be dealt with. The more replicas you have the greater the chance of conflicts.
    • Second it (not splitting the database) makes development of the application much more awkward.
      • You obviously cannot develop new code and forms in the production copy of the database. That would be suicide for the data - corruption is guaranteed.
      • So you need to have a development copy. Then you write new code, build queries, alter existing code, form designs, etc. etc. etc. You get all the new features running just so, and now how do you put that into production?
      • First you need to have all the users log out of the database because you cannot copy the new version of the database to the shared location otherwise.
      • Then you realize that you cannot just copy the file over to the server, because that will obliterate the production data, and replace it with all the test data you generated.
      • So now you need to build a list of all of the objects your development efforts modified and export them to the production copy (assuming everyone got the memo and is still not using the application). That needs a fair bit of book-keeping to manage, but it's not impossible because things have modification dates. Except of course that for modules the last modified date for all modules is the time that the last change was made to any module. So you'll need to keep notes of module changes.
      • OK. That takes care of the local users. What about the remote ones who use replicas? Well replication helps, but at the cost of code bloat.
    • Using a split database the development process is much simpler. Because the application is separate, you can develop in a copy  which is linked to a test back end database, protecting live data. You can then modify the front end at will and test all the new stuff for correctness, and when done you just 'publish' the new version to the LAN.
      • First you need a method of automating the linkage of tables. It is a tedious process indeed, as you know,  to use the Access menus to change the linked tables location(s). To ask a user to do anything more complicated that pointing at the right file in a file dialog is asking for trouble too. On "The Access Web" you will find all sorts of great code, and this page has all the essential code you need to change table linkages via code. It pops up a file dialog box for a user to pick the MDB file that contains the tables (the back end), and then does all the rest automatically. This code can also easily be modified to use a LOCAL table containing a list of linked tables and the default folders in which to find them. I have a version that treats linked Excel files a little differently. Using a Linked Table Table also makes it trivially easy to have *multiple* back ends attached.
      • Once you have your version of the linkage code working, all you need is a way to distribute your new front end to users. For local users that could be as simple as getting everyone out of the app. and copying the new one in place, but that's awkward. What about the user out of the office with the screen saver locking you out of their computer?
      • If you search the Access Web (and elsewhere) you'll find methods for distributing new code that relies on each user having their own copy on the front end. This is a good thing in itself. By running their own copy they cannot tread on the toes of other users: i.e. damage their front end. So with suitable code in a database startup routine, you can autmatically get the new front end to all users on demand, and the data is safely tucked away on the server.
    Sorry for the very long-winded comment, but for the sake of the data you need that split.

    Author Closing Comment

    Thanks for suggestions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now