Visual Basic Expert Solutions

book coverQUE

Chapter 5

Data Management and Data-Driven Programming

By Michael McKelvy


Visual Basic 4 is a very powerful programming language. With it, you can create applications to balance your checkbook, chart daily temperature changes, or calculate the stress load on an airplane's landing gear. Most applications you create have to handle data in some form. A program typically needs to get input from and deliver output to the user. The program may also need to store results for later use.

Advanced programs may handle a lot of data from various sources: data from the primary user, data from other users, data from previous calculations, data from other applications, or data from outside sources. With all this data flying around inside the computer, you need an efficient way to handle it. This is where data management comes in.

If you need to know how to manage data better, stay tuned. This chapter discusses the following topics:

Most of the concepts put forth in this chapter may be applied to any data-management situation and can be used with a variety of database management systems. Discussion and examples in this chapter, however, will focus on the use of the Jet database engine, particularly Access databases.

Explaining Data Management

Data management is a process primarily concerned with the following three objectives:

In addition to these primary objectives, your particular application may need to meet other business objectives. These can include speed of data retrieval, minimizing storage space, and ease of programming.

These objectives are not completely independent, and in many cases, tradeoffs between them must be made. Which objective has priority is determined by the tasks that the system must accomplish and the applications that it must support.

Data management is a process or logical approach to handling the objectives outlined above. It is not the physical means by which information is stored. Data management can be applied to any type of file system. If you have previously written programs that store data in a file and have given any thought to the logical layout of that data, then you have done some data management. In fact, if you are an overly ambitious programmer, you can apply data-management concepts to the writing of your own database management system. (Actually, by writing a small file storage and record indexing system, I gained a great appreciation for the complexity and power of commercial database management systems.)

A relational database management system (RDBMS) facilitates the storage and retrieval of data. The RDBMS is made up of a file system and an engine that handles the accesses to the data. The beauty of an RDBMS is that it frees the programmer from the details of reading data and writing data on the disk. It also provides a consistent interface with the data. This is illustrated in figure 5.1.

Fig. 5.1 Data access is simplified with a relational database management system.

With a system of flat files, you have to define the file structure of your data in every program that accesses the file. With an RDBMS, the file structure is defined once, then stored in the database itself. When you need to access a particular piece of data (a field), you send a request to the database engine asking for the field by name. If you store name data in a flat file, for example, you need to tell your program that a person's last name is contained in the first 20 bytes of each record (or wherever it is located). With an RDBMS, you specify the field name (Lastname) to retrieve the data. Listing 5.1 shows the difference between these two approaches. Both code segments assume that all the appropriate file open functions have been performed.

Listing 5.1 The Difference Between Code Used to Retrieve a Field from a Text File and a Database File

'*************************************
'Get the last name from an ASCII file.
'*************************************
Line Input #1, Inpdat$
Lname = Mid(Inpdat$, 1, 20)
'*******************************
'Get the last name from an RDBMS
'*******************************
Lname = OldTbl("Lastname")

If you are going to use the data with only one program and the format of the data is never going to change—no new fields, the fields don't change size—either method will work. But what if things do change? (They always do, right?) Then an RDBMS makes things much easier. Suppose the length of your last name field changed because you didn't allow enough characters, for example. With sequential file access, you have to change the code for obtaining the last name in every program that accessed the file. This is in addition to actually changing the format of the file itself. With an RDBMS, all you change is the length of the field in the database structure. All programs that access the file remain unchanged because they never knew the field length to start with.

Using a Database Management System

OK, now you're convinced that you can't live without an RDBMS for your next project. How do you get your data into it and use it?

Follow these steps to set up the database (or databases) in an RDBMS for your project:

  1. Organize your data into logical groups (tables).
  2. Determine the relationships between the tables.
  3. Create the database file and define the structure of your tables to the database.
  4. Load your data.

The first two items of the list are the design phase of database management. Good design is extremely important. If the design is a good one, other aspects of data management are made much easier. If the design is bad, operations performed on the database are inefficient and can potentially lead to errors in your data.

Sample Case Definition

A sample case will be used for discussing the data-management concepts presented in this chapter. This sample case calculates the cost and delivery information for fuel assemblies for a nuclear power plant. The production of the fuel assemblies requires several stages of material deliveries and process services, as shown in figure 5.2.

Fig. 5.2 Producing fuel assemblies requires multiple processes performed by multiple vendors.

The production and delivery of one component must be completed prior to the start of production for the next component with each process or material delivery being handled by a separate vendor. Each vendor has its own contract with the utility receiving the fuel. The production of each component requires a specific amount of time to complete. Since the target delivery date of the final product (the assemblies) is known, and the production time requirement for each component is known, a schedule for the start and completion of each stage of production can be calculated. In addition, since each component is produced by a separate vendor, a cost calculation is required for each component. The final cost of the assemblies is the sum of the component costs.

Therefore, the application developed for the utility must calculate the demand for each raw material or service, the timing of deliveries of materials from each vendor, and the cost of each component. For the remainder of the chapter, the application is referred to as CostPlan. The data flow for the application is shown in figure 5.3.

Fig. 5.3 The data flow for the calculation illustrates the complexity of the data-management tasks.

The main business objective of CostPlan is to minimize the cost of the fuel assemblies. This means getting material at the lowest price from competing vendors, and minimizing the amount of time that the fuel is in production. To determine a minimum cost requires multiple scenarios to be run. The management of all the input and output for the multiple scenarios is what makes an RDBMS so valuable to the calculation.

The information used by CostPlan comes from a variety of sources. Much is input by the application's users, but two databases come from outside sources. One is a fuel type database, which is generated by another department in the company. The other is a table of raw material and service quantities provided by one of the vendors. Because of the source of these databases, the design of the data-management system needs to account for any special needs, such as security or data access modes.

Database Design

The design of the database(s) for an application depends on the task the application performs, the sources of the data, and specific requirements of the user. While the topic of database design is much too extensive for a complete treatment in this chapter, we will present some of the basics of good design as a foundation for other data-management topics.

The main activities in the design of a database are as follows:

Determining the Data Needs

The first step in designing a database is to determine what data needs to be stored. This is done by performing an analysis of the application and by talking to the users of the application. This determination process not only tells you what data is needed, but also provides information about the sources of the data, security requirements for the data, and any data validation that needs to be performed by the application or the database engine. In addition, the analysis of data requirements should help you establish the relationships between different parts of the database.

For CostPlan, the demand portion of the calculation requires the following information:

