Chapter 19. A Strategy to Developing Access Applications

IN THIS CHAPTER

• Why This Chapter Is Important
• Splitting Databases into Tables and Other Objects
• Basing Forms and Reports on Queries or Embedded SQL Statements
• Preparing an Application for Distribution
• Using Access as a Front End
• Practical Examples: Applying the Strategy to the Computer Consulting Firm Application

Why This Chapter Is Important

You should know about several tricks of the trade that can save you a lot of time in the development process and help ensure that your applications are as optimized as possible for performance. This chapter addresses these strategies. You should keep all the topics covered in this chapter in mind when developing your Access applications. When reading this chapter, think of the general strategy outlined rather than the details of each topic. I cover each topic in depth in other chapters of the book.

Splitting Databases into Tables and Other Objects

When earlier versions of Access ran in a multiuser environment, it was imperative that you placed the system’s tables in one database and the rest of the system objects in another database. With the advent of replication, you could either split the tables from the other objects or use replication to deploy design changes without compromising live data. Access 2000, Access 2002, and Access 2003 took this a step further with the Access Data Project (ADP), in which Access stores tables, views, stored procedures, and data diagrams in a SQL Server database or the SQL Server 2000 Desktop Engine (formerly the Microsoft Database Engine, or MSDE). Access stored forms, reports, macros, and modules in the ADP file.

Access 2007 creates an entirely new scenario. The new Access file format (.accdb) does not support replication. The ADP file is supported for backward compatibility only. Therefore, splitting tables from other system objects is a viable solution. For simplicity, I’ll refer to the database containing the tables as the Table database and the database with the other objects as the Application database. Linking from the Application database to the Table database connects the two databases. This strategy enhances

• Maintainability
• Performance
• Scalability

Assume for a moment that you distribute your application as one ACCDB file. Your users work with your application for a week or two, writing down all problems and changes. It’s time for you to make modifications to your application. Meanwhile, the users have entered live data into the application for two weeks. You make a copy of the database (which includes the live data) and make all the fixes and changes. This process takes a week. You’re ready to install your copy of the database on the network. Now what? The users of the application have been adding, editing, and deleting records all week. You are left with the task of integrating your application changes with the users’ data.

The simplest solution is to split the database objects so that the tables containing your data are in one ACCDB file, and the rest of your database objects (your application) are in a second ACCDB file. When you’re ready to install the changes, all you need to do is copy the Application database to the file server. You can then install the new Application database on each client machine from the file server. In this way, users can run new copies of the application from their machines. The database containing your data tables will remain intact and be unaffected by the process. (Of course, this is possible only if you finalize your table structure before splitting the database.)

The second benefit of splitting the database objects is performance. Your Table database obviously needs to reside on the network file server so that the users can share the data; however, there’s no good reason why the users need to share the other database components. Access gives you optimal performance if you store the Application database on each local machine. This method also significantly reduces network traffic, and it decreases the chance of database corruption. If you store the Application database on the file server, Access will need to send the application objects and code over the network each time the user opens an object in the database. If you store the Application database on each local machine, Access needs to send only the data over the network. The only complication to this scenario is that each time you update the Application database, you will need to redistribute it to the users. Even on an already overtaxed network, this is a small inconvenience compared to the performance benefits your users will gain from this structural split.

The third benefit of splitting tables from the other database objects is scalability. Because you have already linked the tables, you can easily change from a link to a table stored in Access’s own proprietary format to any database that supports ODBC (such as Microsoft SQL Server). This capability gives you quick-and-dirty access to client/server databases. If you have already thought through your system’s design with linked tables in mind, the transition will be that much easier. Don’t be fooled, though, by how easy this process sounds. Many issues associated with using Access as a front end to client/server data go far beyond simply linking to the external tables. This chapter and Chapter 22, “Developing Multiuser and Enterprise Applications,” cover some of these issues. Alison Balter’s Mastering Access 2002 Enterprise Development covers client/server development techniques in extensive detail.

