Database Evolution: Microsoft Access within an Organization's Database Strategy

Microsoft

®

Access within an

Organization's Database Strategy

by Luke Chung President FMS, Inc.

[email protected]

About the Author

Luke Chung is the founder and president of FMS, Inc., a world leading provider of custom database solutions and developer tools. Luke founded FMS in 1986 to provide custom database solutions, and has directed the company’s product development and consulting services efforts throughout the rapidly changing database industry’s evolution. In addition to being a primary author and designer of many FMS commercial products, Luke has personally provided

consulting services to a wide range of clients. A recognized database expert and highly regarded authority in the Microsoft Access developer community, Luke was recently featured by

Microsoft as an "Access Hero" during their 10 year anniversary celebration. Luke is a popular speaker in the US and Europe, and has published many articles in industry magazines. He is also a former president of the Washington, DC chapter of the Young Entrepreneurs Organization (YEO), and a graduate of Harvard University with Bachelor and Master Degrees in Engineering and Applied Sciences.

FMS Inc.

Executive Summary

It’s all about evolution. The database needs of an organization are unpredictable and change over time. Microsoft Access doesn’t solve every database problem but neither do other tools. What Access offers is the best solution for its range of capabilities. As the most popular database product in the world, Access clearly dominates one of the most important segments of the database ecosystem.

When formulating the database strategy of an organization, it’s helpful to think of individual databases evolving over time. Healthy database applications are not just created once but change and grow. Bad ones go extinct, and sometimes even good ones die because their environment (market) changes. Meanwhile mission critical applications sometimes appear from unexpected sources.

Millions of databases are created in Excel spreadsheets each year, but only a tiny percentage “graduate” to the next level: Access. Similarly, only a tiny percentage of Access applications graduate to a more sophisticated solution. In the interim, a huge number of database needs are solved completely by Access. Access is simply the best at what it does.

An IT manager needs to understand and use Access strategically, and anticipate (not fight) the fact that some Access applications will migrate over time. This is not an indictment on Access, but rather the natural process of database evolution. Sure, it would have been better to build that Access application with a more sophisticated platform from the beginning, but it’s impossible to predict which small percentage of databases created today will migrate three years from now. Most will run perfectly fine in Access forever or go extinct. Making a big investment today makes no sense when a simpler, less risky Access solution is possible. Let time determine which databases evolve and require additional investment to take them to the next level.

Even when Access applications evolve to another platform, Access scales by supporting the migration of Jet to SQL Server while preserving the application development investment or its migration to ADPs. If the application needs to leave Access completely, it’s a testament to the success of the Access application itself. The features developed for Access can be rolled into the new platform guaranteeing the success of the new system (or at least minimizing end-user objections). In that case, Access proved to be a great prototype.

The savvy IT manager learns when Access is effective and when it’s not. If it can be done in Access, the ROI is superior to alternate technologies. Taking

advantage of the strengths of Access gives your organization a significant competitive advantage both financially and in response to market/customer conditions.

Overview

There are Many Database Needs

Database Pyramid

Database Evolution

Database Challenges in an Organization

Strategic Mission and Vision

Access Fills an Important Segment

Database Solution Costs

Advantages of Access

Limitations of Access

Why Access Is Important

Myths of Access Limitations

Is Access a Professional Database?

Using Access Strategically

There are Many Database Needs

Some databases control the survivability of the organization while others are simply quick and dirty systems for ad-hoc analysis. No matter how large or small the organization, databases are used at a variety of levels for a variety of reasons:

Enterprise Level

These are mission critical applications that the entire organization requires for its survival. Examples include accounting systems, customer transaction tracking, high volume data processing, or other critical systems vital to the organization’s ability to complete its mission. In large organizations, this is often considered the function of the data center. Critical issues here include processing large amounts of data, maintaining historical data and legacy systems, accuracy, security, and administrative depth (backups, disaster recovery, etc.)

Department Level

Applications built for departments are less critical for the survival of the entire organization. Although these may still include important data center applications, other applications may be managed in the department itself. Department level applications are usually created by professional developers and maintained by dedicated personnel. They often tap into or pass data into the data center repositories.

Workgroup/Team Level

