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 data is when you need to perform actions based on information at the row level. While you could certainly pull the data in through the user interface and then write code to perform the actions within Excel on another worksheet, that method would result in duplication of data. When I am writing reports where I need to perform calculations at several subtotals, calculate ratios, and/or weighted averages, I control all of that through VBA. The other issue that will become more relevant as you move through this book is the choice between using Excel to pull the data from the data source and using the data source (Access) to automate Excel. This choice makes a large impact on application design.

Another consideration when using Excel to pull the data is macro security . At some organizations where Excel is not used heavily, or at least VBA is not used heavily, Excel's security will not enable macros that are not signed (high) or that are not installed in trusted locations (very high). This is a great precaution to avoid getting infested with macro viruses, but it makes it difficult to create a home-grown application for use in Excel. You can find out how your security is configured by going to Tools Macros Security from the Excel user interface (Figure 3-1). If your security is set at low, I suggest moving it to medium. If it is set to high or very high, I would change it to medium or speak with your system administrator about moving it to medium. At medium, you can choose whether or not to run macros each time you open up an Excel workbook. If you are unable to persuade your system administrator to allow you to change the setting, you will have to perform the automation from Access.

Figure 3-1. The Excel macro security dialog box, which tells Excel how to handle workbooks with macros

While this book focuses on integrating Excel and Access, most of the content in this chapter can be applied to other enterprise data sources such as SQL Server, DB2, and Oracle. For the purposes of this book, I will stick with Access and occasionally SQL Server.