How can i update data in access database created from excel spreadsheet. so, that change is reflected in both?

Question

I have created an access database by importing data from the spreadsheet. If, I want to update the data in access how can i make sure that the changes are reflected in spreadsheet as well.

Answer 1

The correct action is not to import the spreadsheet, rather link it to access database. Anytime you change data in the spreadsheet, it will be changed in the Access file.



Just go to File>Get External Data>Link Tables and choose the appropriate data type and file.

Answer 2

From the Access Menu, choose File>Get External Date>link tables. Choose Excel from the file types. Next select your excel document. Access will treat this linked table almost just like a regular table. The advantage here is changes made in either program will be saved. For a one way connection: You can use an ODBC in the Excel sheet to connect to the database. On the Excel menu, go to import external data>New Database Query Choose MS Access Database from the menu. In the dialogue box choose your database and table. Then select the fields you want. Choose Ok. When you want to update the Access data, click on the table in Excel, then go to the Menu and choose date>refresh data. This is one way only. From access to excel. Hope that helps.

Answer 3

You'd probably have to write a script in Access to export the Access database to a spreadsheet every now and then (exporting everytime a single field is changed would be inefficient I think). I'm not sure if you can bind them together.