excel/access assistance

[BFoxDDS]

i'm sure this is simple to do, but i can't find the answer in the
search engines.

i have an access db with 400,000 records. i produce queries of 5,000
records at a time exported into an excel file. sometimes people will
update items in the excel file, send it back to me in the exact format
i sent it, and i just do an update to the saved query in the db with
the new info.

this time, the file i sent had 3,500 records, and instead of keeping
all the rows in the excel file, the person updated certain rows and
removed the rest - so now i have 500 records.

how do a take this excel file of 500 records and auto-match them up to
my 400,000 db records and update (copy over) with the new info from
excel?

thank you.

[Jeff L]

Did you export your unique identifier to the excel file? If not, I
would suggest that you do so from now on. That identifier is what you
would use to do your updating via an Update Query. There really is not
a good way to update your records without that ID field.


BFoxDDS wrote:

i'm sure this is simple to do, but i can't find the answer in the
search engines.

i have an access db with 400,000 records. i produce queries of 5,000
records at a time exported into an excel file. sometimes people will
update items in the excel file, send it back to me in the exact format
i sent it, and i just do an update to the saved query in the db with
the new info.

this time, the file i sent had 3,500 records, and instead of keeping
all the rows in the excel file, the person updated certain rows and
removed the rest - so now i have 500 records.

how do a take this excel file of 500 records and auto-match them up to
my 400,000 db records and update (copy over) with the new info from
excel?

thank you.

[BFoxDDS]

yes, i did export the id file. problem is, i don't know how to take
records 32, 48, 55, 102, 210, etc. and auto-update only those select
ones in access.

Jeff L wrote:

Did you export your unique identifier to the excel file? If not, I
would suggest that you do so from now on. That identifier is what you
would use to do your updating via an Update Query. There really is not
a good way to update your records without that ID field.

[Jeff L]

You create an update query. You need to import the excel spreadsheet
into Access first. For simplicity I'm calling it NewData. Then create
an update query using and the table you want to update (MainData).
Join the ID fields in MainData and NewData. Now select the fields you
want to update from MainData and in the Update To row, put
[NewData].[FieldName], where FieldName is the actual name of the field
from NewData. Run your query.

You might want to make a backup of MainData, just in case.

Hope that helps!
BFoxDDS wrote:

yes, i did export the id file. problem is, i don't know how to take
records 32, 48, 55, 102, 210, etc. and auto-update only those select
ones in access.

Jeff L wrote:

Did you export your unique identifier to the excel file? If not, I
would suggest that you do so from now on. That identifier is what you
would use to do your updating via an Update Query. There really is not
a good way to update your records without that ID field.

[Keith Hutchison]

BFoxDDS wrote:

yes, i did export the id file. problem is, i don't know how to take
records 32, 48, 55, 102, 210, etc. and auto-update only those select
ones in access.

Jeff L wrote:

Did you export your unique identifier to the excel file? If not, I
would suggest that you do so from now on. That identifier is what you
would use to do your updating via an Update Query. There really is not
a good way to update your records without that ID field.

You could also import via VBA.

Open up the excel table as a recordset.
Step through each record.
With each record execute a SQL statement to update the data.

Keith