With ease-of-use features and enhanced development tools, Microsoft's database manager aims at dabblers and developers
Jim Carls
It's the holy grail of database vendors: building a database manager with the ease of use of a flat-file database and the power to satisfy demanding developers. Microsoft reached for this elusive goal with the initial release of Access and hopes to close in on it with Access 2.0. The new features and capabilities in 2.0 aim at the low-end user with ease-of-use improvements and at the database developer with enhanced connectivity and programming options. But the package can be intimidating and may leave the low end behind.
Objects Through Windows
Access 2.0, like 1.x, is an object-oriented RDBMS (relational database management
system). Users new to this approach to data management will find a major difference between products like Access and traditional command-driven DBMSes: Instead of wrestling with a long list of commands and control statements whose behavior may be less than obvious, you wrestle with long lists of object properties whose meanings may be even less obvious.
An object is any item in the system that you can manipulate as a unit. Objects in Access include data tables, reports, screen forms, and controls such as input fields and buttons. A property is a characteristic of an object and can range from input masks to validation rules to ``what-happens-when'' instructions for specific events (e.g., a mouse-click). The properties available to the user for some objects can be alarmingly long. Still, object orientation can be such a superior way of organizing desirable programmed behaviors that it is well worth the effort to push yourself up and over the required learning curve.
Access also takes the Windows i
nterface about as far as it can go: OLE 2.0, right-click menus, hover help, cue cards, and a host of wizards. And you can copy and paste objects (e.g., an entire table) as easily as you can manipulate highlighted rows and columns.
New and Improved
The previous version of Access was an accomplished data manager, but it could try the patience of anyone used to the kind of blazing speed you get with DOS-based databases. There were also some features of a finished database application that should have been easier to implement.
The new features in this version are extensive. Besides the additions covered in ``A Few New Features in Access 2.0'' on page 160, three other new features deserve notice. The first is the program's more comprehensive set of wizards. It is the function of a wizard to answer the question, ``What on earth do I do with all these properties?'' These programmed helpers can make a big difference in the ease and speed with which you set up the pieces (i.e., objects) in an appli
cation.
The next feature of note is the Solutions database. This bundled sample is a finished application as well as a storehouse of how-to information, including code samples, that help you climb the learning curve. I wish the Solutions database had been available when I was struggling with version 1. Finally, the Rushmore query-optimization technology purchased with the Foxpro acquisition may seriously cramp your coffee breaks.
Space for Your Stuff
A full installation of the new version requires 22.4 MB of disk space. After including the space needed for Windows swap files and such, I squandered 2 hours on a search-and-compress mission to get the 30 MB I ultimately needed.
Space requirements are compounded for advanced 1.x users who have live applications. Changes and additions in the new version make it prudent to keep the old version alive until your applications prove themselves ready for 2.0. Even more prudent (don't say I didn't warn you) would be to read any readme files on
Disk 1 before you install the new version. Tables created under previous versions will automatically convert to 2.0 format when you open the MDB file, but all other major objects must be opened and saved. This can be tedious for a complex application.
A Sample Application
To get a feel for the new version, I created a project-oriented marketing database that might be used by any professional services firm, such as architects, engineers, or data-processing consultants. Many large professional firms must continually produce marketing aids, proposals, and government forms that present the firm's experience in a variety of ways, including cross-tabulated resumes for staff members. Since proposals are often custom-tailored to fit the job being sought, updating and collating this information can be tedious. I set out to create the basic table structure, input forms, and two reports: one for project information and another for a staff resume.
The initial design for the system required 13 tables,
organized around a list of Projects. Two categories of tables were needed. The first would include resource tables, such as lists of employees, regular consultants used by the firm, ``approved'' code lists, and so on. The second category of tables would define the intermediate relationships between the individual projects and the resource lists.
You can create tables manually or with a wizard. I discovered that the new Table wizard is most efficient if you've already created those tables that the wizard cannot. In my sample application, these would be the intermediate tables, which would consist of only two fields, both being primary keys of other tables. For example, the list of employees who had produced particular projects would be defined in a table called Project Team. I created this table manually with two fields, ProjectID and EmployeeID, which together would create unique keys. Creating the compound key was easy; I just highlighted both fields and clicked the Key icon on the toolbar.
As
you work with each table field, a list of applicable properties (e.g., formatting and validation rules) appears below it. An important aspect of designing a table is to finalize, as much as possible, the properties for each field before creating queries and forms. Since the properties you include in the table design are inherited by the queries, forms, and reports based on them, this can save time. Although you can override these properties in other objects, it is more efficient to specify what you want at the table level.
To define the Project table and resource lists, I used the Table wizard. This function works best as a library of predefined tables. The Table wizard ships with over 50 sample tables. A library of tables helps enforce field-naming conventions from one development project to another, so macros and modules developed in one project are more easily usable in a new one. You can customize the default table list with your own; I recommend this, as some of the default fields are too generic
.
The Table wizard also helps you define relationships between tables. You can choose an existing table, and Access will create the relationship to the new table, even adding a linking field if necessary.
You can define and edit relationships with the new graphical Relationships window, using point, drag, and click operations. When defining relationships, you specify whether to enforce referential integrity (preventing the entry of nonexistent foreign keys) and whether to allow cascading updates or deletes (where a change to the ``one'' of a one-to-many relationship also changes the ``many''). One warning: The window can show either all relationships or only direct ones. I wasted an hour trying to change the original CustomerID field type, a modification the system refused to allow because the Customer table was ``involved in one or more relationships.'' I was beginning to believe that the relationship was a bit too clandestine until I realized that I wasn't displaying ``all'' relationships.
Creating Forms
The heart of any database application is its interface, defined by its screen forms. Using the Forms wizard, it is fairly simple to set up basic forms from the four available types--Single Column, Tabular, Graph, and Main/Subform--and in one of five styles. Also, a new AutoForm form type creates best-guess forms based on the table or query selected. In addition to data-editing forms, you can build forms that use Access's rich macro capabilities to organize your application and control processing, eliminating much of the coding this normally requires.
My Marketing database makes heavy use of subforms within the main project form. These handle the data for project consultants, teams--anything with a one-to-many relationship. Data entry for subforms generally depends on first entering a valid ID from a related resource list (e.g., to enter a Project Team member, you enter a valid EmployeeID from the Employees table). This means that the subform will typically use either a list or
combo box for data entry of the key field. Unfortunately, the Forms wizard does not set this up for you; there is a Control wizard for these types of controls. Using it, you can quickly set up one of these for your key field, but you will have to delete the original field, add the new control, and then make sure to edit the Tab Order to restore the original field sequence.
Before a new member can be added to a Project Team, the person must already exist in the Employee List. Most well-designed databases let the user add a new record on the fly when the system detects a nonexistent foreign key. I was surprised to find that this is not possible in Access without some programming. Fortunately, the Solutions database includes a detailed explanation of how to do this (including sample code that you can copy).
Printing forms is only satisfactory, especially if you optimize the screen readability with specific color schemes. When installing a new application, you'll often need a data verification repor
t that mimics the input screen, especially for complex tables. Although you can drop a form into a new report design, the result may be less than readable without further work.
Queries and Reports
Access has a healthy set of querying tools, including a new Query wizard that aids in creating some of the more complex types of queries, such as cross-tab queries and queries to find nonmatching records in a second table. Since most forms and reports are based on queries, this is an important feature. One major drawback of the previous version was the inability to export the results of a query to other data formats. Version 2.0 corrects this.
Access's reporting features are similar to the process of creating forms, both in concept and application--from using the Report wizards to the ability to drag an existing report from the report list and drop it into a report definition as a subreport.
Worthy Effort
Powerful? Certainly. Easy to use? Well. The experience required to take full ad
vantage of this powerful program depends not on the program's design but on the demands of complex data-processing systems. Access cannot design your database for you. Nor can it greatly reduce the tweaking needed for a maturing application as users begin to call for variations on the processing themes they see.
For professional developers, the range of object properties available means the end of a great deal of coding. For the average user, Access shortens the time that needs to be spent setting up a productive database and reduces some of the experience needed. But Access does not fill the niche for flat-file managers at the low end. For anyone with major data management needs who has the time or resources to implement it, this program can pay back the investment handsomely.
The Facts
Access 2.0.........$495
Microsoft Corp.
1 Microsoft Way
Redmond, WA 98052
(206) 882-8080
fax: (206) 936-7329
A Few New Features in Access 2.0
Graph
ical relationships
Visually design the database and define table relationships
Expression Builder
Point and click from a list of common expression elements to quickly build complex expressions
AutoForm/AutoReport
Generate forms and reports automatically based on a table or query
OLE automation
Build a custom environment with Access objects and with objects exposed by other compliant applications
Cascading updates and deletes
Design unlimited levels of cascading updates and deletes to maintain consistency and integrity of data at the table and query level
Input masks
Apply input masks to automatically add formatting or insert special characters into each field or record.
New SQL features
Pass SQL statements directly to ODBC databases and create Union queries and queries that create, change, or delete objects in attached SQL tables.
Illustration: Access 2.0 is an object-oriented RDBMS. Objects in Access include data tables, reports, screen form
s, controls such as input fields and buttons, and other elements. A property is a characteristic of an object and can range from input masks to validation rules to ``what-happens-when'' instructions for specific events (e.g., a mouse-click). The screen shows the properties assigned to the See Product Details button. The On Click option of the Properties window lists actions that can take place when the user clicks on the button. In this case, the mouse-click triggers an event procedure (a block of code). Clicking on the ellipsis next to the Event Procedure label brings up the snippet of code to be executed.
Illustration: Access 2.0's new Relations Window makes it easy to define and edit relationships between tables. Notice that primary and foreign keys are easily visible. When defining relationships, you can specify whether to enforce referential integrity and whether to allow cascading updates or deletes.
Jim Carls is a consultant and freelance writer in Memphis
, Tennessee. He has over 14 years' experience in system design and user training. He provides custom software, training, and technical assistance to corporate clients, primarily in xBase languages such as Clipper. You can reach him on the Internet or BIX at editors@ bix.com.
Access 2.0: The Best of Both Worlds?
