Compare and action table against new recordset

  • Thread starter Thread starter basilbrush
  • Start date Start date
B

basilbrush

I have a database of various related tables.

On a regular basis I will be provided with an updated set of primary
keys for one of my main tables. What I want to be able to do is to use this
to update my table by (i) Deleting records where the key is not in the new
set and (ii) adding a 'blank' record for each new key in the updated set.
Ideally, I would want all records in other tables using one of the deleted
keys (as a foreign key) to also be deleted.

I've been racking my brains but I can't seem to think of a 'slick'
solution for this seemingly simple task.

As a compromise, I could reduce my database to just one table then I
won't have to worry about referential integrity issues but I'm still not
sure on the best approach.

Any advice or pointers in the right direction will be very much
appreciated.

Thanks.

Basil.
 
I have a database of various related tables.

On a regular basis I will be provided with an updated set of primary
keys for one of my main tables. What I want to be able to do is to use this
to update my table by (i) Deleting records where the key is not in the new
set and (ii) adding a 'blank' record for each new key in the updated set.
Ideally, I would want all records in other tables using one of the deleted
keys (as a foreign key) to also be deleted.

I've been racking my brains but I can't seem to think of a 'slick'
solution for this seemingly simple task.

As a compromise, I could reduce my database to just one table then I
won't have to worry about referential integrity issues but I'm still not
sure on the best approach.

Any advice or pointers in the right direction will be very much
appreciated.

Thanks.

Basil.
Click to expand...
Hi Basil,

Deleting the records:

Create a query joining the New table with the Existing table. The arrow
should be pointing to the New table. In the criteria field for the New
Key, specify Null. This is essentially a "missing records" query,
returning Existing records that do not match New records. Test this to
make sure it works, then switch it to a delete query.

To clean up the child records of the Existing table, use Cascade Delete
in the Relationship properties.

Adding "blank" records:

Create a query joining the New table with the Existing table. The arrow
should be pointing to the Existing table. In the criteria field for the
Existing Key, specify Null. This is a "missing records" query,
returning New records that do not match Existing records. Test this to
make sure it works, then switch it to an Append query. Specify the
Existing table as the table to Append in to, and set the "Update To"
field to the name of the key field from the New table.

Hope this enough to get you going.
 
Thank you for these most clear and concise instructions. They work very
well. All I've now got to do is automate things a little...
Automating the queries is straight forward enough. What I didn't mention is
that my updated set of primary keys is in Excel format. Do you by chance
know how to replicate the "File,External Data, Import" menu option in VBA?

Thanks

Basil
 
Back
Top