How to Capture Referential Integrity Error on Import?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I have a multi-tiered user organization spread across the
globe that I developed an Access database to capture their
data.

I designed the database using 3NF and included the
relationships to enforce referential integrity.

Each tier must export their data to next level up, and so
I used the TransferText method, and an Outlook Object to e-
mail the data to accomplish this.

However, it happens frequently that data is not acquired
until later, so that the lower level will send their data
more than once. At which point, the next level user can
not import as this violates the referential integrity rule
of no duplicate keys.

Is there a way to capture this error (and other
referential integrity type errors)?

I haven't been able to figure out how to capture this, so
I thought of this other possible solution.

1) Import all data into a new table
2) Merge data with some queries

Any other solutions or comments would be appreciated.

-Michael
 
Michael,

The problem is with duplicates. Try importing to a temp
table then linking on the ID. If the resulting recordset
is >0 then exit the import.

2nd idea depends on the number of records involved but
you could do a dlookup on an ID to see if it exists.
Since the records are coming in in a batch, finding one
ID would enable you to identify and stop the importing.

David S.
 
Back
Top