Database design with replication in mind

Updated 2009-09-24 | Filed under Geosync | PDF

We will use an example of a Toronto company that has a special project that is currently running on an Access database. They wish to roll out this project to London and Sao Paulo. There will also be a traveling user that will have the database on their laptop. They want everyone to share information with a data consolidation once a day.

In this example the Toronto master database has an Inventory Table that is incrementing with an autonumber field. This field is set to Index: Yes (No Duplicates). It is currently at record 50. The next record entered will autonumber to 51. Upon copying the master database three times to be used by London, Sao Paulo, and the traveling user: each will install the database locally. Each will then independently add records to the Inventory Table. In every case the next record will be 51. There are now 4 new and different records all identified as record 51. Obviously any attempt to consolidate these records into one table, in a field that does not allow duplicates, will fail.

This is why it is important to set unique IDs for each copy of the master database. EQL Data helps make this easy for you. Follow these steps:

Audit your tables’ fields

In the master database, before making any copies, use Access to open the Back End file directly. Inspect each table in Design View. Scan the “Indexed” property of each field. Note the table name and field name of any field that has the “Indexed” property set to: Yes (No Duplicates) and apply the following rules.

Next, select & open the Relationships window found in the upper tool bar. Note all Table & Field names that are linked – or – make a screen shot of this. The relationships feature of Access is dependent upon the database’s initial design. Relationships are not formally designated in the relationships window in some database designs. An audit of query designs will also reveal linked fields between differing tables that will identify primary & foreign relationships. Establishing the list of table/field relationships is a critical aspect of designing a database for error free replication. But it only involves those fields with the property of indexed (no duplicates). If both sides of a relationship (the same field in two different tables) do not have the indexed (no duplicates) property it can be eliminated from this list.

Static or Active?

Decide whether the field is static or active. A common static field example is a table that lists the 12 month’s names to be used in a drop down (combo/list box) selection. This is a static list that will not have any changes out in the field and there is no risk of conflicting new records.

Primary or Foreign?

Conflict avoidance must be established in the Primary field (whether or not it is designated as a key field of the table). Any field which is used as a cross reference to its unique identical value in another table is either the Primary field (where the value is first stored) or the Foreign field (cross referencing to the primary table). There is no risk of conflicting new records in the foreign field once the Primary field is set with a unique ID. Refer back to your Audit list of relationship links. This will designate primary & foreign relationships.

Custom or Autonumber?

An active primary indexed (no duplicates) fields must be creating guaranteed unique values. If the field is NOT designated as an autonumber field then there must be a custom method at work. In general there are two custom methods: manual or automatic.

A typical example of a custom manual method is an Invoice Number. It is common that the Invoice Number is established by the accounting department, where the database user will manually enter the value supplied to them by the accounting department and it is the accounting department which controls that there are no duplicates. In a manual environment the challenge is essentially a management task to assure that the new locations cannot possibly accidentally generate the same value.

A typical custom automatic method almost always involves selecting & formatting key data found on the open data entry form at the moment of data entry for the record. The Access Form that generates the value for this field must be located in the Front End database file, and the custom VBA code that generates the unique ID must be modified. This task *8must take place during step 6 when making the copy for each location’s Front End. Each location’s Front End must have the VBA code tweaked so that the values generated at its location cannot possibly be the same as any other location.

Special mention should be made concerning time stamps. It is not uncommon to involve a time field to generate the PC’s current system time (Now()) as a custom unique value. This of course is fine for a single location but is not feasible for multiple locations as the PCs can definitely generate a record using the same momentary time value.

Autonumber

As described in our beginning example an Autonumber field with the Indexed property set to Yes (No Duplicates) is guaranteed to immediately create conflicts. This is easily resolved by implementing a unique ID per location during Step 6 when you create copies for the new locations. There are two methods to consider when coordinating an Autonumber field in a replicating environment. We have explained both methods for your consideration in the following section below. (See Autonumber Alternatives.) Wherever you have an Autonumber field with the Indexed property set to Yes (No Duplicates) you must implement one of these solutions as part of Step 6.

