Double sided link between Excel and Access

[Maxihero]

Hi there,

Is there any way how to create reliable link between Excel and Access which would work reversibly? I mean, if I change the data in Access (table) they will change in Excel spreadsheet and conversely?

Thank you,

Michal

 

[Pat Hartman]

Allowing updates from both directions is a problem waiting to happen. How are you going to know which side is current? Designate one source as primary and only allow updates there. Then have the other refresh itself when it opens.

 

[Maxihero]

Thank you for your answer Pat. I see what you mean, I've just thought that there might be some way to assume the current side - the one which is currently changed (as there cannot be changed both sides in the same time). The problem is that I have an old Excel file with the records and I must build the Access database from this file. As the users are used to the old Excel file and all the macros are pointing to this file, it would be really convenient to have such a double sided relation. One is obviously from Access to Excel and another one from Excel to Access is due to the changes which will be surely made in the Excel file by users or macros (and which I want to be projected into Access). I hope it's at least bit clear and thanks again for your reply.

 

[Pat Hartman]

Your request was clear from the beginning. Pick a "master" and only allow updates there. That's my advice. Unless you are fluent in VBA for Access and macros for Excel you won't be able to write the complicated sync process that is required to make sure that updates from each side are properly recorded.

In my opinion, this is so dangerous, I would not even consider doing it.

Your users would not need to know anything about Access if you design an application for them to work with. You create data entry forms and reports and the user NEVER sees the underpinings. He doesn't work with tables or queries or code. The user only works with the interface you create so saying they don't know Access is completely irrelevant. If they understand their application, they will understand the application you create to implement it.

If they won't give up Excel, then if you need Access for something else, you will need to import the Excel data each time you open the app. Or you could link to the spreadsheet rather than importing the data but Access won't share the workbook. It will require exclusive use of it.

 

[Maxihero]

Thank you from stopping me creating the whole sync process using VBA. I agree, it'd be too risky.

What I'm thinking is to pick Access application as a "master" and to feed the old Excel file by changes done in Access. So the users would work only with Access (of course I agree that there is no problem (by building the friendly and familiar interface)) and the macros could still point to the same Excel file. Do you think there might be any problem in this approach? I'm not very familiar with these things. I would need the Excel file to be reliably updated straight after changes are done in Access.

Many thanks

 

[Libre]

I have a similar situation here, and for similar reasons. Excel is much more widely distributed than Access - all users in the company have Excel, but very few have Access. So I built my database in Access for me and a front end in Excel for the users. I import/export selected data from and to the Excel application from the Access side.

None of this is automatic - I control the process. I update whenever the data needs to be refreshed - so it is not a "live" connection. That is one way of controlling the risk. In my case the users do not need up to the second updates. A few times a week is sufficient - but I could do it more, obviously.
The way I handle the "risk" of having each system capable of updating the other, is by use of transfer tables on the Access end. These tables structurally replicate the actual tables that the data resides in, but are only used to hold the data going in or out. Once in, the transfer table data is processed and error-checked etc, and once validated, the "real" tables are updated in Access.
That way, when I do an update, I can control the flow of the data and validate it all before I update any of the actual data.

Check out the TransferSpreadsheet method in Access. It can be tricky but once you get all the arguments set right, it works.

 

[Maxihero]

Thanks a lot Libre! Unfortunatelly, I cannot use this method as I want the users to work also (should rather say "mostly") with Access (and the image of them updating the data (not by pressing one button), makes me shake), but it seems to be very useful for the future.

However, I'm really thinking that this Access to Excel connection might be sufficient for me. Is there any reliable way how to build it that all the changes in Access will be updated in Excel "live"? I didn't manage to create it using "export" from Access to Excel. If there's no built-in procedure, VBA code'd be also appreciated.

Thanks again!

 

[Libre]

I've had trouble establishing live connections across different applications, although OFFICE does have that capability. I always run into security and permissions issues.
The method that works for me is the one I described.
Live connections, it seems to me, are much more prone to error and contentions between different users accessing at the same time.
You do realize you can create user input forms with click buttons and macros in Excel.
It is not like the Excel user can only input by entering raw data into a spreadsheet.

 

[DJkarl]

I can't think of any way to "reliably" do this, since doing it this way is not at all advised. You are essentially trying to create two systems of record for the same data set, by all accounts this should not be done. If you want your users to move to Access then have Access be the system of record and pull the data in from Excel to initially populate the tables, then just leave the Excel world all together. Trying to cater to everyone (those who want Access and those who want to stick with Excel) is a recipe for headaches and disaster.

 

[Maxihero]

Libre: Yes, from the beginning I'm realising that the most easy way would be to make it in Excel. It's just that there's very great use of the Access relationships in this case.

Karl: It's mostly about all the macros which are pointing to the Excel file. As I've agreed with Libre, there's no problem of making the users use only Access.

Thank you guys!

 

[Lightwave]

What I've tended towards is encourage users to do all data entry in Access and have standard reports that are used frequently.

If users are wanting to do reports and things using information in excel then I have a simple procedure that exports all records in all tables that users can run themselves.

This means that they can play about with stuff as much as they want but the central source is maintained. I can see that I will never be able to create all the strange reports and things people want to do with the information but allowing them to export the information gives them some control over the information and if they develop particularly useful things I will try and build that into the system. A lot of stuff is one off though and I'd prefer the users to be tackling that than me. The excel export allows this.