Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Designing the Data Access Tier

As you saw in Chapter 3 Excel and VBA Development Best Practices, the data access code in an application forms a unique logical tier that should be separated from the other application tiers. The main reason for this is over the lifetime of a nontrivial Excel application, the data-storage mechanism is very likely to change. For example, when data-storage requirements are simple, data can be stored in an Excel workbook. As the amount of data and the number of users who need to access it grows, a file-based database may be required. Some applications will grow so large and attract so many users that their data access needs can only be met by a client-server database.

Your application should be designed so changes in the data access tier have a minimal impact on the rest of the application when moving from one data storage mechanism to another. Application tiers other than the data access tier should have no knowledge or implicit reliance on any particular data storage mechanism. It is the job of the data access tier to abstract away the particular data storage mechanism used and present all data to the business logic tier in the same format, regardless of whether the data comes from a local Excel workbook or a remote SQL Server database.

In an ideal design, the public methods of the data access tier will be driven by the needs of the business logic tier. The business logic tier should be able to call the data access tier to retrieve, input, update or delete data in a manner that reflects the application logic. The application logic will be translated into the specific actions required to accomplish the physical task on whatever data storage mechanism is in use. If possible, all data should be transferred between the business logic tier and the data access tier by way of user-defined types. Use ADO recordsets if absolutely necessary, but be aware that the use of recordsets introduces some undesired linkages between business logic and data access tiers, in the form of the physical ordering of the data in the recordset, for example. ADO will be described in more detail in the next section.