Basing Forms and Reports on Queries or Embedded SQL Statements

You can base the record source for a form or report on a table object, a query object, or a SQL statement. By basing forms and reports on stored queries or embedded SQL statements, you can improve the performance and flexibility of your applications. In most cases, you don’t need to display all fields and all records on a form or report. By basing a form or report on a query or embedded SQL statement, you can better limit the data transferred over the network. These benefits are most pronounced in a client/server environment. When you base a form or report on a table object, Access sends a SQL statement that retrieves all fields and all records from the database server. On the other hand, if the record source for the form or report is a query or embedded SQL statement, the server returns to the workstation just the fields and records specified within the query.

An Access 2007 form or report based on a stored query or SQL statement is very efficient. This is the case because when you save a query (or in the case of an embedded SQL statement, the form or report), the Access Database Engine creates a Query Plan. This plan contains information on the most efficient method of executing the query. When you save a query or form or report based on an embedded SQL statement, the Access Database Engine looks at the volume of data and the available indexes, determines the optimal method of executing the query, and stores the method as the Query Plan. The Microsoft Database Engine uses this plan whenever it executes a query underlying a form or report. It is up to you whether you use a stored query or an embedded SQL statement as the foundation for your forms and reports. There are advantages and disadvantages to each method. With a stored query, the upside is that multiple forms and reports can use the same query. The downside is that you have another object to manage in the Navigation Pane. With an embedded SQL statement, the advantage is that the SQL is stored neatly with the form or report that it is associated with. The downside is that if multiple forms and reports share the same SQL statement, you will need to maintain each separately. My rule is that, when I feel that the query will be reused by other forms and reports, I create a query. When I feel that the query is unique to the form or report I am creating, I create an embedded SQL statement.

When you base a form on table data, you can’t control the order of the records in the form, nor can you base the form on more than one table. You can’t limit the records that the form displays until the user opens the form. If you base a form on a query or an embedded SQL statement, you can control the criteria for the form as well as the default order in which the form displays the records.

Everything just mentioned applies to reports as well, except the order of the records, which you determine by how the report itself is sorted and grouped.

Preparing an Application for Distribution

You must take some special steps to prepare your application for distribution. Most are steps you’ll probably want to take so that your application seems professional to the user. The following are preparations that you should take before distributing your application:

• Base your application around forms.
• Add startup options to your database.
• Secure your application.
• Build error handling into your application.
• Add some level of custom help.
• Build custom ribbons to be associated with your application’s forms and reports.

Basing Your Application Around Forms

You should base your application around forms. It should generally begin with a main switchboard that lets the user get to the other components of your application. Or, it can start with a core data entry form around which you base the rest of the application. If you opt to go with an application switchboard, the main switchboard can direct the user to additional switchboards, such as a data entry switchboard, a report switchboard, or a maintenance switchboard. You can build switchboards with a tool called the Switchboard Manager. Alternatively, you can design them as custom dialog boxes. Chapter 10, “Advanced Form Techniques,” covers building custom dialog boxes. The primary advantage of custom switchboards is the flexibility and freedom they offer.

An alternative to the switchboard approach is to build the application around a core data entry form, such as a contact management application based around a contacts form. The user accesses all other forms and reports that make up the application via a custom ribbon on the contacts form.

Adding Startup Options to Your Database

Regardless of the approach that you take, you designate a form as the starting point for your application by modifying the database’s startup options. Here’s how:

1. Click the Microsoft Office button and then select Access Options (see Figure 19.1). The Access Options dialog box appears.

   Figure 19.1. Click the Microsoft Office button and then select Access Options.

   

2. Click to select Current Database (see Figure 19.2). In this dialog box, you can set options, such as a startup form, an application title, and an icon that appears when the user minimizes your application.

   Figure 19.2. The Current Database page of the Access Options dialog box lets you control many aspects of your application environment.

   

Securing Your Application

