Replication ID Foreign Key
By Richard Rost
3 years ago
Replication ID as a Foreign Key in Microsoft Access
In this Microsoft Access tutorial I'm going to teach you how to use a Replication ID as a foreign key in your database.
Pre-Requisite
Links
Recommended Courses
VBA Code
DoCmd.OpenForm "CustomerF",,,"CustomerID=" & StringFromGUID(CustomerID)
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, stringfromguid, guidfromstring, replication id, foreign key, openform
Intro In this video, I will show you how to use a replication ID (GUID) as a foreign key in your Microsoft Access database. We'll walk through the steps to change your primary key and related foreign key fields to use replication IDs, update relationships, and modify your VBA code to handle these GUID values correctly. You'll also learn about common error messages and how to convert GUIDs to strings in Access so your queries and forms work as expected. This tutorial is perfect for anyone needing to support offline changes and merging data without duplication in Access.Transcript In today's video, I'm going to teach you how to use a replication ID as a foreign key in your Microsoft Access database.
A couple of weeks ago, I released a video teaching you what replication IDs are and when you should never use one. Basically, if you're going to be taking a database and splitting it, and maybe taking a copy on the road, or other workers are going to have it and you're not all connected together, they are going to go off and type some records in and come back, and you want to merge those together. Well, that's what replication IDs are for. Watch that video for more information.
It is easy to do if you only have one table that you are dealing with. You just set the primary key as a replication ID and that's it. Access pretty much handles the rest of it and you're not going to get any duplicate values. But if you're going to relate that to other tables, for example, if your customer ID is also a foreign key in your contact table, you need a little bit more work.
I got tons of emails from people and some comments on the YouTube channel, so let me show you how to handle that.
Here I am in the TechHelp free template. This is a free database. You can download a copy on my website if you want to.
Let's open up the customer table and let's change this customer ID to a replication ID. Go to Design View. Here is the primary key. We are going to change this from long integer to replication ID, and the first thing it does is yell at you. It says you can't change a data type or the field size of this field if it is part of one or more relationships.
The first thing we have to do is remove that relationship. Go to Database Tools, Relationships. Here is the relationship right here. We are going to delete it. That is fine. It doesn't change any of the data. It just deletes the relationship itself. It removes the referential integrity. We can set it back up when we're done.
Save it and close it. Now we should be able to come in here and change this to a replication ID. It asks if you are sure you want to do it and warns that you can't go back once you do it. Are you sure? Yes.
Save it, preview it, and there we go. Those are all replication IDs now.
Now, what we are also going to have to do is wherever this customer ID exists in other tables, we also have to change those. For example, in our contact table, go to Design View. There is the customer ID. Leave it as a Number, but again, under Field Size, change Long Integer to Replication ID.
Save it, preview it, and there you go. Those numbers should all match, by the way. Once you switch them over, it is basically a 16-byte hexadecimal value, and if you just want to spot-check a few of them, you can.
For example, I am Richard Rost right here. I am 0001, and that should mean these two right here. Yes, James Kirk is the next one. Everything checks out. When the numbers convert, they'll all convert the same. That's fine.
You will do the same thing in your order table as well. In OrderT, open that up. There it is. The relationship here is to the order detail table, so you should be able to change this over to a replication ID. Remove that relationship, change the field, and that should also be good now.
Now, here's the weird part. You are also going to have to change your VB code because this no longer works. Nor does it work if you go to the order form. It says "Syntax error in query expression customerID equals blob." What is that blob?
The default GUID and the replication ID are values that can't be handled this way normally. You have to convert them to a string in order to use them in your VBA code.
I like to put it in its own variable. So, Dim s As String. Then set s = StringFromGUID([CustomerID]). That is a special function you use, called StringFromGUID. That is part of Access, and then whatever your field is - in this case, CustomerID. That is going to take that GUID and convert it to a string that you can now use in your expressions. And that's it.
You have to do the same thing down here. You could do the whole thing right here, and for the order button, you can just replace that with that, and it should work fine, but this is the better way to do it. I have to show you the right way to do it and then the shortcut.
Give it a quick debug compile, close it, save it, open it. Ready? Contacts, done. There they go. Orders, done.
The trick is that in order to use that GUID replication ID in your VB code, you have to convert it to a string first. That's it. It is that simple.
There is one to go backwards the other way, called GUIDFromString. If you need to save that value in your code as well, you can use that.
I know a lot of different programs and services are starting to use GUIDs more frequently now. It's going to help you out to learn this stuff. But if you're just planning on building a replicated copy of your database like this, you can also use an AutoNumber that is random.
Again, I cover that in my Synchromote Databases TechHelp video. We use a random AutoNumber instead of a GUID. Now, with random AutoNumbers, there is still more of a likelihood that you're going to hit a duplicate value, but it is still almost astronomical. I mean, with GUIDs, it should never happen. Random AutoNumbers might happen once in a century, but it's fine. You don't have to reprogram the rest of your database. You don't have to convert the strings and all that stuff.
Random AutoNumbers are easier to use, but this should show you how to work with the GUID.
There you go, developers. There is your fast tip for today. I hope this helps. I hope you learned something. Live long and prosper, my friends, and I'll see you next time.Quiz Q1. What is the main purpose of using a replication ID (GUID) in a Microsoft Access database?
A. To ensure unique values when merging data from multiple disconnected sources
B. To improve performance on single-table queries
C. To shorten the data entry process
D. To increase the maximum size of your database
Q2. Before changing a field involved in a relationship to a replication ID, what must you do first?
A. Delete all records in the table
B. Remove the relationship in the Relationships window
C. Convert the table to a different format
D. Change the primary key to AutoNumber
Q3. When converting a primary key to a replication ID, what happens to existing data?
A. All existing data is deleted
B. Existing values are converted to GUIDs and remain linked
C. Only new records get GUIDs; old records keep old IDs
D. The data type cannot be changed if data exists
Q4. If you use a replication ID as a primary key in one table, what must you do to related foreign key fields in other tables?
A. Leave them as Long Integer
B. Change their field size to Replication ID
C. Change them to Text
D. No change is needed
Q5. Why does VBA code that worked with standard Long Integer keys fail with replication IDs?
A. Replication IDs are not compatible with VBA
B. Replication IDs require conversion to a string for use in expressions
C. Access does not allow foreign keys in VBA
D. VBA cannot handle database relationships
Q6. Which function should you use in VBA to convert a GUID to a string?
A. CStr
B. StringFromGUID
C. GUIDString
D. ToString
Q7. After converting fields and relationships to use replication IDs, how do you use those IDs in queries or code?
A. Use them directly as numbers
B. Reference them as binary objects
C. Convert them to strings first
D. Convert them to dates first
Q8. What is the main advantage of using a random AutoNumber as a primary key in a replicated Access database?
A. It is more secure than a GUID
B. It is easier to implement and does not require code changes
C. It guarantees absolutely no duplicates
D. It is required for split databases
Q9. What is a potential downside of using random AutoNumbers instead of GUIDs in a replicated environment?
A. The database becomes read-only
B. Duplicates are still possible, although rare
C. Replication is not supported at all
D. Only text fields can be used as foreign keys
Q10. If you have a string version of a GUID in your VBA code and need to convert it back to a GUID, which function should you use?
A. ToGUID
B. GUIDFromString
C. CastToGUID
D. GUIDString
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone explains how to use a replication ID as a foreign key in a Microsoft Access database.
Not long ago, I covered what replication IDs are and under what circumstances you should avoid them. To recap, replication IDs are especially useful if you plan to split your database and have multiple users working independently—perhaps while traveling or working offline—before merging those records back into one main database. If you want more background on replication IDs and their purposes, you can find that information in my previous tutorial.
If your database design involves just a single table, using a replication ID as a primary key is straightforward. You simply set that field's data type to replication ID and Access takes care of handling unique values for you. However, things get a bit more complicated once you need to use that replication ID as a foreign key in related tables. For example, if your customer table's primary key is a replication ID, and you want to use that in your contact table as a foreign key, you have to do a bit of extra work.
I received quite a bit of feedback and questions about this scenario, so I want to walk you through the correct process.
Starting with the sample TechHelp free template database, let's take a look at how to convert the customer ID field in the customer table to a replication ID. First, you need to open the customer table in Design View. Locate the primary key field and try to change its data type from Long Integer to Replication ID. At this point, Access will tell you that you cannot change the data type if this field is involved in one or more relationships.
To move forward, you need to delete any relationships that involve this field. Open the Relationships window from the Database Tools menu, find the relationship linked to this customer ID, and delete it. Removing the relationship will not affect your data—it just removes the defined referential integrity for now. You can always recreate the relationship later.
Once the relationship is removed, you can successfully change the customer ID field type to Replication ID. You will get a warning from Access that this change is permanent. If you are sure, proceed, and your numbers will now be replaced by unique replication IDs.
Now, anywhere else that this customer ID is being used as a foreign key, you also need to update those fields to Replication ID. For instance, in your contact table, open it in Design View, find the customer ID field, and change the Field Size from Long Integer to Replication ID. Save your changes and double-check that the values across related tables still line up correctly. The conversion should automatically synchronize the IDs so that relationships remain intact. You can spot-check a few records to make sure everything transferred cleanly.
You will need to repeat this process for any other tables where customer ID is used as a foreign key, like the order table. Remove any relationships first, change the field type, save your work, and then re-establish the relationships as needed.
There is also an important adjustment required in your VBA code. After changing the field types to Replication ID, you will run into errors if you try to use those fields in code as you did previously. If you attempt to set a form filter or build a query using a replication ID directly, Access will generate a syntax error mentioning a 'blob' data type. This is because GUIDs (which are what replication IDs actually are) are a special binary type that can't be compared directly in VBA expressions.
To correct this, you must convert the replication ID (the GUID) into a string before you use it in your code. There is a built-in function in Access called StringFromGUID that handles this conversion. It is best practice to store the string value in a variable before using it in VBA expressions. For example, you can create a variable, convert the replication ID field with StringFromGUID, and then use that variable in your queries and code. This solves the problem and allows your code to interact with the data correctly.
You may need to update each area of your application where the replication ID is referenced in code. Replace any direct use of the GUID with the string version obtained from the conversion function. If you need to go the other direction, converting a string back into a GUID, Access offers another function named GUIDFromString.
More and more platforms are starting to use GUIDs, so gaining comfort with them is bound to be helpful. However, if you are only aiming to replicate your database for use across multiple users or in remote scenarios, you might also consider using random AutoNumbers as an alternative primary key. Random AutoNumbers are easier to use and do not require all the extra programming around string conversion. Still, there is a slightly higher possibility—albeit very remote—of generating a duplicate value, unlike with GUIDs where it theoretically should never happen.
If you want to see an example involving random AutoNumbers, make sure to check my Synchromote Databases TechHelp video, where I discuss how that approach works.
In summary, if you plan to use replication IDs as foreign keys in related tables, be sure to update the data type in every table and adjust your VBA code to handle the data appropriately. This is the correct method for maintaining data integrity and ensuring your relationships work as intended in a replicated database setup.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Changing a primary key field to replication ID in Access
Removing relationships before changing field types
Updating foreign key fields to replication ID
Handling replication IDs in related tables
Using StringFromGUID function in VBA
Using GUIDFromString function in VBA
Converting GUIDs to strings for use in VBA code
Adjusting VBA code for replication ID fields
Spot-checking converted replication IDs
Considerations of using random AutoNumber vs GUIDs |
