Access Data Analysis Cookbook

Chapter 6. Using Programming to Manipulate Data

The ability to work with data and database functionality is greatly enhanced when programmed routines can be put to the task. Using VBA and other programming disciplines, developers can create sophisticated applications that reach beyond the capabilities of plain select and action queries.

This chapter offers a number of examples that show how code routines can be used to improve and enhance applications. Arrays are showcased, with discussions focusing on working with multiple dimensions and sorting. One recipe shows how to tap into Excel’s extensive function library from Access, while others introduce simple and sophisticated methods of encrypting data and illustrate transaction processing. Working with charts, getting to the HTML source of web pages, and running Word mail merges directly from Access are also covered. There is even a recipe that illustrates how to build a user-friendly query construction form that lets users point and click their way through selecting criteria and running a query.

Using Excel Functions from Access

Problem

My work involves financial and statistical number crunching. The business data is stored in Access, but I use many of the functions available in Excel to get my work done. Is there a way to use the Excel functions directly from Access, rather than copying and pasting my data from Access into Excel?

Solution

Thanks to the availability of an object model paradigm for coding, it’s relatively easy to hook ...