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 buy 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, you have finished applications—switchboards, data-entry screens, reports, and the underlying tables that support them. In fact, when Access was first released, many people asked if I was concerned that my business as a computer programmer and trainer would diminish because Access seemed to let absolutely anyone write a database application. Although it’s 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 designing and custom code.

As long as you’re satisfied with a wizard-generated personal application with only minor modifications, no problems should occur. It’s when you want to substantially customize a personal application that problems can happen.

Access is an excellent platform for developing an application that can run a small business. Its wizards let developers quickly and easily build the application’s foundation. 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 for developing a custom small-business application is the time and money involved in the development process. Many people use Access wizards to begin the development process but find they need to customize their application in ways they can’t 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 reached a dead end in the development process. They’re always dismayed at how much money it will cost to make their application usable.

Access is perfect for developing applications for departments in large corporations. It’s relatively easy to upgrade departmental users to the appropriate hardware—for example, it’s much easier to buy 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 the development budgets to produce well-designed applications.

Fortunately, most departments usually have a PC guru, who is more than happy to help design forms and reports. This gives the department a sense of ownership because they have contributed to the development of their application. It also makes my life as a developer much easier. I can focus on the hard-core development issues, leaving some of the form and report design 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 is depends on the corporation. There’s a limit to the number of users who can concurrently share an Access application while maintaining acceptable performance, and there’s also a limit to the number of records that each table can contain without a significant performance drop. These numbers vary depending on factors such as the following:

My general rule of thumb for an Access application that’s not client/server is that poor performance generally results with more than 10–15 concurrent users and more than 100,000 records. Remember, these numbers vary immensely depending on the factors mentioned, as well as on the definition of acceptable performance by you and your users. The basics of when to move to a client/server database are covered in Chapter 20, “Developing Multiuser and Enterprise Applications.” The details of this topic are covered in a separate book, Alison Balter’s Mastering Access 2002 Enterprise Development, also published by Sams.

Developers often misunderstand what Access is and what it isn’t when it comes to being a client/server database platform. I’m often asked, “Isn’t Access a ‘client/server’ database?” The answer is that Access is an unusual product because it’s a file server application out of the box, but it can act as a front end to a client/server database. In case you’re 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 bring 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 can’t display data to the user, so this is where Access comes to the rescue. Acting as a front end, Access can display the data retrieved from the database server in reports, datasheets, or forms. If the user updates the data in an Access form, the update is sent to the back-end database. This can either be accomplished by linking to these external databases so that they appear to both you and the user as Access tables, or by using techniques that access client/server data directly.

Using Access project files (not to be confused with Microsoft Project files), you can build an application specifically for a client/server environment. These project files, known as ADP files (Access Data Project), contain the program’s forms, reports, macros, modules, and data access pages. The project is connected to the back-end database that contains the tables, stored procedures, views, and database diagrams that the program accesses. From within a project file, you can easily modify and manipulate objects stored on the server, using Access’s friendly graphical user interface. ADP files help to bring rapid application development to the process of building client/server applications. Because Access 2002 ships with an integrated data store (the SQL Server 2000 Desktop Engine), you can develop a client/server application on the desktop and then easily deploy it to an enterprise SQL Server database. The alternatives and techniques for developing client/server applications are covered briefly in Chapter 20. The details of how to develop Access projects are covered in a separate book, Alison Balter’s Mastering Access 2002 Enterprise Development.

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 huge volumes of data and a large number of concurrent users. The main issues usually faced by developers who want to deploy such a wide-scale Access application are

Although processing of queries in a client/server application is done at the server, which significantly reduces network traffic, the application itself still must reside in the memory of each user’s PC. This means that each client machine must be capable of running the appropriate operating system and the correct version of Access. Even when the correct operating system and version of Access are in place, your problems are not over. DLL conflicts often result in difficult-to-diagnose errors and idiosyncrasies in an Access application. Furthermore, Access is not the best solution for disconnected users who must access an application and its data over the Internet. Finally, Access 2002 is hardware hungry! The hardware requirements for an Access application are covered later in this chapter. The bottom line is that, before you decide to deploy a wide-scale Access application, you need to know the hardware and software configurations of all your system’s users. You must also decide if the desktop support required for the typical Access application is feasible given the number of people who will use the system that you are building.