Workgroup applications focus on the needs of a smaller group of people working together. These applications can often change rapidly to meet the needs and challenges the workgroup faces either internally or from external market forces. Workgroup applications tend to be PC based (not mainframe) and are often controlled by the line of business using it. These applications often involve professional developers, although many instances of applications created by power users and non-developers exist. These applications often retrieve data from data center systems, but do not commonly send data back. Data analysis, report generation, and managing the needs of the workgroup to perform its functions are common examples.

Individual and Small Groups

On individual PCs, many people create their own databases in Excel and Access. These tend to be single user applications that have relatively short life spans. Their purpose is to simplify the work of the individual or small group of people who created it. Most of these applications are created by people whose primary job function is not programming.

Database Pyramid

Database Pyramid

(number of database solutions for each level)

The vast majority of database solutions are simple. As systems tackle larger and larger problems, the number of applications an organization has or can afford decreases.

At the low end, very flexible and rapid application development (RAD) solutions as used. Life cycles are short, bureaucracy and structure limited, and any mistakes are not life threatening to the organization. Costs are relatively low.

Moving up the pyramid, the solutions become more and more sophisticated and critical. The number of users increase, security and reliability become more important, and solutions need to scale. Maintainability is more important because systems are built by many people and live beyond their participation. More time is spent designing systems because more people and issues are touched. When changes are made, the complexity and critical nature of the system requires longer implementation, testing and documentation. All this drives costs up as mistakes become more and more expensive, and the organization’s survival is more and more dependent on them.

Database Evolution

Simple Databases Evolve Into Sophisticated Ones

The evolution of database applications starts from the bottom of the pyramid. Someone creates a spreadsheet or small database, finds it useful and shares it with a few people. They like it and more features added. More and more people rely on the system, and over time, the simple solution that someone created for their personal use becomes mission critical for the department or enterprise.

Very Few Databases Evolve to the Next Level

It’s important to remember that this is the exception and not the rule. For every application that successfully “evolves” from one level to the next, hundreds if not thousands are created which never evolve. Many are discarded because they weren’t useful or the environment (business) changed, while others remain perfectly fine never needing to migrate.

Hardware Also Evolves

The types of business (database) problems an organization faces remain fairly static over time compared to the hardware gains following Moore’s Law. Problems that required mainframe solutions two decades ago now run

comfortably on laptops. When it comes to performance, time is on the side of the solutions at the bottom of the pyramid. Over time, more and more database challenges are solved by that segment, while the top of the pyramid goes after problems that were previously beyond the reach of computing or budgets.

Evolution is Unpredictable

It would of course be better and cheaper to develop the mission critical

applications of tomorrow correctly today, but that’s usually not possible. First, it’s very difficult to predict which of the multitude of small databases today will become mission critical applications years from now. Second, what’s created or envisioned today for those databases, may not be what’s needed in the future or what makes them mission critical later. It’s the evolution of the databases themselves that make them mission critical, not the original vision of the author.

Anticipate Evolution

Successful databases evolve over time. A good IT strategy is to embrace, not fight, this natural trend. Anticipating the transition is part of a successful database strategy. That means preparing for times when applications need to migrate to new platforms or be completely re-written.

When these occur, one should not blame the existing platform, but rather

celebrate its role of successfully delivering a system that can be taken to the next level. The existing system should be considered a great prototype for the next system since the business needs are well defined and users accept it. This

significantly reduces the risk of the new system in a world where expensive systems are never delivered or built or fulfill a fraction of their original intent. The transition is also an ideal opportunity to add new features and “clean up” the system since after many years and enhancements, many original assumptions were wrong. This need would probably exist even if the system were created on the more sophisticated platform originally. However, it may not have evolved as quickly in that environment, so one may never know if it would have been as successful.

Database Challenges in an Organization

Every organization faces a myriad of database challenges to fulfill their mission. These include:

• Maximizing return on investment (ROI)

• Managing Human Resources

• Rapid deployment

• Flexibility and maintainability

• Scalability is nice, but secondary Return on Investment (ROI) is Critical

Maximizing ROI is more critical than ever. Management demands tangible results for the expensive investments in database application development. And many database development efforts fail to yield the results they promise. Choosing the right technology and approach for each level in an organization is critical to maximizing ROI. This means choosing the best total return, which doesn’t mean choosing the cheapest initial solution. This is often the most important decision a CIO/CTO makes.

