Referential Integrity – Import of Data from a purchased package

[Brad]

We are building an Access application which is going to interface with a
purchased accounting system. This purchased system will be the source for
our “Part Info†(part number and part description). The only way to extract
info from this purchased system is to export all part information via a CSV
file which can be imported into our new Access system. This all works
nicely, except when it comes to referential integrity.

We would like to enforce RI between the Parts Table and our Orders table in
the new Access system. (The Part Number in the Orders Table is a foreign key
to connect to the Part Number (primary key) in the Parts Table)

Is there an easy way in which we can replace the entire contents of the
Parts Table daily and still maintain the RI between the Parts Table and the
Orders table?

I would venture a guess that others have run into similar issues as they
have tried to build an interface between an older system and a new Access
system.

Thanks in advance for your ideas.

Brad

 

[Joan Wild]

You empty and populate your Parts table via a Delete and Append query.

Import the csv to a separate table and then run the queries. This won't
affect the existing table relationships.

However, you may run into errors, if the new import doesn't include a
previous part that exists in your order table.

Joan Wild
MS Access MVP

 

[Brad]

Joan,

Thanks for your thoughts.

I am still a bit confused.

If I have RI between the Orders table and the Parts table and I try to
delete all of the Parts via a delete query, won't I run into a conflict
between wanting to delete all the parts and the enforcement of RI?

Brad

 

[Douglas J. Steele]

I'd suggest importing the CSV file into a separate table, then running
queries to check whether the new file is going to cause any RI problems.

If not, then you can run a single query to update existing records or add
new ones to the "real" table (see my November, 2003 "Access Answers" column
in Pinnacle Publication's "Smart Access". You can download the column, and
sample database, for free at
http://www.accessmvp.com/djsteele/SmartAccess.html ), and another query to
delete obsolete records.

 

[tina]

my question is: why do you want to delete all the Parts records? wouldn't
it serve the same purpose to dump the file data into a temp table, use an
Unmatched query as an Append query to add any parts records that are not
already in the table, run an Update query to update the existing Parts
records with the current file data, and perhaps another Unmatched query as
an Update query to flag Parts records not included in the file data?

hth

 

[Joan Wild]

Yes you would; sorry, I didn't really think it through. Instead of
deleting the existing records, just run the append query of new parts.
The append will generate an error about duplicates; just run it anyway,
it won't actually append the duplicates.

Joan Wild
MS Access MVP

 

[Brad]

Tina, Joan, Douglas,

You folks are GREAT!

I appreciate your ideas on this issue.

Seeing how today is St. Patrick's Day - I owe you all a green beer

Brad