tables and relationships

[vealeww]

I have tables used to create a consolidated table. Each time period I get new tables from a customer. I then import these to my database and get additional tables with a "1" attached. That's OK because in the past I merely delete the existing table and change the name of the new one removing the "1". I can then use my current database to execute my query's and forms etc. Problem with Access 2007 is it wants me to delete all the relationships before I rename the new tables. If I do this, I have to do the renames, and then go in the db and create the relationships again. Any ideas on how to "over write" new data and remove old data (complete tables) without deleting and recreating relationships? Thanks WWV

 

[Jeanette Cunningham]

Delete the data from the existing table.
If you have several tables involved, you will need to delete child records before the parent records.
Create an append query/ies to copy the data from the new table to the existing table.

To stay with the scenario of renaming the tables:
keep a picture of the relationships (as a jpeg, png etc) so that after you have deleted the existing tables, you can easily re-create the relationships after you replace the tables.

 

[Jeanette Cunningham]

In reply to "can you normally keep relationships after renaming tables?", the answer is no. Once you rename a table, its relationships need to be reconstructed.

 

[spikepl]

In the same way as you do not demolish a house and rebuild it from scratch, just because new lodgers are to move in, your concept is flawed. Each batch of your imported data obviously has the same structure as the previous batch.. You should get all that data into the same tables, tagged by their date/provenance/whatever that makes them different from the previous batch. It's the contents that differ, not the container.

You simply delete the contents of the tables prior to each new import, and thus retain whatever structure, relations, queries exist, for the new data.