As you will learn in the next section, a database isn’t secure just because you’re running it from a runtime version of Access. If your application doesn’t have security, anyone with a full copy of Access can modify it, so securing your database objects is an important step in preparing your application for distribution. Chapter 31, “Database Security Made Easy,” covers security.

Access 2000, Access 2002, Access 2003, and Access 2007 also offer you the capability to remove the source code from your applications. This capability protects your intellectual property and improves the performance of your application. Microsoft calls the resulting database an ACCDE.

Building Error Handling into Your Application

If you don’t build error handling into your application and an error occurs while your user runs your application from Access’s runtime version, Access will rudely exit the user out of the program. She won’t get an appropriate error message and will be left wondering what happened. Hence, it’s essential that you add error handling to your application’s procedures. Chapter 17, “Error Handling: Preparing for the Inevitable,” covers error handling techniques.

Adding Custom Help

In most cases, you want your users to have at least some level of custom help specific to your application. You can use the ControlTip Text property of controls and the Description property of fields to add the most basic level of help to your application. The ControlTip Text property provides a description of a control when a user hovers his mouse pointer over the control. The Description property of a field appears on the status bar when a control based on that field has the focus. If you are more ambitious, and if the scope and budget for the application warrant it, you can build a custom help file for your application. To add custom help to your application, you must build a help file; then you can attach parts of it to the application’s forms and controls.

Building Custom Ribbons

You should build your own ribbons that you associate with specific forms and reports. Custom ribbons add both polish and functionality to your application.

After you complete these steps, you’ll be ready for the final phase of preparing your application for distribution:

• Test your application by using the /Runtime switch.
• Install your application on a machine that has never run a copy of either the standard or runtime version of Access.
• Test your application on the machine; make sure it runs as expected.

Begin by testing your application with the /Runtime switch. This switch simulates the runtime environment, allowing you to mimic user actions under the runtime version of Access. Taking this step saves you a lot of time and energy. It will find most, if not all, of the problems associated with the runtime version.

After you test your application with the /Runtime switch, you must test your application by running the install on a machine that has never contained a copy of either the standard or runtime version of Access. The whole idea is to test your application on a machine containing no Access-related files. This action ensures that you have included all the required files on your setup disks.

I suggest that that you use a “ghosting” utility such as Symantec Ghost to create a complete image of your operating system and application drives. Install and fully test your application; make sure you experiment with every feature. After you have completed the testing process, restore the original machine from the Ghost image so that you can use it to test your next installation.

Using Access as a Front End

If you’re planning to use Access as a front end to other databases, you need to consider a few issues. In fact, the whole design methodology of your system will differ depending on whether you plan to store your data in an Access database or on a back-end database server.

In a system where you store your data solely in Access tables, the Access Database Engine supplies all data retrieval and management functions and handles security, data validation, and enforcement of referential integrity.

In a system where Access acts as a front end to client/server data, the server handles the data management functions. It’s responsible for retrieving, protecting, and updating data on the back-end database server. In this scenario, the local copy of Access is responsible only for sending requests and getting either data or pointers to data back from the database server. If you’re creating an application in which Access acts as a front end, capitalizing on the strengths of both Access and the server can be a challenging endeavor.

Factors You Need to Worry About When Converting to Client/Server

The transition to client/server technology isn’t always a smooth one. You need to consider the following factors if you’re developing a client/server application or planning to eventually move your application from an Access database to a back-end structured query language (SQL) database server:

• Not every back-end database supports all field types that Access supports.
• The upsizing process will not convert any security you set up in Access to your back-end database.
• You will have to re-establish many of the validation rules you set up in Access on the back end.
• Not all back ends support referential integrity. Depending on the database that you are upsizing to, the upsizing process might not automatically set up the referential integrity that you established in Access.
• Queries involving joins that could be updated in Access can’t always be updated on the back-end server.

This list is just an overview of what you need to think about when moving an application from an Access database with linked tables to a back-end server or when developing an application specifically for a back end. Many of these issues have far-reaching implications. For example, if you set up validation rules and validation text in your application, you will often need to rewrite the rules as triggers on the back end. If the user violates a validation rule that you set up on the back end, you will get a returnable error code. You have to respond to this code by using error handling in your application, displaying the appropriate message to your user. You can’t use the Validation Text property with your client/server databases.