Using data access pages, intranet and Internet users can update your application data from within a browser. Data access pages are HTML documents that are bound directly to data in a database. They are stored outside your database and are used just like standard Access forms except that they are designed to run in Microsoft Internet Explorer 5.5 or higher, rather than in Microsoft Access. Data access pages use dynamic HTML in order to accomplish their work. Because they are supported only in Internet Explorer 5.5 or higher, data access pages are much more appropriate as an intranet solution than as an Internet solution. In addition to using data access pages, you can also publish your database objects as either Static or Dynamic HTML pages. Static pages are standard HTML and can be viewed in any browser. Database objects can be published dynamically to either the HTX/IDC file format or to the ASP (Active Server Page) file format. HTX/IDC files are published dynamically by the Web server and are browser independent. ASP files published by Microsoft Access are also published dynamically by the Web server, but require Internet Explorer 4.0 or higher on the client machine.

New to Access 2002 is the ability to create XML data and schema documents from Jet or SQL Server structures and data. Data and data structures can also be imported into Access from XML documents. This can all be accomplished either using code, or via the user interface.

Tables are the starting point for your application. Whether your data is stored in an Access database or you are referencing external data by using linked tables, all the other objects in your database either directly or indirectly reference your tables.

To view all the tables that are contained in the open database, click the Tables icon in the Objects list. (Note that you won’t see any hidden tables unless you have checked the Hidden Objects check box in the Tools, Options dialog box’s View page.) If you want to view the data in a table, double-click the name of the table you want to view. (You can also select the table, and then click the Open button.) The table’s data is displayed in a datasheet, which includes all the table’s fields and records. (See Figure 1.2.) You can modify many of the datasheet’s attributes and even search for and filter data from within the datasheet. If the table is linked to another table (like the Northwind Customers and Orders tables), you can also expand and collapse the subdatasheet to view data stored in child tables. These techniques aren’t covered in this book but can be found in the Access user manual or any introductory Access book, such as Sams Teach Yourself Access 2002 in 21 Days.

Figure 1.2. Datasheet view of the Customers table in the Northwind database.

As a developer, you most often want to view the table’s design, which is the blueprint or template for the table. To view a table’s design, click the Design icon with the table selected. (See Figure 1.3.) In Design view, you can view or modify all the field names, data types, and field and table properties. Access gives you the power and flexibility you need to customize the design of your tables. These topics are covered in Chapter 2, “What Every Developer Needs to Know About Tables.

Figure 1.3. The design of the Customers table.

Relationships: Tying the Tables Together

To properly maintain your data’s integrity and ease the process of working with other objects in the database, you must define relationships among the tables in your database. This is accomplished using the Relationships window. To view the Relationships window, with the Database window active, select Tools|Relationships, or click Relationships on the toolbar. (See Figure 1.4.) In this window, you can view and maintain the relationships in the database. If you or a fellow developer have set up some relationships, but you don’t see any in the Relationships dialog box, select Relationships|Show All to unhide any hidden tables and relationships (you might need to click to expand the menu for this option to appear).

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 have 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 Edit Relationships dialog box opens. (See Figure 1.5.) In this dialog box, you can specify the exact nature of the relationship between tables. The relationship between Customers and Orders, for example, is a one-to-many relationship with referential integrity enforced. This means that orders can’t be added for customers who don’t exist. Notice that the check box to Cascade Update Related Fields is checked. This means that if a CustomerID is updated, all records containing that CustomerID in the Orders table are also updated. Because Cascade Delete Related Records is not checked, customers cannot be deleted from the Customers table if they have corresponding orders in the Orders table.

Figure 1.5. The Relationships dialog box, which lets you specify the nature of the relationship between tables.

Chapter 3, “Relationships: Your Key to Data Integrity,” extensively covers the process of defining and maintaining relationships. It also covers the basics of relational database design. For now, remember that relationships should be established both conceptually and literally as early in the design process as possible. They are integral to successfully designing and implementing your application.

Note

