Integrating Excel and Access

Summary

This chapter introduced you to the power of integrating Excel and Access. There is still a lot of ground to cover. At this point, my hope is that you understand how to build a recordset by using ADO and DAO. The rest of the chapter discussion and examples were meant to illustrate additional features already built into Excel. Our code just automated it. A side goal is that you are becoming familiar with the objects used in Excel VBA. When you read Chapter 5 on automating Excel with Access VBA, your familiarity with these objects will greatly increase your understanding of that chapter.

The Northwind database will not be used again until we use SQL Server in Chapter 7. For the rest of the examples, you will need to obtain the sample files available at www.oreilly.com. In these sample files, you will find the Excel example above and the other necessary databases and Excel workbooks, although I encourage you to try to enter the code for yourself, as it will help you get a better feel for VBA.

Figure 3-3. The resulting Excel file when the code in Example 3-5 is run showing subtotals instead of a pivot table, as well as details of the beverages category demonstrating how that works with subtotals

Note that there are appendixes at the end of the book that describe the commonly used Excel and Access objects that you use when doing this type of work. If you need help with VBA, there is ...