The delivery portion of CostPlan requires information about the final delivery date of the assemblies and the lead times required for each step of the manufacturing process.

Finally, cost calculation needs information about the cost of each process on a per unit basis and payment schedule information. The cost information is further broken down into base cost and escalation factors (how much the cost increases over time).

Data Sources

Determining the sources of the data also provides you with essential design information. If the data is from an outside source, the design of that particular database or table may be beyond your control. In this case, you must tailor the design of your database and application to mesh efficiently with the external data. Also, because the users of your application are not responsible for maintaining or updating the data, this will determine the access level that your application uses for the data. Typically, if your application has no data maintenance functions for a database, read-only access should be used for that database.

CostPlan uses two outside information sources.

The remaining data, batch requirements information, and vendor contract information will be entered and maintained by the CostPlan users. The rest of the design discussion will focus on this information.

Grouping Data in Tables

After determining what data is needed and which data is to be created and maintained by your application, you will need to group the data into separate tables and possibly separate databases. One reason for placing some data in separate databases is to segregate data that is used only internally from data that is created by your application, but shared with other applications or users. Placing this shared information in separate databases makes access control easier. This was done with the enrichment and fuel type information for CostPlan.

For all the data you are maintaining, you will want to store related information in tables. Each table should correspond to a particular topic, and data in the table should be easily identifiable as belonging to that topic.

From CostPlan, two major information topics are not provided by outside sources. These topics will be the basis of two tables: BATCHINF for the batch requirements information and CONTRACT for the vendor contract information. The structures of these two data tables are presented in Tables 5.1 and 5.2. The Batch Information table (see Table 5.1) contains user input about batch requirements. The Contract table (see Table 5.2)contains information about material deliveries and pricing for each vendor.

Data Normalization

Once the data has been grouped for inclusion in tables, review the tables for redundant data. Even if all the information in a table is related to the same topic, you may have repetitive records in the table. In this case, further separate the data into other tables to eliminate the redundancy. Separating the information into multiple related tables to eliminate data redundancy is called data normalization.

As an example, consider the youth program from Chapter 4, "Advanced Database Front Ends." It would be possible to store all the information about each youth in a single record of one table. This information would include the youth's name, address, phone number, parents' names, and so on. Figure 5.4 shows a table of information organized in this manner.

Fig. 5.4 Youth and parent data stored in a single table is an inefficient design.

This design will work fine as long as there is only one youth per family (no brothers or sisters). If there are siblings in the youth group, there will be redundant information— address, phone number, and parents' names—for the related youth. Instead of allowing this redundant information, a better design is to separate the information into two tables: one for the youths' names (and other personal information) and one for the family information (the data that might be common to two or more youth). These tables are then related by a key field, in this case, an ID assigned to each parent record. Figure 5.5 shows the two tables resulting from the elimination of redundant data.

Fig. 5.5 Using separate tables for the parent and youth data eliminates redundancy.

For the CostPlan data, looking at the contract information shows that there can be several prices for materials over the life of the contract. Using a single table for all the contract information results in repeating much of the information for each different price level. It is therefore necessary to create a different table to contain the price information. The same problem exists with the delivery and payment information for the contract. The final design for CostPlan results in three tables for contract information. These tables and their relationships are illustrated in figure 5.6. The detailed data structure of the tables can be found in the COSTPLAN.TXT file on the companion CD.

Fig. 5.6 Creating multiple related tables eliminates redundant data in CostPlan.

Types of Tables

A final consideration in the design of the database is the type of tables used for the information. In this discussion, we are specifically talking about two types of tables, temporary and permanent. A permanent table is one that contains data that is necessary for multiple calculations or reports, and is not easily reproducible from a calculation. A temporary table is one that is needed only for the current calculation (or other task) and may be discarded upon completion of the calculation.

In the design process, you must decide what information needs to be permanently maintained and what can be stored in temporary tables. Typically, if the data can be obtained by a quick calculation from a single source, it should be stored in a temporary table and reproduced for each calculation. On the other hand, if the data is user input or is created by a complex and time-consuming calculation, you should create a permanent table for it.

You may also want to use a temporary table to hold a copy of the information in another table. In the CostPlan application, for example, we want to maintain the inventory information on a permanent basis. However, for some of the calculations, the inventory data needs to be modified as the calculation proceeds. By using a temporary copy of the data for the calculation, the original inventory data is preserved for future use.

For CostPlan, the final output of the calculation (batch demands, delivery schedules, and batch costs) needs to be kept as a permanent table. The information in these tables is needed as input to other parts of the CostPlan system, and the users need to be able to generate a variety of reports from the data. CostPlan also makes use of a temporary table for the tracking of material inventory during the calculation. The use of this table will be covered later in the section "Using Temporary Tables."

Creating the Database

Creating the database for an application involves creating the actual database file, then defining the tables and fields of the database structure. These tables and fields come from the design information you developed using the methods described in the design section of this chapter.

The database, tables, and fields are created with the Jet engine using the data access objects (DAOs). These objects are like handles that enable you to access and manipulate data. The DAOs have methods associated with them that enable you to perform the operations on the database. Listing 5.2 demonstrates how the DAOs are used to create the database and one of the tables of the sample case.

Note: A workspace provides an area in which the database engine can work. Within each workspace, you can have multiple databases and tables open, but you may have only one user and one set of transactions. By using multiple workspaces, you can handle multiple transaction sets.

Listing 5.2 DAOcost.txt How to Create the CostPlan Database

'*****************************
'Dimension data access objects
'*****************************
Dim Ws As Workspace, NewDb As Database
Dim NewTbl As TableDef, F1 As Field, F2 As Field, F3 As Field
Dim F4 As Field,F5 As Field, F6 As Field
'****************
'Set up workspace
'****************
Set Ws = DBEngine.Workspaces(0)
'***************
'Create database
'***************
Set NewDb = Ws.CreateDatabase("A:\COSTPLAN.MDB", dbLangGeneral)
'**********************
'Create tabledef object
'**********************
Set NewTbl = NewDb.CreateTableDef("Contract")
'********************
'Create field objects
'********************
Set F1 = NewTbl.CreateField("CompType", dbText, 6)
Set F2 = NewTbl.CreateField("Quantity", dbInteger)
Set F3 = NewTbl.CreateField("Price", dbSingle, 30)
Set F4 = NewTbl.CreateField("CompDate", dbDate)
Set F5 = NewTbl.CreateField("Vendor", dbText, 6)
Set F6 = NewTbl.CreateField("ContractID", dbText, 3)
'**********************
'Add fields to tabledef
'**********************
NewTbl.Fields.Append F1
NewTbl.Fields.Append F2
NewTbl.Fields.Append F3
NewTbl.Fields.Append F4
NewTbl.Fields.Append F5
NewTbl.Fields.Append F6
'************************
'Add tabledef to database
'************************
NewDb.TableDefs.Append NewTbl
NewDb.Close

