Since you have some data you want to add and other data you want to replace,
it will take two queries to do this.
The reason you can't do it with just the import method you used to get the
initial data into the spreadsheet is that when you import data from an Excel
spreadsheet, it overwrites all data currently in the existing table.
You will still use the import, but rather than importing the data, I suggest
you link to the spreadsheet as if it were a table. The difference is that
linking to a spreadsheet does not copy the data from the spreadsheet into a
table, but makes it available to your Access database as if it were a table.
The only major difference in how you do the import, is when you do Files, Get
External Data, instead of selection Import, select Link Tables. The wizard
will guide you throught the rest of it.
The two queries you will need are one to replace or update existing data and
one to append records in the spreadsheet that are not in the Access table.
To build these queries, you will need to link to the spreadsheet so the query
builder will know about the spreadsheet table and the fields it has.
Once you have linked the spreadsheet and created your queries, you can use a
Macro to automate the procedure. You will need these actions:
TransferSpreadsheet - Enter the information the Macro needs and it will link
the the speadsheet for you.
OpenQuery - Execute the Query to Update records
OpenQuery - Execute the Query to Append new records.
DeleteObject - The object name to use it the name of the linked spreadsheet
table. This will not delete the spreadsheet. It only deletes the reference
to it in Access.
Now, the queries I can't help you with because I don't know the names of the
fields in your table or spreadsheet and I don't know the rules on what to
replace and what to add.