In an Access project (ADP), relationships appear in the Database window as Database Diagrams. They are not stored in the Access project file. They are instead stored in the SQL Server database to which the ADP is connected.

Queries in Access are powerful and multifaceted. Select queries allow you to view, summarize, and perform calculations on the data in your tables. Action queries let you add to, update, and delete table data. To run a query, select Queries from the Objects list and then double-click the query you want to run, or click to select the query you want to run, and then click Open. When you run a select query, a datasheet appears, containing all the fields specified in the query and all the records meeting the query’s criteria. (See Figure 1.6.) When you run an action query, the specified action is run, such as making a new table or appending data to an existing table. 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 tables’ data.

Figure 1.6. The result of running the Employee Sales by Country query.

When you store a query, only its definition, layout or formatting properties, and datasheet are actually stored in the database. Access offers an intuitive, user-friendly tool for you to design your queries. Figure 1.7 shows the Query Design window. To open this window, select the Queries from the Objects list in the Database window, choose the query you want to modify, and click Design. The query pictured in the figure selects data from Employees, Orders, and Order Subtotals. It displays the Country, LastName, and FirstName from the Employees table, the ShippedDate and OrderID from the Orders table, and the Subtotal from the Order Subtotals query. Only records within a specific Shipped Date range are displayed in the query’s output. This special type of query is called a parameter query. It prompts for criteria at runtime, using the criteria entered by the user to determine which records are included in the output. Queries are covered in Chapters 4, “What Every Developer Needs to Know About Query Basics,” and 11, “Advanced Query Techniques.” Because queries are the foundation for most forms and reports, they’re covered throughout this book as they apply to other objects in the database.

Figure 1.7. The design of a query that displays data from the Employees and Orders tables and the Order Subtotals query.

Although you can enter and modify data in a table’s Datasheet view, you can’t 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, and they’re very flexible and powerful.

To view any form, select Forms from the Objects list. Then double-click the form you want to view, or click to select the form you want to view, and then click Open. Figure 1.8 illustrates a 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 Customers table, and the subforms display information from the Orders table and the Order Details table. As the user moves from customer to customer, the orders associated with that customer are displayed. When the user clicks to select an order, the products included on that order are displayed.

Figure 1.8. The Customer Orders form includes customer, order, and order detail information.

Like tables and queries, forms can also be viewed in Design view. To view the design of a form, select the Forms icon from the Objects list (refer to Figure 1.1), choose the form whose design you want to modify, and then click Design. Figure 1.9 shows the Customer Orders form in Design view. Notice the two subforms within the main form. Forms are officially covered in Chapters 5, “What Every Developer Needs to Know About Forms,” and 9, “Advanced Form Techniques.” They’re also covered throughout this text as they apply to other examples of building an application.

Figure 1.9. The design of the Customer Orders form, showing two subforms.

Forms allow you to enter and edit information, but, with reports, you can display information, usually to a printer. Figure 1.10 shows a report being previewed. To preview any report, select Reports from the Objects list. Double-click the report you want to preview or choose the report you want to preview, and then click Preview. Notice the graphic in the report, as well as other details, such as the shaded line. Like forms, reports can be elaborate and exciting, yet can contain valuable information.

Figure 1.10. A preview of the Catalog report.

If you haven’t guessed yet, reports can be viewed in Design view, as shown in Figure 1.11. To view the design of any report, select Reports from the Objects list and click Design after selecting the report you want to view. Figure 1.11 illustrates a report with many sections; you can see a Report Header, Page Header, CategoryName Group Header, and Detail section—just a few of the many sections available on a report. Just as a form can contain subforms, a report can contain subreports. Reports are covered in Chapters 6, “What Every Developer Needs to Know About Reports,” and 10, “Advanced Report Techniques,” and throughout the book as they apply to other examples.

Figure 1.11. Design view of the Catalog report.

Data access pages, discussed earlier in the chapter, appeared in Access 2000. They allow you to view and update the data in your database from within a browser. Although they are stored outside the Access database (.mdb) file, they are created and maintained in a manner similar to that of forms. Figure 1.12 shows a data access page being viewed within Access. Although data access pages are targeted toward a browser, they can also be previewed within the Access application environment.

Figure 1.12. An example of a data access page based on the Employees table.

