Chapter 1

Access as a Development Tool

Can every data manipulation problem be solved with Access? Are there any data-centric solutions where Access simply does not fit?

Of course, Microsoft's marketing mavens would have you believe that the answers to these two questions are an unqualified yes and no. My own answers to the questions are similar to Microsoft's, but slightly more qualified:

To expand on these questions and their answers, I think it is beneficial to clarify how Access can best be used in a variety of circumstances. User and developer satisfaction with Access is not enhanced when Access is misused or applied when it's not appropriate.

To help you use Access most effectively, this chapter does the following:

Table Wizardry

About thirty percent of my company's Access development projects begin when a client hands us a large Excel worksheet filled with data and says, "This is out of control, move it to Access." Without a relational data model where one record can point to many other records, expressing information in a worksheet format quickly hits its limit.

The Access group at Microsoft heard about this data migration from our clients and other users and devised an elegant solution. Beginning with the 95 version, Access ships with the Table Analyzer Wizard that breaks Excel worksheets and other tabular data down into component Access tables, complete with relationships. Unfortunately for me, the wizard is so good that now some of our clients can migrate their data without our help!

Access works your machine very hard, and can seem slow at times when running applications that rely heavily on forms and VBA code. I don't find that our clients are satisfied running our Access applications in 8 megabytes of RAM. Access is much more efficient in 12, 16, or ideally 32 megabytes. If you are committed to Access, consider whether or not your hardware is up to the challenge.

A test suite we ran to load Access included opening a complex application, checking user permissions, linking tables, and loading the main menu. The following list shows the length of time the test took on different hardware configurations:

486/50 with 8M RAM 78 seconds
486/50 with 16M RAM 28 seconds
586/90 with 32M RAM 7 seconds

Notice the substantial performance increases gained by spending a few hundred dollars on RAM or around $1,500 more on the higher-powered machine. When considering such extra hardware expenditures, evaluate the cost of the time people spend waiting for their computer against the cost of the upgrade.

Access User Statistics

The registration card completed by purchasers of Access contains a short survey about the environment Access will be used in. Although Microsoft qualifies the validity of any information gleaned from registration cards by saying that experienced users return the cards more frequently than new users and thus skew the results, a summary of the survey information is still enlightening for our purposes in this chapter. (Note that the most recent statistics available from Microsoft were for Access 2.)

62% of the Access 2 survey respondents develop applications for use by others. Of these, 43% work in an IS/IT group, in systems analysis, or doing PC support in their corporation. (The other 57% presumably create applications in an unofficial capacity.) Of the users that registered Access 2, 16% are also registered for Visual Basic or Visual Basic Professional (although there may be some overlap in these numbers).

6% of Access users responding are self-employed, 33% work for a company with fewer than 100 employees, and 41% work for a company with 1000 employees or more. Perhaps the most interesting statistic is that 43% of registered buyers use Access at work only; 22% use Access at home only; and an impressive 35% use Access at both work and home.

While such information is not statistically significant, it does indicate a measurable diversity among Access users and their installations.

Access at Home

Four years ago, if you thought using Access 1.x at home was like using a sledgehammer to swat a fly, you were correct. At that time, home PC users lacked sophistication and most could not grasp the relational data model. Access had only a few wizards and thus a long learning curve. Few home PCs had the 16 megabytes of memory and 486 or 586 processors that Access demands.

Now the home marketplace is quite different. The explosion of multimedia PCs-machines with enough random access memory and processor horsepower to deal with complex sound and graphics-has brought suitable equipment for use with Access into many homes. Also, surveys show that one quarter of all home PC owners regularly take some of their work home to do on their computers; so the sophistication of home computer users has climbed steadily as a result. Finally, Microsoft Office Professional, of which Access is a part, is convenient for home users who want to use the same software at home that they have already learned to use at work.

If you use or intend to use Access at home, and you are reading this book, you most likely fit into one of the previous two categories: either you are a business user bringing Access work home; or you are a home user, who knows Access through your job, and want your home machine to resemble your work machine. It is natural for you to reason that if Access can manage your business data, it can certainly handle your home data.

Just because personal computers at home are getting more powerful, and their owners more adept, is Access necessarily the right tool for non-business projects? Microsoft thinks so. In Access 97, when you create a new database, you can select a template for the Database Wizard to use to meet your specific purpose. Some of these database templates are quite obviously designed for home PC users: Book Collection, Donations, and Household Inventory, to name a few (see Figure 1.1).

Fig. 1.1