Autonumber Alternatives

There are two entirely differing methods to eliminate the conflict that an Autonumber field [Indexed: Yes (No Duplicates)] will create when you copy your master database for use in other locations. One method is to reset the Autonumber to a new number with each copy of the database (let’s call this ‘Reset’). The other method is to replace Autonumber with an auto sequencing method that generates differing values in each copy of the database (let’s call this ‘Replace’).

There are pros & cons with both alternatives. The decision as to which is better for you though will depend on your specific situation. In Reset you must space your numbers apart so that there never can be a duplicate. In our Toronto company example the autonumber was at value 50. If you set the London copy to begin at value 100 then of course the Toronto copy would eventually reach 100 also and you would begin causing duplicates. Obviously this can be avoided by resetting to greatly differing values. So if it is estimated that the table will never go beyond 250k records it would be ultra safe to set London at 500,000. Set Sao Paulo at 1,000,000 and the traveling user value at 1,500,000. So this first issue is easily resolved if you are confident of the maximum number of records. Implementing the Reset requires running a one line query for each autonumber field in each copy of the database. We provide instructions below.

The Replace method does not rely on Autonumber. It implements a sequencing method that will guarantee the copies never generate a duplicate value. In fact you can use alphabetical differentiators if you wish. It requires linking two new tables between Front and Back end files, and setting VBA at an event on your form that inserts the value into the table for each autonumber field. We provide instructions below. Once Replace is in place, setting the unique ID per database copy is simply opening one table and entering one unique value.

Selecting Reset vs Replace

A mature application with a lot of tables and a lot of fixed relationships has the most difficult choice. Replace must be used if determining unique (never over lapping) number ranges is not possible. But given that one can use very large spans it probably is possible. An advantage of Reset is that is does not involve the Forms at all. This could be a key consideration in a large database with a large quantity of forms. If you have 20 autonumber fields you will need to run 20 queries per copy in order to implement Reset. The Replace method also has issues for the mature large database. Replace requires inserting vba code in controls of all affected forms. A large database with 20 autonumber fields will probably have at least 20 forms that must be modified. To implement Replace into a mature application you must temporarily delete relationships, change Autonumber to Number and then reset the relationships. A large, mature database is worse case and should be considered as a candidate for the EQL Data OnWeb product that allows an Access database to become browser enabled with no modification at all.

If you have a brand new application or a modest size application then the choice is much easier. The Replace method may be preferable if you have a great number of copies to make and replicate together, because setting the unique ID per copy is simple. It is also the solution if you are unable to confidently predict number ranges that will never over lap. The Replace method allows you to differentiate database copies with an alpha prefix i.e. A1111 vs B11111 vs C11111 which is perhaps advantageous in sorting requirements. The Reset method is preferable if there is a small quantity of autonumber fields in a mature database where Replacing the autonumber will be interruptive, and overlapping numbering is easily avoided. The good news is that neither approach is difficult.

Resetting Autonumber

  1. We will presume that the Autonumber value of your master database will not change. In our example Toronto is the master database and the autonumber value is 50. In the first copy made, in our example for London, you plan to set the Autonumber value to 500,000.

  2. Open the Back End file of the London copy and begin to create a new Query in Design View.

  3. The Query Design wizard will offer you a choice of the tables to add, close this without adding any tables so that you now have a completely blank query design. With your cursor in the upper half blank space, right click and select SQL View. You will see: SELECT;

  4. Change the text to: 

    INSERT into YourTableName 
    (AutonumberFieldName)
    values (500000);

    The capitalization is not important but the parenthesizes and the ; are very important. Run the query by pushing the ! button.

    You will receive the normal warning that you are going to insert a line. Press ok and finish.

  5. Check your result by opening the table. You will find a new record with the autonumber field value of 500000. All the other fields in this new record will be blank. The next record that is created will be 500001. If you do not want a blank record in this table be sure to delete this record.

  6. In our example it is determined that spacing the autonumber ranges by 500000 will assure of never having an overlap. The master database is at

  7. London is now at 500000. The Sao Paulo copy will be set to 1,000,000 and the traveling user’s copy will be set to 1,500,000

  8. Repeat these steps for every autonumber field in the database that is set to Indexed Yes (No Duplicates).

  9. If the blank new record created at reset must be deleted, once you are confident and fluent with your autonumber reset method you can save time and avoid manually deleting the new, blank reset record by changing your query statement to: 

    DELETE from YourTableName
    where AutonumberFieldName=500000

    and run this query immediately after your INSERT query. The autonumber value will remain in memory so that the next record generated will be 500001.

