Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Referential Integrity issue doing an Insert to SQL

Status
Not open for further replies.

sts125

Programmer
Oct 19, 2007
5
US
I am trying to insert records into a Microsoft Dynamics database (SQL). I believe there is Referencial Integrity set up between two files.

I load the SQL Insert statement for one of the 2 tables then issue:

DoCmd.RunSQL sqlInsert (sqlInsert contains my insert statement)

I get "Microsoft Office Access ...and it didn't add 1 record(s) to the table due to key violations"

What is the best approach when inserting into 2 tables that have Referencial Integrity set?

I can go into SQL 2005 and insert a record without any kind of error? I am using Access 2007.

Thanks!
 
I also forgot to mention, both these tables have an autonumber field.
 
The answer is pretty simple, you need to look at what the relationship is between your two tables in Access. Do you have other relationships that you are not factoring in?

You are probably attempting to insert a row in one table where your foreign key does not match a primary key (other field?).

Hope that helps,
Gary
gwinn7
 
As gwinn7 said...

...You may need to add your Parent record first, obtain it's primary key, then use that value, for the foreign key.
 
How are ya sts125 . . .

You need the primarykeys of the records appended to the parent, appended as well with those records to the Child!

There's no way to do this unless its on a single record basis, where you can hold the parent PK for appending with the child!

Hope this makes sense! . . .

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top