Referential Integrity in SQL server not working as expected in Access (1 Viewer)

[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.

 

[Pat Hartman]

Jet/ACE - In a query that joins tblA and tblB, where the query contains the FK from tblB and at least one other column -- when you add a row to tblA - nothing happens to tblB UNLESS you also modify at least one column in tblB (not the FK). Then you will see the FK automatically populate.

SQL Server - I don't believe you can insert rows into both tables at the same time but try it. Newer versions of SQL server may allow it. Just remember, you ALWAYS need to update at least one column in tblB to propagate the FK. A new row will NEVER be added to tblB automatically. You MUST update some column in tblB to cause the FK to fill. When you do this in SQL Server, you may not see the FK fill automagically the way you do with the same query in Access but the two table insert may work after you save the record by moving to the next row.