0

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.

kit
  • 83
  • 4
  • 16

1 Answers1

0

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.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thanks Johnny. I have found this example which lets you run the access query from excel and return the results: http://www.myengineeringworld.net/2013/10/running-access-queries-from-excel-vba.html . Anyone got any thoughts on how to update the tables with the latest information before executing the query and returning the result? The data to be updated will initially come from excel spreadsheets, so if there is a way to do this directly from excel that would be ideal. – kit Sep 01 '16 at 19:48
  • I think I can do this in access by importing external data and selecting Link to the data source by creating a linked table – kit Sep 01 '16 at 20:01
  • It's kind of hard to suggest more without knowing more. If the spreadsheets will always be in the same location with the same name, you can link directly to the spreadsheets. If they will change but have the same layout, you can import with a saved import specification. There are a lot of ways to do it, depending on your structure. – Johnny Bones Sep 02 '16 at 12:52