Data access pages can also be viewed and modified in Design view. Figure 1.13 shows a data access page in Design view. As you can see, the Design view of a data access page is similar to that of a form. This makes working with data access pages, and the deployment of your application over an intranet, very easy.

Figure 1.13. A data access page shown in Design view.

Macros in Access aren’t like the macros in other Office products. They can’t be recorded, as they can in Microsoft Word or Excel, and they aren’t saved as VBA (Visual Basic for Applications) code. With Access macros, you can perform most of the tasks that you can manually perform from the keyboard, menus, and toolbars. Macros allow you to build logic into your application flow. Generally, you use VBA code contained in modules, rather than macros, to do the tasks your application must perform. This is because VBA code modules give you more flexibility and power than macros do. Although in earlier versions of Access certain tasks could be performed only by using macros, they are rarely used by developers today. The development of applications using macros is therefore not covered in this book.

To run a macro, select Macros from the Objects list, click the macro you want to run, and then click Run. The actions in the macro are then executed. To view a macro’s design, select Macros from the Objects list, select the macro you want to modify, and click Design to open the Macro Design window. (See Figure 1.14.) The macro pictured has four columns. The first column is the Macro Name column, where you can specify the name of a subroutine within a macro. The second column allows you to specify a condition. The action in the macro’s third column won’t execute unless the condition for that action evaluates to true. The fourth column lets you document the macro. In the bottom half of the Macro Design window, you specify the arguments that apply to the selected action. In Figure 1.14, the selected action is MsgBox, which accepts four arguments: Message, Beep, Type, and Title.

Figure 1.14. The design of the Customers macro, containing macro names, conditions, actions, and comments.

Modules, the foundation of any application, let you 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; subroutines do not. By using code modules, you can do the following:

These are just a few of the tasks you can accomplish with modules. To view the design of an existing module, click Modules in the Objects list, choose the module you want to modify, and click Design to open the Module Design window. (See Figure 1.15.) The global code module in Figure 1.15 contains a General Declarations section and one function called IsLoaded. Modules and VBA are discussed in Chapters 7, “VBA: An Introduction,” and 12, “Advanced VBA Techniques,” respectively, and are covered extensively throughout this book.

Figure 1.15. The global code module in Design view, showing the General Declarations section and IsLoaded function.

According to Microsoft documentation, these are the official minimum requirements to run Microsoft Access 2002:

As if all that hardware isn’t enough, my personal recommendations for a development machine are much higher because you’ll probably be running other applications along with Microsoft Access. You also want to greatly reduce the chance of hanging or other problems caused by 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 or hard drive capacity. The more you have, the happier you will be using Access.

Although the user’s PC doesn’t need to be as sophisticated as the developer’s, I still recommend the following in addition to Microsoft’s requirements:

The first step in the development process is task analysis, or considering each and every process that occurs during the user’s 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, 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, task analysis in the development process seems to have gone out the window. I maintain that if care isn’t taken to complete this process at least at 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’re ready to work on the data analysis and design phase of your application. In this phase, you must identify each piece of information needed to complete each task. These data elements must be assigned to subjects, and each subject will become a separate table in your database. For example, a subject might be a client; every data element relating to that client—the name, address, phone, credit limit, and any other pertinent information—would become fields within the client table.

You should determine the following for each data element:

You should also determine whether each data element can be updated and whether it’s entered or calculated; then you can figure out whether your table structures are normalized.

Normalization Made Easy

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 an application that runs efficiently with as little data manipulation and coding as possible. Normalization and database design are covered in detail in Chapter 3, “Relationships: Your Key to Data Integrity.” For now, here are six of the basic normalization rules:

1. Fields should be atomic—that is, each piece of data should be broken down as much as possible. For example, rather than creating a field called Name, you would create two fields: one for the first name and the other for the last name. This method makes the data much easier to work with. If you need to sort or search by first name separately from the last name, for example, you can do so without any extra effort.

2. Each record should contain a unique identifier so that you have a way of safely identifying the record. For example, if you’re changing customer information, you can make sure you’re changing the information associated with the correct customer. This unique identifier is called a primary key.

