Appending to Access DB from Excel

Shanan212
Shanan212 used Ask the Experts™
on
Hi,

I want expert suggestion on this.

I have an excel db with unique ID.

I want to create and maintain an Access DB based on this unique ID.

The excel file I get everyweek contains previous 3 weeks of data. So every week, when I append to Access db, I have to remove 2 weeks of data (date method) OR remove all the unique IDs that are present on both files.

I don't want to go through the 'date method' as user entry errors are present in Excel file hence automating it would ruin the db.

I want to know how I can compare each unique ID in excel with Access?

Do I take the access database into a temp table (call it AccessTable), then delete each unique ID that is present on ExcelTable - that just go imported?

^ This seems to be the best idea. However, if  you can show me the resource to compare and delete duplicates inside Access, that would be great.

Rest, I can do!

Thank you for your time!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could use queries to do it. Import your new data into a temporary table (or just the IDs even), then create a query that looks at both the temp table and your main data, and retrieves only the ID numbers that are in both (link the fields). Then create a delete query that links the ID numbers in that first query with your main table and deletes those records. Then import your new data into the main data. That assumes you want to overwrite the old data with the new data, of course. If not, then it would be better to import all the data into a temporary table and run the delete query on the temporary table, then import what's left into your main data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial