Edit through Excel to Access & vice versa
Situation: For production administration my client uses an Access database for storage. The database is being used for safety reasons. However, it isn't that friendly for the average users to screen/analyze & report data from it. For those reasons we want to (externally) connect it with Excel (as front end & Access as backend).
What options are there to edit Access data from Excel and vice versa? So far I was only able to edit data within Access itself. We want edits from both ways because we use another sync platform to sync data from mobiles to the Access database.
What options are there to edit Access data from Excel and vice versa? So far I was only able to edit data within Access itself. We want edits from both ways because we use another sync platform to sync data from mobiles to the Access database.
Forget about Excel...just that.
Work your way from Access to provide the necessary screen/analyze & report ...all the tools are available....you will be amazed on how easy just about everyone will forget about Excel...just with a click all the power would be right there....
Work your way from Access to provide the necessary screen/analyze & report ...all the tools are available....you will be amazed on how easy just about everyone will forget about Excel...just with a click all the power would be right there....
ASKER
Thanks Noah, I'll check the videos.
Hey John, what tools would you suggest?
Hey John, what tools would you suggest?
Ms Access has all the tools you need...builtin
ASKER
Hey John, my client has been MS Access for years and they don't even know about it. Can you at least give some specific direction / resources / references? My client prefferes to use MS Access but we haven't been able to accomplish the functions I mentioned earlier with Access.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Alright John, thanks for making your point clear. You are right for the most part. It's just that it the client has a tight budget with which it wants to make a huge improvement.
No money...no party....isn't that life is all about... :)
ASKER
Hey Noah, I checked your recommended article & video. In what cases is VBA better than making a "Excel Power Query connection" to the database?
We also used in Access a Excel Linked table. Changes made in Excel are reflected in the Access table. However, no changes can be made from within Access to the Excel doc.
We also used in Access a Excel Linked table. Changes made in Excel are reflected in the Access table. However, no changes can be made from within Access to the Excel doc.
ASKER
No money, no party? Haha, John 🙈
All this talk and we still don't know what kind of setup you are having to find a solution for.
Is your MS Access file just a bunch of tables (and queries?) on a server, the cloud, a local client machine, etc.?
What do you know about creating an Access file to use as a "front-end" (user-interface) for your users that has screens (forms and reports) with buttons and controls for filtering/sorting/editing data while the "back-end" database (with just the tables) sits on the server?
What do you know about Excel tables and the special things you can do with them when it comes to databases and database data display/editing?
Coming up with something that does exactly what you want is as simple as reaching into a bag of tricks and pulling out the right one for your situation - once we know what that is....
Is your MS Access file just a bunch of tables (and queries?) on a server, the cloud, a local client machine, etc.?
What do you know about creating an Access file to use as a "front-end" (user-interface) for your users that has screens (forms and reports) with buttons and controls for filtering/sorting/editing data while the "back-end" database (with just the tables) sits on the server?
What do you know about Excel tables and the special things you can do with them when it comes to databases and database data display/editing?
Coming up with something that does exactly what you want is as simple as reaching into a bag of tricks and pulling out the right one for your situation - once we know what that is....
VBA may be better if there are many repetitive steps for many similar documents that require the same routine. Furthermore you can add a passive code so that you don't need to manually update the query every time there is a change, it can be something like once every time the Excel or Access database is opened, whichever file you are collecting information to.
I think the main thing is you will need someone who knows VBA to connect both Excel and Access to work the way you want. Below are some articles you can reference to for examples.
Get Data from Access Database using Excel VBA: https://www.youtube.com/watch?v=MWLBM2t7azc
Updating Tables in an Access Database Using Excel VBA: https://software-solutions-online.com/updating-tables-access-database-using-excel-vba/