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

Step by step in Excel 2003



In the Excel workbook select the worksheet where you will create the database query.



Select Tools > Macro > Record New Macro > OK



Select Data > Import External Data > New Database Query and create your query



Select Tools > Macro > Stop Recording



Press Alt-F11 to view your code and locate what you just recorded in one of the Modules



Paste the following code in that module below the code you found and execute it once.



Sub SetRefresh()

ActiveSheet.QueryTables( ActiveSheet.QueryTables.Count ).RefreshPeriod = 30

ActiveSheet.QueryTables( ActiveSheet.QueryTables.Count ).RefreshOnFileOpen = True

End Sub



(The extra spaces will disappear when the code is pasted into the module – they are there to ensure readability in Answers. You will need to make sure that = True is on the same line as RefreshOnFileOpen)



That code will result in the Excel worksheet being refreshed every time it is opened and every thirty minutes while it is open. If you don’t need the refresh to happen more often than each time the workbook is opened don’t execute the first line above or change the 30 to 0. Or set a different refresh period by entering the value in whole minutes that you want.



You may have noticed that I told you to record your query creation but did not tell you to look at it. You should take the time to see what is set when you create a query (that’s how I found out about the two values I told you to set) but it is not essential to the stated task. And it gives you something to compare if you ever have a query that works and one that doesn’t.



If you can make all of your changes in Excel rather than Access keeping in sync is even simpler. Just use the File > Get External Data > Link Tables and your Access table will be updated every time your Excel workbook is saved.



I haven’t tried it, and don’t intend to, but I am relatively certain that you will run into trouble of you try to build an Excel query on a table that is linked to Access from the same workbook.



Note: Contrary to what seems to be implied in another answer, tables created in Access with a link to an Excel worksheet are read only in Access and can not be updated there. So you have to pick one or the other of Access or Excel to record all of your changes.

Answer 2

This Site Might Help You.



RE:

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

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 3

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.