Replacing Autonumber

  1. In the master database Back End file, open in Data View each table with an autonumber field to be replaced, sort your autonumber field and note the highest value for later use. Close the table and reopen the Table in Design View, change the field from ‘Autonumber’ to ‘number’. Be sure also that the default value is NOT set to 0; blank that property empty. Save the table and close. Repeat this for every Autonumber field in your database now and keep track of which is the highest numerical value. Record that value for later use. Doing this first in the master database Back End file will assure that it is in place for all copies you make.

    Note 1: you cannot change data type (i.e. from Autonumber to Number) for a table’s field that has a fixed relationship (as viewed in the Relationships window). You must first delete these relationships, make the changes to all fields/tables involved, and then remake the relationships.

    Note 2: you can alternatively change the data type from Autonumber to text, if you prefer an alpha based sequencer. But only do this if it is a newly designed database where forms and combo boxes have not yet been made. The change from autonumber to number remains a number. But the change from autonumber to text will interrupt any form, query or vba that is expecting the autonumber field to be a number type field.

  2. You have just made changes in the Back End file. Now open the Front End file and link to new special tables needed to replace AutoNumber. These tables were added to your Back End file during the EQL plug in installation. In the upper tool bar go to ‘Files’, and then to ‘External Data’ and browse to your Back End file where you will find 2 new tables: eqlKeyID and eqlKeyNumber. Highlight those both and link to them.

  3. Now that it is a linked table, doubleclick and open the table named: eqlKeyNumber You will see it is a one record table with one field that contains the value 1. Replace this value with the value that you recorded just earlier as the highest autonumber value. Save and close the table. This table must remain as having one record and just one field. You do this one time only in the master database, and do not have to repeat this in any copies of the master database.

  4. DoubleClick on the newly linked table named: eqlKeyID and open it in data view. You will see it is a table with just one record and just one field and it holds the value: 1 For your master copy leave this value as 1. For each location database copy that is to participate in replication, you will set a unique ID by changing this number to a unique value. In our example Toronto is the master database and will remain as 1. The traveling user’s database, open this table and change the value to 2. Sao Paulo will be changed to 3. London will be changed to 4. In all cases the table must remain as having one record and just one field.

    Note: if your database is new and you wish to differentiate copies with an alpha ID. Change this table’s field definition from Number to Text. Change the value found in the table from 1 to A. In our example Toronto is the master database and will remain as A. The traveling user’s database, open this table and change the value to B. Sao Paulo will be changed to C. London will be changed to D.

  5. In the Front End file, locate all the Access Forms with controls that are sourced on the autonumber fields. Although we have set a unique ID generator in the Back End file we must also ‘force’ the value into the field during the user’s data entry similar to the way that autonumber auto inserts a value into a field. Determine an Access Event in the form that is best and copy this VBA at that event (use your actual autonumber’s field name into this code): 

    If IsNull(Me.AutonumberFieldName) Then                 
    Dim KeyGet As Variant
    KeyGet = GetNextKey()
    Me.AutonumberFieldName = KeyGet
End If

You have made these changes in the master database Front End file. In our example this is the Front End for Toronto. Make copies of the Front End file for Traveling User, London and Sao Paulo. You do not have to customize the Front End per location.

Related Articles

Unrelated Articles