3. The primary key is a field or fields that uniquely identifies the record. Sometimes you can assign a natural primary key. For example, the social security number in an employee table should serve to uniquely identify that employee to the system. At other times, you might need to create a primary key. Because two customers could have the same name, for example, the customer name might not uniquely identify the customer to the system. It might be necessary to create a field that would contain a unique identifier for the customer, such as a customer ID.

4. A primary key should be short, stable, and simple. Short means it should be small in size (not a 50-character field). Stable means the primary key should be a field whose value rarely, if ever, changes. For example, although a customer ID would rarely change, a company name is much more likely to change. Simple means it should be easy for a user to work with.

5. Every field in a table should supply additional information about the record that the primary key serves to identify. For example, every field in the customer table describes the customer with a particular customer ID.

6. Information in the table shouldn’t appear in more than one place. For example, a particular customer name shouldn’t appear in more than one record.

Take a look at an example. The datasheet shown in Figure 1.16 is an example of a table that hasn’t been normalized. Notice that the CustInfo field is repeated for each order, so if the customer address changes, it has to be changed in every order assigned to that customer. In other words, the CustInfo field is not atomic. If you want to sort by city, you’re out of luck because the city is in the middle of the CustInfo field. If the name of an inventory item changes, you 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 the customer orders: name, supplier, quantity, and price. This design would make it extremely difficult to build sales reports and other reports your users need to effectively run the business.

Figure 1.16. A table that hasn’t been normalized.

Figure 1.17 shows the same data normalized. Notice that it’s 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 is uniquely identified by a contrived CustID field, which is used to relate the orders table, tblOrders, to tblCustomers. The tblOrders table contains only information that applies to the entire order, rather than to a particular item that was ordered. This table contains the CustID of the customer who placed the order and the date of the order, and it’s related to the tblOrderDetails table based on the OrderID. The tblOrderDetails table holds information about each item ordered for a particular OrderID. There’s no limit to the potential number of items that can be ordered. As many items can be ordered as needed, simply by adding more records to the tblOrderDetails table. Finally, supplier information has been placed in a separate table, tblSuppliers, so that if any of the supplier information changes, it has to be changed in only one place.

Figure 1.17. Data normalized into four separate tables.

Although the task analysis and data analysis phases of application development haven’t changed much 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 moving 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 programmers would go off for days and work arduously to develop each screen and report. They 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 finished, the developer can design the tables and establish relationships among them. The form and report prototype process can then begin. Rather than the developer going off for weeks or months before having further interaction with the user, the developer needs 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 Windows 98, Windows NT, Windows 2000, and Windows Millennium Edition, you test in all environments. I also suggest you test your application extensively on the lowest common denominator piece of hardware—the application might run great on your machine but show unacceptable performance on your users’ machines.

It usually helps to test your application both in pieces and as an integrated application. Recruit several people to test your application and make sure they range from the most savvy of users to the least computer-adept person you can find. These different types of users will probably find completely different sets of problems. Most importantly, make sure you’re not the only tester of your application because you’re 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 and make sure they know they’re performing the test case. Make them feel honored to participate as the first users of the system, but warn them that problems might occur, and it’s their responsibility to make you aware of them. If you distribute your application on a wide-scale basis and it doesn’t operate exactly as it should, it will be difficult to regain the confidence of your users. That’s 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 the one 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 of the scope of the application changing, you could very well end up on the losing end of that deal.

There are three categories of maintenance activities: bug fixes, specification changes, and frills. Bug fixes need to be handled as quickly 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 go, try to involve the users as much as possible in adding frills by teaching them how to enhance forms and reports and by making the application as flexible and user defined as possible. Of course, the final objective of any application is a happy group of productive users.

You’ll find several new features available for Access forms. The following is an overview of these features. They are covered in detail in Chapter 5 and Chapter 9.

Four new events make forms easier to work with. They include the form-level and control-level OnUndo events, the OnRecordExit event, and the OnDirty event. The form-level OnUndo event occurs when a user undoes all edits on a form. Like its form-level counterpart, the control-level OnUndo event occurs when a user undoes changes to an individual control. The OnRecordExit event occurs just before a user navigates away from a record, immediately before the current event. It is intended to simplify form/subform validations. Finally, controls now have OnDirty events. This means that you can react to a control being dirty just as you can react to a form being dirty.