As an alternative to using DAOs to create tables in a database, you can use SQL statements. With SQL, you can create, alter, or delete tables in a database. The code in Listing 5.3 uses an SQL statement to create a new table in an existing database.

Listing 5.3 SQLCost.TXT Using SQL Statements to Create the Table

'*****************************
'Dimension data access objects
'*****************************
Dim OldWs As Workspace, OldDb As Database
'*****************************
'Set up workspace and database
'*****************************
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("A:\COSTPLAN.MDB")
'******************************************
'Set up SQL statement to create a new table
'******************************************
SQLCreate = "CREATE TABLE Batchinf (BatchID TEXT (6),FuelTp Text"
SQLCreate = SQLCreate + "(6), NumAsm INTEGER, DelvDate DATE)"
'*********************
'Execute SQL statement
'*********************
OldDb.Execute SQLCreate
OldDb.Close

Entering Your Data

Once the database and all its associated tables have been created, you can enter data into the database. If part of your application includes a user interface for data entry, this is one way for you to enter data as well. If you already have existing data in electronic form, you could also write a short program to import the data.

As part of the sample application, a data entry form is needed to enable the user to enter batch information. This form is shown in figure 5.7.

Fig. 5.7 A data-entry form enables the user to enter and modify batch information.

The data-entry form shown in figure 5.7 was created using the data control and bound controls. These are design objects that simplify the development of some data entry and display screens. (A more detailed discussion of database front ends may be found in Chapter 4, "Advanced Database Front Ends.") The form is set up as follows:

  1. Draw the data control on the form.
  2. Set the DatabaseName and RecordSource properties of the control to attach it to a database and recordset. For the sample case, DatabaseName is set to A:\COSTPLAN.MDB and RecordSource is set to Batchinf.
  3. Draw a text box on the screen for each field in the recordset—a total of four.
  4. Set the DataSource property of each text box to Data1.
  5. Set the Datafield property of each box to the fields in the recordset, as shown in Table 5.3.
  6. Place label controls on the form as prompts for the fields.
  7. Place two command buttons on the form, one to add new records and one to delete the current record. The code to attach to the Click events of these two buttons is shown in Listing 5.4.

Listing 5.4 Using Program Code to Add or Delete Records in a Database

'******************************
'Code for add new record button
'******************************
Data1.Recordset.AddNew
'*************************************
'Code for delete current record button
'*************************************
Data1.Recordset.Delete

Table 5.3

Table 5.3 DataField Settings for the Four Text Boxes

Text Box              Name            DataField Setting

Text1                     Batch              ID

Text2                     Fuel                Tp

Text3                     Num               Asm

Text4                     Delv                Date

Data Validation

Data validation is concerned with ensuring the accuracy of the data in the database. Validation is usually performed by checking a given piece of data against a list or range of acceptable values. This is data field validation. There are, however, some validation requirements that are applied to an entire record (such as comparisons between two fields), or to groups of records.

Data validation can be implemented in three different processes:

Each validation technique is covered in this section.

Note: Validation of groups of records is typically handled by a batch process only. Field and record validation can be handled by any of the three processes. The process used depends on the particular situation.

Engine-Level Validation

Engine-level validation is performed at the field and record level by the database engine. The validation rules for fields and records are embedded in the definitions of the tables in the database. When data is added to or changed in a field or record, the engine checks the entry against the validation rule. If the data meets the criterion of the rule, the data is stored to the database. Otherwise, the user is informed that a rule has been violated and is given the opportunity to correct the data.

Field validation rules typically compare the contents of a field to a value or set of values. The rules are usually expressed in terms that will return either a True or False value. An example of this type of rule is Price > 1.00.

Record validation rules are typically used to compare two fields in the same record. For example, you might have a rule that verifies that the retail price of an item is greater than the wholesale price.

For Access databases used in Visual Basic, the validation rules are simple value checks. These rules cannot contain any user-defined functions or comparisons to variables.

The advantage of engine-level validation is that the rules are enforced whether the data is changed by your application, by another application, or by someone directly manipulating the database with Access or another product. Therefore, the rules are consistently applied for any data transactions.

Program Validation

Program validation of data is performed by your application as users enter data or as data is added by a calculation. The field and record validation rules can include a greater complexity than is possible with engine-level validation rules. You can either include user-defined functions in the validation rule or compare the contents of a field to a variable within the application.

Program validation can also accomplish some tasks that are difficult with engine-level validation. You may have a data entry function where either of two fields may be left blank, but both fields cannot be blank. This rule is hard to code in the engine validation, but easy to set up in a program.

Program validation can also be used to compare the value of a field in one table to the contents of another table. This cannot be accomplished with engine-level validation. For CostPlan, program validation would be used to ensure that the fuel types entered by the user in the batch requirements information exist in the fuel type database.

Note: With Access databases, you can establish a relationship between two tables in the same database and tell the Jet engine to enforce referential integrity. This performs the same function as the program validation of comparing one table to another. This is not possible for CostPlan because the data resides in separate databases. Also, referential integrity cannot be enforced by Jet on non-Access databases. Referential integrity is further discussed later in the section "Data Integrity."

Another use of program validation is to check a value against a list of valid entries. Then if the entry is invalid, pop up the list of valid entries from which the user may choose. You may wonder, "Why not just use a List control for the user input?" If the user is familiar with the data being entered, it is often faster to type an entry than to navigate a list of values each time the field is edited. By checking the input value against the list, then showing the list if the entry is invalid, you give the user the best of both worlds—rapid data entry and good data validation.

Note: While a combo box combines the capabilities of a list and a text box, it does not restrict the user to entering only items that are contained in the list portion of the control.

Offline or Batch Data Validation

Offline or batch data validation carries program validation one step further. This validation technique is actually a program that checks the contents of an entire table against a set of validation rules. The rules can be for individual fields or records, or they may be used to test a group of records. For example, you may want to verify that the average value of a field is within a certain range, even if some individual records are outside the range.