Managing Human Resources

Managing people to customize technology is very challenging. The more complex the technology or application, the fewer people are qualified to handle it and the more expensive they are to hire. Turnover is always an issue, and having the right standards in place is critical to successfully supporting legacy applications. Training and keeping up with technology is also very challenging.

Rapid Deployment is Critical

Being able to create database applications quickly is important not only for reducing costs, but responding to internal or customer demands. The ability to create applications quickly provides a significant competitive advantage. The IT manager is responsible for offering alternatives and making tradeoffs to support the business needs of the organization. By using different technologies, you may be able to give the business decision makers choices such as a 60%

solution in three months, a 90% solution in 12 months, or a 99% solution in 24 months (instead of months, it could be dollars). Sometimes time to market is most critical, other times it may be cost, and other times the features or security most important. Business changes quickly and is unpredictable. We live in a “good enough” rather than perfect world, so knowing how to deliver “good enough” solutions quickly gives you and your organization a competitive edge.

Flexibility and Maintainability is Important

Even with the best system design, by the time multi-month development efforts are completed, needs change. Versions follow versions, and a system that’s designed to be flexible and able to accommodate change can mean the difference between success and failure for the users’ careers.

Scalability is Necessary, but Often Secondary

Systems should be designed to manage the expected data and more. But many systems never get completed, get thrown away soon after use, or change so much over time that the initial assessments are often wrong. Scalability is nice, but this is often less important than having a solution quicker. If the application

successfully supports growth, scalability can be added later when it’s financially justified.

Strategic Mission and Vision

Matching the Correct Technology to the Solution Maximizes Returns

We’ve already seen how different levels of an organization have different database needs. Choosing the right technology and approach for each level impacts the ability of that level to perform long-term, and the returns it generates.

Using Multiple Tools is Critical to Success

An organization faces a variety of database challenges. No tool solves every issue. Many tools and approaches are available each with their own strengths and

weaknesses. Some manage large amounts of data in a very structured and secure manner. Other tools mange a relatively small amount of data in an unstructured, minimally secure, yet highly flexible manner. Depending on the objectives, one tool may be superior to the other.

Military Analogy

Like a CIO/CTO, a commanding general has many types of battles to fight and multiple weapons to use. The general wants the most powerful weapons but would be handicapped without tanks, artillery, and rifles. That’s because all battles aren’t the same. Choosing the right weapon for the right job is critical to meeting objectives, managing budgets and resources, and responding to the unique requirements of each situation.

Access Fills an Important Segment

Even though Excel is not a database, in many organizations, people store more data is spreadsheets than any other platform. This drives IT professionals crazy, but works. Decision makers need to analyze data and they know Excel. This is one of the greatest benefits of desktop computing.

Although Excel is not a relational database, it solves many database problems completely. That’s because many database problems can be solved with simple database solutions. Only a tiny percentage of Excel spreadsheets ever reach the limits of Excel, but when they do, many migrate to Access.

Microsoft Access Fills a Large and Important Segment

The success of Access as the most popular database in the world is a testament to its capabilities and the pervasive need for database solutions by productivity workers. Access is the first weapon of choice when it comes to relational databases because of its ability to quickly create useful database solutions. It may not have all the features of reliability, scalability, performance, and security of more sophisticated solutions, but for many situations, those features are irrelevant or secondary to what Access offers. Access offers an excellent solution for database challenges facing individuals, small teams, and workgroups across a network.

The number of database challenges within an organization that can be solved by Access is much larger than solutions solved by more complex and expensive solutions. And over time, with the drop in hardware prices and increases in performance, more and more database situations are solved by Access.

Database Solution Costs

Different database problems require different solutions. If an organization’s only database response is a $200K+ solution, it cannot profitably manage opportunities worth less than that. That may or may not be a problem today, but it gives

competitors an opportunity if they have less expensive solutions. Over time, some of those small opportunities grow into big ones.

The cost of solutions and the solutions themselves vary significantly by the platform selected. Here are some ballpark numbers:

Platform Average Cost

Excel $ 500

Access Individual $ 3,000

Access Simple Multi-user $ 10,000

Access Workgroup/Department $ 50,000

