0

We need to update about 40 Excel data tables from queries in a single Access database, on demand. Ideally, Data > Refresh All should perform the update.

I’ll want to use VBA to set this up; a master Excel table identifies the data tables and corresponding query names.

What’s a clean way to design this? Should my VBA use ODBC, OLE DB, or ADO to create 40 database connections? (For maintainability, I'd prefer not to have a lot of VBA complexity, since our shop doesn't otherwise use these APIs -- just Excel with simple VBA.)

I’m running MS Office 2013.

Mugwort
  • 1
  • 3
  • I'd use Microsoft Query to connect directly to the Access table. This has a benefit of being able to connect to existing queries, which I think is a good thing. You'd want to separate the data access layer of your app (Access) from your Display layer (Excel). MS Query can also use parameterized queries too --> https://stackoverflow.com/a/26443279/483982. Once this setup in one file, simply make sure the Access file is in a shared location, and distribute the Excel file. I would recommend you strongly consider SQL Server Express though. – Ryan Wildry Nov 21 '19 at 14:26
  • Nice idea to use parameterized sql queries, so the source database tables can easily be changed by updating the master table. I'm a bit fuzzy on the relationship between Microsoft Query and layers like ODBC; can you suggest a reference? I'd be using VBA to create the 40 connections. – Mugwort Nov 22 '19 at 06:31
  • You wouldn’t need to worry about creating connection with VBA, you can do this through the data menu in Excel. Other than writing some SQL, this is a very low code solution. Check the link in my first comment. You can see how the connection is created after you set it up by looking at the connection string in data sources – Ryan Wildry Nov 22 '19 at 11:19
  • Reading up, I see Microsoft Query is just a front end to create connections. So are 40 ODBC connections the best solution? (The first would be created by MS Query, then used as a sample for VBA calls to crate the remainder.) – Mugwort Nov 22 '19 at 19:29
  • ‘Best’ solutions don’t really exist. Each have pluses/minuses. This is what I’d do if managing a VBA isn’t something you/team wants to do. SQL is a much more pervasive language than VBA is. So keeping it supported should be easier – Ryan Wildry Nov 22 '19 at 20:41

0 Answers0