Overwrite existing data

[daver676]

I have a macro that imports a spreadsheet into a table.
The problem is the new import information doesn't
overwrite the old, it just combines it all, so there are
many duplicate records. How can I tell Access to
overwrite, or delete all the old info before it imports
the new info? Thanks

Dave

 

[Eric Butts]

Hi,

Recommend you do the following:

- Create a temporary table that stores the information from your Spreadsheet

- Create a Delete Query that deletes all records from the above temporary
table

- Create an Update Query that updates the records in your Final table with
the data from the above temporary table

- Create an Append Query that appends all the records from the Temporary
table into the Final table

- Create a Primary key value in your Final table (so that duplicates are
not added to table)

- Then create a macro that performs the following actions in this order
1. Run Delete Query to delete all records
from the temporary table
2. Import records from Spreadsheet into
Temporary table
3. Run Update Query to update records in
Final table from records in Temporary table
4. Run Append Query to append all records
from Temporary table to Final table

Note: the only records that will be appended to Final table are the new
records

Related knowledge base article: 208870 ACC2000: How to Update and Append
Records in One Update Query http://support.microsoft.com/?id=208870


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support