VB and Jet $ 200,000

VB/VS.NET/Java and SQL Server $ 500,000

Oracle, IBM db2 $ 2,000,000

SAP, Tandem, etc. $ 10,000,000+

We can argue over the fact that there are million dollar Access applications and $20,000 .NET applications, but that misses the point. These numbers show order of magnitude for a large organization, and what they generally spend for solutions on those platforms.

It is worthy to note that solutions created for the first three platforms (Excel and simple Access applications) are often created by non-IT professionals. Managers, analysts, and administrators create these solutions without IT budgets or guidance. It’s simply part of their job. Most of these solutions would rarely make economic sense if IT staff fulfilled them, nor would they be able to create them in a timely manner. That said, many applications created by non-IT professionals are not maintainable and suffer from poor design.

Once you get into workgroup applications, defined budgets, design processes and more structured development efforts occur, and people specializing in application development get involved. But even at this point, costs vary widely based on the platform selected.

Quantity of Database Solutions

As illustrated in the Database Pyramid, there are a lot more small databases than large ones. Here’s an estimate of the relative number of database solutions by platform in a large organization:

Platform Quantity

Excel 50,000

Access Individual 5,000

Access Simple Multi-user 1,000

Access Department 500

VB/Jet 100

VB/VS.NET/Java and SQL Server 50

Oracle, IBM db2 25

Quantity vs. Cost

When you compare quantity and cost, there’s an exponential relationship between the number of solutions and average cost. Here’s the comparison on a logarithmic scale: 1 10 100 1,000 10,000 100,000 1,000,000 10,000,000 Excel Access Individual Access Simple Mult i-user Access Depart ment

VB and Jet VB, VS.NET, Java and SQL Server IBM db2, Oracle SAP, Tandem, et c.

Quant it y Avg. Cost

Not surprisingly, as the cost of each implementation increases, the number of solutions decreases. It’s the CIO/CTO’s responsibility to survey the entire spectrum of database challenges facing the organization and deploy the appropriate technology to meet them given limited resources and time.

Advantages of Access

Access is the most popular database program because non-IT professionals can cost-effectively solve a wide range of database problems with it, and professional developers can create very sophisticated multi-user solutions.

Tremendous ROI

If it can be solved in Access, it’s probably cheaper than alternative solutions which maximizes ROI

Rapid Application Development

The Access development environment lets you create results fast. Access solutions often require significantly less code that alternatives. It’s a great platform for prototyping.

Integrates with Microsoft Office

Access is part of Office and integrates with the most popular interface users use: Office.

Great for Data Entry – Windows Still Beats Web

Somehow web users are trained to accept behavior that would cause howls in Windows applications. For instance, changing the quantity and pressing [Update] to refresh total sales. Access easily (cheaply) supports this, copying and pasting records, displaying multiple one-to-many relationships, and other basic features (e.g. spell checking) that provide a much friendlier and richer data entry

experience than Web solutions.

Interfaces with Lots of Database Formats

Access links to all sorts of data sources.

Excellent Report Generator

The Access report generator is second to none. Sub-reports are also extremely useful for showing multi-table relationships. Combine this with Access’ ability to link to many data sources and you have a great report generator. Many database applications have significant report generation features.

Web reports still don’t compare or print on paper properly, even with a lot more effort.

Approachable Development Environment

The VBA IDE is the same as VB and offers a very productive development environment. Unlike .NET or Java, you can edit code while debugging which is a real time saver.

Access Solves Many Solutions with Less Code than Alternatives

The less code required for a solution, the better. It’s easier to create and easier to maintain. N-tier solutions are definitely not RAD, and not beneficial if you never need to share your data.

Ideal for Network Solutions

Access is designed for file server solutions on local area networks.

Handles Non-Connected Situations

Access supports laptops and disconnected solutions that can’t be handled by web applications.

Limitations of Access

Of course, Access has limitations that prevent its use in some cases.

Not for Web Solutions

Access simply isn’t designed to create web sites. The Data Access Pages are of limited use in Intranets but not Internets. The underlying Jet Engine is also not useful except when the number of simultaneous users is low. Access is optimized for Windows, not the web.

Deployment Issues

