Update query from Excel to Access

[neeraj_chow]

I have a database in Access 2000 and some part of that
info is in Excel 2000 which changes on a daily basis. I
want that after end of each day I run an update query from
Access which picks up the appropriate value from the excel
sheet and update the corresponding value in the Access
table. Both the table and the sheet have the same primary
key. Can this be done. If yes, how ? Please guide.
.

 

[Russell Hauf]

Yes, link the Excel sheet to the Access database. Then run an update query using the 2 "tables" (which are one access table and one linked table - the Excel sheet). Do you need help with an update query?

-rh

 

[neeraj_chow]

Yes, help would be appreciated. I am unaware on the technique used to link access table and excel sheet and how to write such a query which links two different entities as in this case. Elaboration on the topic would be very helpful.

 

[Russell Hauf]

To link an Excel sheet to Access, go to File-Get External Data-Link Tables. Then, at the bottom of the dialog, change "Files of type" to "Microsoft Excel." Choose the workbook that contains the sheet that you want to link, press ok, then follow the steps as the wizard guides you. After this is linked, it is just like a regular Access table - you just add it to your query like any other table.

Also, instead of going to File-Get External Data, you can go to the Tables tab of the Database Window and right-click anywhere but on a table name, and select Link Tables.

Hope this helps,

Russell