Create Data Queries

Working with Data Queries in Excel

    Sometimes in Excel you need to get data from a table in an Access database. Access, apparently, is very good at storing and relating data. However, Excel's power is analyzing and displaying it.

    In this example I will be using the Northwind database, this is a sample Access database which you can download and use free from Microsoft; and you don't need MS Access (the program) to use it (I certainly don't). I will show you a method to rapidly & repeatedly pull data from an Access file, and use the query in VBA as well.

Download Northwind database

    To create a query, open Excel and go to Data Menu > Import External Data > New Database Query. You should see a dialog box like the one below.

    Select "MS Access Database" and click OK. Now you must locate the Northwind database file. In this example I placed the file on my desktop.

    After you select the file, click OK. The Query Wizard now appears, with the field names from each of the tables in the Access database. Now, in order of appearance, you would select the fields (columns) you want to import into Excel. For this example I selected both fields from the 'Current Product List' table.

    When you click Next, you can filter the data so that Excel (actually, MS Query) only draws matching records (rows) into the worksheet. Further down I will show you a technique that simulates the Input Box function to ask the user for this input. For now we just import as is.

    The next screen on the Query Wizard is Sort Order which I usually ignore since I can sort the data in Excel any way I want later on. Click Next and here is the Finish Screen. You can return the data to Excel (what we want), edit the query (very useful as we will see in a moment), or create an OLAP cube.

    Now, if you have any intention of automating this process with VBA, you definitely want to click the 'Save Query' button and save the file. In fact, I recommend you do this anyway, just to study the resulting file and SQL statements. When you click Finish, you get the following dialog box:

    If you click 'Edit Query' you will be taken directly to MS Query where you can view the underlying SQL. Here it is:

SELECT `Current Product List`.ProductID, `Current Product List`.ProductName FROM `C:\Users\Jimmy Pena\Desktop\Nwind`.`Current Product List` `Current Product List` (yes I am using Vista. If you were in Win XP, the path would be "C:\Documents And Settings\Jimmy Pena\Desktop\Nwind"

    Click OK and the data will be fetched and imported into Excel.

    Remember: Excel will only import the first 65,536 data items, so if you know you have more, consider creating a Pivot Table instead, using the database file as the external data source. Just go to Data Menu » PivotTable and PivotChart Report…, in Step 1 select "External data source" click "Get Data" and then it is the same exact steps as above. You'll end up with a traditional PivotTable which functions exactly like the usual one, except the data is coming from a cache created from the data in the Access database!

    At this point we are done with the importing and now turn our attention to the SQL and DQY file we created earlier. If you locate the file you can right-click it and select several options:

• Open in Microsoft Excel — this will start Excel and immediately repeat your query! If a co-worker or client
  has updated the database table, any new information would be imported.
• Edit with Microsoft Query — This brings up the MS Query program which you can use to view/edit the SQL or the
  query itself (see below).

    If you select 'Edit with Microsoft Query' here is where you can create filter parameters. Click on View » Criteria and in the Criteria field, select ProductID. In the Value row, type ">[Minimum Value]" (without quotes). When you type the criteria inside the brackets [], it causes the query to prompt you (using a more professional looking input box in my opinion) for a value, using the text inside the brackets as the message prompt. The results returned by the query will only have ProductIDs with higher numbers than the one you type in.

    Setting up this type of query is perfect for when you are working with a large amount of data and need to query it in different ways at different times. So one day you (or your boss) might only need the ProductIDs below a certain number, or above a certain number, or between two numbers. Now, when you double-click the query file, or go to Excel and Data Menu > Import External Data > Import Data and run the query, you get an input box like this:

    (FYI — A quick way to create DQY files, if you already know the SQL you want to use, is to create a dummy .txt file then rename the extension to .dqy. Right-click to edit.)

    To use the query in a VBA project, I simply recorded myself going to Data > Import External Data > Import Data, selecting the query file, and typing in the ProductID filter. Here is the resulting code:

    On this page I demonstrated a simple query in Excel to draw data from an Access database. I hope this explanation for how to create queries has been useful to you. I've seen some criticism of this approach (i.e. saving DQY files and calling them from VBA) but I think it's actually better than hard-coding your queries into VBA. With this approach, you can update your queries without having to edit the VBA, or worse, let someone else touch your VBA code. The VBA code should be written to operate independently of the query, so the query can be changed without detrimental effects on the VBA code. Then you could have someone responsible for managing the queries, and someone else responsible for writing VBA. For example, if you use the method above, you could easily create a bunch of queries and write a macro that takes the dqy filename as a parameter and executes it. That code would be much more portable and flexible than VBA code that has the SQL coded directly in it.