ACCESS IS A TOY: Fact or fiction?
Over the years, many have had a love/ hate relationship with Microsoft Access; you either love it or hate it. Many love it for its ease of use features and polished interface, which make it one of the best Rapid Application Development (RAD) tools around. However many professional developers and IT managers hate it and consider it to be a toy for a variety of reasons. Some are justified, but many are not. How did these two different views come about?
Access has been around now for over fifteen years and with that many misconceptions about how it works and what it does have come into being. In this article were going to explore some of the fact and fiction of the Access world and you can decide for yourself if Access should be considered a toy or not.
First, what is it exactly? Microsoft Access started out as a relational database product designed to produce departmental level applications running over a Local Area Network (LAN). What everyone thinks of as Access is actually three separate components; The Access User Interface (UI), Visual Basic for Applications (VBA), and JET, the default database engine.
Over the years, Access has changed fundamentally in a number of ways:
Visual Basic for Applications replaced Access Basic in Access 95.
Database Replication was added in Access 95.
Support of class modules was added in Access 97.
MSDE/SQLwas supported as alternate database engine to JET in Access 2000.
JET added record level locking starting with Access 2000.
Replication was enhanced starting with Access 2000 by offering Column level Replication, Replica Hierarchies, and bi-directional replication between JET and SQL Server.
Data Access was expanded in Access 2000 with ADO now being the default data library. JET now supports ANSI-92 SQL extensions.
There are numerous other features that have been added over the years, which have greatly expanded its capabilities. These features along with its ease of use features (like Wizards), is what has made Access a widely popular database product. However that has been a blessing as well as a curse.
Anyone who has used Access for any length of time will attest that it is a lot more then a simple end user tool. Just take a look at some of the recent editions of the Access Developers Handbook (considered by many to be the bible of Access Development); two volumes, with a total of 2,500 pages between the two. A quick skim through these or any other books on developing with Access and one would have to agree.
It is because of this that Access often gets unfair criticism and a bad reputation. Many users start off developing an application thinking it is a simple process and quickly get in over their heads. Access like any other product was designed to handle specific tasks and when used properly will perform those tasks well. Conversely, when not used properly, problems will abound.
As an example, I once answered a question on Compuserve's MS Access forum that went something like this:
"URGENT: We're in the middle of a commercial product rollout and just discovered that Access locks on pages and not records!"
They were using Access 97 at the time and back then, this was indeed the case. Record level locking was not added until JET 4.0 (released with Access 2000). However this person continued on, blaming Access for lacking a feature that they assumed was there. They just didn't do their homework and didn't understand the way the product worked.
Another example in poor planning is the folks that try running Access with JET over a WAN. I would hate to count the number of times I've answered questions related to that one! JET is a client side engine (all data is processed on the client; the server acts as nothing more then a file sharing device) and as such was never designed to run over a WAN. Can you do it? Sure, but if you do so, you're just begging for trouble in the form of database corruption.
Microsoft is partially to blame for all this, as they have always marketed Access as an end user tool. It has never been part of the lead development technologies that Microsoft offers. In fact at a Access Advisor conference in Las Vegas not too many years ago, Richard McAniff, Corporate Vice President of Access, Excel and Office Programmability in his keynote address had this to say:
"With the largest Access development team since the early days of Access, Microsoft is refocusing its efforts on making Access the no-brainer choice for Excel users who need more power. By simplifying the development of database applications, information workers will be empowered to solve more database problems on their own. Meanwhile, the developer features will allow the continued creation of professional solutions."
Yet it's that very ease of use that leads to numerous applications being written that don't work well or not at all. So many times, I and other experts at Experts Exchange have responded to questions on performance by first asking, "Have you normalized the design?" Typical response; "What are you talking about?" Then after being told they need to change the design, they say it's too late because too much has already been done and continue on with a poor design. The end result is an application that works and performs poorly and is complicated to maintain and change.
At this point, I'd like to address some specific myths that have grown up around Access and discuss some of its short comings.
Access with JET as the database engine can only support 5 to 6 users: FICTION
Apps that can support only 5 or 6 users are just poorly developed. Typically the database is not normalized and as a result, performance is poor. Poorly performing networks or trying to run JET over a WAN link is another reason we hear of this so often.
JET with proper design and development can support 30 to 40 users without problem. Technically, JET can support 255 users, but the only time you will see that is if the application is a read-only reporting type app and the next section will explain why.
Access with JET as the database engine is prone to corruption: FACT
As mentioned previously, JET was designed as a department level database engine (in fact there are two different JET engines, red and blue, one to be used with Access and the other as an embeddable engine). All database processing is carried out client side. The server that holds a JET database is nothing more then a file share. Each client that connects to the database carries out its own processing.
While that design works extremely well in reducing administration tasks on the server, it also means that in the case of an abnormal disconnect by a client (i.e. power outage, network problem, or a ctrl/alt/del), there is no central server process that can roll a transaction on the database back. If this happens while a client has pending writes or is in the process of writing to the database file, corruption will occur.
This is the reason why in a typical read/write app, you can only get to 30 or 40 users. By the time you reach that number, it's hard to keep that many stations stable. Performance is not the issue and that's proven out by the point that if an app is read-only, you can easily go to 200+ users with no problems.
Stability of the environment is the key problem here, not of JET.
Access with JET as the database engine does not lock at the record level: FACT and FICTION
Starting with JET 4.0, Access can lock records at the record level as well as the page level. However there are several issues in this area (i.e. the DAO object library not being updated since JET 3.5) and as a result record level locking sometimes does not act the way a developer believes it should.
Also, while JET 4.0 does support record level locking, it does not do so for all operations. Index updates and DDL operations still lock at page level. Bottom line is that record level locking was an add on and it doesn't work all that well.
Generally, most apps don't really require record level locking, but if you really want it, there is a simple workaround. That is to leave the default page level locking in place and for each table where you want record level locking, pad the record so that only one record can exist on a page. This is done by taking advantage of the fact that JET will never split a record across multiple pages, so for any table with a record size which is larger then half a page, you end up with "record level locking".
While it does chew up some disk space that you would not normally use, the technique effectively allows you to enable record level locking on a table by table basis. With the built-in record level locking, it is an all or nothing deal for the tables.
JET makes a poor backend for a web site: FICTION
Most argue that JET cannot serve as a backend for a web site as a web site may receive hundreds of hits and they point to the fact that JET dies with more then a handful of users.
However what they forget to consider is that in the web site scenario, JET is only touched by the web server software itself. It doesn't matter how many users hit the web site. As far as JET is concerned, there is only one user. If the server software and the JET db sit on the same server, then it becomes a very stable and workable situation.
Access cannot generate a standalone EXE: FACT
Access uses VBA for its code support and while VBA is compiled, it is not a full compile in the traditional sense and cannot be linked into a standalone .EXE file.
Access is difficult to install: FACT
Access apps are more like a document that is read rather then a program that is executed. Because of that, Access depends heavily on the runtime environment when it is executed.
The first issue it has is having multiple versions of Access installed on the same machine at one time. One is fairly safe if versions are installed in order (Access 97, followed by Access 2000, followed by Access 2003, etc) and in separate directories. But when distributing an app, one can never be sure of that. Most developers have found that minimizing installation issues means purchasing Sage-Key scripts for automating their installs.
Second, Access depends on VBA for code support, which uses references. Often those references break. This is a problem within VBA itself and not Access per say, but the end result is that it affects your app. There are ways to deal with this, but many developers ignore them and have installation issues as a result.
JET security is weak: FACT
JET User Level Security (workgroup security) is easily crackable and there are many services / tools out on the net that will do so. Also given that understanding the security model is not all that simple, and it is no wonder that Microsoft has dropped it from the product starting with Access 2007 (along with a bunch of other features as well) and the new ACE data engine (the successor to JET). What's surprising though is that they did not replace it with anything and left application developers hanging.
Why they simply did not fix it is beyond me, although to be fair, most Access developers now use SQL Server for a data store rather then a JET db.
In terms of being a modern development environment Access is also lacking in two other key areas:
It is not OOP based.
It cannot be used to do a 3-tier design on its own.
But with all that said, it is a fantastic tool that can be used as a front end to other database systems and with its strong heterogeneous capabilities, you can connect to and join data from just about anything. The report writer is first rate as is the query designer.
In conclusion, like anything else, Access is just a tool and when used with its proper purpose in mind, it will work well. And just like anything else, you need to understand what it brings to the table in capabilities and what it does not. Use it incorrectly and it will not work well.
But anyone who ignores Access because they believe it is a toy is only cheating themselves of another valuable tool they could have in their tool box.