Update data in existing fields from Excel worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access table created from an Excel import. Fields withing the
Excel worksheet are frequently updated with new information. For example,
contact names change, or records are added or deleted. How do I import the
new data and get Access to automatically compare with the old records and
change only the fields with edits? For example, revise the contact name, but
leave company name, address, and phone number unchanged within a record?
Whenever I have re-imported a revised Excel worksheet, Access imports
duplicate records and it is painstaking to compare and cross-reference.
 
Instead of importing the Excel spreadsheet, link to it. You can then use it
just like an Access table. Then create an update query that will update only
chaned values. So the sequence is:
Link to the spreadsheet
Run the update query
Delete the Link
 
Thank you.
Does the spreadsheet need to have the same name each time? The spreadsheet
typically comes to me multiple times over the course of weeks from an
external source, so would I need to be sure the filename is always the same?

Also, I'm very new at Access-- any tips for me on creating the query? There
are about 20 fields in each record that could be revised.

THANKS!
 
I don't know what your skill level is with VBA, so I will give you the easy
answer.
The file name does not always have to be the same. If you don't need your
app to be really automated, then you could use the manaul process to link to
the spreadsheet. That is with File-->Get External Date-->Link. Then you can
follow the bouncing ball to link to the spreadsheet.
As far as the queries go, The easiest approach would be to create an update
query that would update your Access table from your linked spreadsheet table.
Note you will need the spreadsheet linked while you do it. Just tell it to
update every field and it will.
If you also need to add new records, you will need an append query. To
avoid adding duplicates, you will have to filter out existing record in the
table. You do that in the Criteria row of the column of the primary key.
 
Back
Top