Excel and access synch

[JSH720]

I need to be able to synch my changes in Access and Excel automatically as they are made. MY db is simple (used for this one purpose only and them trashed). But it is extremely important that the data is the same in both places. Used to be able to do this, but now can only synch one way.

I may have multiple spreadsheets set up as tables in Access (each with its own table) linked by a common item number. I'll query and report based on those tables/spreadsheets.

this is a one off (although this function is extremely useful for future projects) and db will be trashed after project.

Thanks for your help.

 

[Joe4]

If all you are using Access for is to create your queries and reports, and are only updating the underlying table data in Excel, then if you set up your tables to be linked Excel tables in Access, any data changes you make in Excel should be automatically reflected in the Access.

 

[JSH720]

Thank you and yes, I am only using Access to generate reports. Can it go the other way as well, changes to access reflected in excel?

 

[Joe4]

Can it go the other way as well, changes to access reflected in excel?

If your table is a linked table (to the Excel table), you will not be able to update it from Access (at least not directly).
Years ago, you used to be able to do that, but Microsoft lost a lawsuit in which they were using some functionality created by someone else.
Now, I think you need to use VBA to update the Excel file directly.

 

[JSH720]

Thanks. I thought not. Access is easier to see and update from than these massive (data) excel tables that are used in my industry. Most of which is not calculations, but contract information stuff. I'll post for vba to go back from access to the excel spreadsheet.

 

[Micron]

take a look at TransferSpreadsheet. Not being a macro user, I can't say for sure - but I'd bet you could use an Access macro if you can't find usable vba code, or write your own. The method will work either way - tables to spreadsheets and vice versa.