Access applications require users to not only have the Access database but also install Access. Access is huge and different versions of Access/Office also cause problems. However, in many organizations, Access is already installed on each desktop.

Updating Access databases when updates are released is also challenging.

Fortunately, our Total Access Startup program addresses both the Access version and database deployment, but it’s not a built-in feature of Access.

A great advantage of web applications is the centralized application. No

deployment is required assuming everyone has a web browser, and updates to the application are made in one place only and immediately available to all users.

Security and Data Integrity

Although Access/Jet Engine databases can be password protected and encrypted, Jet Engine databases do not have the same level of security as SQL Server or mainframe database systems.

Similarly, data integrity is not as robust on file based databases like Jet compared to SQL Server. Our Total Visual Agent product addresses the administrative needs of daily database maintenance (compacts and backups), but it’s not the same as alternatives like SQL Server.

Limited Scalability with its own Database Format

One Access/Jet Engine database is limited to 2 GB. If a database exceeds that, the solution can’t be entirely solved by Access. Jet databases also run into problems with too many simultaneous users. The number depends what they’re doing.

Limited User Interface

Applications built in Access, unlike Visual Basic, are limited in appearance. Multiple document interface (MDI) applications cannot be built in Access and in general, users can tell if an application is written in Access. For some situations, programs like VB provide a more desirable user experience on Windows.

Why Access Is Important

Access is the best solution for the segment between Excel spreadsheet and more sophisticated database solutions. Access is the most popular database in the world by servicing this segment extremely well.

Many Database Problems are Completely Solved by Access

Access simply does its job well and for many situations, a more sophisticated solution would offer very little beyond what Access delivers.

ROI: Access Solutions Cannot be Cost Justified on Other Platforms

Access is a RAD tool. Solutions created in Access often require much less code than other platforms, and can be created by people who cost a lot less. Some databases are simply not worth a lot. A $40K business opportunity may support a $10K Access solution. But if the IT shop can only offer $50K solutions, the choice is simple: it can’t be done.

Access Provides Tremendous Competitive Advantage

By being low cost, Access offers the opportunity to go after business that would otherwise be left to competitors. A tiny fraction of those seemingly “small” opportunities may become significant in the future. Being able to profitably participate in such engagements is strategically important for an organization. Many baseball players built their careers by hitting lots of singles. Every now and then one of them goes over the fence. You just don’t expect it or know when it will happen.

Myths of Access Limitations

Access is often criticized for its scalability and migration limitations, which I believe is misplaced.

Most Database Problems are Small

Most database problems manage relatively small amounts of data and usually well under 100 MB. This is well within Access’ strength and using a product like SQL Server would be overkill for such small amounts of data (SQL Server does offer features that might be important beyond database size).

Few Database Problems Exceed Access’ Capabilities

Access/Jet databases can support up to 2 GB of data. Access applications can link to multiple databases, so even using Jet databases, Access applications can

SQL Server Eliminates the Scalability Issue

Microsoft has designed Access to be scalable. Access applications can eliminate Jet and use SQL Server as its data repository. Access databases (MDBs) can link to SQL Server data, and ADPs work directly against SQL Server. SQL Server eliminates the scalability issue for data size and number of users.

When people focus on the limitations of Access scalability, it’s important to note that the issue is really about the Jet Database Engine, and not Access as the front-end to SQL Server. Of course it takes extra work to migrate to SQL Server or convert an MDB to ADP, but a significant portion of the development investment is preserved.

Hybrid Solutions Work

If an application exceeds Access’ capabilities, a hybrid solution with Access and other interfaces against SQL Server is often appropriate. We’ve created VS.NET applications for web solutions against SQL Server, with Access still playing a role inside the organization for administrative functions and reports. Using Access where it’s appropriate maximizes ROI.

Is Access a Professional Database?

Over the years Access has gained a bad reputation in some circles by being considered a “toy” database or is somehow inappropriate for professional

development. This is amazing since Access remains the most popular database in the world, and absolutely ridiculous since very powerful database applications are created in Access.

The misconception is the result of two evolutionary trends:

• The evolution of Access developers

• The evolution of databases

Evolution of Access Developers

