Moving From Plodding To Speed — Dynamic Queries and Building Blocks To Export Data to Excel

When the United States was established, the mode of transportation was the horse, or if you wanted to ride in some comfort, the horse and buggy. As time progressed, we moved up to railroads, a major revolution, then zoomed forward with cars and jet planes.

    

Each of these steps represented a tremendous jump in the ability to move from one part of our nation to another.

In last month’s Wizard, I showed you how to move data from Access to Excel. Those techniques required an existing table or an existing query in order to make things work. This month I will show you a technique that does not rely on a query or table existing, but rather on “If you have the data, you can get it into Excel.”
 

The First Building Block:  A Query Exported to Excel

In the October 2016 Wizard, I showed you how to use a query to send data to Excel with the following routine:

Sub ExportQueryOrTableToExcel
   DoCmd.OutputTo acQuery, "MyQuery", "MicrosoftExcel(*.xls)", "", True
End Sub

The concept here is that, if you have a query, sending data to Excel is very simple. For full details in the building blocks, take a look at the October 2016 Wizard.
 

The Second Building Block:  SQL Statements

No doubt you are familiar with queries. However, you may not know that a query is nothing more than a SQL statement. SQL (structured query language) is nothing more than a computer language that is used across many database applications to manipulate data.

If you need to catch up on how to understand the SQL generated by a query, take a look at the June 2005 Wizard.
 

The Third Building Block:  Dynamic SQL

At the end of the day, SQL is nothing more than a text string. Although it may be truly complex and wonderful, at the end of the day, it is just text. This is a critical concept because if it’s just text, that means it can be manipulated within VBA. If you are new to VBA, you will find a variety of tutorials on the web that will help you get started, including the December 2006 Wizard. 

Let’s say you have a table “tblOffices” that includes offices all around the country and you would like take a selection of the data from the table and send it to Excel. To export all the data you could always use the technique I referenced from last month:

DoCmd.OutputTo acTable, "MyTable", "MicrosoftExcel(*.xls)", "", True

Further let’s say that the table has the following fields:

• OfficeKey
• City
• State
• OfficeManagerFirstName
• OfficeManagerLastName
• Etc.

A SQL statement to get everything out of the table would be:

Select * from tblOffices

To get all the offices out of Massachusetts, you would simply change the SQL statement to:

Select * from tblOffices where State = ‘MA’

So in your SQL you could easily just change the ‘MA’ to ‘VT’ which would give you everything in Vermont. For a way to manipulate the string programmatically, take a look at this month’s tip where I explain the replace function.
 

The Final Building Block:  A Dynamic Query

Since we know that the query is nothing more than a SQL statement, which is nothing more than a text string, the question then becomes how do we manipulate the query so that we can send it directly out to Excel.

Well if you know the trick, it is amazingly simple as the code below shows.

For the first example, I will use an existing query called qryUtility.

Sub UpdateQuerySQL()
  Dim db As dao.Database
  Dim qdf As dao.QueryDef 
  Set db = CurrentDb
  Set qdf = db.QueryDefs!qryUtility
  qdf.SQL = “Select * from tblOffices where State = ‘MA’
  Set qdf = Nothing
  Set db = Nothing
End Sub

When you do this and then open up query utility, you will see that it returns everything every record where the state is Massachusetts.

To pull it apart, the first two lines are creating variables. The next couple of lines that start with the word “Set” declare that the DB variable is equal to the current database, and the qdf variable is equal to the query called to qryutility.

The critical statement is:

 qdf.SQL = “Select * from tblOffices where State = ‘MA’

This is where the magic takes place. It essentially says that for referenced query, its underlying SQL statement becomes:

“Select * from tblOffices where State = ‘MA’

To make this even more powerful, we just pass in a couple of parameters and use them in the routine, as you see below:

Sub UpdateQuerySQL(strQryName As String, strSQL As String)
  Dim db As dao.Database
  Dim qdf As dao.QueryDef 
 
  Set db = CurrentDb
  Set qdf = db.QueryDefs(strQryName)
  qdf.SQL = strSQL
 
  Set qdf = Nothing
  Set db = Nothing
End Sub

This more powerful technique allows you pass in two parameters:

strQryName - the name of the query that you would like to manipulate

strSQL - the SQL string that should be contained within the query

Using this approach, you can choose virtually any existing query in your database and change its SQL statement to whatever it is that you would like.

You can now combine that with the statement shown above

DoCmd.OutputTo acQuery, "MyQuery", "MicrosoftExcel(*.xls)", "", True

to send any set of data that in your database to Excel.
 

Conclusion

This month I have helped you understand some building blocks with this and prior Access Wizards:

• Understanding SQL statements,
• Manipulating SQL statements
• Understanding what’s behind queries
• Exporting queries to Excel

Putting these together, you now have to a powerful tool that allows you to take virtually any data within your application and send it out to Excel.

As you are creating applications for yourself or for others, keep in mind that it makes life easier to create small simple tools, that you then assemble to make a powerful tool.