Forum Discussion
Solved
#Deleted issue with GUID
Hi all,
I'm working on a project with linked tables in SQL Server. Primary Keys of the tables are Guid for sync pruposes.
When I'm trying to make a form and a subform with GUID keys, parent and child, I get '#deleted' records when I inserting a new record.
I think the issue is comming from GUID as far as with integer keys, system is working fine.
I'm using ODBC Driver SQL Server 17, latest version of Microsoft Access for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20070) 64 bits
Thanks for your help
Working with GUIDs is one way to achieve your goal, but it is a more complex method than relying on Integers set to Identity to autogenerate new values for a Primary Key.
You need to ensure that your method for adding new records includes creating a new GUID for the PK in each table before trying to save that record. As Arnel said, using GUIDs is not a common approach, so finding an example of that might require a more extensive search.
Also, you must be sure that Access recognizes this Primary Key field as the Primary Key field when linking those tables. When a table has an integer with Identity Specification set, Access identifies it as the PK, but in this case, it might not do so. You'll have to be sure you designate it when creating the links so that Access can use it in forms and subforms, etc.
5 Replies
Working with GUIDs is one way to achieve your goal, but it is a more complex method than relying on Integers set to Identity to autogenerate new values for a Primary Key.
You need to ensure that your method for adding new records includes creating a new GUID for the PK in each table before trying to save that record. As Arnel said, using GUIDs is not a common approach, so finding an example of that might require a more extensive search.
Also, you must be sure that Access recognizes this Primary Key field as the Primary Key field when linking those tables. When a table has an integer with Identity Specification set, Access identifies it as the PK, but in this case, it might not do so. You'll have to be sure you designate it when creating the links so that Access can use it in forms and subforms, etc.
Thank you George,
I have added a Guid as default value in my form and subform and it corrects the issue. Thank you very much.
Do you think working with a PK based on 2 columns, one text and one integer/Identity specification could be an option ? When data is consolidated, I may have the same numbers in the integer PK column, but still have a unique PK with text+int columns.- I wouldn't do that, no. I would keep the two columns and make them unique, but I would only use them for synching data, not as a PK. However, I'd have to study the whole process more to feel comfortable making that decision.
- you can try and use simple Long Integer for your PK.
Rarely anybody uses GUID (unless you know what you are doing and its purpose).Thank you for your reply,
My goal is a off-line application. For example, 10 off-line apps will consolidate on the same database, depending on when they are able to connect to internet. The idea is not to have the same PK for differents records from different apps. Would you have a suggestion ?
