Data Access from Excel VBA
(Page 1 of 4 )
In this article, the first of two parts, you will learn how to handle situations in which VBA is the only way to obtain data (such as when you want to restrict access to data). It is excerpted from chapter three of the book
Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Copyright © 2005 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
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.
Next: Writing a Reusable Module for Data Access >>
More Database Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
