| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
You should know about several tricks of the trade that can save you a lot of time in the development process and help to 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.
In a multiuser environment, it is 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 will refer to the database containing the tables as the Table database and the database containing the other objects as the Application database. The two databases are connected by linking from the Application database to the Table database. The reasons for this strategy are
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 are 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 Chapter 23, "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 are ready to install the changes, all you need to do is to 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 the new copy 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 that the data can be shared among the users of the system. There is no good reason why the other components of the database need to be shared. Access provides optimal performance if the Application database is stored on each local machine. This method not only dramatically improves performance but it also greatly reduces network traffic. If the Application database is stored on the file server, the application objects and code 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 needs to be sent over the network. The only complication of this scenario is that each time the Application is updated, it needs to be redistributed to the users—a small inconvenience relative to the performance benefits that are gained through 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 is easy to change from a link to a table stored in Access' 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 the design of your system with linked tables in mind, the transition is 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 many others are covered in Chapter 20, "Client/Server Techniques."
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. An example would be a State table. In most situations, this table rarely, if ever, changes. On the other hand, it is continually accessed to populate combo boxes, participate in queries, and so on. By placing the State table on each local machine, you improve performance and reduce 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 utilizes temporary tables, a conflict occurs as 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 have included all the tables in the same database as the rest of your database objects, don't despair; Access 95 includes a Database Splitter Wizard. This valuable tool can be accessed by selecting Tools|Add-ins|Database Splitter. The Database Splitter, as well as linked tables, is covered in Chapter 19, "Utilizing External Data."
If you are 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 part of Access provides all data retrieval and management functions. All security, data validation, and the enforcement of referential integrity are handled by the Jet engine.
In a system where Access acts as a front-end to client/server data, the server provides the data management functions. The server is responsible for retrieving, protecting, and updating data on the back-end database server. In a situation where 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 are creating an application where Access acts as a front-end, you should capitalize on Access' strengths as well as on the server's strengths, which can be a very challenging endeavor.
The transition to client/server is not always a smooth one. You need to consider several things if you are developing a client/server application or are planning to eventually move your application from an Access database to a back-end database:
This list is just an overview of what you need to think about when moving an application from an Access database with attached tables to a back-end or when developing specifically for a back-end. Many of these issues have far-reaching implications. For example, if you set up validation rules and validation text within your application, the rules need to be rewritten as triggers on the back-end. This is not your only problem. If a validation rule is violated on the back-end, you get a returnable error code. You have to handle this returnable error code using error handling within your application, displaying the appropriate message to your user. The Validation Text property cannot be used.
With all the issues discussed in the previous section, you might ask "Why bother with client/server?" Client/server technology provides significant benefits but involves large costs in time and money to be implemented properly. In each case, you need to evaluate whether the benefits of client/server technology outweigh the costs. The major benefits include
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 the movement of your data 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. This means that all data is stored on the server, and all queries are processed on the server. This approach is implemented using pass-through queries rather than stored Access queries. With pass-through queries (covered in Chapter 20), a back-end-specific SQL statement is passed to the back-end rather than 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 have done all this, though, you have eliminated all the features that make Access a strong product in the first place. Unfortunately, you have not eliminated all the overhead associated with the functionality that you removed. If you want to use this approach, you are 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 attached tables, SQL pass-through queries, and local Access tables. The idea is that you take advantage of Access' functionality and strong points wherever possible. Pass-through queries are used to perform functions that can be performed more efficiently by communicating directly to the back-end or that are not available at all using 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. 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 optimize performance in a given situation.
This section has provided an overview of the issues you need consider when building an application for client/server or with the idea that it might be moved to client/server in the future. More-detailed information is given in Chapter 20. The issues behind developing client/server applications were highlighted here to reduce the chances of unexpected grief in the future. If you read through the book with these issues in mind, you will be a much happier developer. If you are 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 regarding the development of client/server applications.
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 dramatically improve the performance and flexibility of your applications. In most cases, you do not 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 that is 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 do not realize that basing a form or report on a stored query is more efficient than basing the same form or report on an SQL statement. When you save a query, the Access database Jet engine creates a Query Plan. This plan contains information regarding 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. It 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 occurs when the form or report is opened and the Query Plan is executed on the fly.
When basing a form on table data, you cannot control the order of the records in the form, nor can you base the form on more than one table. You cannot limit the records displayed on the form until after the form is opened. 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. The order of the records included on a report is determined by the sorting and grouping of the report itself.
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 Record Source property of the form each time the user wants to view a different record. This technique, and other form techniques, 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 Access Developer's Toolkit (ADT) 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 ADT so that I can compile my applications." These are just two of the many misconceptions that exist regarding exactly what Access does and does not have to offer.
The ADT is a separate product you purchase from Microsoft. The most basic but important feature of ADT is a royalty-free distribution license that allows you to distribute unlimited copies of your application without your users having to own copies of Access. This means that by using the ADT, you can create applications that you distribute to your users. The users of your application can run the application with the runtime engine that you distribute to them. The ADT also provides several additional development tools:
Figure 2.1. The Setup Wizard used for application distribution.
Figure 2.2. The Replication Manager tool is used to assist with the replication process.
Figure 2.3. The Windows API Viewer contains Declares, Constants, and Type Structures required by API calls.
It is important that you understand the differences between the standard and runtime versions of Access. The following differences have definite implications on the way you develop any applications that you expect to run from the runtime version:
Some of the disabled features protect your applications. For example, the absence of the Database and Design windows means that your users cannot modify your application while running it under the runtime version of Access. Other disabled features translate into additional coding for you. One example is the absence of the Query-by-Form feature. This feature, available in the full version of Access, allows your users to easily filter form information. The absence of this feature can mean extra work for you.
With all the features absent from the runtime version of Access, it is 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 that you should probably take so that your application appears professional to the user. Six steps are involved in preparing your application for distribution with the runtime version of Access:
Your application should be based around and controlled via forms. It should generally begin with a main switchboard that allows the user to access 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. Switchboards can be built using an add-in called the Switchboard Manager, or they can be designed as custom dialogs. Building a switchboard as a custom dialog is covered in Chapter 13 "Let's Get More Intimate With Forms: Advanced Techniques." Using the Switchboard Manager to create switchboards is covered in Chapter 35, "Distributing Your Application with the Access Developer's Toolkit." The main advantage of using the Switchboard Manager is that it allows you to quickly and easily create a polished application interface. The primary advantage of custom switchboards is the flexibility and freedom that they provide.
You set a form as the starting point for your application by modifying the start-up options for your database. Set these options by selecting Tools|Startup. The Startup dialog appears (see Figure 2.4). Using this dialog, you can set start-up options for your application, including 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 34.
Figure 2.4. The Startup dialog enables you to control many aspects of your application environment.
As you will learn in the next section, a database is not secure just because you are running it from a runtime version of Access. Without security, your application can be modified by anyone with a full copy of Access. Securing your database objects is therefore an important step in preparing your application for distribution. Security is covered in Chapters 29 and 30.
If error handling is not built into your application and an error occurs while your user is running your application from the runtime version of Access, the user is rudely exited out of the program. The user does not receive an appropriate error message and is left to wonder what happened. It is therefore vital that you add error handling to the procedures within your application. Error handling is covered in Chapter 17, "Handling Those Dreaded Runtime Errors."
In most cases, you want your users to be provided with custom help specific to your application. To add custom help to your application, you must build a help file and then attach parts of the help file to forms and controls within your application. Help is covered in Chapter 33.
Finally, because built-in toolbars are not available in the runtime version and most of the features on the standard built-in menus are disabled, you should build your own toolbars and menus that are associated with specific forms and reports. This touch adds both polish and functionality to your application.
After you complete these steps, you are ready to take some final steps to ready your application for distribution:
Before you bother running the Setup Wizard (a somewhat lengthy process), it is 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 finds most if not all of the problems associated with running under the runtime version.
After you test your application using the /Runtime switch, you are ready to run the Setup Wizard. The Setup Wizard allows you to create setup disks or perform a network install. When your users are ready to install your application, they run the installation program using A:Setup (or the appropriate network drive and path). They are presented with a professional-looking, familiar setup program similar to that included with most Microsoft products.
After you run the Setup Wizard, it is important that you 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. I suggest that you use a 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 that you experiment with every feature. When you are done testing, delete everything but the zip file and then unzip the zip file into the Windows System directory (so that it contains all the files that it contained prior to the installation of your program). The whole idea is to test your application on a machine containing no Access-related files. This ensures that all 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 the machine to test your next installation.
Although this process cleans up much of what was changed as a result of installing the application, it does not fully restore the machine to its original state 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 the registry after you are done testing the application.
[ic:resource]PKZIP is a shareware utility. It can be obtained from PKWARE, Inc. It is important to properly register the utility when you begin to use it. This involves sending the appropriate fee to PKWARE, Inc., in Brown Deer, Wisconsin. The fee and full address can be obtained by typing PKZIP/?.
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 34. If you plan to distribute an application with the runtime version of Access, remember which features are available to your users; otherwise, you 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 that is distributed with the runtime version of Access can be modified just like any other database.
A user can run your application using the runtime version of Access. All the rules of running an application within the runtime version apply. This means that while running under the runtime version of Access, the user cannot go into Design view, they cannot create their own objects, they do not have access to the built-in toolbars, and so on.
This same user can install their own copy of the standard Access product. Using the standard version of Access they can open the same database. If the objects within the database have not been secured, the user can modify the application at will.
In short, a database prepared with the Setup Wizard is no different than any other database. The Setup Wizard does not modify an MDB file in any way. It simply compresses all the files necessary to run your application, including the database and runtime engine, and creates a network install or distribution disks containing the compressed files. Therefore, unlike an environment where an EXE prohibits users from viewing or modifying your code, only the implementation of security protects the design of your application.
By now you should understand the importance of securing your application. Setting up security is a fairly complex but extremely worthwhile process. Security can be set up at either a group or user level. You can assign rights to objects. These rights can be assigned to either individual users or a group of users. Figure 2.5 shows the User and Group Permissions dialog. As you can see, rights can be assigned for each object. In the case of 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 design of the table. Different groups or 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, and another group to view only. An additional group 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 cannot 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 illegally access as an executable file.
Figure 2.5. The User and Group Permissions dialog enables you to assign users and groups rights to each database object.
The computer consulting firm time and billing application, introduced in Chapter 1, will be made up of two databases: one containing the majority of the tables, and the other containing 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. It will be 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. This maximizes the flexibility and efficiency of the forms and reports. Finally, the application will be designed so that it can easily run from the runtime version of Access and will be secured so that its data and other objects cannot be accessed by unauthorized users.
It is important that you have a strategy before you begin the application development process. This chapter introduced many strategic issues. It began by discussing the idea of splitting tables and other objects. It then talked about using Access as a front-end. Issues such as what you need to worry about when converting to client/server, benefits and costs of client/server technology, and the various options available to you, were discussed in detail. The chapter then tied things together by explaining what you can do to prepare your applications for future growth.
Many people do not fully understand the Access runtime engine. This chapter explained in detail what the runtime engine is and what it isn't. It also explained what you need to be concerned about in preparing an application for distribution. The chapter concluded by stressing the importance of properly securing your databases.
