You should know about several tricks of the trade that can save you a lot of time in the development process and help make sure 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 later chapters of the book.
In a multiuser environment, it's almost imperative that the tables that make up your system be placed in one database and the rest of the system objects be placed in another database. 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. The two databases are connected by linking from the Application database to the Table database. Here are the reasons for this strategy:
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, covered in Chapter 24, “Replication Made Easy,” could help you with this problem.
The simplest solution is to split the database objects so that the tables (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 need to do is copy the Application database to the file server. The new Application database can then be installed 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 remains intact and is unaffected by the process.
The second benefit of splitting the database objects has to do with 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 not only improves performance, but greatly reduces network traffic. 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, only the data will need to be sent over the network. The only complication of this scenario is that each time the Application database is updated, it will need to be redistributed to the users—a small inconvenience compared to the performance benefits gained from this structural split.
The third benefit of splitting tables from the other database objects has to do with 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 a matter of simply linking to the external tables. Some of these issues are covered in this chapter, and others are covered in Chapter 20, “Client/Server Techniques.”
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.
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 will occur 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 97 includes a Database Splitter Wizard. You can find this valuable tool by choosing Tools | Add-ins | 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 that none of them are split in the manner I recommend until Chapter 19. This is because until you learn all you need to know about splitting database objects, I don't think it's helpful to split the sample databases. Each chapter, from Chapter 19 on, uses the strategy recommended in this chapter.
The record source for a form or report can be based on a table object, a query object, or an SQL statement. By basing forms and reports on stored queries, 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, 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 an 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, just the fields and records specified within the query are returned to the workstation.
Many developers don't realize that basing a form or report on a stored query is more efficient than basing it on an SQL statement. When you save a query, the Access database Jet Engine creates a Query Plan, which 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 as well as 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. When a form or report is based on an SQL statement, the optimization process happens when the form or report is opened, and the Query Plan is executed on-the-fly.
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, unless you are using Access's OpenForm method with a Where argument. By basing a form on a query, 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 record set. 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. This technique, and others, are covered in detail in Chapter 13, “Let's Get More Intimate with Forms: Advanced Techniques.”
Many developers misunderstand what Access has to offer out of the box and what the Office Developer Edition (ODE) 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 ODE so that I can compile my applications with the ODE tools.” These are just two of the many misconceptions about exactly what the ODE 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 ODE, which includes a license for Office Professional plus all the features from the old Access Developer's Toolkit (ADT). An important feature of the ODE is a royalty-free distribution license that allows you to distribute unlimited copies of your Access application without your users having to own copies of Access. This means that by using the ODE tools, you can create applications you distribute to your users, who can run the application with the runtime engine you distribute to them. The ODE tools also include the following:
The Microsoft Access Language Reference and Microsoft Office 97 Data Access Reference.
A Setup Wizard that helps you create disks containing compressed files with everything you need to install and run your application. The Setup Wizard is covered in Chapter 37, “Distributing Your Application,” and is pictured in Figure 2.1.
A host of ActiveX custom controls that can be used to enhance your application's functionality and distributed to your users as part of your ODE license. ActiveX custom controls are covered in Chapter 26, “Using ActiveX Controls.”
The Microsoft Replication Manager helps you with the replication process by letting you schedule updates between replicas, determine which objects in the database are replicated, display all the replicas in a replica set, and manage multiple replica sets. The Replication Manager is covered in Chapter 24 and shown in Figure 2.2.
The Windows 95 Help Compiler and accompanying documentation, covered in more depth in Chapter 36, “Developing a Help File.”
The Windows API Viewer has all the declares, constants, and type structures used with the 32-bit Windows application programming interface (API). It allows you to easily copy the function, constant, and type declarations into your Code modules. The Windows API Viewer is covered in Chapter 31, “Using External Functions: The Windows API,” and shown in Figure 2.3.
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 your 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 that you must take some special steps to prepare your application for distribution. Some of the steps are specific to running from the runtime version, but most are steps you'll probably want to take so your application seems professional to the user. There are six steps to prepare your application for distribution with the runtime version of Access:
Basing your application around forms
Adding start-up options to your database
Securing the objects in your application
Building error handling into your application
Adding a help file to your application
Building 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. The main switchboard can bring the user to additional switchboards, such as a data-entry switchboard, a report switchboard, or a maintenance switchboard. You can build switchboards by using an add-in called the Switchboard Manager or by designing them as custom dialog boxes. Building a switchboard as a custom dialog box is covered in Chapter 13, and using the Switchboard Manager to create switchboards is covered in Chapter 37. The main advantage of using the Switchboard Manager is that it lets you quickly and easily create a polished application interface, but the primary advantage of custom switchboards is the flexibility and freedom they offer.
You set a form as the starting point for your application by modifying the start-up options for your database. Set these options by choosing Tools | Startup to open the Startup dialog box. (See Figure 2.4.) In this dialog box, you can set start-up options, such as a start-up form, an application title, and an icon that appears when your application is minimized. These options are covered in detail in Chapter 37.
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 32, “Database Security Made Easy,” and 33, “Complex Security Issues.”
In addition to security, Access 97 offers 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 37).
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. He or she won't get an appropriate error message and will be left wondering what happened, so it's essential that you add error handling to your application's procedures. Error handling is covered in Chapter 17, “Handling Those Dreaded Runtime Errors.”
In most cases, you want your users to have custom help specific to your application. To add custom help to your application, you must build a help file, then attach parts of it to forms and controls in your application. Help files are covered in Chapter 36.
Finally, because built-in toolbars aren't available in the runtime version 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:
Test your application by using the /Runtime switch.
Create setup disks or perform a network install with the Setup 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 to make sure it runs as expected.
Before you bother running the Setup Wizard (a somewhat lengthy process), it's best that you run your application using the /Runtime switch. This switch simulates the runtime environment, allowing you to simulate 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 running under the runtime version.
After you test your application with the /Runtime switch, you're ready to run the Setup Wizard, which lets you create setup disks or perform a network install. When your users are ready to install your application, they run the installation program by using A:Setup (or the appropriate network drive and path) to get a professional-looking, familiar setup program similar to those included with most Microsoft products.
After you run the Setup Wizard, you must test your application by running the install on a machine that has never had a copy of either the standard or runtime version of Access. I suggest you use a compression utility such as PKZIP to zip all the files in the test machine's Windows System directory or back up the entire Windows directory to another directory. Install and fully test your application; make sure you experiment with every feature. When you're done testing, delete everything but the zip file, then unzip the zip file into the Windows System directory (so that it holds all the files it contained before your program's installation). 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. After you test your application, restore the machine to its original state so that you can use it to test your next installation.
Warning
Although this process cleans up much of what was changed as a result of installing the application, it doesn't fully restore the machine to its original state. This is because the registry is modified during the install process. If you want to fully restore the machine to its original state, you must back up the registry before the install and restore it once you're done testing the application.
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 37. 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. A database distributed with the runtime version of Access can be modified just like any other database.
Users can run your application using Access's runtime version, and all the rules of running an application under the runtime version apply. This means that while running under the runtime version of Access, 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.
These same users can install their own copies of the standard Access product. Using the standard version of Access, they 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 Setup Wizard is no different from any other database. The Setup 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 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 2.5 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 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.
Figure 2.5. The User and Group Permissions dialog box lets you assign user and group rights to each database object.
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 can't be violated, 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 access illegally as an executable file.
If you're planning to use Access as a front-end to other databases, then 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 part of Access supplies all data retrieval and management functions. The Jet Engine also handles security, data validation, and enforcing 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. When Access acts as a front-end, 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 isn't always a smooth one. You need to consider several 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, but that isn't your only problem. If a validation rule is violated on the back-end, you will get a returnable error code. You have to handle this returnable error code by using error handling in your application, displaying the appropriate message to your user. The Validation Text property can't be used.
Tip
Some of the issues covered in this chapter can be handled by the Upsizing Wizard. This tool, available from Microsoft, automates the migration of data from the native Access data format to Microsoft SQL Server. The Upsizing Wizard is covered in Chapter 20, “Client/Server Techniques.”
With all the issues discussed in the previous section, you might ask “Why bother with client/server?” Client/server technology offers important benefits but requires high costs in time and money if it's to be used properly. In each case, you need to evaluate whether the benefits of client/server technology outweigh the 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 costs:
Increased development costs
Hardware costs for the server machine
Setup costs for the server database
Full- or part-time database administrator (DBA)
These lists summarize the major costs and benefits of client/server technology; they are meant only to alert you to what you need to think about when evaluating your data's movement to a back-end database server. These and other issues are covered in more detail in Chapter 20.
Client/server is not an all-or-none proposition, nor is there only one way to implement it using Access as a front-end. One option is to use Access as a true front-end, which means all data is stored on the server and all queries are processed on the server. This is done by using pass-through queries rather than stored Access queries. With pass-through queries (covered in Chapter 21, “Client/Server Strategies”), a back-end–specific SQL statement is passed to the back-end instead of being processed by Access. To make Access a true front-end, you must also disable its natural ability to bind data to forms and reports. After you've done all this, though, you have eliminated 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.
Another approach is a hybrid method in which you use a combination of linked tables, SQL pass-through queries, and local Access tables. The idea is that you take advantage of Access's features and strong points whenever possible. Pass-through queries 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. A new feature called ODBCDirect allows you to communicate with the back-end database without loading the Microsoft Jet Engine. With ODBCDirect, you can improve both the performance and functionality of a client/server application. Furthermore, data can also be downloaded to Access in bulk so that additional processing is 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 an application for client/server or considering moving it to client/server in the future. More detailed information is given in Chapters 20 and 21. The issues behind developing client/server applications are highlighted here to reduce the chances of unexpected grief in the future. If you read this book with these issues in mind, you will be a much happier developer. If you're using Access as a front-end, make sure as you read through this book, particularly the more advanced chapters, that you take special note of any warnings about developing client/server applications.
When it's finished, the Time and Billing application for the computer consulting firm introduced in Chapter 1, “Introduction to Access Development,” will be made up 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. The application will be developed with the idea that the data might eventually be moved to a back-end database server and designed so that the transition to client/server will be as smooth as possible. The forms and reports that make up the application will be based on stored queries to maximize their flexibility and efficiency. Finally, the application will be designed so that it can easily run from Access's runtime version and will be secured so that its data and other objects can't be accessed by unauthorized users. As you move through the chapters in the book, each of these design strategies will be carried out.
It's important that you have a strategy before you begin the application development process. This chapter has introduced many strategic issues, such as splitting tables and other objects and using Access as a front-end. It has also covered converting to client/server, the benefits and costs of client/server technology, and the different options available to you. These concepts have then been tied together by explaining what you can do to prepare your applications for future growth.
Many people don't fully understand the Access runtime engine, so this chapter has explained what it is and what it isn't. It has also explained what you need to be concerned about in preparing an application for distribution, including the importance of properly securing your databases.