One of my favorite Excel features is the Edit OLE DB Query dialog, where you can edit a table’s data connection and SQL. If you’re not familiar with table data connections, I’m talking about tables created by clicking something like “From Access” in the ribbon’s Data menu. Besides Access, you can connect to other databases, Excel files, the web, and who knows what else. Here’s a nice example of connecting to Access.

To get to the Edit OLE DB Query dialog, right-click in a table and choose “Table” then “Edit…”

Each time I do so I’m filled with child-like wonder at the ability to throw in a new connection string, switching, for example, from Access to SQL Server.

In addition you can change from the default Command Type of “Table” – which returns all the contents of a table, query or view – to “SQL,” which allows you to enter SQL directly into the Command Text box.

As far as I can tell this SQL can be as complex as what you would use directly in that type of database. For example, you can use With statements with a SQL Server connection.

That being said, and depending on my access privilegees, I try to limit the amount of SQL on the Excel side. If possible, I connect to an existing database view and then maybe filter it in the Command Text box. So with a SQL Server view called vwScoresAllYears, I might narrow the results with SQL like “SELECT * FROM vwScoresAllYears WHERE vwScoresAllYears.year IN (2012,2013).”

As cool as that is – and it is – it pales next to the ability to switch connections to a completely different type of database. One time we were porting a project from Access to SQL Server. The front end was a big old workbook with a data table pointing at an Access query. The table had lots of calculated columns and several pivot tables pointing at it. To do the switch, we could have created a new table pointing at the SQL Server view, rebuilt the calculated columns and pointed the pivot tables at it. Instead, I just switched the connection string so that it pointed at the SQL Server view. Since the output columns were exactly the same, the transition was barely noticeable. I just hit refresh and the SQL Server data poured into the table.

The following two pics show what I mean. The first connection is to an Access database…

The second connection is to a SQL Server database:

If you’re wondering, the connection shown in the first picture in this post is to another Excel workbook. In that case there are three worksheets serving as tables. The SQL looks a bit different, because when referring to Excel sheets or ranges, the names are surrounded in brackets, and followed by dollar signs in the case of sheets. If the idea of using SQL on Excel workbooks is new to you, this MSDN page is a good start.

I often find myself copying query settings from one table to another. For a while I’d open the Edit OLE DB Query dialog for a table, copy the connection data, open the dialog for the second one and paste the data. That got kind of boring, so guess what? That’s right, I wrote a tool to do what I want:

The way it works is you select a Source table – by clicking into a table and clicking the form button – and then do the same for the Target. You then select which parts of the query – Connection, Type, or Text – to copy over. You can also just edit the text in the Connection or Command Text boxes. Clicking the Copy button just copies the selected properties from the source side of the form to the target side – it doesn’t change the query properties themselves. You can make further edits in the target textboxes as needed. Clicking the Set Properties button copies applies the properties to the target table and attempts to refresh the table. If the refresh fails you get an error message. At that point you can tinker with the properties some more or click the Restore Last Good button, which will revert the table properties to the last working query.

This is different than the built-in dialog, which just reverts to the last working query. I find this ability to tweak a query until it works quite handy. Another advantage is that it opens up queries created by MS Query without the dialog telling you that you won’t be able to edit it.

To make it user-friendly for Dick and other shortcut-only types, the checkboxes can be reached by clicking the Alt Key combo for that property twice.

I uploaded an empty workbook that has a button to display this modeless form. If you download it be sure to save your work before trying this tool. I’ve used it for a few months now with no big issues, but better safe than sorry. Here’s the link.