Benefits and Costs of Client/Server Technology

With all the issues discussed in the previous section, you might ask, “Why bother with client/server?” In each case, you need to evaluate whether the benefits of client/server technology outweigh its costs. The major benefits include the following:

• Greater control over data integrity
• Increased control over data security
• Increased fault tolerance
• Reduced network traffic
• Improved performance
• Centralized control and management of data

These are some of the major expenses:

• Increased development costs
• Hardware costs for the server machine
• Setup costs for the server database
• The cost of employing a full- or part-time database administrator (DBA)

These and other issues are covered in more detail in Chapter 22.

Your Options When Using Access as a Front End

Client/server applications are not an all-or-none proposition; there is more than one way to implement them through Access. One option is to use Access as a true front end, which means that you store all data on the server and process all queries on the server. You do this by using pass-through queries and stored procedures, rather than stored Access queries. With pass-through queries, you pass a back-end–specific SQL statement to the back end instead of allowing Access to process it. When you use stored procedures, you store SQL statements on the back end and then execute them using Data Access Objects (DAO) or ActiveX Data Objects (ADO) code. (I cover this scenario briefly in Chapter 22 and in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.)

To make Access a true front end, you must also disable its natural ability to bind data to forms and reports. Doing so, however, eliminates all the features that make Access a strong product in the first place. Unfortunately, you haven’t eliminated all the overhead associated with the functionality you removed. If you want to use unbound forms for most or all of your application, you’re better off developing the entire application in a lower-overhead environment, such as Visual Studio .NET.

Another approach is a hybrid method in which you use a combination of linked tables, SQL pass-through queries, stored procedures, unbound forms, and local Access tables. The idea is that you take advantage of Access’s features and strong points whenever possible. You use pass-through queries and stored procedures to perform functions that you can accomplish more efficiently by communicating directly to the back end or that aren’t available at all with Access SQL. To further improve performance, you can perform many tasks locally and then communicate them to the server as one transaction, after you have completed any initial validation. In addition to the solutions just discussed, you can also download data to Access in bulk so that you can perform additional processing locally. Many possibilities exist, and each is appropriate in different situations. Experience and experimentation are needed to determine the combination of methods that will optimize performance in a given situation.

What Are the Considerations for Migrating to a Client/Server Environment?

The preceding sections have given you an overview of the issues you need to consider when building a client/server application or considering moving to a client/server environment in the future. Chapter 22 provides more detailed information. If you’re using Access as a front end, make sure that, as you read through this book, particularly the more advanced chapters, you take special note of any cautions about developing client/server applications. If you want in-depth coverage of client/server development techniques, refer to Alison Balter’s Mastering Access 2002 Enterprise Development.

Practical Examples: Applying the Strategy to the Computer Consulting Firm Application

The time and billing application for the computer consulting firm introduced in Chapter 1, “Access as a Development Tool,” could be composed of two databases: one containing the majority of the tables and the other with the remainder of the database objects, including static and temporary tables. To design the application properly and to make the transition to client/server as smooth as possible, you would develop the application with the idea that you might eventually move the data to a back-end server. You would base the forms and reports that make up the application on stored queries or embedded SQL statements to maximize their flexibility and efficiency. Finally, you would design the application so that it can easily run from Access’s runtime version, and you would secure it so that unauthorized users could not access its data and other objects.

Summary

Having a strategy before you begin the application development process is important. This chapter introduced many strategic issues, such as splitting a database into tables and other objects, and using Access as a front end. It also covered converting to a client/server environment, explored the benefits and costs involved in such a conversion, and discussed the different options available to you. The chapter tied these concepts together with an explanation of what you can do to prepare your applications for future growth. The chapter also explained what you need to be concerned about when preparing an application for distribution, including the importance of properly securing your databases.