Integrating Excel and Access

Chapter 3. Data Access from Excel VBA

Up to this point, all interaction with the data source has been through the Excel user interface. I hope that you have seen how effective it can be in certain situations. You have also used VBA to enhance data access from the user interface. VBA gives you the opportunity to take more control of the data; it performs queries that are not possible from the user interface, and you can now automate many operations.

There are numerous situations when VBA is not only the preferred way to obtain data, but the only way. For example, you may run into situations in which you want to control access to the data by the time of day or another variable. Giving the user the ability to pull the data straight from the user interface limits your ability to restrict access to that data. You do not want people running queries that join a table with several hundred thousand customer records and a couple million financial transactions during the middle of the day. At the same time, though, you also cannot shut down the database to block the users' queries when the database needs to be available to perform official transactions. When a developer runs into a situation like this, an easy solution is to write code to pull data from the database while ensuring that the moment the query runs doesn't interfere with performance. The developer can then protect the code so that this restriction cannot be bypassed.

Another situation when it is preferable to use VBA to pull the ...