In the CostPlan application, one calculation (not part of the sample case) needs to verify that the total number of assemblies in a group of batches exactly matches the number of assemblies used in the reactor. This verification (or validation) is performed prior to the remainder of the calculations in the module.

Batch validation can also be used in situations where a data entry operator is putting data in the database, but another person is verifying the data before it is posted. The data entry operator may not know how to correct an invalid entry. For example, someone transcribing medical notes may input an invalid diagnosis, but if given a list of valid entries would not know which to choose. A batch validation could be used to create a report of invalid entries, along with the record number containing the entry. Then a more knowledgeable person could review the report and input the correct information.

Data Integrity

Like data validation, data integrity is concerned with maintaining the accuracy of the information in a database. In this case, it is concerned with maintaining the references between tables. This is called referential integrity. A reference is a link between two tables, such as the parent ID that links the youth and parent tables in the youth information system shown in Chapter 4, "Advanced Database Front Ends." The main threat to referential integrity in that example occurs when a parent record is deleted or the parent ID of a record is changed. If the parent ID listed in a youth records refers to a parent record that no longer exists, the youth record is said to be orphaned. What this means for an application is that some of the information about the youth is no longer available.

For Access databases, referential integrity may be enforced at the engine-level. You can specify in the set up of the relationship between two tables whether referential integrity will be enforced. If you choose to have it enforced, you may also choose to allow updates and deletions to be cascaded. Cascading means that a change to a parent record will also be made in the child records related to it. Also, if a parent record is deleted, the related child records will be deleted. Figure 5.8 shows the relationship between two tables and shows an orphaned record.

Fig. 5.8 Referential integrity maintains the relationship between two tables.

Engine-level enforcement of referential integrity can be performed by the Jet engine only on Access databases, and only between related tables in the same database. For all other relationships, enforcing referential integrity is the responsibility of the application.

Structured Query Language (SQL)

Structured Query Language (SQL) is a set of commands that is used to manipulate information in a database. SQL commands can be used to do the following:

SQL commands for retrieving records are the heart of many data-management tasks. These commands enable you to set up recordsets from a single table, or combine fields from a group of tables. They also enable you to specify the order in which the records are presented, and filter the records returned by a selection criteria (for instance, deliveries made after 10/31/94).

SQL commands can also provide you with summary information about the records in a recordset. The commands can be used to provide record counts, summations and averages of a field, maximum and minimum values of a field, or the standard deviation of the values of a field. For these functions, one SQL command can take the place of many lines of equivalent program code and can perform the functions many times faster than the program could.

Database Maintenance

If you create a database application, you will at some time need to perform maintenance on the database. This is not maintenance of the records or everyday housekeeping, but occasional operations that are required to keep your database operating at peak efficiency.

The main task to be performed on your database is compacting. This operation eliminates any wasted space in the database and reduces the database file size. The reason this operation needs to be performed is that when records or tables are deleted, the Jet engine does not automatically recover the space that was previously used by those records. Other records may be written into the space, but over time, you end up with a fair amount of unused space in the database file. This takes up disk space and can slow the performance of data access operation. Wasted space can become a big problem if an application uses a number of temporary tables (discussed later in the chapter). The way to get rid of unused space in the database is by using the CompactDatabase method. This method copies the information from the current database to a new database. The old database may then be deleted, and the new database renamed to the old name. The following code shows how the COSTPLAN.MDB database would be compacted and the original database kept as a backup.

DBEngine.CompactDatabase "COSTPLAN.MDB", "TEMPCOST.MDB"
Kill "COSTPLBK.MDB"
Name "COSTPLAN.MDB", "COSTPLBK.MDB"
Name "TEMPCOST.MDB", "COSTPLAN.MDB"

Note: If you are attempting to compact a database, you must have exclusive access to it. That is, no other users may be working with the database. Also, the Jet engine can compact Access databases only. If you are working with other database formats, you need to use other methods to recover unused space.

The other database maintenance operation that you may have to perform (unless you are extremely lucky) is a database repair. Database management systems are reasonably stable, but from time to time, something will occur that will corrupt your database. You usually find this out when a user attempts to open a database and gets a message that there is a problem with the database. If this occurs, you will need to attempt to repair the database using the RepairDatabase method as shown in the code below.

DBEngine.RepairDatabase "COSTPLAN.MDB"

A typical problem encountered when a database is corrupt is that the record count for a table does not match the physical number of records stored. This can occur if processing is interrupted while a record is being added. In this case, the repair function resets the record counter to the physical number of records.

One final note about database maintenance. As with all other files, you should back up your database on a regular basis. This is your best protection against a number of threats to your data. It is also about the only way to recover your data if a repair operation fails.

Advanced Data-Management Concepts

While many types of applications benefit from the use of data-management techniques, calculation applications may gain the most benefit. These applications often require a means of setting up the initial condition from a variety of data sources; creating, maintaining, and deleting temporary tables; and managing the output of the calculations. For many calculation applications, the volume of data handled can seem almost overwhelming. This section looks at several aspects of a calculation program to show how data management is used to make the program more efficient and potentially more flexible.

Setting Up the Input Tables

Once the user has started a calculation, an application needs to set up the input data from individual tables. This means pulling information from external tables and setting up the proper recordsets for processing in the calculation. Often, only a portion of a table is needed, and the application sets the appropriate filters for the recordset. Or if a specific processing order is required, the application creates the recordset with a sort order specified. When using a table, the application must set or create the proper index to establish the desired order. Setting up the input data is one area where SQL statements are invaluable. These statements enable the application to combine data from multiple tables, filter the data, and set the sort order for the recordset.

For the demand calculation, CostPlan opens DAOs to set up the enrichment information table, the fuel type table, the batch information table, and the demand output table. Batch information is sorted by final delivery date so that batches will be processed in chronological order. The other setup operation is to delete the results of previous studies from the demand output table—so that the results of the current study don't get mixed in with previous runs. Listing 5.5 shows the code that set up all the tables for the demand calculation. Note that the initial table setup is performed using SQL statements and data access objects.

Listing 5.5 Opencalc.txt Opening all the Necessary Tables for the Demand Calculation