Several database templates are included with the Access 97 Database Wizard.

It is true that Access has the computational power to solve any challenge that a home computer user can throw at it. The real question is not whether Access can handle your needs, but rather whether you can handle Access. Because Access is powerful, it requires a greater expenditure of time to learn and money to provide it with adequate hardware than some simpler data-management products, such as Microsoft Works, for example.

Access can work for you as a personal database management system at home, but only if you commit to the following items:

The following list details the features of Access 97 that are most appealing to home users. Use this to help you explore features with that you might not yet be familiar:

Many of the concepts in this book are more appropriate for complex business applications and are overkill for a home application. Other concepts still provide value to the creation of personal applications. For example, trapping and handling usage and application errors easily is as important for your children's telephone list solutions as for your boss's contact management database.

Access in Small Businesses

Access is best suited for small businesses. Microsoft had this market in mind when they started creating wizards in the Office product line. Because this market is comprised of people short on both time and money, they won't use Access if it can't solve their problems quickly and cost-effectively.

Many small business owners and managers use Access themselves as a productivity and decision-support tool. Often small businesses have only a few computer-literate employees on staff; so the ability of Access to manage a few dozen simultaneous users is quite adequate. Business owners on a tight budget find that they, or a staff member, can learn enough about Access to produce a simple but effective custom application with a few weeks of training and a few more weeks of development time.

Of course, a very small business with one or two decision makers may not even need Access for the application development power it provides. Using Access interactively may be satisfactory for your needs in such a case. Even without an application and its forms, you can be productive with Access by entering data into table datasheets, running summary queries, exporting data to Excel for analysis, and printing reports. (The reasons for going beyond this direct interaction as your company grows and placing an application layer over the data becomes clear in Part I, "The Leszynski Development Framework.")

If you are automating data collection in your small business, you can have good results using Access as your database platform, if you adhere to the following guidelines:

One of the best investments you can make in your business hardware is an uninterruptable power supply (UPS) for each machine, especially the server. These items protect your sensitive devices, as well as your database records, from the damage and information loss caused by storms and power outages. More importantly, many smaller office buildings and home offices are subject to minor power fluctuations, which are not detected by humans but lead to the premature demise of computer parts. A good UPS traps and smoothes these variances.

Tie salary increases or one-time bonuses to employee training milestones. Many employees perceive computer classes as dull or even intimidating and need extra motivation to go to their first one. From an economic standpoint, it is cheaper for your business to give someone a $200 training bonus to attend a $700 course than to have that $20/hour worker spend 100 hours self-teaching Access, right?

The most important features when using Access 97 in a small business are as follows:

Access in Corporate Businesses

Many midsize and large companies rely heavily on Access; but none rely exclusively on Access. Companies of any significant size usually have complex data needs, multiple database platforms, competitive dynamics, and dozens to thousands of application users. In such an environment, no single product is sufficient to satisfy all needs. Access becomes one piece of an often complex puzzle of application development tools.

Virtually all automated companies with more than one hundred employees have some in-house development staff. These departments are usually called Information Systems (IS) or Information Technology (IT). Often an IT group is composed of programmers becoming redundant by changing technology, as mainframe databases are brought to the desktop or to PC servers. Corporations with changing technology have the challenge of efficiently retraining their application development staff to use that technology. Access wins big in such a circumstance for two main reasons.

First, Access has a reasonable learning and implementation cycle. It is neither the easiest nor the hardest development tool to learn. There are enough books, videos, courses, and conferences built around Access that companies can shop competitively and select the best staff retraining option they can find. There are also thousands of consultants and contractors that can help the IT staff make the transition to Access without wandering in the dark.

Secondly, Access is extensible. Access fits well into corporate development models because it can be extended in the following ways:

Corporations that are adding Access to their tools suite or replacing some of that suite with Access should adhere to the first five guidelines for small business implementation of Access. Don't scrimp on hardware; keep the application running; designate an in-house expert; safeguard your data; and train users well.

However, the sixth guideline given for small companies, teach users to be interactive, is a debatable objective for users of corporate data. A production database in a corporate environment has scores or hundreds of users. If these users can add, edit, and delete data directly (and perhaps freely roam the Database window) the introduction of erroneous records may be significant. There is a better sixth guideline for corporate use of Access; create solid applications. The company must take its data very seriously and use IT or outside resources to create application layers over the data. An application layer protects data from accidents and misuse, and makes it usable by a broad user base.

