Using random fields in Excel to populate Access table

[cryswater]

I have a situation where I run a query out of a few tables in Access. This query is sent out and randomly updated by multiple users. There are text fields, date fields and a yes/no field.



There are other fields of data in the Excel spreadsheet that would not need to be populated into the Access table and are just there for the users' reference.

The field names in the table and the Excel spreadsheet are identical.

Here's my dillema... Currently, I am having to manually update each Access record in the table, by filtering on the unique ID and copying the data in Excel and pasting it into the corresponding record field in Access. This is a painful process. I know there has to be a better way to update the Access table, without damaging/overwriting or losing the data that is not being updated.

Any help would be appreciated. Appologies for the boneheaded question.

[llkhoutx]

Import the Excel data into a temporary table.

Write a routine to step through that tables recordset, updating the real table's recordset based upon the appropriate ID.

[MAF4Fam6]

Currently, I am having to manually update each Access record in the table, by filtering on the unique ID and copying the data in Excel and pasting it into the corresponding record field in Access. This is a painful process. I know there has to be a better way to update the Access table, without damaging/overwriting or losing the data that is not being updated.

Is there any way your multiple users can supply a spreadsheet with only those records that contain updates? This way you can batch process your records into your Access table.

Import the Excel data into a temporary table.

Write a routine to step through that tables recordset, updating the real table's recordset based upon the appropriate ID.

As llkhoutx mentions...you would need to import the spreadsheet into a temporary table and then run an update query to batch process your updated fields into your main table based on the appropriate ID (Primary Key).

-RC

[Castillb]

Hi Chris,

I'm trying to do the exact same thing. Did you have any luck?