Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page