I have an Access database with a few tables and several queries. I already have this working perfectly in Access but my boss wants me to replicate the results using excel 2010. I have tried using the MS query inbuilt into excel but I can’t get the SQL query to do the same thing, I have asked several experienced people but they also didn’t know. I have decided to try a different approach.
The users need to regularly update the information in the tables and then re-run the queries. The result is then exported to excel for further manipulation. Would it be possible for me to make the database accessible via runtime. Could I have it so that the user puts all the tables that need updating as excel spreadsheets in a certain folder and then in VBA in excel have the runtime access application update the tables, run the queries and output the results in excel? The main thing is that the users won't have the full access application.
I have had a look around but couldn’t find much on this. If not, any other ideas would be greatly welcome.
From the Access wiki:
The runtime version allows users to view, edit and delete data, along with running queries, forms, reports, macros and VBA module code. The runtime version does not allow users to change the design of Microsoft Access tables, queries, forms, reports, macros or module code.
Therefore, you can send code to the Access runtime version that runs queries that update your data, and have Excel linked to the tables that result from those updates.