'*****************************
'Dimension data access objects
'*****************************
Dim OldWs As Workspace, Fltp As Database, Cstpln As Database
Dim Enrsrv As Database
Dim FlTbl As Recordset, Btch As Recordset, Dmnd As Recordset
Dim EnrTbl As Recordset
'Set up workspace and open databases
'***********************************
Set OldWs = DBEngine.Workspaces(0)
Set Fltp = OldWs.OpenDatabase("C:\COSTPLAN\FUELTYPE.MDB")
Set Cstpln = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
Set Enrsrv = OldWs.OpenDatabase("C:\COSTPLAN\ENRICHER.MDB")
'************************
'Open fuel type recordset
'************************
Set FlTbl = Fltp.OpenRecordset("TypeData", dbOpenDynaset)
'**********************************
'Open enrichment services recordset
'**********************************
Set EnrTbl = Enrsrv.OpenRecordset("Services", dbOpenDynaset)
'****************************************************
'Set up SQL statement for batch information recordset
'****************************************************
SqlQry = "SELECT * FROM BatchInf ORDER BY DelvDate"
'*********************************************
'Open batch data recordset using SQL statement
'*********************************************
Set Btch = Cstpln.OpenRecordset(SqlQry, dbOpenDynaset)
'***********************************************
'Delete results from previous demand calculation
'***********************************************
DelQry = "DELETE FROM Demand"
Cstpln.Execute DelQry
'Open demand output recordset
'****************************
Set Dmnd = Cstpln.OpenRecordset("Demand", dbOpenDynaset)

The use of database functions doesn't end with the setup of the data. For each batch in the study, the program looks up information in the fuel type table to get the weight and enrichment of the assemblies. Then the enrichment value is used to look up an entry in the enrichment services table to get the quantities of raw materials and services needed for the batch. This calculation flow is outlined in Listing 5.6. The listing shows only the use of the DAOs. Other calculation steps are indicated by a comment line only.

Listing 5.6 DMDCalc.TXT Showing the Data Retrieval Used in the Demand Calculation

'***************************
'Start batch processing loop
'***************************
Btch.MoveFirst
Do
'***********************************
'Get information about current batch
'***********************************
asmtype = Btch("FuelTp")
asmy = Btch("NumAsm")
btid = Btch("BatchID")
'**********************************
'Find fuel type and get information
'**********************************
FlTbl.FindFirst "FuelTp = '" & asmtype & "'"
enrtype = FlTbl("Enrich")
flweight = FlTbl("AsmWeight")
'**************************************
'Find enrichment type and get constants
'**************************************
EnrTbl.FindFirst "Enrichment = '" & enrtype & "'"
uf6unit = EnrTbl("Feed")
swunit = EnrTbl("SWUReq")
'******************************************************
'Calculate component demands for current batch.
'Demands will be stored to variables uox, cnv, enr, fab
'******************************************************
'***********************************************
'Generate output demand record for current batch
'***********************************************
Dmnd.AddNew
Dmnd("UOXdmd") = uox
Dmnd("CNVdmd") = cnv
Dmnd("ENRdmd") = enr
Dmnd("FABdmd") = fab
Dmnd("BatchID") = btid
Dmnd.Update
'*************************
'Move to next batch record
'*************************
Btch.MoveNext
Loop Until Btch.EOF 'Continue until all batches are processed

Using Temporary Tables

Temporary data tables are those that are needed for a part of a calculation, but whose contents are not needed on a permanent basis. A temporary table can be an exact copy of a table, a portion of a table, or the combination of multiple tables. A temporary table is actually stored in a database for the duration of the calculation. It should not be confused with dynaset or snapshot recordsets that do not exist physically in the database.

If you can create a dynaset type recordset to access part of a table or multiple tables, why do you need a temporary table? You need a temporary table when your calculation has to modify data in the table, but you also want to preserve the original information in the table.

In CostPlan, for example, materials are delivered in discrete quantities. Then the materials are assigned to batches based on the material needs. Because the quantities delivered don't exactly match the quantities needed by a batch, a batch may require multiple deliveries, and a delivery may be a part of several batches. This is illustrated in figure 5.9.

Fig. 5.9 Deliveries of material can be used in several batches.

From a calculation standpoint, it means that the quantity of material in a delivery record must be reduced each time part of it is used to fill a demand. However, the original delivery quantity information needs to be retained for use in the next calculation. Therefore, a copy of the information needs to be created in a temporary table.

Creating a Temporary Table

The easiest way to create a new table is with an SQL statement. The INTO clause of the SELECT statement enables you to store the results of the query to another table. Because this is a physical table in the database, it can be handled like any other table with the DAOs. Because it is a copy of the required information, the data can be modified without any effect on the original table or tables. Listing 5.7 shows how a temporary delivery information table can be created for the CostPlan application.

Listing 5.7 Creating a Temporary Table Using an SQL Statement

Dim OldWs As Workspace, OldDb As Database
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("A:\COSTPLAN.MDB")
SqlQry = "SELECT * INTO TempDelv FROM Deliveries"
OldDb.Execute SqlQry

Once created, the new table can be accessed with standard DAO methods as shown in the following code:

Dim OldTbl As Table
Set OldTbl = OldDb.OpenRecordset("TempDelv",dbOpenTable)

Deleting the Temporary Tables

Deleting a temporary table is easily accomplished using an SQL statement. For the table created above, the following lines of code will close and delete the table:

OldTbl.Close
SqlQry = "DROP TABLE TempDelv"
OldDb.Execute SqlQry

One problem with creating and deleting temporary tables is that it can increase the size of your database, even though you end up with the same amount of information in the database that you started with. This is because the database size is increased when the table is created, but the space is not recovered when the table is deleted. You can recover the space by running the CompactDatabase function as part of your database maintenance.

Putting Temporary Tables in a Separate Database

One way to avoid the size increase in your database when using temporary tables is to place the temporary tables in a separate database. This way you can simply delete the entire database when the calculation is finished. The SQL statement used to create a table can be modified to direct the table to a different database. The other database must already exist; it will not be created by the SQL statement. The code in Listing 5.8 shows how you can create a new database and store your temporary table in it.

Listing 5.8 Tempdata.TXT Storing the Temporary Table in a Separate Database

Dim OldWs As Workspace, OldDb As Database, NewDb As Database
Set Ws = DBEngine.Workspaces(0)
'***********************
'Create the new database
'***********************
Set NewDb = Ws.CreateDatabase("A:\COSTTEMP.MDB",dbLangGeneral)
'*******************************************************
'Open the source database and create the temporary table
'*******************************************************
Set OldDb = Ws.OpenDatabase("A:\COSTPLAN.MDB")
SqlQry = "SELECT * INTO TempDelv IN Costtemp FROM Deliveries"
OldDb.Execute SqlQry
'************************
'Open the temporary table
'************************
Dim OldTbl As Table
Set OldTbl = OldDb.OpenRecordset("TempDelv",dbOpenTable)

