You can't build a reliable application unless you use or create a good set of components and combine them appropriately. This chapter teaches you about application ingredients, and describes the following:
"When we were designing the first release of Access, many people on the Access team didn't see the need for separate report objects in the product. As we had already decided that users would be able to print forms, all we really needed to do was to enable forms with sorting, grouping, and a few other report-specific features, and presto-forms would have both personalities.
"Ultimately, we deserted the concept as too confusing for users, because it would give forms quite a few more features and properties to wade through. To this day, however, we're not convinced that it was a bad idea." Tod Nielsen, General Manager, Microsoft Access Business Unit
I used to enjoy rock and ice climbing in my youth. You can take two approaches to scaling a rock face:
Many programmers employ the second approach when climbing their coding cliffs. With an objective in hand (solving a described business need), they begin building tables, then layer application objects on top in whatever order suits them. For these developers, some of the satisfaction of building an application lies in surmounting the unforeseen hurdles that crop up when developing with no clear direction.
Unfortunately, in this ad-hoc variation of both solution development and rock climbing, it is easy to navigate into obstacles that are not surmountable. Having reached an unplanned barrier, the climber or developer must then pull back and find another direction around the problem. On the rock face, this involves "down climbing," which is much more difficult and dangerous than "up climbing." In solution development, this involves reworking or replacing objects that have been partially developed, which is embarrassing and wasteful.
When I was teaching others to rock climb, I would try to stress to them that there is no shame in choosing the first approach (planning) over the second approach (ad-hoc). Excluding the radically macho types, most people can be taught to derive as much raw enjoyment from the sport using a thoughtful approach as from a random one, and with less risk.
And so it is with developers. Programming a solution should not be like unstructured rock climbing or playing a video game-the thrill must not be linked to the resolution of short-term hurdles, but rather to conquering the overall business problem.
I believe that an important milestone in the maturation of an application developer is when he or she learns to derive satisfaction from the creation and execution of a solid development plan.
I discussed planning automated solutions to business problems in Chapters 3, "Preparing for Development," and 4, "Creating Design Specifications." You recall that I focused on these main concepts in the two chapters:
With a grasp of these issues and a development plan in hand, you are ready to begin creating application objects, right? Not so fast. While successful design means creating a good plan, effective development does not mean blindly executing the plan. Rather, the planning process takes grains of sand (facts) and assembles them into a rock wall (the project), but there are multiple routes (project plans) that would get the team up and over the top to the completed solution. The developer's mission is to find the best route to the solution by mixing and matching these ingredients:
In this chapter, we will explore how to blend these ingredients successfully and define the conceptual issues that impact how you select a route up an application development cliff. Along the way, I will expose various elements of the Leszynski Development Framework (LDF) approach to application architecture. (Recall from Chapter 2, "Introducing the Leszynski Development Framework (LDF)," that LDF Standards Precept 2 says "Use a Consistent Application Architecture Model.")
An Access-centric application may have scores or even hundreds of objects. On the surface, the fundamental relationship between these objects is straightforward-queries build on tables, forms get data from queries, and so on.
Looking at application objects as if they were construction materials puts them in a different light, however. You can't build a house with only one shape of wood or a single type of nail. Neither can you build an application with one kind of table or form. It is important to understand the subtle and not-so-subtle "types" or "subclasses" of application objects available for constructing a solution.
Much of the work of defining the attributes of application objects occurs at development time. A specification may call for a data-entry form, and describes the layout of controls and list of events that will populate the form, yet the developer retains some discretion about exactly how to empower the form to achieve the stated objectives.
It is useful for developers to create standard definitions of different Access object types and their roles. These standard object types become the building blocks of applications. The definitions are not rigid and can be modified at development time to suit particular user needs. The definitions are clear and precise, however, to achieve these two results:
Let's consider a simple example. Historically, users of database-centric applications have been exposed to their data through forms that display a single record at a time. This was for technical reasons (limitations in mainframe terminal software), business reasons (the safety of exposing users to a limited set of data), and usability reasons (to minimize user confusion). If your users have this background, they may not be aware of the flexibility of Access forms to present data in various layouts unless so instructed. To allow such users to help design the correct application for their needs, they must be made to understand the difference in capabilities and relevance between single-record, continuous, form/subform, and datasheet form object types.
To create a set of application building blocks, enumerate and describe the standard object types that your development team will use. For example, LDF uses the following attributes to define application object types:
I'm using the term template here in a broader sense than Access does. A form or report template to Access is an object designated in the Options dialog as a Form Template or Report Template. In my parlance, a template object may be used in this fashion as well or may also be used by developers as a source object that is copied, renamed, and modified to create a new object.
In the next six sections, I review the six primary Access objects and define the various LDF application object types for each object. The LDF object type designations are summarized in Tables 7.1 and 7.2.
These designations are my own-no industry standard classifications have been defined for PC database application objects. You may use my designations as they are or use them only as seeds for your own definition discussions.
Table 7.1 LDF application object types.
|Log||Family View||Class Module||Export|
|System||Report Source||Switchboard Menu|
Notice that the LDF object types named in Table 7.1 are often more concerned with user interaction and application requirements than the internal workings of Access. For example, a crosstab query is a legitimate query type in Access, but from an application construction standpoint the purpose of the query is usually to provide records to a Detail Report or Summary Report. Thus, LDF places more importance on the fact the query is a Report Source type than that it's constructed as a crosstab.
When you create your own standard object classifications, you may find that object types have sub-types, and that these sub-types deserve clarification in your architecture model. For example, Table 7.1 notes the LDF form type Query-By-Form, which actually has more than one sub-type (such as the Get form I describe in Chapter 16, "Presenting Data to Users".)
Table 7.2 LDF programming object types.
Defining Table Types
The majority of Access tables hold raw data, as entered by users or application processes. I term these transaction tables because their records describe real-life events: buying, selling, consuming, moving, counting, meeting, inspecting, fixing, calling, and so forth. (Some traditional relational database terminologies call these base tables.)
I group transaction tables into three flavors (see Figure 7.1):
I catalog transaction tables in an application into these three groups.
Sprinkled around transaction tables in the database schema are other table types that serve as supporting players. LDF groups non-transaction tables using these terms and capabilities:
An additional use for archive tables is to store deleted records for later review or retrieval. Deleted data records are usually stored in separate tables from aged (archived) records. See Chapter 15, "Protecting and Validating Data," for an expanded discussion of saving deleted records.
An example of a many-to-many table relationship with a relation table between the data tables.
Figure 7.3 diagrams these various table types.
The LDF table types and the Access objects that use them most frequently.
Depending on your tastes, you can define tables and their purposes more broadly than I have here (for example, transaction and supporting tables only) or devise terminology that is much more detailed than the LDF list. You may also find yourself adding new types of table definitions over time as your application needs dictate. Defining table types and their roles in an application provides a good foundation for developing policies and procedures as well as a common syntax for developers and application designers.
Defining Query Types
Access intrinsically defines nine types of queries:
A query inherits its basic personality from its type, as listed here, and from its source tables. Access strictly defines the personality of each of the listed Access query types, requiring little additional characterization by the developer.
In addition to a query's Access type or action, however, there is a purpose for the object that is related to its role in its host application. The needs of your applications or development style may dictate specific query type definitions. The following list is the expanded type terminology that LDF uses for queries.
A family view query joins parents and children.
A grand join query includes multiple generations plus related lookup values.
While the query types listed here are most often constructed as select queries, a query of a specific LDF type can be defined using various action types (as an example, a family view query might be built as a select, append, or make table query).
Defining Form Types
If you have to choose to put effort into creating standardized object definitions for only one type of Access object, you should choose forms. These objects are the primary device for allowing user interaction with data and, as such, provide the most visible and heavily-used application elements.
The most important forms in your application are data entry/edit transaction forms. Often, one transaction form provides both of these capabilities; in other cases, an application's needs dictate a separation of entry and edit tasks into form pairs. You will want to consider creating form templates for these two form varieties and standardizing button designations, navigation code, validation styles, and property definitions. You can use the form templates and their code as the basis for creating new objects. Alternately, some developers create customized form wizards to build forms matching their company's standardized layouts.
Forms are extremely versatile and thus are used to create other types of application object subclasses. They add functionality to your application by providing a medium for collecting information (as data), displaying information (as messages), and enabling the flow (as switchboard menus). In addition to entry/edit transaction forms, LDF defines these other form types and creates standards for using each type:
Custom alert forms can provide more information and features than a simple MsgBox() dialog.
A class module that is displayed to the user can be created as a form class module, and technically any form or report that has code is loosely defined as a class module. However, Access 97 now provides class module objects that are a sub-type of the module object. Use this type of class module when your object won't have a display view.
A dialog form collects information from the user but does not save the information to a transaction record.
Access's new Filter By Form and Filter By Selection capabilities remove some of the value of homemade query by form and other record selection forms.
It can be helpful to design teams to provide them with hardcopy examples of each form type defined for your organization. This allows the team to understand Access's-and the developers'-abilities with respect to forms, helping the team to design more usable and capable applications overall.
Defining Report Types
When defining the capabilities and properties of your organization's standard report types, it will be tempting to gravitate to one of two extremes. At one end is the belief that reports have no attributes worth standardizing, because they are always defined by the users. At the other end of the spectrum is the temptation to define report subclasses for a wide variety of data sources, grouping and sorting algorithms, distribution methods, and user communities.
Because reporting is more heavily user-driven than form design, many developers evolve a hands-off attitude to reporting standards and let the design team create reporting object definitions on a per-application basis. LDF mostly leans this way, based on the belief that reports are the most widely visible and used component in most applications and thus should be custom tailored to the users' needs. Thus, LDF defines a minimum set of standard report types simply to provide a standardized vocabulary to guide the design process:
Chapter 18, "Creating Expert Reports," discusses additional reporting issues and techniques.
One reality of application development is that the report design process never ends. Whenever the ultimate users of a report (usually managers) have a slow day, an infusion of new management, or a corporate restructuring meeting, reports tend to get redesigned in the process.
Defining Macro Types
The distinct flavors of macros are readily discernible to any Access developer. They do not require a complex level of definition except for naming conventions and a strategy for enforcing and phrasing the Comment property strings. Access macro objects are grouped under LDF using these type designations:
These macro definitions are primarily programming constructions and have no impact on users, thus they are not usually described to the application's design team. For example, the team can effectively define application menus even without understanding what technique is used to program them.
Defining Module Types
Modules are grouping objects whose use is completely arbitrary-in theory, all the code for one application could be placed in a single standard module. Developers are well served by using modules to create smaller subsets of grouped procedures, however. LDF defines procedure groups using modules with these personalities:
Because these groupings are arbitrary and the implementation is flexible, you will most likely group procedures slightly differently from one application to the next (with the exception of library procedures which, by definition, tend to remain static across applications).
Access applications do not exist in a vacuum; they inherit some of the characteristics of their operating environment such as screen resolution from the workstation and window display attributes from the operating system. For more complex applications, Access can also be made to interact with a variety of external components to create a robust solution that taps into readily-available feature providers.
Let's review the primary common components that Access can reach out and use to extend its abilities:
ODBCDirect is a new client/server connection technology added to Access 97 that allows for connections to ODBC data sources without using any resources of the Jet Database Engine. If you are a Visual Basic programmer, you will have first been exposed to the concept of going directly to ODBC for data services while working with the RDO (Remote Data Objects) technology introduced in VB4.
Design and development teams must be made to understand this full gamut of extensions available to Access to not limit their design cosmos only to that provided by Access's feature list.
Once you have defined the functional requirements of an application, you must list the appropriate set of application building blocks and organize the selected blocks into an application architecture.
I'm using the term "application architecture" loosely in this chapter to describe the conceptual and physical arrangement of objects and processes that make up an Access solution. In this context, the term means how elements and processes in an application are organized. Within application architecture, LDF includes these object-related subsets: data architecture (as detailed in the section "Deciding Where the Data Will Reside," that follows), code architecture (described in the section "Fitting VBA Code into the Architecture" later in this chapter), and interface architecture (discussed in the section "Defining User Interface Elements" later in this chapter). In contrast, some current client/server vocabularies define application architecture more narrowly than I do here and separate it from the related solution construction concepts of data architecture and business architecture.
Not every application contains every object type you have defined as part of your standards, although most of your sizable applications will contain a majority of your defined Access object types as well as one or more of the external components described in the previous section.
You can represent the mix of components in your application visually in an application architecture diagram, a simple example of which is shown in Figure 7.8. This diagram provides a view of the component mix in a robust Access-centric application. Application architecture diagrams can be drawn to be much more complex than this example, showing procedures within modules, modules within objects, indexes within tables, and so forth.
This simplified Access application architecture diagram shows the various application components.
This type of architecture diagram provides a conceptual view of the application, as opposed to a logical view. At the conceptual level, components are shown based on their functionality or contributions. At the logical level, the actual physical location of objects is noted, including the machine, drive, and directory.
Certain logical (physical) considerations come into play when you design an Access solution, such as locating library routines and other shared objects. You can design an Access application using virtually any layout that your network infrastructure supports. Figure 7.9 shows the extreme layout options as contrasted with the most common (balanced) scenario for Access solutions.
Several physical topologies may be used for combining Access components into an application.
Physical layout considerations are limited by Access's current inability to directly call class module interfaces located in a referenced database. Specifically, Access cannot call custom methods and properties of form or standard class modules in referenced database library files by referencing the class, as in this line:
AES_Lib.Form_fclsNameFix.prpNameFirst = "Bob" This limitation prevents you from creating class modules as service objects that are referenced in the same fashion as library code and wizard forms. See the section "Segregating Solution Services" later in this chapter for more information.
Most developers currently engineer their Access applications in two parts: the front-end application database file with form, report, and program objects and the back-end data file with tables that are linked to the front-end (see the sidebar and Figure 7.9). This model creates a file server database application, detaching the shared data (located on a shared drive for multi-user support) from the application objects (placed on a local hard drive for better performance).
A Not So Fond Attachment
In the original version of Access (1.0), using table attachments to separate application objects from data storage was something of a challenge. For example, there was no add-in like the current Linked Table Manager to manage table attachments and Access's performance issues were not yet documented.I remember the first Access 1 application we created during early alpha testing. It was a database to manage information about graphical images. We used dynasets (now called recordsets) in those early days to fetch table data programmatically from attached tables. This revealed a serious performance drawback because the FindFirst method did not use indexes. Looking for a specific record in a large table with FindFirst took more than 40 seconds. We next changed all of our DAO coding to use Table objects (now table-type recordsets) against a database object pointing to a named back-end file to use the Seek method to find records. The same single-record search took only 4 seconds. Based upon what we learned from this experience, we redefined our application architecture model to include a reliance on specific DAO coding methods whenever recordset searches were involved. Many architectural decisions and standards derive from such real-world application needs and experiences.
While the separation of data from the interface allows for multi-user data access, some applications require deeper consideration of data architecture, which defines the types of data elements and their optimal locations. In addition, a discussion of the various roles that data can play in the application is often relevant when "architecting" an Access solution.
In the "Defining Table Types" section earlier in this chapter, I hinted at the variety of tables that you can construct in an application. While my list of definitions may not be comprehensive for your specific development needs, it nevertheless represents the majority of table types in common usage.
Not all table types are optimally located in the back-end database and linked to the front-end. Generally, certain tables obviously belong in the back-end and others in the front-end. In some applications, you may put some tables in their own distinct database.
When structuring an application with a variety of table types, you must consider several facets of the application's environment to determine the best architecture for locating the tables:
You may infer from reading the previous list that some application architectures favor more than one database back-end file. After you've grouped related tables into a database file, the database can then be easily classified by its type. The LNC designations for back-end files as defined by their primary content are listed here:
When moving large numbers of records from a transaction database to the history tables in an archive database, it's good practice to compact the transaction database. Compacting the database will defragment the file and free up the space previously occupied by the deleted records.
Figure 7.10 shows three common table distribution architectures that may improve data access times. The multi-disk model allocates a separate disk to the transaction database apart from the supporting databases. The multi-server model places the separate database disks on separate server machines. The multi-file model simply copies the central lookup database to the user's workstation at regular intervals for improved performance.
You can distribute data tables using three different physical layout models that each involve multiple back-end files.
Table 7.3 reviews the list of LDF table types from earlier in this chapter and notes the logical locations for each type in your application's architecture. The table groups the archive and lookup database types under the heading Supporting.
Table 7.3 Preferred locations for various table types
|Table Type||Local UI File||Transaction Back-end||Supporting Back-end|
Once you have decided which tables in an application are to be native to the interface database and which are linked, the application architecture is complete with respect to table objects exposed to users. Non-exposed data tables (those accessed directly through code with the OpenDatabase method and not linked into the front-end database) may also deserve consideration with respect to application architecture.
For example, assume that login routines in your company's Access library provide a common login form object for use in each application. The company maintains a centralized database with a table holding login information: user names, rights, mailbox names, and so forth. The login process in each application checks the supplied password and assigns application variables (e-mail name, rights, and so on) based on information in the login table in the communal database. This database is part of the application architecture for referencing applications even though it is used only from code and not exposed to the users. (Chapter 17, "Bulletproofing Your Application Interface," includes a login form and code that demonstrates this exact scenario.)
At the highest level, by considering application architecture, you can describe the location of objects and their relationships. At a more detailed level, your architectural analysis proceeds to forms, reports, controls, data access code, and so forth, because the architectural decisions made at the higher level trickle into the configuration of these application elements.
Providing Records to Forms and Reports
The manner in which you provide records to forms and reports will be directly affected by the table architecture you've mapped for the application. If the application's data layout includes a database server, for example, you are presented with far different options for supplying rows to forms than if its architecture is solely the Access/Jet file server model.
Forms and reports include a powerful variety of options for collecting their records. Data can be served to these objects via some combination of the following:
The location and structure of each form or report's source data may impact how you balance these sources. In most cases however,, your decision regarding the appropriate record selection mechanism for a form or report will not be based on the location of the data but instead will weigh factors such as the following:
In previous versions of Access, the execution plan for the data source of a form or control was only saved in the database if a named query was used. In Access 97, the execution plan for a raw SQL statement used as a RecordSource or RowSource property is also saved with the form definition, reducing some of the necessity of using saved queries for these properties.
You can see that no single factor helps you make the decision about constructing RecordSource properties. Instead, your decision will be based on applying the listed considerations to the specific needs of each object, on an application-wide design model created to ensure a consistent approach across the development team, and on performance considerations defined after testing various architectures on your specific application.
For any given form, there is usually only one possible source of records. (In a replicated environment, this may not be true per se, but there will nevertheless be a best source.) For reports, an application with a repository (warehouse) of data provides you with a more complex decision process:
Figure 7.11 helps to diagram this predicament. It shows that some records in the archive table are no longer in the transaction table, and that the records added on 10/11/95 to the transaction table have not been copied to the archive table.
Records in a non-replicated archive table will not exactly match those in its source transaction table.
For each report in this architecture, you will need to compare the report's primary objective against the data architecture. The currency of data may be important enough to warrant reporting off of the live transaction tables even at the expense of slowing other users. Alternately, if a specific report can be run off of the non-live data in a repository, it will have access to historical records much older than are found in the transaction database.
Providing Records to Controls
Drilling down from forms and reports into their dependent objects, consider next the example provided by combo box and list box controls. A number of different vehicles can provide the records for these controls:
It is also possible to load a combo box with the list of fields for a table, query, or SQL statement using the Field List value for the RowSourceType property, but this feature is not relevant to the discussion at hand.
For each combo or list box control, the decision on which of these data sources to use as a record source will be impacted by the larger architectural decisions. For example, a combo box based on a small, native table in the front-end file will load quickly from either a table or a query, thus confronting you with an architectural decision:
In contrast, moving the same table to the back-end file and linking the front-end to the table will cause the combo box to load more slowly than when it is native due to the network traffic incurred when fetching the records. Your architectural decision here is simplified-layering a query on top of the table to restrict the number of columns and/or rows pulled down the network pipeline becomes an obvious architectural choice.
As a third scenario to this combo box example, imagine that the application architecture placed this same table of values as a text file on a shared server, rebuilt each day by some external process. Once again, your approach to loading the data into a combo box is affected by the application architecture. You now need to prototype and test several of the available options before you are able to make an informed architectural decision on the fastest and most logical approach. The options include:
A file that mimics a data table, like the mainframe-produced text file described here, is often called a flat file.
You can see that the architecture of the application impacts your decisions involving data access, from query construction to form RowSource properties to VBA coding against the DAO. At every point in the application where table rows are fetched, you must consider your alternatives for grabbing those rows in light of the application's data layout.
Confronting Complex Architectural Decisions
Spreading data tables across multiple database files, disk drives, and even file servers (review Figure 7.10) seems on the surface to be an appropriate direction considering the current industry penchant for componentization. Indeed, a data distribution strategy may provide performance and maintenance benefits to a widely-used application. Then again, the trouble may not outweigh the benefits.
Consider the complex ramifications of separating lookup tables into a distinct file on a different server from the production tables. The performance of reads and writes to the tables may be positively affected by such a strategy in these ways:
Negative performance factors are also built in to this strategy, including the following:
Taking these factors into account, the real performance increase or decrease of this scenario is virtually impossible to estimate. You would need to benchmark the problem with test cases before finalizing your architecture plan.
Of course, performance is only part of the equation in this example. Diffusing application tables across multiple databases and servers solves and creates a variety of non-performance problems as well, including extra security and maintenance burdens and opportunities. Building a well-tuned application involves exploring each of these issues and making decisions that best fit the combined personalities of the application, its users, and the infrastructure available to it.
The concept of application architecture trickles to even the lowest level of an application-program code. Code architecture can be considered as the "site plan" for the VBA elements of the application architecture.
Code architecture has three primary components:
Defining Your Coding Style
Your coding style for VBA development is defined by your techniques for authoring individual procedures. Your style is reflected in your consistent approaches to these code components:
Figure 7.12 shows examples of these coding style elements.
This diagram shows the various elements of a VBA code procedure.
Coding style is one of the most consistent elements of application architecture, meaning that the coding model used by your development team should not change much between applications. As you design the architecture of an application, review your coding style elements from the previous list and determine whether each existing style item will fit the current project unmodified, with modifications, or not at all. Issues related to coding style are discussed further in Chapter 11, "Expert Approaches to VBA."
Locating Code Routines
In contrast to code style, the concept of code architecture has a broader impact on an application. Whereas style issues apply to the attributes of code lines within a procedure, the code architecture determines the organization of the procedures themselves within the application.
Access cares less about where its code lives than developers do, so as you craft an application, you will consider the location of code routines as related to these issues:
Most of your applications will follow a similar code architecture because the opportunities for locating code are limited. To establish the appropriate layout of code, you must first identify which of these criteria a routine fits best:
Figure 7.13 shows how these various types of code routines interweave.
An example of a code hierarchy that employs object-specific code (relevant to one object), application-specific code (relevant to the database), and generic code (the calling database is irrelevant).
Having discerned the primary type of a routine, the decision where to locate it is mostly straightforward. See Chapter 11, "Expert Approaches to VBA," for an expanded discussion of the decision points for locating code.
Defining a Procedure's Role
In addition to the style and location of code, the purpose of each VBA object comes into play when defining code architecture. Unlike objects such as tables, which drive the architecture of the application, code routines derive their traits from the architecture of the application. Thus, the role of code in an application is clearly defined. Nevertheless, a varying degree of architectural decisions are always left to the developer.
For example, the data entry/edit needs of a table determine the needs of a specific form bound to it. That form's needs then dictate a list box control to manage the presentation of lookup data related to the table. This list box requires callback code (described in Chapter 13, "Mastering Combo and List Boxes," to provide its RowSource.
The application has already made most of the code architecture decisions for this example procedure for you:
Thus, the only architectural decisions left to the developer are simple ones:
As a contrasting example, the code architecture of a data import routine is less tightly defined. Assume that your application requires the weekly import of a flat file (a simple ASCII text file) that is created from your company's mainframe. You must craft a procedure to open the file, grab each line, parse and validate the items, and post the items to an application table. As you design this routine, several different approaches will achieve the stated objective, leaving you to choose from a variety of layouts:
The challenge of coding this single procedure exposes you to several decision points. You must consider the different alternative approaches to the stated problem and define an appropriate architecture for the VBA solution to it. The way you create this definition will be influenced by three factors:
Thus, the code architecture subset of application architecture presents you with the greatest number of decisions at the mechanical level (defining what each line of code does), and the least number of decisions at the structural level (where to locate code and how to relate it to other objects).
Almost no application is an island with a fixed dataset. Instead, data enters an application from outside it via several mechanisms:
As you map the data layer in an application, you must confront each of these possible data sources and find the optimal mix for the specific application.
Posting Data From External Sources
Data sent from an external source to your application provides a specific set of challenges, the most urgent of which are these questions:
As an example, assume that your application manages inventory data but that a different application catalogs the orders that actually consume the inventory items. To reconcile a product's inventory records against the transactions that consume them, the order details must be sent from the order entry database to your application's database.
If the external database can submit records to your application while users are querying or reporting data, the information users see may not be current. Imagine the situation that arises when a user prints two reports consecutively that have a relationship to each other. If the external data is loaded into your application at the time lag between printing the first and second report, only the second report will contain the posted information and will not balance to the first report printed minutes before it.
This is one small example of the issues that arise when external data can "raid" your system in the background. As you build your system, you must be aware of such issues and resolve them to the benefit of both of the interacting applications.
I'm strongly in favor of letting the host application control all of the data that comes into its tables. If possible, I build the implantation of remote data so it is driven from the destination database as an import rather than as an export from the external application.
Sharing Data with Other Programs
You can easily establish table links with other programs' databases in Access. This ease may fade quickly, however, if the purpose of the links is to edit data.
For example, several retail contact management and accounting products are created in FoxPro, whose database tables can be linked to your Access application. You have no knowledge of, or control over, the manner in which the retail application locks edited records, refreshes data in your linked recordset, or builds indexes, however. Thus, the potential for collision and confusion exists when multiple users of your Access application interact with data that is simultaneously managed by users of the separate retail application that owns the data.
Pulling data from an external data source without fully understanding its layout and structure may lead to an improper or inaccurate representation of that data to your users.
In confronting these challenges, you must consider the architectural options available when you work with databases that are not "owned" by your application:
Architectural decisions like these require that you understand not only the needs of your application but also the workings and capabilities of the host system managing the external data. Creating the layout of an Access application with active links to external data requires a team effort between your development team and the team that maintains the external database.
"Raiding" an external data store without a cooperative effort involving your development team and the owners of that data may create a negative political climate between the two teams.
Importing External Data
Data located in external data sources can be easily imported into your application if the data source is supported by a Jet ISAM driver. Jet's capabilities to link to data sources as diverse as Paradox tables and delimited text files greatly reduces the complexity of imports involving supported data formats.
Access 97 includes new ISAM drivers that link to HTML-based information and Microsoft Exchange data stores.
The prevailing architectural issue that derives from imports of external records is data integrity. Any data that is not keyed into your application's tables through one of the application's forms is suspect in its accuracy and should be checked on the way in.
As external data is brought into your application's database, subject each record to the same stringent tests that are applied to records keyed-in by the application's users through forms. You can buffer external data's entry into your application by loading it to a temporary table and validating it there before posting it to transaction tables.
Consider the following scenario. Your company's mainframe exports a delimited file each week containing customer order information. Your application's users need to import the information and report it as part of their weekly account balancing process. The file is de-normalized and contains the order header information merged with each order detail record.
The least costly approach to this task is to import the orders from the file into a table each week using the TransferText method in a piece of program code. The import's target table can then serve as the RecordSource for a related report, which groups and sorts the data to suppress display of the redundant fields. Importing the data into a table allows indexes to be created, which improves printing performance over simply linking to the external text file.
In this model, the impact on your application's architecture is the addition of four objects:
The downside of a simple import like this is that data in the file is not validated before being printed. On its own, Access makes its best effort to pull the text file data into table fields, but is not able to check the data for validity beyond simple data type testing (in other words, Access will not import a long integer value into an Integer type field).
A more solid approach would be to import the data via a code routine that validates each field as it is posted to the target tables. Notice the pluralization of table into tables in this model because you can also use the import routine to normalize the order data into parent and child records. In this model, your application architecture is impacted by the addition of the following objects:
Notice that the numbers and types of objects required in the second scenario greatly exceed those produced by the first scenario. Taking the proper and most robust approach to the import problem adds complexity to your application's architectural layout, but is always worth the extra effort if data accuracy is improved.
Where an application's data comes from is quite important to you as a developer. What is more important to the application's users, however, is where the data goes to. Possible "To" destinations for application data include the following:
Be aware that your architecture must provide a provision for restricting a linked user from making changes to your data. You must enable this blockade by providing remote users with read-only rights to the data, usually by applying an Access security layer.
Note the architectural impact of charting these various data destinations-giving users the ability to extract, review, or print records in a database requires adding new objects. It also requires discussions of the attributes of those objects and of the relationship of the objects to the remainder of the application.
Defining the architecture of an application includes considering the data and code attributes discussed previously in this chapter. The effort also includes considering the roles of various interface elements in the solution:
These questions are addressed in the sections that follow.
Almost all of a user's interaction with an application is through forms. Forms are the gateway to data records, provide the mechanism for displaying switchboard menus and selection dialogs, and can now be used to provide programmable class objects. Nevertheless, contemplating an application's interface architecture is a broader effort than simply deciding on a list of forms. Access exposes other features to developers to use when defining an interface's structure:
Most application data elements have interface (presentation) requirements that require the designer to explore interface options. These options lead to decision points. Decision points produce object definitions that add more elements to the architecture. Thus, the interface subset of your application's architecture flows directly from the stated needs and wishes of the users and their data.
The following chapter provides a thorough discussion of the user interface objects that make up an application's interface architecture. The chapter also discusses the properties and roles of interface objects within an application's blueprint. Thus I limit the discussion of these objects in this section.
A primary consideration in your application's interface architecture is: How will my users navigate between features? In fact, before you can define form objects for an application you must first answer this question and produce a policy from the answer.
For example, an application for sophisticated users who regularly review and print data could contain only a list of queries displayed on a form's list box. Users could run one or more specific queries as required to audit or correct data records, tile or otherwise rearrange the datasheet windows, and even print the query datasheets. These objects have no navigation requirements except for allowing the user to open and close them.
In contrast, an entry/edit application for unskilled data entry personnel must use forms as the lenses to data. Each form has an enforced navigation plan and contains record validation code. Selecting forms, opening and closing forms, and moving between forms are all regulated by the application's structure.
The difference in numbers and types of objects in these two example applications is significant, as is the coding complexity. Thus, any decisions about the types of objects used to present data in an application, and the manner in which users move between these objects, will create the paradigm under which the objects and their roles are defined.
Delimiting the navigation and presentation elements of an interface involves debating and defining these attributes:
Even though detailing the architecture of data tables, indexes, and queries has the largest overall impact on the structure of an application, the definition of interface objects, their roles, and their navigation has the most visible impact.
Because forms are the most visible, and arguably the most important, objects in an application, I devote considerable space to their design and usage in this book. The discussion of form navigation started in this section continues in the following chapter, carries on to Chapter 14, "Navigating in Forms and Applications," and is completed in Chapter 17, "Bulletproofing Your Application Interface."
Closely related to the discussion of navigation methods and objects is a discussion of data entry and data editing objects. Forms and datasheets provide the primary tools for these tasks as well as the majority of interface objects in an application's structure.
For a specific application there are appropriate and inappropriate approaches to take when presenting data items to users. Selecting the most suitable approach is facilitated by an understanding of how users interact with data items. There are five primary means:
In Access, there is usually more than one interface device available for addressing a defined need. For example you can provide selective browsing with a table datasheet with filtration options, a query, a form, and so forth. Your determination of which interface devices to use for each need impacts the structure of an application.
The database industry is replete with buzzwords describing the current technologies and paradigms. One hot concept is "replication," which was added to Access 95 and enhanced in Access 97.
Access can facilitate two other popular database concepts as well, but only to a limited extent. The following concepts are:
Let's fit Access into these new models.
Enabling the three-tier model in its pure form requires a client application and a database server. The client application provides "user services" and the database server provides "data services." Between these two engines lies a group of objects providing "business services" (see the sidebar), a collective term for processes that provide these capabilities to an application:
Applying this new paradigm to Access is not possible in a purist sense because Access does not fully support the client/server data distribution model, but a discussion of it may nevertheless prove informative.
Tongue-tying Three-Tier Terminology
I've been struggling with how to present the three-tier model simply to our clients. Ideally, because they are accustomed to the terminology "front-end" and "back-end," I'd like to be able to refer to the new business services layer as the "middle-end." In practice, however, this phrase has not proven popular.Alternately, I merged the terms "business services" and "middle-end" and came up with "business-end." This phrase quite effectively enjoys a different meaning, however, and refers to the active terminus of a device (as in "He ended up on the business-end of his enemy's sword"). Once again, I produced confusion rather than clarification. Amusingly enough, the phrase that has made the most sense to many of my clients is one with which they are already familiar. What does a business services engine do? It validates and manipulates records by itself-in other words, it autonomously "processes" data. A business services layer is simply a set of objects for "automatic data processing." Thus, the simplified three-tier model I show to clients looks like this: User Interface-->Automatic Data Processing-->Data Storage
The term "automatic data processing" was in style twenty years ago when I was in college and originally referred to any work done by a computer. Now I use it to describe the magic processes in the middle-tier of a database structure. Life sometimes spins in circles.
Segregating Solution Services
To adhere to three-tier architecture, your Access application would need to locate all services for data validation, bulk processing, and security in a separate application, ideally on a separate server from the database (see Figure 7.14). Why the separate location? Because the object-centric development model dictates that you reuse code when possible in an organization. Some of the code that is embodied in a business services object (or, simply, business object) may have value to more than a single application and database.
A business object for data validation acts as a gatekeeper between the client application and the data.
As an example, several applications in a single company may rely on one central database of customer information. A good role for a business object is to validate a customer number against the customer database and tell the user if the customer number is active, discontinued, or nonexistent. Many client applications within the company could call a single, central business service to perform this task.
In Automation, Microsoft has created an ideal model for enabling these business services. Using Visual C++ or Visual Basic, a developer can easily create an OLE-enabled business object. To use this object from Access, your user interface front-end application needs simply to reference the server application and to contain the appropriate code to control the Automation server. If you have written VBA code to propel Excel from Access you will grasp that this is a straightforward process.
Thus, Access might refer to our imaginary business object for customer number inquiry in the current example simply as the following code:
Dim oappCustInq As Object ' A business object
Dim oclsCustNum As Object ' A class in the object
Set oappCustInq = CreateObject("CustInquiry.Application")
Set oclsCustNum = oappCustInq.clsCustNum
blnValid = oclsCustNum.mtdNumIsValid(lngCustNum)
As with other Automation objects, the fictional CustInquiry server application in the code sample would contain properties and methods (referred to as interfaces or members) that you could view in Access's Object Browser dialog.
In this sample code, the server CustInquiry exposes a class object called clsCustNum with a method mtdNumIsValid. The server provides a validation routine that is tied to a database back-end but independent of any front-end applications that call it.
You can use Access itself as your business rules server because you can remotely automate it. Create a class module in a database separate from your application and drive the module via Automation code from the current session of Access to the remote session. The remote session provides the business services. However, you may find that Visual Basic or Visual C++ produce faster-executing business objects than Access.
Locating Access in a Three-Tier Model
Because Access uses Jet for its data services, enabling the three-tier model precisely as defined is not possible. Jet is a file-server database engine that relegates the work of the data services tier to the user's workstation. In other words, the user's CPU provides both the user services and the data services, as shown in Figure 7.15.
The client workstation does the majority of the data retrieval work in an Access/Jet application.
For practical purposes, the work of business services is also usually consigned to the user's workstation in an Access-centric model. While it is possible to create remote Automation objects that reside on a business server and to have these objects handle all data management tasks by virtue of their own connections to a Jet database, such an architecture may not provide tangible benefits when contrasted to the amount of work to create and maintain it and the horsepower requirements of the workstations that will use it. Because the user interface portion of an application would need connections to Jet tables (to fill combo boxes and so forth) and the business services objects would need their own connections to the same data, this paradigm would prove awkward and taxing for a file server database. Nevertheless, the possibilities are interesting and you can explore this opportunity to see if you like it.
By removing Jet from the picture and using Access as a front-end for SQL Server or another ODBC back-end database server, you can establish a structure that more closely resembles a true three-tier model. Moving data between the server and Access forms via the business service objects provides the appropriate middle layer of validation and data manipulation. (See Chapter 19, "Exploring Client/Server Issues," for more discussion on using Access with SQL Server.)
So, how do you make the most of this new paradigm today without creating an overly complex application architecture? The answer is in the objects. The primary features of a business object are that it is
Thus, if you create stand-alone, smart, reusable objects in your Access applications to provide data-manipulation and data-protection services for your users, you are going down the right path. I'm not guaranteeing that if you create a class form or class module (as detailed in Chapter 12, "Understanding Form Structures and Coding") or library code (see Chapter 20, "Applying Development Shortcuts") to do validation work that your code will be 100 percent portable into a client/server business object in a year or two. But your code won't be far off, either.
Here are a few examples of how to apply the ideas of three-tier paradigm to Access today, within its limitations:
If you think that I've provided you with conflicting directives in this book regarding self-contained objects, you're correct. In one breath I've suggested that you create objects that stand alone and can be used in other applications (such as an order entry form with all of its code in the form module). Yet in the next breath I'm telling you to move routines out of the form and into public procedures where they can be shared by other forms. While there is certainly no single correct answer to this conundrum, in general I will sacrifice self-containment for the opportunity to reuse business services code.
Where should old data live? The answer depends on who needs the data and how they need to see it. Earlier in this chapter, I noted that you should move aged records (and perhaps deleted records) to archive tables if the transaction tables they reside in suffer from reduced performance as a result of large records counts.
Whether or not you choose to move historical data out of active tables or keep it there, the important point here is never to throw any legitimate transaction records away (see the sidebar). Even data that may seem trivial may prove valuable in an audit, a merger, or for statistical analysis.
I use the concept of data warehousing here to primarily define a location for historical information, mostly because of the nature of Access. In common usage, the term does not require that all data in a warehouse be aged. In its broadest sense, the concept of warehousing is really about accessibility-how to expose large amounts of data to users for ad-hoc research through Decision Support Systems and Executive Information Systems.
The concept of data warehousing simply means something like this: Keep all of your data stored together in a clean, dry place. Let's dissect this phrase:
Have you ever been to a farm? Farmers never throw anything away. They're clever enough to know that an old piece of wire may fix the tractor, which in turn may save the year's crop. (They're also resourceful enough to actually fix the tractor with the wire, in the dark, in a hailstorm...)One delight of my youth was exploring the attics and barns of my grandmother's farm. The dusty corners and creaky trunks held secrets and surprises from years long past. I believed that I might even find the Holy Grail somewhere in Grandma's warehouses of clutter, there was so much neat stuff there. Grandma saved everything. Which is probably why, in my application development efforts, I save everything, too. We've not thrown any client's data records away in fourteen years-instead, we build archive tables that eventually are moved to archive databases, that eventually are moved to archive tapes, but that never are moved to the dumpster. And often our clients don't even know we're doing this for them. Thus, when a client's needs change and they tell me that they wish they'd saved more historical data because they now need to analyze some trend or research some problem, I take them to Stan's Data Attic and pull their archive tapes out of one of my creaky trunks. The client smiles, and somewhere in Heaven, Grandma smiles, too.
You can easily apply the concept of data warehousing to Access applications. At periodic intervals, your administrator can run a routine that copies records from the application database to the warehouse database. Tie this routine to a button on the administration menu or to a process that runs frequently, such as a daily posting or backup routine.
The most simplified model of a warehouse posting routine follows this flow:
There are multi-user concerns during the warehousing operation. For example, if the process runs in the background and a user has saved edits to an order record but not yet saved the order details, the parent record could be copied to the warehouse but an unsaved child record may not be (the copy on disk, not in the form, would go to the warehouse). Information in the warehouse will be out of synchronicity. Thus, it is preferable for the administrator to run the warehousing routine only after securing an exclusive lock on the source database or at least on the records to be warehoused.
The simplified model I've provided for this illustration conveys the concept of warehousing, but is actually not robust enough for most real-world situations. Its shortfall is that it does not include a provision for records that are changed in the source database after they are warehoused. If this is an issue in your implementation, you will need to modify my logic steps to include updating a record in the warehouse if it has changed in the source database since it was last archived (warehoused). (One helpful technique to aid this is to time-stamp records as they change, as is described in Chapter 17, "Bulletproofing Your Application Interface.")
If this seems a bit like writing your own custom replication, it is. Unfortunately, Access's current implementation of replication does not facilitate the warehousing paradigm. Two problems exist:
After you've built the structure and code to create a data warehouse, you need to provide users with access to it. Here are some simple rules to follow:
Though I noted earlier that implementing replication for the creation of data warehouses is problematic, it would be possible to replicate information from the data warehouse into the shrinkage data mart described in this example. Using Access 97's new partial replication feature, you could synchronize selected shrinkage records from the data warehouse out to the data mart based on SQL criteria.
Query parameters are a handy device when you build saved queries for users to interrogate data marts and warehouses. Query parameters make it easy for users to enter a value or range of values to tailor the result set of a canned query or its report. For example, placing parameters like [Enter From Date] and [Enter To Date] on a date value in a warehouse query causes Access to produce dialogs that prompt users for these values.
Of course, users making decisions based on historical data will not be well served if the data is not robust and current. When you build a data warehouse, you must make certain to update the warehouse frequently and to ensure that the data is accurate and comprehensive.
In this chapter, we reviewed the techniques for selecting a route up the application development cliff. Your rope should now be stronger and you should slip and fall less given your new understanding of application architecture issues.
© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.