Much of this book is devoted to strategies that protect Access data from its users. Imagine back to your school days when you were learning about defensive driving. If you apply that mind-set-always be alert to potential problems-to your applications, you come up with a philosophy of defensive development, which is one of the pervasive themes in this book.

When using Access 97 in business, the features that are most appealing to corporate IT groups and other development staff are as follows:

Although a powerful product, there are places in a corporate environment where Jet, the data back end of Access, hits the wall, runs out of performance horsepower and must yield to more serious data management products. A common rule of thumb is that Access as a front end, with Jet as its back end, is limited to around 50 simultaneous users on one database. The key word here is simultaneous; Access can support many more casual users. Fortunately, in such a case, a well-designed database can be upsized, moving the data from Jet to SQL Server while continuing to use the Access application as the interface component. (See the Que book Access 95 Client/Server Development by Michelle Poolet and Mike Reilly for more information on upsizing.)

Access and Independent Software Vendors

Access 97 application files can now be locked (as MDE files) to prevent user modifications to code and objects. Additionally, the Office Developer Edition (ODE) product provides a runtime version of Access for distributing applications broadly and a setup wizard to create a setup program to aid such distribution. But even with these tools, can you create retail products built around Access? There are actually two distinct opportunities provided by Access to an independent software vendor (ISV).

ISVs and Retail Applications

The first business opportunity is to create retail applications using Access as the programming environment. Such products are usually distributed using the ODE. The advantages of using Access over Visual Basic, Visual C++, and similar truly-compiled languages are as follows:

Flipping the coin over, the following are some disadvantages when using Access to create a retail product:

Clearly, any decision to use Access as the basis of a retail product must involve weighing the factors above and any others you feel are relevant. In general, poor performance of Access retail applications on some user machines is the most common complaint voiced by ISVs and their customers. The availability of an Access application's source code for customization and the accessibility of the Jet database structure and data generate the most frequent praise.

A Retail Product Vendor's Story

"As a provider of electronic accounting products, MTX International, Inc. annually surveys its customers, and we found consistently over a ten year period that our customers want more and better access to their accounting data. They also want cost-effective customization of their accounting solution as their business changes. We decided to develop a solution that meets both needs and decided that Access would be the database engine and user interface tool.

"We found that Access met the challenge of developing a serious accounting application because it offers these features: referential integrity, transaction processing, and integrated security. Another major element in our decision-making process was the speed at that they could get the product to market and start recovering the substantial development investment. The rapid nature of Access development met this criteria as well.

"Basing MTX Accounting on Access lets our customers extend their accounting system by modifying its Basic source code, so the customer never feels trapped in a limited solution. Finally, integration with the other Microsoft Office applications fulfilled a common user request to enable custom reporting capabilities in Word, Excel, and PowerPoint. We are pleased with Access as a development platform."

Michael Mullin, MTX, Inc.

ISVs and Add-In Applications

The second opportunity for ISVs is to produce add-in products for Access users. The most common add-ins are development tools linked to the Access environment (called menu add-ins), wizards (providing step-by-step assistance for creating objects), and builders (a dialog that helps create a property setting or otherwise simplifies a task).

User expectations for add-in products are different than those for a retail application product. Because the add-ins shipped with Access are built using Access, these provide the comparative benchmarks for both performance and usability. Users of add-ins don't necessarily expect the performance of a compiled executable application.

Add-ins are usually written in Access VBA and added to a user's Access environment via a setup mechanism that establishes a reference, in the case of a code library, or creates Windows registry entries, in the case of add-ins.

The market for Access add-ins has grown as users have become more sophisticated and are able to explore new ways to increase their productivity in Access. However, ISVs that create add-ins warn prospective entrants into this field that Microsoft has a history of taking functionality found in successful third-party products and adding it to their retail products; thus removing the demand for some ISVs' products after only a short time on the market. For example, my first retail Access add-in product was called Access To Word, a mail-merge wizard that let users perform powerful data merges into Word documents from Access 1.x. Most of the capabilities provided by my product were added to the subsequent releases of both Access and Word, rendering the add-in obsolete after only 18 months on the market.

An Add-In Vendor's Story

"FMS is the leading Microsoft Access add-in vendor with several popular products: Total Access Analyzer (a documentation program), Total Access Detective (an object comparison Wizard), Total Access Speller (a spell checker), and Total Access Statistics (a statistical analysis program). Overall, we are very pleased with the success of our product line. Obviously, our growth is directly tied to the success of Access in the marketplace, and we're fortunate to have invested in a product that became the Windows database standard. However, the success of Access was not quite so obvious when we began supporting it in early 1993.

