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 and also explains several commonly misunderstood aspects of the Jet Engine, Access Runtime Engine, and security. All the topics covered in this chapter should be kept in mind when developing your Access applications. When reading this chapter, think of the general strategy outlined rather than the details of each topic. Each topic is covered in depth in other chapters of the book.
When earlier versions of Access ran in a multiuser environment, it was imperative to place the system’s tables in one database and the rest of the system objects in another database. With Access 95 and the advent of replication, you could either split the tables from the other objects or use replication to deploy design changes without comprising live data. Access 2000 and Access 2002 take this a step further with the Access Data Project (ADP), in which tables, views, stored procedures, and data diagrams are stored in a SQL Server database or by the SQL Server 2000 Desktop Engine (formerly the Microsoft Data Engine, or MSDE). forms, reports, macros, and modules are stored in the ADP file.
As mentioned earlier, splitting tables from other system objects is still a very 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 MDB 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, live data has been entered 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. Data replication, which is covered in Alison Balter’s Mastering Access 2002 Enterprise Development, could help you with this problem, but replication isn’t always feasible.
The simplest solution is to split the database objects so that the tables containing your data are in one MDB file, and the rest of your database objects (your application) are in a second MDB file. When you’re ready to install the changes, all you would need to do is copy the Application database to the file server. The new Application database could then be installed on each client machine from the file server. In this way, users could run new copies of the application from their machines. The database containing your data tables would 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 the data can be shared among the system’s users; however, there’s no good reason why the other database components need to be shared. Access gives you optimal performance if the Application database is stored on each local machine. This method also significantly reduces network traffic, and it decreases the chance of database corruption. If the Application database is stored on the file server, the application objects and code will need to be sent over the network each time an object in the database is opened. If the Application database is stored on each local machine, then only the data will need to be sent over the network. The only complication to this scenario is that, each time the Application database is updated, it will need to be redistributed to the users. On an already overtaxed network, this is a small inconvenience compared to the performance benefits gained from this structural split.
The third benefit of splitting tables from the other database objects is scalability. Because the tables are already linked, it’s easy to change from a link to a table stored in Access’s own proprietary format to any ODBC database, 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 sounds. There are many issues associated with using Access as a front end to client/server data that go far beyond simply linking to the external tables. Some of these issues are covered in this chapter, and others are covered in Chapter 20, “Developing Multiuser and Enterprise Applications.” Client/server techniques are covered in extensive detail in Alison Balter’s Mastering Access 2002 Enterprise Development.
Tip
A few special types of tables should be stored in the Application database rather than the Table database. Tables that rarely change should be stored in the Application database on each user’s local machine. For example, a State table rarely, if ever, changes, but it’s continually accessed to populate combo boxes, participate in queries, and so on. Placing the State table on each local machine, therefore, improves performance and reduces network traffic. Lookup tables containing localized information such as department codes should also be placed in the Application database.
Temporary tables should also be placed on each local machine—this is more a necessity than an option. If two users are running the same process at the same time and that process uses temporary tables, a conflict occurs when one user overwrites the other’s temporary tables. Placing temporary tables on each local machine improves performance and eliminates the chance of potentially disastrous conflicts.
If you have already designed your application and included all the tables in the same database as the rest of your database objects, don’t despair; Access 2002 includes the Database Splitter Wizard. You can find this valuable tool by choosing Tools | Database Utilities | Database Splitter. The Database Splitter, as well as linked tables, is covered in Chapter 19, “Using External Data.”
Note
I split all the applications I build into two databases. However, you might notice when looking at the sample databases in this book that, until you reach Chapter 19, none of the chapters show databases split in the manner I recommend. This is because, until you learn all you need to know about splitting database objects, I don’t think it’s helpful to be working with a split sample database. From Chapter 19 on, however, each chapter offers some sample databases split according to the strategy recommended here.
The record source for a form or report can be based 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, just the fields and records specified within the query are returned to the workstation.
In Access 2.0, a form or report based on a stored query was more efficient than a form or report based on a SQL statement. This was the case because when you save a query, the Access database Jet Engine creates a Query Plan. This plan contains information on the most efficient method of executing the query. When the query is saved, the Jet 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. This plan is used whenever a form or report based on that query is executed. With Access 2.0, when a form or report was based on a SQL statement, the optimization process happened when the form or report was opened, and the Query Plan was executed on-the-fly. With Access 97, Access 2000, and Access 2002, an embedded SQL statement is optimized just like a stored query. It is therefore up to you whether you prefer to use a stored query or an embedded SQL statement as the foundation for your forms and reports.
On the other hand, when basing 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 displayed on the form until after the form is opened. By basing 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 records are displayed.
Everything just mentioned applies to reports as well, except the order of the records, which is determined by the sorting and grouping of the report itself.
Tip
Many other techniques are available to you when displaying a form based on a large recordset. My favorite involves basing the form on only a single record at a time and changing the form’s RecordSource property each time the user wants to view a different record. Another technique that I use is to base the form’s RecordSource property on the value selected in a combo box in the Header section of the form. I use the After_Update event of the combo box to requery the form. Because the form’s RecordSource uses the combo box value for criteria, the desired record is displayed. These techniques, and others, are covered in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.
Many developers don’t understand what Access has to offer out of the box and what the Microsoft Office Developer (MOD) tools can add to the picture. They often tell me, “I can’t develop applications in Access because my company refuses to buy each user a copy of Access,” or “I’m going to buy the MOD so that I can compile my applications with the MOD tools.” These are just two of the many misconceptions about exactly what the MOD tools do and don’t have to offer.
You no longer need to buy a separate product to create runtime versions of your Access applications. As a developer, you will likely buy the MOD tools, which include Office Premium and all the features from the old Office Developer Environment (ODE), plus many new components. An important feature of the MOD tools is a royalty-free distribution license. It allows you to distribute unlimited copies of your Access application; your users don’t have to own separately licensed copies of Access. By using the MOD tools, you can create applications and distribute them to your users, along with the necessary runtime engine. The MOD tools includes numerous additional tools that are covered in Alison Balter’s Mastering Access 2002 Enterprise Development.
It’s important to understand the differences between the standard and runtime versions of Access. The following differences have definite implications for the way you develop any applications you expect to run from the runtime version:
The Database, Macro, and Module windows aren’t available in the runtime environment.
No Design views are available in the runtime environment.
No built-in toolbars are available in the runtime environment.
Many windows, menus, and commands are invisible in the runtime environment. For example, the Window | Hide and Window | Unhide commands are invisible. Although these and other commands aren’t visible, their functions are generally accessible by using code.
You must build error handling into your runtime applications. If you don’t, when an error occurs, the application displays a standard Access dialog box indicating an irrecoverable error and then exits to the desktop.
You must build your own custom help files for each runtime application.
Some keystrokes aren’t available in the runtime application.
Some of the disabled features protect your applications. For example, the absence of the Database and Design windows means that your users can’t modify your application while running it under Access’s runtime version. Other disabled features translate into additional coding chores for you, such as the absence of command bars. If you want your application to offer toolbars, you have to build your own and then assign them to the forms and reports in your database.
With all the features absent from the runtime version of Access, it’s not surprising 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 specific to running Access from the runtime version:
Base your application around forms.
Add startup options to your database.
Secure the objects in your application.
Build error handling into your application.
Add some level of custom help.
Build custom command bars to be associated with your application’s forms and reports.
Your application should be based on and controlled through 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 the rest of the application is based. 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 an add-in called the Switchboard Manager. Alternatively, you can design them as custom dialog boxes. Building custom dialog boxes is covered in Chapter 9, “Advanced Form Techniques”; using the Switchboard Manager is covered in Chapter 32, “Distributing Your Application.” The main advantage of using the Switchboard Manager is that it lets you quickly and easily create a polished application interface. 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. All other forms and reports that make up the application are accessed via custom menu bars and toolbars on the contacts form.
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. Choose Tools | Startup to open the Startup dialog box. (See Figure 18.1.) In this dialog box, you can set options, such as a startup form, an application title, and an icon that appears when your application is minimized. These options are covered in detail in Chapter 32.
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. Without security, your application can be modified by anyone with a full copy of Access, so securing your database objects is an important step in preparing your application for distribution. Security is covered in Chapters 27, “Database Security Made Easy,” and 28, “Advanced Security Techniques.”
Access 2000 and Access 2002 also offer you the ability to remove the source code from your applications. This protects your intellectual property and improves the performance of your application. The resulting database is called an MDE (covered in Chapter 32).
If error handling isn’t built into your application and an error occurs while your user is running your application from Access’s runtime version, the user will be rudely exited 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. Error handling is covered in Chapter 16, “Error Handling: Preparing for the Inevitable.” The VBA Error Handler, included with the Microsoft Office Developer (MOD) tools, can also assist in building error handling into your application.
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 attach parts of it to application’s forms and controls. The HTML Help Workshop, included with the Microsoft Office Developer (MOD) tools, can assist with the process.
Finally, because built-in toolbars aren’t available in the runtime version of Access and most of the features on the standard built-in menus are disabled, you should build your own command bars associated with specific forms and reports. Creating custom command bars adds 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, which includes the following steps:
Test your application by using the /Runtime switch.
Create setup disks or perform a network install with the Package and Deployment Wizard.
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.
Before you bother running the Package and Deployment Wizard (a somewhat lengthy process), it’s best that you run 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’re ready to run the Package and Deployment Wizard (covered in Chapter 32). It lets you create setup disks or perform a network install. Selecting A:Setup (or the appropriate network drive and path) provides a professional-looking, familiar setup program similar to those included with most Microsoft products.
After you run the Package and Deployment Wizard, 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 ensures that all the required files are included on your setup disks.
I suggest 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. When you’re done testing, restore the original machine from the Ghost image, so that you can use it to test your next installation.
Tip
Symantec Ghost allows you to restore individual files, selected directories, or entire hard drives as needed. When a backup image file is created, it can be compressed by up to 70%, greatly reducing transfer times and storage requirements. Amongst its many other uses, Symantec Ghost greatly facilitates the testing process by allowing you to easily restore a test machine to its pretesting state. Learn more about Symantec Ghost at http://www.ghostsoft.com.
You have just read an overview of the differences between the full and runtime versions of Access. The process of preparing an application for distribution with the runtime version of Access is covered in detail in Chapter 32. If you plan to distribute an application with the runtime version of Access, remember which features will be available to your users; otherwise, you and your users will be in for some big surprises.
Many developers mistakenly think that distributing an application with the runtime version of Access is equivalent to distributing an EXE. An unsecured database distributed with the runtime version of Access can be modified just like any other database.
Users can run your application n, and all the rules of running an application under the runtime version apply. This means that users can’t go into Design view, can’t create their own objects, don’t have access to the built-in toolbars, and so on.
Using their own copies of the standard version of Access, users can open the same database. If the objects in the database haven’t been secured, users can modify the application at will.
In short, a database prepared with the Package and Deployment Wizard is no different from any other database. The wizard doesn’t modify an MDB file in any way. It simply compresses all the files needed to run your application, including the database and runtime engine, and creates a network install folder or distribution disks containing the compressed files. Two ways to protect the design of your application are to set up security and to distribute your application as an MDE file.
By now, you should understand the importance of securing your application. Setting up security is a complex but worthwhile process that can be done at either a group or user level. You can assign rights to objects, and those rights can be assigned to either individual users or a group of users. Figure 18.2 shows the User and Group Permissions dialog box. As you can see, rights can be assigned for each object. For a table, the user or group can be assigned rights to read, insert, update, and delete data as well as to read, modify, or administer the table’s design. Different groups of users can be assigned different rights to an object. For example, one group can be assigned rights to add, edit, and delete data. Another group can be assigned rights to edit only, another group to view only, and another can be denied the right to even view the data.
Available rights differ for tables, queries, forms, reports, macros, and modules. The types of rights that can be assigned are appropriate to each particular type of object. When security has been properly invoked, it is difficult to violate, no matter how someone tries to access the database objects (including using the runtime version of Access, a standard copy of Access, programming code, or even a Visual Basic application). If properly secured, the database is as difficult to illegally access as an executable file.
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 your data is stored solely in Access tables, the Jet 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.
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 all field types supported in Access are supported in every back-end database.
Any security you set up in Access won’t be converted to your back-end database.
Validation rules you set up in Access need to be re-established on the back end.
Referential integrity isn’t supported on all back ends. If it is on yours, it won’t automatically be carried over from Access.
Queries involving joins that could be updated in Access can’t 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, the rules will need to be rewritten as triggers on the back end. If a validation rule is violated 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. The Validation Text property can’t be used.
Tip
The Access 2000 and Access 2002 Upsizing Wizards address most of the transitioning issues covered in this chapter. These tools, included as part of Access 2000 and Access 2002, respectively, automate the migration of data from the native Access data format to Microsoft SQL Server. The Upsizing Wizard is covered in Alison Balter’s Mastering Access 2002 Enterprise Development.
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 20, “Developing Multiuser and Enterprise Applications.”
Client/server applications are not an all-or-none proposition, nor is there only one way to implement them through Access. One option is to use Access as a true front end, which means that all data is stored on the server and all queries are processed on the server. This is done by using pass-through queries and stored procedures, rather than stored Access queries. With pass-through queries, a back-end–specific SQL statement is passed to the back end instead of being processed by Access. With stored procedures, SQL statements are stored on the back end and are executed using DAO or ADO code. (Both scenarios are covered briefly in Chapter 20 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 this approach, you’re better off developing the entire application in a lower-overhead environment, such as Visual Basic, or instead developing a Web—based solution.
Another approach is a hybrid method in which you use a combination of linked tables, SQL pass-through queries, stored procedures, and local Access tables. The idea is that you take advantage of Access’s features and strong points whenever possible. Pass-through queries and stored procedures are used to perform functions that are done more efficiently by communicating directly to the back end or that aren’t available at all with Access SQL. To further improve performance, many tasks can be performed locally and then communicated to the server as one transaction, after any initial validation has been done. Access Project files, introduced in Access 2000, allow you to communicate with the back-end database without loading the Microsoft Jet Engine. With Access Projects, commonly referred to as ADP files, you can improve both the performance and functionality of a client/server application. The basics of ADP files are covered in Chapter 20. The ins and outs of working with ADP files are covered in Alison Balter’s Mastering Access 2002 Enterprise Development. In addition to the solutions just discussed, data can also be downloaded to Access in bulk so that additional processing can be done locally. Many possibilities exist, and each is appropriate in different situations. It takes experience and experimentation to determine the combination of methods that will optimize performance in a given situation.
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. More detailed information is given in Chapter 20. 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.
It’s important that you have a strategy before you begin the application development process. 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. These concepts were then tied together by the explanation of what you can do to prepare your applications for future growth.
Many people don’t fully understand the Access runtime engine, so this chapter explained what it is and what it isn’t. It also explained what you need to be concerned about in preparing an application for distribution, including the importance of properly securing your databases.