Appending Data to related tables with RI

[staceyd]

I am having a problem appending data into a table that has the structure and referential integrity already set up. The trouble is that I am getting an error for about 70% of the records due to validation rule violations. However this data is coming from two tables with an identical relationship set up in identical fields.

Here is the background. I am rebuilding and modifying an Access 97 database into Access 2000. I have built most of the new tables and queries and I am now trying to import the data from two related tables with referential integrity to two new tables with the same one to many relationship with referential integrity. The two tables are holding infromation on Institutions and Contacts. These tables have each have an AutoNumber primary keys; Intcode and Contcode respectively. And of course the contacts table contains Intcode field as a number long integer.

After I imported the tables from 97 into 2000, I modified the tables by deleting some fields and all their data (without changing the any primary keys). Then I appended the data into the 2000 structure that has all the queries and forms based off of it. The Institution table appended with no problem. However, the Contact tables shows that 70% of the records can't import because of validation rule violations.

I would really appreciate any suggestions you may have!

 

[CJBIRKIN]

Hello
I had a similar problem when i converted a 97 Db to 2000.

For some reason some of the fields in a table ended up with the duplicates priority set to No. I had cleaned out one table before the change and intended to to an append to it afterwards.

The data i was appending had valid duplicate entries but as the field i was trying to append to wouldn't allow them the query rejected most of the records. I don't know if this is any help, and i've got no other ideas (sorry i'm not that bright).

Chris

 

[Pat Hartman]

Check all the text fields in the "new" tables. Any of them with the Allow zero length strings property set to No could be causing the problem if any fields that you are importing are null.

 

[staceyd]

Thanks you so much for your help.

I changed all the text fields to Allow Zero Length to yes. Then I did a side by side comparison and found that one field was null in some cases and also a required field. Once I updated all the requirements, the append query worked!

Thanks Again!