How can I reuse a Query Builder query?

February 1, 2011 12:08 PM

Is there any way to save a query in MS Office 2011's Query Builder that isn't tied to a specific spreadsheet?

I've got a MySQL database, Excel 2011 (the latest Mac version), and Actual's Open Source database ODBC connector. I'm using Microsoft Query to populate spreadsheets using this basic workflow:

• I go to Data | Get External Data | New Database Query ...
• When Microsoft Query pops up, I select my tables, set up the relationships by dragging the "id" column between tables in query view, and set up my criteria
• Once everything's set up, I test then click "return data" to get the query results into the spreadsheet

That's all fine. The thing I'd like to be able to do is save the query I've set up somewhere outside the spreadsheet. It's possible to do that with saved Web queries (the menu item Data | Get External Data | Run Saved Query ... points to a directory with a few saved Web lookups in text files), but I don't see how to do it with Microsoft Query SQL queries.

The ways I've figured out I can essentially reuse the same basic query with all the relationships I've set up between the six or so tables I'm querying involve:

• copying and pasting the SQL version of the query into a snippet file somewhere and pasting that into a new query, modifying as needed each time
• saving the spreadsheet with the required query as a stationery pad and using the Data | Get External Data | Edit Text Import menu item to edit as needed
• punting and setting up a Ruby script that just dumps what I need out to a CSV file, modifying that as needed and importing the results, since the MySQL database is largely held together by ActiveRecord, anyhow, and my models have all the necessary relationships

So is there any other way to save a query in Microsoft Query the same way I could save those Web queries?

As a side question, does Microsoft Query ever understand relationships between tables without being told explicitly each time? My tables are all InnoDB and I set up relationships between them in MySQL. MS Query acts like those relationships don't exist. I have to recreate them with every new query, which is really the biggest issue for me: I hate all that dragging and dropping. Has anyone had better luck with MySQL's official ODBC connector? It wasn't ready for Snow Leopard back when I bought my Actual license.

posted by mph to Computers & Internet