Referential Integrity in SQL server not working as expected in Access

[Gaztry80]

Hi,

I am having some trouble with Referential Integrity, hopefully somebody can help me in the right direction.
I have two tables, 1.) tblA with products and 2.) tblB with the corresponding demand of the product. The two tables are linked together with ProductId column.
My goal is to have a query with column Product from tblA and Demand from tblB and when I fill in a new product and/or demand, TblB is also automatically filled with ProductID. With local tables, i have managed this with Referential Integrity, please see the attached file and "testqry". However, my real database consists of SQL server tables and I am using SMSS. Unfortanetely, I am not able to reproduce the Referential integrity in SQL server. So when I make the same database with the linked tables, I get the error "Cannot add a record. The primary key of dbo_tblB does not exist in the recordset". I have used the below following settings in the relationship settings.
Does somebody know what is causing the problem? I have tried different settings for the Delete / Update rule, but i am unable to get it working properly.

Thank you!

 

[arnelgp]

I don't think that is a Normal behaviour (adding a PK to tblA will also add FK record to tblB?).
but it works in my A2021!
testing further on old access (2007) it did not add record to tblB and complains that i cannot
add quantity since the Foreign key is not created in tblB.
And this is consistent with mssql server.
Another bug i supposed was discovered!

 

[Gaztry80]

I don't think that is a Normal behaviour (adding a PK to tblA will also add FK record to tblB?).
but it works in my A2021!
testing further on old access (2007) it did not add record to tblB and complains that i cannot
add quantity since the Foreign key is not created in tblB.
And this is consistent with mssql server.
Another bug i supposed was discovered!

So, if I understand you correctly. I have made something with local tables and used without releasing it a bug to reach my goal. Now I want to implement it in SQL and therefore it is not possible, because there the bug does not exist.. haha

 

[Minty]

I suspect this something to do with how the Autonumber works.
From memory (so dodgy), Access gets the new number immediately, whereas SQL server only assigns it once/as the record is written.

 

[arnelgp]

So, if I understand you correctly. I have made something with local tables and used without releasing it a bug to reach my goal. Now I want to implement it in SQL and therefore it is not possible, because there the bug does not exist..

as i have said before this is a bug.
previous version of msa does not allow you to Automatically create new record
on Foreign table, when you add record to Primary table.
I confirmed this by re-creating your table / relationship and query in A2007.

 

[GPGeorge]

So, if I understand you correctly. I have made something with local tables and used without releasing it a bug to reach my goal. Now I want to implement it in SQL and therefore it is not possible, because there the bug does not exist.. haha

Not so fast, I cannot replicate this in MS 365.

Product "G" was added directly in the table. Product "H" was added in your query. Neither generated a corresponding Foreign Key in the Demand table, TableB.

Unfortunately, I do not have Access 2021 installed so can't test it in that version.

Please explain, step by step, exactly how you performed this "automatic addition of the FK" using only this query. Obviously, in a main form/subform interface, using Parent/Child Linking Fields, that would happen. I can't see it happening, though, either via direct entry into the one-side table nor via the test query in the supplied accdb.

 

[GPGeorge]

I suspect this something to do with how the Autonumber works.
From memory (so dodgy), Access gets the new number immediately, whereas SQL server only assigns it once/as the record is written.

That is correct. Access generates an AutoNumber when a new record is started, and before it is saved to the table. SQL Server generates a new Identity value when the new record is saved to the table. I have seen VBA code, in fact, where relying on Access default behavior broke when the tables were migrated to SQL Server for this exact reason. There is no value available until after the one-side table has been saved. VBA which relied on it being available sooner than that breaks.

That said, I doubt that's a factor here.

 

[GPGeorge]

Hi,

I am having some trouble with Referential Integrity, hopefully somebody can help me in the right direction.
I have two tables, 1.) tblA with products and 2.) tblB with the corresponding demand of the product. The two tables are linked together with ProductId column.
My goal is to have a query with column Product from tblA and Demand from tblB and when I fill in a new product and/or demand, TblB is also automatically filled with ProductID. With local tables, i have managed this with Referential Integrity, please see the attached file and "testqry". However, my real database consists of SQL server tables and I am using SMSS. Unfortanetely, I am not able to reproduce the Referential integrity in SQL server. So when I make the same database with the linked tables, I get the error "Cannot add a record. The primary key of dbo_tblB does not exist in the recordset". I have used the below following settings in the relationship settings.
Does somebody know what is causing the problem? I have tried different settings for the Delete / Update rule, but i am unable to get it working properly.

View attachment 103051

Thank you!

I suspect that what REALLY happened is that you added records to both sides of the relationship in your query, didn't you?

Here is a second screen shot showing how that can happen.

If you add a new Product name in this query AND add a new demand value into the same record at the same time, then Access does have the ability to save the two records at the same time. This generates the Foreign Key value automatically for tableB.

It also means Minty was right and I was wrong about the default behavior of Access -- creating the new AutoNumber value immediately -- is a factor.