Append Query that won't add any records

[Guest]

I have a simple append query to append a ProviderID and an OfficeID to a
table. In the desired table both of these data fields are primary keys and
linked to a Provider table via the ProviderID and and Office Table via the
OfficeID.

I think the relationship is causing the problem since I designed another
table with ProviderID and OfficeID and appended the records agaiin and it
worked.

If it is the relationships that are causing the problem what is the work
around? I have tons of referential relationships.

 

[Roger Carlson]

Actually, you've got this backwards. The relationships are not the problem.
They are preventing you from creating a problem, that is, orphaned records.
You have to be sure you have the corresponding OfficeID and ProviderIDs in
their respective tables before you can add data to the linking table. Then
your append query will work properly.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

I have a simple append query to append a ProviderID and an OfficeID to a
table. In the desired table both of these data fields are primary keys and
linked to a Provider table via the ProviderID and and Office Table via the
OfficeID.

I think the relationship is causing the problem since I designed another
table with ProviderID and OfficeID and appended the records agaiin and it
worked.

If it is the relationships that are causing the problem what is the work
around? I have tons of referential relationships.

It might help us if you would post the SQL of the query and the actual
error message.

All I can suggest is "ensure that your append query populates all
required fields and doesn't violate referential integrity". Knowing so
little about your database and the data, I can't suggest just how you
can do so!

John W. Vinson[MVP]

 

[Guest]

Yes I figured that would be a problem so I imported ProviderID's in the
Provider table and OfficeID's in the Office table before I tried the append
query to import in a separate table containing the ProviderId and the
OfficeID.

I realized that the ProviderID and OfficeID had to exist before I tried the
append.

When I go to the form that I created to add the ProviderID and OfficeID to
the table I am trying to append to, it works. So why won't the append query
work?

 

[Guest]

Is it because I have many cascading relationships from the Provider and
Office tables?