Import Updated Data from and Excel to Access

[Lynn L]

I manage data in Microsoft Access. Many of my co-worker do not use this. I
export an Excel file for them to update. They send me back the updated
spreadsheet. I need to know the best way to update the Access data from the
Excel spreadsheet. They have the same fields and IDs. No new rows would be
added, it's just adding more data to existing records. Can someone let me
know the best way to do this.

 

[Piet Linden]

I manage data in Microsoft Access.  Many of my co-worker do not use this.  I
export an Excel file for them to update.  They send me back the updated
spreadsheet.  I need to know the best way to update the Access data from the
Excel spreadsheet.  They have the same fields and IDs.  No new rows would be
added, it's just adding more data to existing records.  Can someone letme
know the best way to do this.

Oh, before I mislead you and create a serious mess. Make a backup of
the table you want to update. (in case of failure...)

Link to th spreadsheet,
create a query that joins the spreadsheet (linked table) and the table
you want to update. Join the two tables on the primary key(s).

Turn the query into an update query.
specify the values in the attached table
e.g.
=[xlsTable].[ColumnName]

If you use an attached table, you can update the link to the original
source document by modifying the Connect property of the given
table... either in code (if you do a lot of these in sequence) or
manually using the linked table manager.

 

[Lynn L]

I was able to follow and recreate your instructions until the Specify the
values in the attached table e.g. =[xlsTable].[columnName]. In my case, I
want the Access table to be updated with the Excel. Should the Field and
Table tab contain fields from the Excel table and the Update to field contain
Access field info. Can you show me an example using Test at the Access Table
and Master as the Excel table and Status for the field for both. I keep
getting enter parameters.

Thanks, Lynn

I manage data in Microsoft Access. Many of my co-worker do not use this. I
export an Excel file for them to update. They send me back the updated
spreadsheet. I need to know the best way to update the Access data from the
Excel spreadsheet. They have the same fields and IDs. No new rows would be
added, it's just adding more data to existing records. Can someone let me
know the best way to do this.

Oh, before I mislead you and create a serious mess. Make a backup of
the table you want to update. (in case of failure...)

Link to th spreadsheet,
create a query that joins the spreadsheet (linked table) and the table
you want to update. Join the two tables on the primary key(s).

Turn the query into an update query.
specify the values in the attached table
e.g.
=[xlsTable].[ColumnName]

If you use an attached table, you can update the link to the original
source document by modifying the Connect property of the given
table... either in code (if you do a lot of these in sequence) or
manually using the linked table manager.