Lets clarify a few things. You are importing the spreadsheet into a table in
which the data appears in a form. You are wanting to know how to update the
Access table with changes that were made in the Excel file. It would be
easier if you just linked the spreadsheet into Access and then you can use
the data to create reports.
You could create a form against the 'linked' spreadsheet and any entered
data entered into the form would automatically update the Excel spreadsheet
and vice versa. This is assuming the spreadsheet is located on the local
network. The problem with this method is that if someone has the Excel
spreadsheet open then you may not be able to enter data into the form and
vice versa.
If you just want to print reports and never enter data directly, then
linking is fine.
If the data is from outside your network, then you will need to import the
Excel file to a temp table and then use an Update query to update the
current records with the temp table data using some unique value that is
common to both tables so the Update can occur. The problem lies if there is
new data mixed in with data to update as the update query can't update
something that does not exist. You would need to write a function in Access
that would check if the unique value existed in the current table as it
looped through the temp table data and if not it would append the temp table
record out of the temp table into a new table that will be used for
Appending new data. You would then need to compare the new append temp
table's unique value and delete that record out of the temp table for
Updating. Once that is done then you could use the temp table to update
existing data.
Hopefully, I explained that well enough for you to get the 'jest' of what
needs to be done. As you can see there may be no easy solution and it may
require a little programming knowledge of VBA.