In addition to all the new events available for forms, one new property and one new method are included in Access 2002. The new property is the Movable property. It is available for both forms and controls, and is used to designate whether the form or control is movable. The new method is the Move method. It allows you to programmatically move a form.

Finally, the design-time manipulation of subforms is dramatically improved in Access 2002. Scrolling through a subform in Design view is much less clumsy in Access 2002. Most importantly, you can right-click a subform control and select View|Subform in Own Window. This much-needed command opens the subform in Design view in its own window.

Just as there are several new features available for forms, there are several new features available for reports. They are covered in detail in Chapter 6 and Chapter 10.

Several new properties are available for reports. They include Modal, PopUp, BorderStyle, AutoResize, AutoCenter, MinMaxButtons, CloseButton, and ControlBox. These properties all work like their form counterparts, and act to give reports much of the same flexibility as found in forms.

Two new arguments are included with the OpenReport method. A WindowMode argument allows the user to control how the report window behaves (for example, modally). An OpenArgs argument and property allow you to easily pass information to a report as it is opened and to retrieve that information from within the report.

Just as a Movable property and Move method were added for forms, they were also added for reports. They allow you to designate whether a report is movable, and to easily write code to move the report window.

Access 2002 allows the user to view any table, query, form, ADP table, ADP view, ADP stored procedure, or ADP function in PivotTable or PivotChart view. PivotTables and PivotCharts allow users to easily perform rather complex data analyses. This means that many of the data analysis tasks once left to Microsoft Excel can now be performed directly within Microsoft Access. PivotTables and PivotCharts are available in data access pages and subforms, you can programmatically react to the events that they raise.

Several new programmability enhancements are included in Access 2002. They include the following:

As you can see, several of these features improve what a developer can do at runtime. Others enhance or improve the performance of the development environment. All help you to get your job done more efficiently and effectively. Each feature is covered in additional detail in the appropriate chapter of this book.

A significant amount of XML support is included in Access 2002. Both data and schema can easily be exported to XML or imported from XML, either programmatically or via the user interface. Data and schema are exported as a snapshot of the persisted table or query. Schema are exported in the W3C XSD standard. The presentation, or the format for the output, is based on an XSL document that generates HTML 4.0. The XSL presentation document and the XML data document are bound with a .htm file that executes on the user machine. XML support in Access 2002 is covered briefly in this text, and is covered in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

The new SQL Server 2000 Desktop Engine replaces what was formerly known as MSDE (Microsoft Database Engine). This robust desktop engine offers the same features that are new to SQL Server 2000. Several new features are available in the SQL Server 2000 Desktop Engine:

Data access pages in Access 2000 were limited and somewhat difficult to work with. They have been greatly improved in Access 2002. For example, the Data Access Page Designer in Access 2002 supports multiple levels of undo and redo. The Designer also allows you to select and manipulate multiple objects either with the mouse or with the keyboard. Many new sizing and drag-and-drop options are included in the new and improved Designer. The Designer supports right-click options that you would expect, and the Properties window is now limited to properties applicable to the selected object(s). In a nutshell, the Access 2002 Designer is much more like its form counterpart, rather than like a neglected stepchild.

In Access 2000 banded data access pages were very limited. In Access 2002 banded data access pages are updatable. Many new properties are available at the band level. They include AllowAdditions, AllowDeletions, and AllowEdits. A new Autosum feature facilitates the process of creating aggregate calculations.

In addition to the standard data access page layouts, Access 2002 offers Tablular, PivotChart, and Spreadsheet options. This allows you to easily customize the look and feel of your data access pages to your needs.

Finally, Access 2002 data access pages are much easier to deploy than their Access 2000 predecessors are. A page-level script notifies users who do not have a compatible browser. Relative paths (rather than absolute paths) can be set to Access databases, and Office Data Connections (ODCs) and Universal Data Links (UDLs) allow you to centralize database connection information. To top it off, the Link property of the page is exposed programmatically and can therefore be easily accessed at runtime.

