| 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 |
I often find myself explaining exactly what types of applications can be built with Microsoft Access. Access offers a variety of features for different database needs. It can be used to develop five general types of applications:
At its most basic level, Access can be used to develop simple personal database management systems. I caution people against this idea, though. People who purchase Access hoping to automate everything from their wine collections to their home finances are often disappointed. The problem is that Access is deceptively easy to use. Its wonderful built-in wizards make Access look like a product that anyone can use. After answering a series of questions, the user has a completed application—switchboards, data-entry screen, reports, and all. In fact, when Access was first released, many people asked me whether I was concerned that my business as a computer programmer and trainer would diminish because Access appeared to let absolutely anyone write a database application. Although it is true that the simplest of Access applications can be produced without any thought of design and without a single line of code written by the user, most applications require at least some design and custom code.
As long as the user is satisfied with a Wizard-generated personal application with only minor modifications, no problems should occur. It is when the user wants to substantially customize a personal application that problems arise.
Access is an excellent platform for developing an application that will run a small business. Its wizards allow developers to quickly and easily build the foundation of the application. The ability to build code modules allows developers to create code libraries of reusable functions, and the ability to add code behind forms and reports allows them to create powerful custom forms and reports.
The main limitation of using Access as a platform to develop a custom application for a small business is the time and money involved in the development process. Just as many end users use Access Wizards to begin the development process only to find that they need to customize their application in ways that they are unable to accomplish on their own, small-business owners often experience this problem on an even greater scale. The demands of a small-business application are usually much higher than those of a personal application. I have been called in many times after a doctor, attorney, or other professional reaches a dead end in the development process. They are always dismayed at how much money it will cost to render their application usable.
Access is perfect for developing applications for departments within large corporations. It is relatively easy to upgrade the users within a department to the appropriate hardware—it is much easier to purchase additional RAM for 15 users than it is for 4,000! Furthermore, Access's performance is adequate for most departmental applications without the need for client/server technology. Finally, most departments in large corporations have adequate development budgets to produce well-designed applications.
Most departments usually have a PC guru who is more than happy to help design forms and reports. This makes my life as a developer much easier. I can focus on the hard-core development issues, leaving some of the simpler tasks to the local talent.
Although Access might be best suited for departmental applications, it can also be used to produce applications that are distributed throughout the organization. How successful this endeavor will be depends on the corporation. There is a limit to the number of users that can concurrently share an Access application while maintaining acceptable performance. There is also a limit to the number of records that each table can contain without a significant degradation in performance. These numbers vary depending on various factors:
My general rule of thumb for an Access application that is not client/server is that with more than 10 to 15 concurrent users and more than 100,000 records, poor performance generally results. Remember that this number varies immensely depending on the factors mentioned as well as on the definition of acceptable performance by you and your users.
Developers often misunderstand what Access is and what it isn't regarding client/server. I'm often asked "Isn't Access client/server?" The answer is that Access is an unusual product because out of the box it is a file server application, but it can act as a front-end to a client/server database. In case you are lost, here's an explanation: If you buy Access and develop an application that stores the data on a file server in an Access database, all data processing is performed on the workstation. This means that every time the user runs a query or report, all the data is brought over to the workstation. The query is then run on the workstation machine, and the results are displayed in a datasheet or on a report. This process generates a significant amount of network traffic, particularly if multiple users are running reports and queries at the same time on large Access tables. In fact, such operations can slow the entire network to a crawl.
A client/server database, such as Microsoft SQL Server or Oracle, processes queries on the server machine and returns results to the workstation. The server software itself is not capable of displaying data to the user. This is where Access comes to the rescue. Acting as a front-end, Access can display the data retrieved from the database server. It can display this data in the form of reports, datasheets, or forms. If the data is updated by the user in an Access form, the update is sent to the back-end database. This is generally all accomplished by attaching to these external databases so that they appear to both you and the user as Access tables. This process is covered in detail in Chapter 20, "Client/Server Techniques."
When you reduce the volume of network traffic by moving the processing of queries to the back-end, Access becomes a much more powerful development solution. It can handle significant volumes of data and a large number of concurrent users. The main issue usually faced by developers who want to deploy such a wide-scale Access application is the type of hardware that each user possesses. Although the processing of queries is done at the server, which significantly reduces network traffic, the application itself still must reside in the memory of each user's PC. The hardware requirements for an Access application are covered later in this chapter. Before you decide to deploy a wide-scale Access application, you need to ascertain the hardware configurations of all the users of your system.
One of Access's strongest points is its scalability. An application that begins as a small-business application running on a stand-alone machine can be scaled to an enterprise-wide client/server application. If you design your application properly, scaling can be accomplished with little to no rewriting of your application. This feature makes Access an excellent choice for growing businesses as well as for applications being tested at a departmental level with the idea that they might eventually be distributed corporate-wide.
The great thing about Access is that even acting as both the front-end and back-end with data stored on a file server in Access tables, it provides excellent security and the ability to establish database rules previously available only on back-end databases. As you will see in Chapters 29 and 30, security can be assigned to every object in a database at either a user or group level. Referential integrity rules can be applied at the database level, ensuring that orders are not entered for customers that do not exist. Data validation rules can be enforced at either a field or record level, maintaining the integrity of the data in your database. In other words, many of the features that have been previously available only on high-end database servers are now available using Access's own proprietary data storage format.
The term database means different things to different people. For many years, in the world of xBase (dBASE, FoxPro, CA-Clipper), database was used to describe a collection of fields and records. In a client/server environment, database refers to all the data, schema, indexes, rules, triggers, and stored procedures associated with a system. In Access terms, a database is a collection of all the tables, queries, forms, reports, macros, and modules that compose a complete system.
Access databases are made up of tables, queries, forms, reports, macros, and modules. Each of these objects has its own special function. The Access environment also consists of several miscellaneous objects. These include relationships, toolbars, menus, database properties, and import/export specifications. Together, these objects enable you to create a powerful, user-friendly, integrated application. Figure 1.1 shows the Access Database window. Notice the six tabs, one for each main type of object contained within a database. Let's take a tour of the objects that make up an Access database.
Figure 1.1. The Access Database window, with tabs for each type of database object.
Tables are the starting point for your application. Whether your data is stored in the Access format or you are referencing external data using linked tables, all the other objects in your database either directly or indirectly reference your tables.
To view all the tables that are part of your database, click on the Table tab of the Database window. If you want to view the data contained within a table, double-click on the name of the table that you want to view (you can also click on the Open button). The data within the table is displayed in a datasheet (see Figure 1.2). Notice that all the fields and records within the table are displayed. Many of the attributes of the datasheet can be modified. Furthermore, you can search for and filter data from within the datasheet. These techniques are not covered in this book but can be found in the Access user manual or any introductory Access book.
Figure 1.2. The datasheet view of the tblTimeCardHours table.
As a developer, you will most often want to view the design of the table, which is the blueprint or template for the table. To view the design of a table, click Design with the table selected. The design of the table appears (see Figure 1.3). Here you can view or modify all the field names, data types, and field and table properties. Access provides you with significant power and flexibility in customizing the design of your tables. These topics are covered in Chapter 3, "What Every Developer Needs to Know About Tables."
Figure 1.3. The design of the tblTimeCardHours table.
To properly maintain the integrity of your data and facilitate the process of working with the other objects in the database, you must define relationships among the tables in your database. This can be accomplished using the Relationships window. To view the Relationships window, select Tools|Relationships or Click Relationships on the toolbar. The Relationships window appears (see Figure 1.4). This window allows you to view and maintain the relationships in the database.
Figure 1.4. The Relationships window, where you view and maintain the relationships in the database.
Notice that many of the relationships in Figure 1.4 contain a join line between tables with a number 1 and an infinity symbol. This indicates a One-to-Many relationship between the tables. If you double-click on the join line, the Relationships dialog appears (see Figure 1.5). The Relationships dialog enables you to specify the exact nature of the relationship between tables. The relationship between tblClients and tblProjects, for example, is a One-to-Many relationship with referential integrity enforced. This means that projects cannot be added for clients that don't exist. Notice that the check boxes to Cascade Update Related Fields and Cascade Delete Related Records are both checked. This means that if a client is deleted, the client's projects are deleted. If a ClientID is updated in tblClients, all records containing that ClientID in tblProjects are automatically updated.
Figure 1.5. The Relationships dialog, which allows you to specify the nature of the relationship between tables.
Chapter 4, "Relationships: Your Key to Data Integrity," extensively covers the process of defining and maintaining relationships. For now, you should remember that relationships should be established both conceptually and literally as early in the design process as possible. They are integral to the successful design and implementation of your application.
Queries in Access are powerful and multifaceted. Select queries enable you to view, summarize, and perform calculations on the data in your tables. Action queries enable you to add to, update, and delete table data. To run a query, select the Queries tab, select the query that you want to run, and then click Run. A datasheet appears, containing all the fields specified in the query and all the records meeting the query's criteria (see Figure 1.6). In general, the data in a query result can be updated because the result of a query is actually a dynamic set of records, called a dynaset, based on your table's data.
Figure 1.6. The result of running the qryCustomerOrderInfo query.
When you store a query, only the definition of the query and the layout or formatting properties of the query and its datasheet are actually stored within the database. Access provides an intuitive, user-friendly tool with which you can design your queries. Figure 1.7 shows the Query Design window. To access this window, select the Queries tab and click Design. The query pictured in the figure selects data from tblClients, tblProjects, and tblTimeCardHours. It groups the query result by client name, outputting the name of each client and the amount of sales generated from the client within a certain period of time. This special type of query is called a Parameter query. It prompts for criteria at runtime, using the criteria to determine what records are included in the output. Queries are covered in Chapters 5 and 12. Because queries are the foundation for most forms and reports, they are covered throughout this book as they apply to the other objects in the database.
Figure 1.7. The design of a query that displays data from the tblClients, tblProjects, and tblTimeCardHours tables.
Although you can enter and modify data in the Datasheet view of a table, you cannot control the user's actions very well, nor can you do much to facilitate the data-entry process. This is where forms come in. Access forms can take on many traits. They are very flexible and powerful.
To view any form, select the Forms tab and then click Open. The form is opened in Form view. Figure 1.8 illustrates a data-entry form in Form view. This form is actually three forms in one, one main form and two subforms. The main form displays information from the Time Cards table. The subforms display information from the Time Card Hours table and the Time Card Expenses table. A combo box is used to facilitate the selection of the employee associated with a particular time card.
Figure 1.8. A time card data-entry form that includes time worked and expenses for an employee in a given time period.
Like tables and queries, forms can be viewed in Design view. To view the design of a form, select the Forms tab and click Design. Figure 1.9 shows the Time Cards form in Design view. Notice the two subforms contained within the main form. The main form has three sections: header, detail, and footer. Nothing is contained within the header of this form. Most of the content of the form is contained within the detail section. The form footer, which is not visible in the figure, contains a command button that allows the user to preview a printed time card containing all the information on the form. Forms are officially covered in Chapters 6 and 13. They are also covered throughout the text as they apply to other examples of building an application.
Figure 1.9. The design of the Time Cards form showing two subforms.
Forms allow you to enter and edit information; reports allow you to display information, usually to a printer. Figure 1.10 shows a report being previewed. To preview any report, select the Reports tab and then click Preview. Notice the graphic contained within the report, as well as other details such as the thick horizontal line. Like forms, reports can be elaborate and exciting while containing valuable information.
Figure 1.10. A preview of the Time Sheet report.
If you haven't yet guessed, reports can be viewed in Design view. Figure 1.11 shows a report in Design view. To view the design of any report, select the Reports tab and click Design after selecting the report you want to view. The report in Figure 1.11 illustrates a report containing many sections. In the figure, you can see a Report Header, Page Header, TimeCardID Group Header, and Detail section. These are actually only a few of the many sections available on a report. Just as a form can contain subforms, a report can contain subreports. The Detail section of this report contains two subreports, one containing hours, and the other containing expenses. Reports are covered in chapters 7 and 14 and throughout the book as they apply to other examples.
Figure 1.11. The Report Design view of the Time Cards report.
The macros in Access are not like the macros in other languages. They cannot be recorded, as in Microsoft Word or Excel. Access macros allow you to perform most of the tasks that you can manually perform from the keyboard, menus, and toolbars. They provide for conditions, allowing you to build logic into your application flow. Generally, you use VBA code contained in modules, rather than macros, to accomplish the tasks that your application must perform. This is because VBA code modules provide you with significantly more flexibility and power than do macros. Certain tasks can be performed only by using macros. Some macros are therefore included in most applications. Chapter 11, "What are Macros, and When Do You Need Them?," covers macros and their uses in detail.
To run a macro, select the Macros tab, click on the macro you want to run, and then click Run. The actions contained in the macro are executed.
To view the design of a macro, select the Macros tab, select the macro you want to modify, and click Design. The Macro Design window appears (see Figure 1.12). The macro pictured has four columns. The first column is the Macro Name column. This column allows you to specify the name of a subroutine within a macro. The second column allows you to specify a condition. The action in the third column of the macro does not execute unless the condition for that action evaluates to true. The fourth column allows you to document the macro. The bottom half of the Macro Design window allows you to specify the arguments that apply to the selected action. In Figure 1.12, the selected action is MsgBox. This action accepts four arguments: Message, Beep, Type, and Title.
Figure 1.12. The design of the Customers macro, containing macros names, conditions, actions, and comments.
Although macros are quite powerful, most of your application should be written in modules, using the Visual Basic for Applications (VBA) language, which is much more powerful and robust than macros. However, a few things can be accomplished using only macros. Macro basics and related topics are covered in Chapter 11.
Modules are the foundation of any application. They enable you to create libraries of functions that can be used throughout your application. Modules are usually made up of subroutines and functions. Functions always return a value, whereas subroutines do not. Using code modules, you can do the following:
These are just a few of the tasks that you can accomplish using modules. To view the design of an existing module, select the Modules tab and click Design. The Module Design window appears (see Figure 1.13). The module in Figure 1.13 contains a General Declarations section and one function called IsLoaded. Modules and VBA are discussed in Chapters 8 and 9 and covered extensively throughout this book.
Figure 1.13. The Global Code Module in Design view showing the General Declarations section and the IsLoaded function.
Finding a set of naming conventions and sticking to it is one of the keys to successful development in Access or any other programming language. In selecting a set of naming conventions, look for three characteristics:
The Leszynski naming convention, proposed by Stan Leszynski of Leszynski Company, Inc. and Kwery Corporation, is by far the best set of naming conventions currently published in the development world.
The Leszynski naming conventions provide a standardized approach for naming objects. They were derived from the Leszynski/Reddick naming conventions that were prominent with Access versions 1.x and 2.0. These standards were adopted and used extensively by the development community. They are found in most good development books and magazine articles written within the last couple of years. The new Leszynski naming conventions have been revised to deal with issues faced by people developing concurrently in Access, Visual Basic, Excel, and other Microsoft products that contain the VBA language. These conventions provide an easy-to-use, consistent methodology for naming the objects in all of these environments.
A summarized and slightly modified version of the Leszynski conventions for naming objects are published in Appendix B. I use them throughout the book, and I highlight aspects of them as they apply in each chapter.
One of the downsides of Access is the amount of hardware resources it requires. The requirements for a developer are different from those for an end user, so I have broken the system requirements into two parts. As you read through these requirements, be sure to note actual versus recommended requirements.
According to Microsoft documentation, the official minimum requirements to run Microsoft Access 7.0 for Windows 95 are as follows:
As if all that hardware isn't enough, my personal recommendations for a development machine are much higher because you will probably run other applications along with Microsoft Access. You also want to greatly reduce the chance of hanging or causing other problems because of low-memory conditions. I recommend the following for a development machine (in addition to Microsoft's requirements):
The bottom line for hardware is the more, the better. You just can't have enough memory. The more you have, the happier you will be when using Access.
Although the user's PC does not need to be as sophisticated as the developer's, I still recommend the following in addition to Microsoft's requirements:
Many developers believe that because Access is such a rapid application development environment, there is absolutely no need for system analysis or design when creating an application. I couldn't disagree more. As mentioned earlier in this chapter, Access applications are deceptively easy to create. Without proper planning, they can become a disaster.
The first step in the development process is task analysis, or consideration of each and every process that occurs during the users' workday—a cumbersome but necessary task. When I first started working for a large corporation as a mainframe programmer, I was required to carefully follow a task-analysis checklist. I had to find out what each user of the system did to complete his or her daily tasks, document each procedure and determine the flow of each task to the next, relate each task of each user to his or her other tasks as well as to the tasks of every other user of the system, and tie each task to corporate objectives. In this day and age of rapid application development and changing technology, this step in the development process appears to have gone out the window. I maintain that if care is not taken to complete this process on at least some level, the developer will have to rewrite large parts of the application.
After you have analyzed and documented all the tasks involved in the system, you are ready to work on the data analysis and design phase of your application. In this phase, you must identify each piece of information required to complete each task. These data elements need to be assigned to subjects. Each subject becomes a separate table in your database. An example of a subject would be a client. Every data element relating to that client would become a field in the client table. For example, the name, address, phone, credit limit, and any other pertinent information about the client would become fields within the client table.
You should determine the following for each data element:
You should also determine whether each data element is updatable and whether it is entered or calculated. You are now ready to determine whether your table structures are normalized.
Normalization is a fancy term for the process of testing your table design against a series of rules that ensure that your application will operate as efficiently as possible. These rules are based on set theory and were originally proposed by Dr. E.F. Codd. Although you could spend years studying normalization, its main objective is to create an application that runs efficiently with as little data manipulation and coding as possible. Here are six of the rules:
Let's look at an example. The datasheet pictured in Figure 1.14 is an example of a table that has not been normalized. Notice that the CustInfo field is repeated for each order. This means that if the customer address changes, it would need to be changed in every order assigned to that customer. The CustInfo field is not atomic. If you want to sort by city, you are out of luck because the city is in the middle of the CustInfo field. If the name of an inventory item changes, you would need to make the change in every record where that inventory item was ordered. Probably the worst problem in this example involves items ordered. With this design, you must create four fields for each item that the customer orders: name, supplier, quantity, and price. This design would make it extremely difficult to build sales reports and other reports that your users need to effectively run the business.
Figure 1.14. An example of a table that has not been normalized.
Figure 1.15 shows the same data normalized. Notice that the data has been broken out into several different tables: tblCustomers, tblOrders, tblOrderDetails, and tblSuppliers. The tblCustomers table contains data that relates only to a specific customer. Each record in this table is uniquely identified by a contrived CustID field. This field is used to relate the orders table, tblOrders, to tblCustomers. The orders table contains only information that pertains to the entire order, rather than to a particular item that was ordered. This table contains the CustID of the customer that placed the order and the date of the order. The tblOrders table is related to the tblOrderDetails table based on the OrderID. The tblOrderDetails table contains information about each item that was ordered for a particular OrderID. There is no concern about limiting the potential number of items that can be ordered. As many items can be ordered as necessary, simply by adding additional records to the tblOrderDetails table. Finally, supplier information has been placed in a separate table so that if any of the supplier information changes, it can be changed in one place.
Figure 1.15. An example of a table with a normalized design.
Although the task analysis and data analysis phases of application development have not changed significantly since the days of mainframes, the prototyping phase has changed. In working with mainframes or DOS-based languages, it was important to develop detailed specifications for each screen and report. I remember requiring users to sign off on every screen and report. Even a change such as the movement of a field on a screen meant a change order and approval for additional hours. After the user signed off on the screen and report specifications, the programmer would go off for days and work arduously to develop each screen and report. The programmer would return to the user after many months only to hear that everything was wrong. This meant back to the drawing board for the developer and many additional hours before the user could once again review the application.
The process is quite different now. As soon as the tasks have been outlined and the data analysis completed, the tables can be designed and the relationships among them can be established. The form and report prototype process can then begin. Rather than the developer going off for weeks or months before having additional interaction with the user, the developer can go off for only a few days, using the Access Wizards to quickly develop form prototypes.
As far as testing goes, you just can't do enough. I recommend that if your application is going to be run in both Windows 95 and Windows NT, you test in both environments. I also suggest that you test your application extensively on the lowest common denominator piece of hardware. The application might run great on your machine but provide unacceptable performance on your users' machines.
It is generally useful to test your application in pieces and as an integrated application. Recruit several people to test your application. Make sure your testers range from the most savvy of users to the least computer-adept person you can find. Both groups of people will be certain to find a completely different set of problems. Most importantly, make sure you are not the only tester of your application. You are the least likely person to find errors in your own programs.
Your application is finally ready to go out into the world, or at least you hope so! Distribute your application to a subset of your users. Make sure these users know that they are the test case. Make them feel honored to participate as the first users of the system. Warn them that problems might occur and that it is their responsibility to make you aware of those problems. If you distribute your application on a wide-scale basis and it does not operate exactly as it should, it will be difficult to regain the confidence of your users. This is why it is so important to roll out your application slowly.
Because Access is such a rapid application development environment, the maintenance period tends to be much more extended than for a mainframe or DOS-based application. Users are much more demanding. The more you give them, the more they want. For a consultant, this is great. Just don't get into a fixed bid situation, because you could very well end up on the losing end of that deal.
The three categories of maintenance activities are bug fixes, specification changes, and frills. Bug fixes need to be handled as rapidly as possible. The implications of specification changes need to be clearly explained to the user, including the time and cost involved in making the requested changes. As far as frills, try to involve the users in the addition of frills as much as possible by teaching them how to enhance forms and reports and by making the application as flexible and user-definable as possible. Of course, the final objective of any application is a happy group of productive users.
Your goal throughout this book is to build an application. The application will be a time and billing system for a computer consulting firm. Let's talk about the application from a design perspective.
The system will track client contacts and the projects associated with those clients. It will allow the users to record all hours billed for and expenses associated with each client and project. It will also allow the users to track pertinent information about each employee or subcontractor. The tables in the system are based on the tables that are produced by the Database Wizard. They have been modified somewhat, and their names have been changed to follow the Leszynski naming standards. The system you will build will be far more powerful and flexible than the one provided by the Database Wizard. Ten tables will be included in the system. Some of these tables are built in Chapter 3, "What Every Developer Needs to Know About Tables." They can all be found in the application databases located on the sample code CD-ROM:
The relationships among the tables are covered in more detail in Chapter 4. They are shown in Figure 1.16.
Figure 1.16. Relationships among tables in the time and billing system.
Before you learn about the practical aspects of Access development, it is important that you understand what Access is and how it fits into the application development world. Access is an extremely powerful product with a wide variety of uses. Access applications can be found on everything from the home PC to the desks of large numbers of corporate personal computer users going against enterprise-wide client/server databases.
When you understand what Access is and what it does, you are ready to learn about its many objects. Access applications are made up of tables, queries, forms, reports, macros, modules, menus, toolbars, relationships, and other objects. When designed properly, an Access application effectively combines these objects to provide the user with a powerful, robust, utilitarian application.