After the calculation has been completed, you can close and delete the temporary database. The space used by the database is then released for use by any other files. Your original database will not have been impacted by the use of the temporary table. The following code closes and deletes the temporary database.

NewDb.Close
Kill "A:\COSTTEMP.MDB"
Kill "A:\COSTTEMP.LDB"

Note that in the above code, two Kill statements were used. This is because Access databases consist of two files, one with the LDB extension, and one with the MDB extension. If you are using other database types, you may encounter a similar situation.

Note: LDB files keep track of the record locks in the database file. The information in the LDB file is not accessible by your programs.

Duplicating with the Clone Method

You may have noticed at some point in reviewing the user's manuals or help files for Visual Basic that the recordset object supports a Clone method that makes a duplicate of the recordset. The duplicate recordset created in this manner is not a separate table. It is a duplicate handle or entry into the same base information. Therefore, if you change the data in either the original or cloned recordset, the data in the underlying table is changed. This method then does not accomplish the objective of preserving the original data, which is why a temporary table is used in the first place.

Output Data Tables

As previously stated, a calculation program generally produces an output table or set of tables. These tables are usually already in existence when the calculation is run, but you may want to include code in your application that creates the table(s) the first time the calculation is run. Optionally, you may want your application to check for the existence of the table before the run and create the table if it doesn't exist. This is good practice if your users may be creating multiple sets of data for different studies.

You can check for the existence of the table in either of two ways. First, you can use the TableDefs collection of the database to see if it contains the table. This method is shown in Listing 5.9.

Listing 5.9 ChkTable.TXT Determining if a Specific Table Exists

Dim OldWs As Workspace, OldDb As Database
Dim TblDf As TableDef
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
tblcrt = True
'*************************************************
'Scan table definitions for the desired table name
'*************************************************
For Each TblDf In OldDb.TableDefs
If TblDf.Name = "Costout" Then
tblcrt = False
'*************************************
'Exit the loop when the table is found
'*************************************
Exit For
End If
Next TblDf
If tblcrt Then
'*********************************
'Create table if it does not exist
'*********************************
Else
'*********************
'Open the output table
'*********************
End If

With the second method, you just try to open the table. If the table does not exist, an error will be generated, and you can create the table as part of your error-handling routine. If you have multiple tables that you are handling in this manner, you need to be able to tell the error handler which table to create. This can be done by setting a global variable to the name of the table you are trying to open. Listing 5.10 shows the components of this type of file checking.

Listing 5.10 Error.txt Using an Error Handler to Create a Table When it Doesn't Exist

Sub OpnTables()
Dim OldWs As Workspace, OldDb As Database, OldRc As Recordset
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
'********************
'Set up error handler
'********************
On Error GoTo HandlErr
'************************
'Try to open output table
'************************
Set OldRc = OldDb.OpenRecordset("Costout", dbOpenTable)
On Error GoTo 0
Exit Sub
HandlErr: ' Error-handling routine.
Select Case Err.Number ' Check error number.
Case 3011
'******************************************
'If output table does not exist, create it.
'******************************************
End Select
'*********************************
'Return to statement opening table
'*********************************
Resume
Exit Sub

Case Management

Complex applications are never built to run a calculation once and then be discarded. They are built to enable the user to analyze many cases of a problem or run new cases on a regular basis. Many times an application is used to handle parametric studies. A parametric study is a series of cases where only certain data is changed and the changes in the output are analyzed. Whatever the reason, users will typically build input data and run a calculation many times over the life of the application.

Users also will usually want to keep the data from each case they run. The data can be archived to a tape or a diskette (if the amount of data is small), or stored in a separate directory on the disk. However, much of the data in each case is unchanged, and storing multiple copies of the data wastes a lot of space.

Note: For some applications, you may need to archive and store the complete information for a case to meet record retrieval or regulatory requirements for the data.

Data currency is another problem that is introduced when users frequently change the input data for an application. It often becomes difficult to keep straight which input belongs to which case. This can lead to errors in the data analysis being performed.

Storing Multiple Cases

Storing multiple cases in the same database and tables provides a solution to many data-management problems associated with multiple runs of an application. Adding a case ID field to the databases enables your application to track the data belonging to each case. This provides the following advantages:

By adding a CaseID field to each table in the CostPlan application (except those provided by outside sources) and adding a case description table, the user's task of running many cases is simplified. For each new case, a new ID is entered and data is changed for the item of interest. For CostPlan, a typical change is the number of assemblies per batch.

Handling Case-Specific Data

Without the benefit of a database system, case management would be extremely difficult. With a database system, recordsets can be easily set up for the case. The user tells the application which case to run, and the system does the rest. The behind-the-scenes handling of the data for case management is just about as easy. Case management takes advantage of SQL's ability to retrieve selected records based on a conditional expression. In an SQL statement, this is handled by the WHERE clause.

In a particular CostPlan run, the user has requested the case identified as "ShortRun". As part of the setup for the run, the program sets up information from the BatchInf table, using only data for the "ShortRun" case ID. Figure 5.10 shows the original BatchInf table and the recordset returned by the setup code. The code for the task is shown in Listing 5.11.

Listing 5.11 Retrieving the Information Needed for a Specific Case

Dim NewDyn As Recordset
inpcas = "ShortRun"
'*******************************************
'Set up SQL statement for creating recordset
'*******************************************
SqlQry = "SELECT BatchID,FuelTp,NumAsm,DelvDate FROM Batchinf"
SqlQry = SqlQry + " WHERE CaseID = '" & inpcas & "'"
'*************************************************
'Create dynaset type recordset using SQL statement
'*************************************************
Set NewDyn = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)

Fig. 5.10 The program builds a recordset from the base table for the requested case.

Once the recordset is created for the desired case data, the calculation is ready to run. Most, if not all, other references or calls to the recordset in the program are unchanged. Therefore, for many applications, the only code change necessary to enable case management is in the setup of recordsets.

Handling Generic Data

As stated above, much of the data for a calculation remains unchanged between cases. This data can be referred to as generic data. The generic data can be further divided into the following two types:

Each type of generic data requires different handling in an application.

If data is truly constant and will never change, no case information needs to be added to the data table. When the data is set up for the calculation, recordsets based on these tables will be set up the same as if case management didn't exist. One table of this type in the CostPlan application is the fuel type table. This table is set up for a calculation using the following code:

