Integrating Excel and Access

Writing and Using Queries in VBA

One of the nice things about working in Access is that you can write queries in a graphical design mode and save them for use in VBA. While that is useful, you might also want to modify the queries from VBA. This can be as simple as changing the query to a parameterized query, or it can be a situation when you want the VBA to choose which columns of data the query should return. In other cases, you might want to change the query from a query that uses the Sum function on the rows to one that takes an Average or Standard Deviation.

Referring to Queries

You can refer to queries with DAO or ADO. I am going to focus on using DAO in the next examples. However, there is no reason why you can't use ADO and ADOX to do the same thing. I believe that DAO is a little more intuitive and easier to use, but use the one you are most comfortable with.

Earlier in the book, you saw how to pull data from Access when you are in Excel and use the CopyFromRecordset method of the Range object to put the data into the Excel worksheet. You do the exact same thing in Access when you automate Excel; refer to Chapter 3 to refresh your memory if necessary. While in Access, you need a few variables to use the DAO objects: one variable for the database, one variable for a recordset to hold the results of a query, and possibly a QueryDef object if you are going to create and/or modify queries, as we will do in the next examples. Example 5-9 shows how to create a new query that selects ...