I heard recently from John Stevens, who uses an Access database to record departments and the people working in them.

He uses a GUID (Global Unique Identifier) field as the primary key in his tables of people and departments so he and his colleagues can enter records independently and combine their tables later without fear of primary key clashes.

The database contains two tables – one called Depts for departmental information and one called People. There is a one-to-many relationship between Depts and People: many people can work in one department and each person can only work in one department at a time.

John took copies of the two tables by copying the structure only (by highlighting the table name, clicking the Copy button and the Paste button on the menu bar, and choosing the Structure Only option). He copied the records from the originals into the new tables by copying and pasting.

He then tried to establish the one-to-many join between the new tables, but Access reported that data in the copy of the People table, called PeopleCopy, “violates referential integrity rules”.

When is a copy not a copy?
The data should be a direct copy of each table. The screenshot (above left) shows the original Depts table and the copy, DeptsCopy. The copying process has failed to work in the GUID field called DeptID. All the GUIDs, which are acting as primary key values, have been changed, thus violating the referential integrity constraints.

Here’s a little background on GUID fields. In Access, the data type Number can have various field size settings, one of which is Replication ID. This field size can also be used with the Autonumber data type. A Replication ID is Access’ equivalent of a GUID and is designed to hold a string with a high probability of being unique. They are mostly used as primary key fields, where several people are entering records into different copies of a database. The guarantee of uniqueness means that when the copies are brought together, problems caused by records having the same primary key are unlikely to occur.

When designing a table that uses GUIDs, most users choose the Autonumber/Replication ID setting. This lets Access generate the IDs automatically: they’re made up from blocks of mixed letters and numbers, separated by dashes and wrapped in curly brackets. They do the job, but they’re a nightmare viewed from a human angle because they don’t have any instantly recognisable meaning.

Their unwieldiness also extends to their behaviour, which can vary with the different copying processes that can be used with the autonumber version. John’s problem is a good example: copying rows using cut and paste from one table to another causes the GUIDs to be regenerated. In this case the behaviour is exactly what John didn’t want, but we could argue that it is reasonable because this is not only a GUID, it is also an autonumber. The job of an autonumber field is to generate new, unique numbers, so that is what it is doing. This sounds reasonable at first sight, but it unravels after a little more experimentation.

If you repeat the experiment but use an Append query to add rows from the original table to the new one (copied as the structure only), the GUIDs are transferred in perfect condition. And if you create a new table by copying both the structure and the data from the original table, the GUIDs are again copied across without alteration, even though the new field is still an autonumber.