Dim FuelDb As Database, FuelTbl As Table
Set FuelDb = OldWs.OpenDatabase("A:\FUELDATA.MDB")
Set FuelTbl = FuelDb.OpenRecordset("Fueltypes",dbOpenTable)

Note that this code opens the table directly. It is not necessary to use an SQL statement to create a recordset, nor is any filter condition needed for the table.

Handling data that can change is a little trickier. You want your program to look for data specific to the case that the user specified. If that data is available, a recordset is created based on the case specific data. If there is no data for the specified case, you want the program to look for generic data. The best way to indicate generic data of this type in a table is to leave the case ID blank, or if you are using numeric IDs, make the case ID 0. This way, your generic data is treated like any other case data but with a special ID.

Listing 5.12 is used to set up contract price information for the CostPlan application. The code uses the RecordCount property of the recordset to determine if any case specific data was found. If not, the code looks for generic data by searching for a blank case ID. If no generic data is found, an error message is presented to the user. In Listing 5.12, the case ID is represented by the variable Rnid.

Listing 5.12 Case.txt Retrieving Either Case-Specific or Generic Data

Dim PriceTbl As Recordset
SqlQry = "SELECT * FROM Price WHERE CASEID = '" & Rnid & "'"
Set PriceTbl = OldDb.OpenRecordset(SqlQry,dbOpenDynaset)
PriceTbl.MoveLast
If PriceTbl.RecordCount = 0 Then
PriceTbl.Close
Blnk = " "
SqlQry = "SELECT * FROM Price WHERE CASEID = '" & Blnk & "'"
Set PriceTbl = OldDb.OpenRecordset(SqlQry,dbOpenDynaset)
PriceTbl.MoveLast
If PriceTbl.RecordCount = 0 Then
MsgBox "No price data available"
End
End If
End If

Comparison Programming

Another benefit of storing information from multiple cases is that results comparisons are made easier. You can include methods in your application that provide your users with comparison tables or graphs to enable them to further analyze the results of their studies. More sophisticated users can even use query tools in Access or Excel (or other programs) to develop their own comparisons.

Without case management, users would have to store copies of the output files or write critical information down for each case. Then to perform a comparison between cases, the data would have to be imported from the various files, or re-entered from the written data. This is an awkward and time-consuming task, and in the case of re-entering data, prone to errors.

With case management, a single SQL statement using the GROUP BY clause can be used to retrieve summary information for the desired fields. If detailed information is needed, a series of SQL queries can be used to retrieve the data for the different cases. In this case an MDI form could be used to create multiple instances of the data display form, one for each case considered. Listing 5.13 shows the SQL statement used to retrieve the total component costs for each case in a CostPlan study. The listing also shows how the results of the query were then fed to a graph control for display as a bar chart. The resultant chart is shown in figure 5.11.

Listing 5.13 Compare.txt Using a Graph to Display Summary Information for Component Costs

'**************************
'Set up data access objects
'**************************
Dim OldWs As Workspace, OldDb As Database, OldRc As Recordset
Dim Cases As Recordset
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
'**************************************************
'Set up SQL statement to obtain summary information
'**************************************************
SqlQry = "SELECT CaseID, SUM(Uoxcst) AS Uox,SUM(Cnvcst) AS Cnv,"
SqlQry = SqlQry & " SUM(Enrcst) AS Enr,SUM(Fabcst) AS Fab FROM "
SqlQry = SqlQry & "Costout GROUP BY CaseID"
'************************
'Create summary recordset
'************************
Set OldRc = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
OldRc.MoveFirst
curset = 1
'************************************************************
'Create one set of data points for each case in the recordset
'************************************************************
Do
Graph1.ThisSet = curset
'**********************************************
'Input cost information for the four components
'**********************************************
Graph1.ThisPoint = 1
Graph1.GraphData = OldRc("Uox")
Graph1.ThisPoint = 2
Graph1.GraphData = OldRc("Cnv")
Graph1.ThisPoint = 3
Graph1.GraphData = OldRc("Enr")
Graph1.ThisPoint = 4
Graph1.GraphData = OldRc("Fab")
Graph1.ThisPoint = curset
'*************************************************
'Set the legend for the set to the current case ID
'*************************************************
Graph1.LegendText = OldRc("CaseID")
OldRc.MoveNext
curset = curset + 1
Loop Until OldRc.EOF
'********************************
'Set the X-axis label text values
'********************************
Graph1.ThisPoint = 1
Graph1.LabelText = "Uranium"
Graph1.ThisPoint = 2
Graph1.LabelText = "Conversion"
Graph1.ThisPoint = 3
Graph1.LabelText = "Enrichment"
Graph1.ThisPoint = 4
Graph1.LabelText = "Fabrication"

Fig. 5.11 Summary results of multiple cases can be displayed in a graph.

Listing 5.14 shows how the detail information for multiple cases would be retrieved and displayed in a series of MDI child windows. Figure 5.12 shows the results of the code for two cases.

Listing 5.14 Details.txt Using the Data-Bound Grid Control to Show Detail Information in Multiple Child Forms of a MDI Form

'**************************
'Dimension child form array
'**************************
Dim NewGrid() As New Form2
'**************************
'Set up data access objects
'**************************
Dim OldWs As Workspace, OldDb As Database, OldRc As Recordset
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
'************************************************
'Create a recordset with just the unique case IDs
'************************************************
SqlQry = "SELECT DISTINCT CaseID FROM CostOut"
Set OldRc = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
OldRc.MoveLast
cases = OldRc.RecordCount
'****************************************************
'Set the number of child forms to the number of cases
'****************************************************
ReDim NewGrid(cases)
OldRc.MoveFirst
csidx = 1
Do
'********************************************************
'Set up information to pass to child form containing grid
'*********************************************************
MydbName = "C:\COSTPLAN\COSTPLAN.MDB"
Mycase = OldRc("CaseID")
MyrcSource = "SELECT * FROM Costout WHERE CaseID='"
MyrcSource = MyrcSource & Mycase & "'"
posmov = 150 * (csidx - 1)
'*******************************************
'Show the child form for the current case ID
'*******************************************
NewGrid(csidx).Show
csidx = csidx + 1
OldRc.MoveNext
Loop Until OldRc.EOF

Fig. 5.12 Detail information for multiple cases can be displayed in multiple child forms.

Data-Driven Programming

Another area where data management can be useful is in enhancing program flexibility and reducing program maintenance. This is done by taking some of the task management functions of the program and designing them into the data itself.

