Best way to import and update tables?

[wildbill]

I have an Excel spreadsheet with 1000+ rows that I need to import into
an Access 2002 db once a month or so. I then need to use that
information to update any existing records(multiple fields may need
updating) and/or add any new records.

I figure one way is to just "brute force" it, i.e., import the
spreadsheet then run some code to do a sequential read thru the new
table and do lookups in the existing table and update whatever fields
may have changed. Not real elegant but seems workable.

Any other "cleaner" way of doing this?

URLs, code-snippets, etc. welcome. TIA

bill W

[Larry Linson]

If the Excel spreadsheet is in row/column arrangement, you can link it as
though it were an Access table. Then it should be (relatively) easy to run
queries to determine what needs to be updated. On the other hand, if the
Excel spreadsheet is the "authoritative source" for the information, you
could delete the existing table, and use it as the data source for a "make
table" query to create a new version.

Larry Linson
Microsoft Access MVP

"wildbill" <we*******@charter.net> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I have an Excel spreadsheet with 1000+ rows that I need to import into
an Access 2002 db once a month or so. I then need to use that
information to update any existing records(multiple fields may need
updating) and/or add any new records.

I figure one way is to just "brute force" it, i.e., import the
spreadsheet then run some code to do a sequential read thru the new
table and do lookups in the existing table and update whatever fields
may have changed. Not real elegant but seems workable.

Any other "cleaner" way of doing this?

URLs, code-snippets, etc. welcome. TIA

bill W

[wildbill]

Linking is not a good option as the spreadsheet comes from a web-site
in another location. Oh...and the name of the spreadsheet may change
and the name of the worksheet may change

Zapping the existing table won't work either as there are
fields/information that are not in the original-spreadsheet and I don't
want to lose the info.

[Larry Linson]

"wildbill" wrote

Linking is not a good option as the
spreadsheet comes from a web-site
in another location. Oh...and the
name of the spreadsheet may change
and the name of the worksheet may
change
You could download the spreadsheet, rename it, and link on the local
machine, but that may have no advantage over importing the spreadsheet into
a table.

You can create Queries including both the old and new tables, to return the
new values for fields where they exist, then use those queries as the Data
Source for an update Query, to replace the old values. It still does not
make much sense to check for differences... if the spreadsheet is the
"authoritative source", you can just overlay those fields that are in the
spreadsheet. If the values are identical, nothing will change; if there's a
new value, they will change just as if you had checked and only replaced
those where there is a difference.

You'll probably also need a Query to find Records in the Spreadsheet data
that don't exist in the table and use an Append Query to add them. Update
does not handle that case.
Zapping the existing table won't
work either as there are fields /
information that are not in the
original-spreadsheet and I don't
want to lose the info.

The update approach I suggest above works on individual Fields, rather than
replacing the whole table, as I had previously suggested.

Larry Linson
Microsoft Access MVP