Update on Import

[weswilson88]

Hello, I am trying to update a table based of Excel spreadsheets that are sent to me daily. The data on the Excel spreadsheet has a Unique ID that corresponds with the Unique ID in the Access table. The problem that I run into is that if the Unique ID already exists in the Access Table then Access will ignore it. What I would like is for Access to overwrite all of the other fields of the Unique ID record for ones that exist and Add records for the Unique ID's that are not already in Access. I have read quite a bit about update queries and using two tables but I cannot seem to get anywhere with it. If there is any examples or suggestions you can refer to me I would appreciate it. Thank you.

[Dal Jeanis]

Here's my suggestion. This assumes that the Unique ID key field is NOT autonumber. If it is autonumber and needs to remain so, then your problem will require a different strategy to solve.

First, before writing and testing the routine, back up your database.

Second, set up the routine in a VBA module to do this -

1) Import the data into a temporary version of the table.
(test, and when it's working, back it up.)

2) Delete all records in the main table which have a matching key in the temp table.
(test this by manually entering one or two keys at a time in the temp table,
and when it's working, back it up.)

3) Insert new records into the main table for each record in the temp table
(test this using the above two facilities, and when it's working back it up.

4) Delete the temp table.
(you know what I'll write here)

Third, once that is all tested, take a brand new backup of the main database, copy just the VBA module into it, and test all functions. If they work, then you can move it to production.

If not, then you changed something else in your testing. Find that something else. Repeat with fresh copies of the production backup until you can install
your new features without a glitch.

[weswilson88]

I will give this a shot. Thank you.