In summary, data access pages in Access 2002 have the functionality that you would expect from a “form-designer.” They are very easy to work with and include a very rich set of features. Data access pages are covered in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

As if everything mentioned thus far is not enough, well, there’s more. Other new features include increased robustness, conversion error logging, worldwide access, accessibility features, and speech recognition.

Two main pieces of functionality improve the robustness of Access 2002. The first is a much-improved compact and repair utility; the second is a better search mechanism and error-resolution process for broken references. To help with the conversion of Access 2000 applications to Access 2002, Access 2002 provides a table of information about problems that might be encountered during the conversion process.

Access 2002 adds many features that improve its ability to compete in the international market. These features include the capability to

In terms of accessibility, Access 2002 adds several keyboard shortcuts, as well as additional zoom powers. Finally, new speech options allow the user to dictate text and navigate menus using speech and voice commands!

A Practical Example of Application Design: A Computer Consulting Firm

Consider a hypothetical computer consulting firm that wishes to track its time and billing with an Access application. First, look at 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 to, and expenses associated with, each client and project. It will also let users track pertinent information about each employee or subcontractor. The tables in the system are based on the tables produced by the Database Wizard. They have been modified somewhat, and their names have been changed to follow the Reddick naming conventions. Twenty-one tables will be included in the system. Some of these tables are built in Chapter 2, and they can all be found in the application databases on the sample code CD-ROM that accompanies this book:

• tblClients—. This table contains all the pertinent information about each client; it’s related to tblProjects, the table that will track the information about each project associated with a client.

• tblClientAddresses—. This table contains all addresses for each client; it’s related to tblClients and tblAddressTypes.

• tblAddressTypes—. This table is a lookup table. It contains all valid address types for a client; it’s related to tblClientAddresses.

• tblClientPhones—. This table contains all phone numbers for each client; it’s related to tblClients and tblPhoneTypes.

• tblPhoneTypes—. This table is a lookup table. It contains all valid phone types for a client; it’s related to tblClientPhones.

• tblTerms—. This table is a lookup table. It contains all valid payment terms for a client; it’s related to tblClients.

• tblContactType—. This table is a lookup table. It contains all valid contact types for a client; it’s related to tblClients.

• tblProjects—. This table holds all the pertinent information about each project; it’s related to several other tables: tblClients, tblPayments, tblEmployees, tblTimeCardHours, and tblTimeCardExpenses.

• tblTimeCardHours—. This table is used to track the hours associated with each project and employee; it’s related to tblProjects, tblTimeCards, and tblWorkCodes.

• tblPayments—. This table is used to track all payments associated with a particular project; it’s related to tblProjects and tblPaymentMethods.

• tblTimeCardExpenses—. This table is used to track the expenses associated with each project and employee; it’s related to tblProjects, tblTimeCards, and tblExpenseCodes.

• tblEmployees—. This table is used to track employee information; it’s related to tblTimeCards and tblProjects.

• tblTimeCards—. This table is used to track each employee’s hours; it’s actually a bridge between the many-to-many relationship between Employees and Time Card Expenses, as well as between Employees and Time Card Hours. It’s also related to tblEmployees, tblTimeCardHours, and tblTimeCardExpenses.

• tblExpenseCodes—. This table is a lookup table for valid expense codes; it’s related to tblTimeCardExpenses.

• tblWorkCodes—. This table is a lookup table for valid work codes; it’s related to tblTimeCardHours.

• tblPaymentMethods—. This table is a lookup table for valid payment methods; it’s related to tblPayments.

• tblCorrespondence—. This table is used to track the correspondence related to a project; it’s related to tblProjects and tblCorrespondenceType.

• tblCorrespondenceTypes—. This table is a lookup table for valid correspondence types; it’s related to tblCorrespondence.

• tblCompanyInfo—. This table is a system table. It is used to store information about the company. This information is found on forms and reports throughout the system.

• tblErrorLog—. This table is a system table. It is used to store runtime errors that occur.

• tblErrors—. This table is a system table. It is used to store valid error codes and descriptions.

The relationships among the tables are covered in more detail in Chapter 3, but they’re shown in Figure 1.18.

Figure 1.18. Relationships among tables in the time and billing system.