<

ACCESS IS A TOY: FACT OR FICTION?

Published on
19,164 Points
6,464 Views
32 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
Jim Dettman (Microsoft MVP/ EE MVE)
Independent consultant specializing in the writing of custom packages for businesses.
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.
32
Comment
9 Comments
 

Expert Comment

by:michaelmccomb
Nicely stated.  We use MS-Access in a very complex medical environment that is constantly changing and growing.  I WISH there were alternatives but we have found nothing that comes remotely close to what MS-Access with a good bit of VB can do.
0
 
LVL 59

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Michael,

Thanks for the commnet.  Access often deserves more credit then it's given, especially since you can use it as simply a front end for many relational database products.

But it is like anything else; you need to understand the product and what it was designed to do.  If you do, then it has it's place and can work extremely well for a very reasonable cost.

JimD.
0
 
LVL 10

Expert Comment

by:Tony Hungate
JDettman,

Very nice article, MS-Access is one of my favorite applications.  I have used if for years to organize all sorts of information.  I even had a boss once that saw a finished product and tried to tell me he wanted to re-created it for the entire work force, then deploy it globally.  He was so very unhappy when I informed him that would not be a good idea.  He was more than confused when I explained to him that the application was never made for that kind of use, and that it would not work.

You hit some very debated topics here that I have heard over, and over.  I like the way that you laid out the article and the way in which you stay on the side line and allow the reader to take in some facts so that they can better understand the applications capabilities, and strengths vs. weaknesses.

Well done.

TLH
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LVL 59

Author Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Thanks.  Glad to hear you found the article useful.

JimD.
0
 
 

Administrative Comment

by:Patrick Matthews
JDettman,

Congratulations!  Following a review by the Page Editors, your article has received the Editors Choice desination.

Cheers,

matthewspatrick
Page Editor
0
 

Expert Comment

by:DanCooperstock
For professional applications to be written by professional developers, I think you will end up with far better products (and more productivity, after a somewhat steep learning curve) with PowerBuiloder.
0
 

Expert Comment

by:cgavant1
I am a power Excel user, but sometime the data I am manipulating is just too large for Excel. In that case for me Access is incredibly useful. Also, I have used its ODBC capability as a front end to SQL Server databases. These applications are strictly for me as an end  user. Many times I have written quick reports that my colleagues would have to ask for help from the IT dept which is usually overworked and underpaid.
0
 
LVL 2

Expert Comment

by:badgerfire
I have written several applications in Access.  And you can deploy it using tools provided by Microsoft for the developer (at additional cost).  But I always tell the executives that if we want to take this to the next level we need to use a true development tool.  

The biggest reason is the stink Access gets in the industry.  But in my industry a funny quandary has developed.  Excel, an honestly weaker tool, gets high praise.  There are two things that cause this paradox.  

The first is the ease and versatility of Excel.  Why would you want to move your applications to Access when Excel does a mighty fine job.  Excel has a full chart wizard making it easy for the novice user to create stunning charts.  Excel even uses the VBA engine so you can code complete applications in it.  You can secure the spreadsheets.  Really why do anything in a more complex and cumbersome tool?

The second is the complexity and lack of development on the Microsoft side.  Access is a complex tool and can do a lot with multiple data sources.  It is an excellent tool for taking sources from different data engines and combining and reporting.  Access provides quick and rapid development; but sometimes at a price.  If you a novice Access user the VBA system can be daunting.  Where in Excel you whipped it out in a couple of hours you find yourself stumped and working for days.  What a user like that does not see is the days prepping the system to work properly will save him days of hours he was manipulating Excel.

The other part of the problem was Microsoft's lack or plan to not include as a robust chart interface like the one in Excel.  But the funny thing about that is that the core engine for the charting is the same in both applications.  Yet an Access developer finds that he has fewer options in the chart wizard than an Excel user.  To truly manipulate the chart system in Access you need to delve into VBA and the object model.  Something a novice user would not do and an intermediate user may become frustrated with.

It sounds like I am touting the benefits of Excel over Access.  I am not.  Excel has its place and so does Access.  I consider Access an interim solution, a test bed if you will, to greater things.  The problem arises when executives see the test bed and assume it is a final solution.  And to me there is no final solution.  You should always improve the application by listening to the users and applying well thought out, value added features.
0
 

Expert Comment

by:developingprogrammer
Thanks for writing this article Jim, it really helps a lot! = )
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month