Replace Primary Key Violations 1

[IanGlinka]

I have a table in my database which will constantly be updated from downloads from a hospital. These downloads work in a similar way to the archive bit does with backups. It seems whenever there is activity in a patient's record, his/her record is tagged for export for the consulting companies... The problem I have is, I already have some of their information in my databse... so when I try to import the 'updated' information, I get a key violation and that's the end of it.

My question - Is there a way to tell Access to REPLACE the record causing the violation with the new one that you are importing?

The only way I can think to do this right now is to import the new data, then try to append my old table to that new imported one, so the key violation keeps out the old data.

Any better way to do this?

Thanks
Ian

 

[KenReay]

Hi

If you do not already, import the data into a seperate table.

Then you need to either write code to read through the incomming data and depetermine if an update or insert is required -

or

Make an append query which will add new records

An update query which will update existing records and run then boh (update first)

To make the append query make a query joining the 'new' table with the 'old' table and right click the join, change the property to show all from New Table, and put a criteria on the query to select only those where prime key of 'old' table is null (ie only those present in the new table but not the old, convert this query to and append query

For the update query, make query joining old and new on prime key, but with a normal join (ie only matches from both tables), and convert it into an update query Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK