How to update records in Access using data in excel

[Douglasrac]

Hi,




I have a query in acess which brings data from different tables. Each month I print this query and I send to a person that will modify the numbers (manually - yes with a pen) and give it back to me. Then I update it manually (in the computer) and print it back to verify.


Once I have the final version done, I have to put the updated data into access again. And this is the part I would like to automate (since its not possible to automate the first step because the other person is a retarded on computers and want to use a pen).


So how can I import the data I updated in excel to access? As far as I saw I can only import data into a table, not update using a query. Plus while we are making this manual updates, the data in access is changing. So copy and paste is not an option since the order of the records in the query will be heavily different.

[ranman256]

When you export the data, add an extra field that has the table name
or export each table to its own sheet tab in the workbook.

Once the sheets are corrected, put the corrected workbook in the 'imported' folder. Where The workbook has already been linked.
now you can run a macro that has all the update queries needed to update the tables from the linked workbook.

[CJ_London]

So how can I import the data I updated in excel to access? As far as I saw I can only import data into a table, not update using a query. Plus while we are making this manual updates, the data in access is changing. So copy and paste is not an option since the order of the records in the query will be heavily different.

You can link to excel and run update queries but

I have a query in acess which brings data from different tables.

implies you need to update multiple tables which means you will need multiple update queries. Further, assuming you are updating an existing record, you will need in your excel table a uniqueID (typically a primary key autonumber) for each table you want to update.

[Douglasrac]

Well if I update only one table it would already help me a lot. I do have an unique ID. I just don't know the step on how to import this data in a way that will update existing records without creating a new table.

The excel sheet have 100 records. Unique ID and Value to be updated.
The access table have 1000 records with many other fields.

How that would work step by step?

[CJ_London]

I'm assuming you don't mean 'Unique ID to be updated'

1. create a linked table to excel (no need to import it)
2. create an update query where you have your destination table linked to the excel linked table on your uniqueID which updates the appropriate fields in the destination table with the values from the linked table.
3. run the query

tips, so you don't have to keep relinking to the next excel file next week etc, give the excel file a generic name (e.g. ImportData.xlsx) when you first create your linked table

then next week, you get your file back, keep a copy by all means, but copy and replace it to the same name and location as the excel file ready for importing- the linked table will then be looking at the new data.

I liken it to a linked table being represented by a microscope and the excel files for import as a slide -slide one out, slide the next one in

Try it