Most Access developers evolved from non-programming professions. They fell into Access, discovered the amazing productivity gains, learned VBA, and become more and more sophisticated. Over time, they became VB and .NET developers using SQL Server, and now consider Access applications trivial. But the change is with the person and not Access. Access still does what it does well when that person is ready to move on. They now look down on people like their former selves challenged by database fundamentals they now take for granted. They forget they’ve become the people in the IT shop that their former selves tried to avoid, and that Access was their gateway to their successful career.

Their evolution away from Access is okay, even expected, as others follow in their footsteps discovering the amazing solutions they can create with Access.

Visual Basic Developers Look Down On Access

When Access was introduced, it took the database market by storm and became the #1 Windows database. Many database developers in DOS flocked to Access. Later Visual Basic, a pure programming language, attracted the hardcore database programmers and they started using the Jet Engine through VB and later SQL Server.

In general, VB developers look down upon Access developers. This occurs even though the languages and IDE are identical. I consider this a religious

disagreement rather than a fundamental difference. Using VB for all database solutions rather than Access, which was designed for databases, is not optimal. Anyone who’s compared the report writing capabilities will attest to that. The problem here is with the developer and not Access.

People who voluntarily change platforms (or religions) have negative impressions of their former beliefs. The same occurs when C++ and .NET developers look down on VB programmers. Likewise, the next level looks down on those people too. This has nothing to do with the technology but the journey of the individual.

Evolution of Databases

We’ve already discussed the evolution of databases and how that’s a natural phenomena. What gives Access a bad name is IT shops that are not prepared when Access applications evolve into their laps.

When IT departments see an Access database, it’s often a result of an emergency or other problem. They were not involved in its development, never saw it before, and are now asked to support and enhance a system with an impossible deadline. There’s no documentation, the original developer is long gone, and it’s a mess. Of course there’s going to be resentment, but this is not Access’ fault.

Many Access databases are created by database novices and don’t perform optimally, but blaming Access is not correct:

• Access is not bad; the author who built the application wasn’t skilled.

• Access got what they needed done. Now they are coming to get skilled

help and can justify the cost.

• This is the natural evolution of database applications; it’s why database

professionals have jobs. IT shops should be offering services to these “customers” to take their applications to the next level.

• Complaining that Access is too easy for non-IT professionals to build databases is wrong. IT exists to support the business not the other way around.

What aren’t recognized by IT shops are the thousands of Access databases they never see. These are databases in production and doing their jobs, or died along the way. Databases the IT department never had the manpower to create, and solutions line of business managers wouldn’t want to pay IT departments build. Recognizing the evolutionary trend of Access applications is critical to managing their life cycles and integrating it with the rest of the organization’s database strategy.

Using Access Strategically

Now that we’ve discussed the pros and cons of Access, how should it be used?

Why Use Access

1. An organization faces a wide range of database challenges, and those

challenges evolve over time.

2. Access solves the largest segment of the database challenges.

3. Database solutions solved with Access offer tremendous ROI.

4. Many solutions are not cost effective with more expensive alternatives.

When to Use Access

1. Windows based, single and multi-user database solutions. The number of

simultaneous users Jet can support depends on what’s being done. We generally consider 50 to be a reasonable number (which can support many more users). Replacing Jet with SQL Server eliminates this limitation.

2. For prototyping and often, the prototype is sufficient or “good enough”

3. For cost and concept-justifying solutions BEFORE starting larger and more

expensive solutions.

4. Avoid worrying that Access may not be the ultimate solution since most

database projects will never reach that point.

Migrating Access Applications

Using Access, like any other database, also means preparing for alternatives when its limitations are encountered. Only a tiny fraction of Access solutions ever need to migrate to the next level. Options include:

1. Optimizing and fixing problems in the Access application to keep it in Access.

Make sure skilled Access developers are available to support important Access applications.

2. Migrating the data from Jet to SQL Server

3. Converting the Access MDB to an ADP

4. Converting the Access application to something else like VS.NET, Java, IBM,

BEA, etc.

Conclusions

Databases evolve over time. Access cannot and was never designed to solve every database problem. What it does offer is a great, cost-effective, and quick solution for a wide range of common database challenges in Windows.

Anticipate and welcome the natural evolution of databases, and you’ll find an important role for Access in the overall database strategy of your organization. Compared to alternatives, Access offers tremendous ROI opportunities and competitive advantages to those who use it properly.