"Our first product, Total Access Analyzer , shipped for Access 1.1 in July 1993. It was an immediate success and led to the development of additional products for Access 2.0. In the shrink-wrap software business, including retail products and add-ins, a huge investment is required up front before the first copy is sold, so the risk is substantial. The success of our first products enabled us to continue developing more and upgrading existing products.

"One of the most difficult tasks we face is creating products quickly for each new version of Access. With new versions of Access appearing every 12 to 18 months, our products need to be quickly developed and marketed before the next version eliminates the previous version's add-ins. Realistically, our products have life cycles of under 12 months. Such an aggressive schedule requires intense efforts to understand each new Access version and create products that take best advantage of the new features.

"The other risk we face is the potential that a competitor may create a product similar to ours (or better) before we can get to market. If this happens, a considerable investment can be lost. A final risk is that Microsoft will include the features from our add-in in a release of Access, making our product unnecessary in the marketplace.

"Nonetheless, we are happy to be in the Access add-in business. It is a profitable business for us, and has the added benefit of giving us instant credibility in our consulting engagements (the other half of our business). Add-ins remain an important part of FMS's strategic plan and we anticipate launching several add-ins for Access 97."

Luke Chung, FMS, Inc.

Access as Part of an Integrated Solution

Access is an excellent tool for multifaceted solutions that involve integration with other Microsoft applications. Access 97 communicates better than ever before with its siblings in Microsoft Office, because of the following features:

Here is an example of integrating Access with Excel, e-mail, and Word to provide a complete solution. The Pencil Pushers Office Supply company has four employees. Dave, the salesperson, uses Access to log prospect records as he contacts new businesses or receives a referral from an existing customer. When Dave qualifies a prospect and converts the prospect to a customer by assigning a customer number, the Access application detects this and sends an e-mail to Kathy, the store manager, with the information about the new customer account.

Once a month, Dave's assistant, Sarah, creates a flyer in a Word document by exporting Access product information from the inventory database. Then, using Word's mail merge features, she produces letters and envelopes based on the Access customer data, and sends a mailing to all existing customers. When customers call in to place orders, Shannon, the order clerk, keys the orders into Access and uses the data to manage inventory stocking levels and shipping schedules for the orders.

Once per month, Kathy prints various sales reports from Access for the company meeting and reviews the company's performance with Dave, Sarah, and Shannon. She also exports the same reports to Excel where she reviews the data by customer, by region, and by product, and makes month-to-month comparisons against similar worksheets she saved from previous months.

Figure 1.2 shows the main application switchboard for the hypothetical application used to manage the Pencil Pushers company data. The switchboard layout is similar to Access 97's default switchboard style applied by the Database Wizard.

Fig. 1.2

The main switchboard for this Pencil Pushers Office Supply solution includes data export options linking to other Microsoft Office applications.

Of course, significantly more complex examples can be constructed showing how Access data can flow into other applications and integrate with other environments. Writing Automation code in Access VBA against the object models exposed by various other Microsoft products lets your applications insert schedule items into Schedule+, to create PowerPoint slides displaying Access data, to pull project schedules from Microsoft Project into Access tables, and to drive Microsoft Graph to present Access data visually.

Access and Visual Basic Together

During Access 2's reign, a significant two-way migration of developers occurred. Many Access developers realized that the investment they had made in learning Access Basic enabled them to learn Visual Basic more easily and added another powerful product to their skill set. From the other direction, most Visual Basic programmers adopted the Jet Database Engine as their preferred file-server database technology and fell into using Access to create the database structures and saved queries and to provide users with ad hoc reporting.

Thus, many Access developers became Visual Basic developers, and the reverse. This trend will not be reversed with the 97 versions of both of these products; it will only accelerate. The following are three key areas to be aware of whether both products are to be used by you or your team of developers:

The Jet developers at Microsoft are separate from the Access developers there and consider serving the needs of Visual Basic users as important as serving the needs of Access users.

One of the biggest challenges for developers using both of these Microsoft tools-deciding whether to use Access or Visual Basic as the primary language for a project-has not gotten any easier with their 32-bit releases. See the "Selecting the Tools" section of Chapter 3, "Preparing for Development," for a discussion of this dilemma.

From Here...

This chapter gave you a feeling for how Access fits into your workday or personal projects. You should now better understand what the capabilities of Access are and what your obligations are to be successful when using Access to meet your specific needs, some of which may be the following:

1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.