-1

I am working on appending some additional data from an Excel spreadsheet to an existing Microsoft Access database. The primary key is "RecordID", however, when I go to create the one-to-one relationship between a current table and the new table with enforced referential integrity, I am receiving the error message, "Microsoft Access can't create this relationship and enforce referential integrity". The weird thing is I went through and compared every single RecordID between the two tables, and they match 100%.

I then ran a "Find Unmatched Query" to compare the two tables' RecordIDs, and 5 RecordIDs appeared. However, they LOOK like they exist in both tables. For example, both table 1 and table 2 contain "ST101_1", but "ST101_1" was listed in the unmatched query.

Even stranger, I then opened table 1 (the master table), and for those records listed in the unmatched query, I tried to add a duplicate record. It shouldn't let me do this because "RecordID" is the primary key. However, again, for those records listed in the unmatched query, it allowed me to enter a duplicate value. For example, under "RecordID", I typed "ST101_1" and then added a new record with "RecordID", "ST101_1", and it did not give me an error message that I am entering a duplicate value!

Any input is very much appreciated - this is driving me crazy!

Relationships (the newly imported table is tblFreesurfer, which I want to connect to tblRecordID with enforced referential integrity) 1 Result of unmatched query between tblRecordID and tblFreesurfer 2 Image of my front-end, and database allowing me to enter what looks like the same RecordID 3

CeenCat
  • 1
  • 1
  • Put everything needed to ask your question in your post. Use text for anything that can be expressed as text--not just a link or image. Use images only for what cannot be expressed as text or to augment text. [mre] [ask] [help] – philipxy Mar 13 '20 at 20:35

1 Answers1

0

You have to post your table desings and relationships.

However, some unmatched query is just that - a query. Such a query can operate on any table.

You have to pick + assume that you have a master table. Of course you can add lots and lots of new records without having a record in the child table. (because you can't add two records at once!!!).

However, if the PK of this main table is in fact set as a PK, then you cannot enter the same PK value more then one time. If this is the case, then I suspect that the data being imported has trailing blanks, as this is not possible. Either your setup is wrong, or the imported data has trailing blanks. I would bring up two records with the SAME pk, and place my cursor in one of the columns, and hit f2 - you see the value in the field highlight, and you see extra spaces. While editing from Access you can't enter trailing blanks, you can with code, or during a import.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • I just added some images, please let me know if anything else can help. I will check for trailing blanks and let you know if this resolves the issue. Thank you! – CeenCat Mar 13 '20 at 17:33