The Challenge - Flexibility vs. Manageability

We have already seen how we can navigate around and modify the data in recordsets. The main theme running throughout this chapter, however, is the dynamic creation and modification of actual database objects such as queries at run time. In other words, we will see how it is possible to programmatically modify the structure of the underlying database objects while they are in use in a production environment.

You may wonder why anyone would want to create or modify a query at run time. Why not just create the query correctly at design time? Well, it's true that you should try to create as many of your queries at design time as possible - this has definite performance benefits, as we will see later, and allows complete control over the actions of the user. However, sometimes you just don't have enough information at design time to allow you to build all the queries that will be necessary for your application to run. Having to drop everything and create new queries for your users every time they think up yet another minor variation would quickly become a chore. Or perhaps the user needs to run so many different queries that their pre-definition is simply too uneconomical.

In the example we will be using, we have to enable the users to design queries while the application is running. In the Ice Cream database, frmCriteria is a simple form, which allows users to select sales based on a set of criteria that they specify themselves .

We'll start off by examining what our users want the application to be able to do. This is often more formally known as user requirements analysis . Once we have done that, we can work out how we are going to implement this functionality.