To illustrate the concept, consider a simple example. Banks handle a wide variety of loans, including mortgage loans, car loans, boat loans, home equity loans, and others. From a marketing standpoint, these loans are all different and are targeted at different borrowers. However, from the standpoint of the calculations of payments and amortization schedules, the loans are the same. That is, all use the same equations for their calculations. If you had a different program segment for each type of loan, you would be constantly updating the program as new loan types came out. Instead, one calculation module is created for all loans, and the data contains all the necessary information to drive the calculation, including interest rate, term of the loan, and compounding period. Then when a new loan is created, data for the new loan is added to the system, but the program remains unchanged. This is the essence of data-driven programming.

The loan example is probably intuitively obvious to most people. What is not obvious is how this method can be applied to other situations, and how the method affects the design of both your database and your application program. To further develop this concept of data-driven programming, let's look again at the cost calculation portion of CostPlan.

Traditional Programming Approach

The cost calculation looks at the demand for each component of the production process and matches that to deliveries and their associated costs. This matching process, called allocation, was illustrated in figure 5.9. The demand table used as input for the cost calculation contains a field for the batch ID and a demand quantity and required delivery date field for each fuel component. This type of table structure is referred to as a horizontal table. The demand table is shown in figure 5.13.

Fig. 5.13 The original demand table contains fields for each fuel component.

As each batch is processed, a separate calculation has to be performed for each of the fuel components. This requires setting up a separate deliveries recordset for each of the components, even though all the delivery information is contained in the same table. The code to set up these recordsets and run the calculation is shown in Listing 5.15.

Listing 5.15 Fuelcomp.txt Performing a Separate Calculation for Each Fuel Component

Dim OldWs As Workspace, OldDb As Database, Uoxdlv As Recordset
Dim Cnvdlv As Recordset, EnrDlv As Recordset
Dim Fabdlv As Recordset
Dim Dmnd As Recordset, Costs As Recordset
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
'*************************************************************
'Four recordsets are required for the material deliveriesdata
'*************************************************************
SqlQry = "SELECT * FROM Deliveries WHERE CompType = 'UOX'"
Set Uoxdlv = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
SqlQry = "SELECT * FROM Deliveries WHERE CompType = 'CNV'"
Set Cnvdlv = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
SqlQry = "SELECT * FROM Deliveries WHERE CompType = 'ENR'"
Set EnrDlv = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
SqlQry = "SELECT * FROM Deliveries WHERE CompType = 'FAB'"
Set Fabdlv = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
Set Dmnd = OldDb.OpenRecordset("Demand", dbOpenTable)
Set Costs = OldDb.OpenRecordset("CostOut", dbOpenTable)
Dmnd.MoveFirst
Do
'*************************************************************
'A separate calculation section is required for each component
'*************************************************************
'Assign UOX material to demand
uox = Dmnd("UOXdmd")
'Material assignment from Uoxdlv recordset
'Assign CNV material to demand
cnv = Dmnd("CNVdmd")
'Material assignment from Cnvdlv recordset
'Assign ENR material to demand
enr = Dmnd("ENRdmd")
'Material assignment from Enrdlv recordset
'Assign FAB material to demand
fab = Dmnd("FABdmd")
'Material assignment from Fabdlv recordset
Dmnd.MoveNext
Loop Until Dmnd.EOF

The code in Listing 5.15 will perform the calculation for each component even if the demand for the component is zero. This is one inefficiency of the program. Now consider what would happen if another component was introduced into the production process. This would require a change to the demand table to accommodate the new information and a change to the program to add a new calculation step for the component.

Data-Driven Programming Approach

Now take a look at how this would be handled using a data-driven approach. Because the allocation of delivered material to demands is the same for each component of the process, it would be more efficient to use just one calculation routine. The key to doing this is a redesign of the demand data table. Instead of a pair of fields for each component, the table is changed so that each record contains only four fields: the batch identifier, the component identifier, the demand quantity, and the required delivery date. This type of table is called a vertical table. Figure 5.14 shows this new demand table structure.

Fig. 5.14 The revised demand table contains only four fields.

During the initial setup of the calculation, this demand table is sorted by component type. This enables all the batch demands for a single component to be processed at once. This enables the program to set up only one deliveries recordset at a time, instead of having to create four. Then each time a different component is encountered, the deliveries recordset is re-created to handle the different component. This process is no more time-consuming than creating four recordsets initially. The program for the data-driven method of handling allocation is shown in Listing 5.16. The data-driven programming approach simplifies code maintenance and increases flexibility.

Listing 5.16 Datadriv.txt Code that is More Flexible and Easier to Maintain than Listing 5.15

Dim OldWs As Workspace, OldDb As Database, Delvry As Recordset
Dim Dmnd As Recordset, Costs As Recordset
Set OldWs = DBEngine.Workspaces(0)
Set OldDb = OldWs.OpenDatabase("C:\COSTPLAN\COSTPLAN.MDB")
'**************************************************************
'Demand recordset is sorted by component type and delivery date
'**************************************************************
SqlQry = "SELECT * FROM Compdmd ORDER BY CompType, DelvDate"
Set Dmnd = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
Set Costs = OldDb.OpenRecordset("CostOut", dbOpenTable)
Dmnd.MoveFirst
cmptp = " "
Do
'*************************************************************
'The deliveries recordset is re-created for each new component
'*************************************************************
If Dmnd("CompType") <> cmptp Then
cmptp = Dmnd("CompType")
SqlQry = "SELECT * FROM Deliveries WHERE CompType = '"
SqlQry = SqlQry & cmptp & "'"
Delvry.Close
Set Delvry = OldDb.OpenRecordset(SqlQry, dbOpenDynaset)
End If
'****************************************
'Only one generic calculation is required
'****************************************
'Assign material to demand
qty = Dmnd("Quantity")
'Material assignment from deliveries recordset
Dmnd.MoveNext
Loop Until Dmnd.EOF

Using this data-driven approach, if a component is not needed for a particular batch, no demand record will exist for the component. If there is no demand record, the calculation will not be run for that component. This can make the program run more efficiently.

More importantly, this new table structure and program design can much more easily handle new components in the process. Instead of having to change the table structure to accommodate new fields and adding another calculation step to the program, demand data for the new component is just added to the demand table. No program changes would be required.

From Here...

By necessity, several topics in this chapter were only discussed briefly. The following references will help you learn more about these topics.


| Previous Chapter | Next Chapter | Search | Table of Contents | Book Home Page |

| Buy This Book | Que Home Page | Digital Bookshelf | Disclaimer |


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.