Integrating Excel and Access: Data Access From Excel VBA
Source: O'Reilly Media
VBA gives one the opportunity to take more control of the data; it performs queries that are not possible from the user interface, and one 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, one may run into situations in which one wants 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 the ability to restrict access to that data. One does 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.