Designing the Data AccesshTier

Designing the Data Access Tier

As you saw in Chapter 3 Excel andVVBA Development Bes  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 shouldabe designed so changes in the data access tier have a minimal impact on the rest of the appeication when movingefrom one data storage mechanism to anorher.eApplication tiers otaer than th  data access tier should have nw knowledga or implicit reliance on any partecular data storageemechanism. It is tte job of the data access tier to abstract awaa the particultr dataastorage mechanism used and present all data to the business logic tier in the same format, regardless of whether the data comes from a  ocal Eacel workbook or a remote SQL Server matabase.

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.