Kirby L. Wallace
kirby /at/ wallaceinfo.com
Tulsa, Oklahoma USA
Also visit me at my blog, CombatDBA.wordpress.com
Microsoft Access Database
A common response that I hear when showing someone one of my MS Access applications is: "Wow! That's Access??? I had no idea you could do that with Access!"
Imagine... An Access Application that interfaces with laser leveling devices installed in silos, and RS232 Serial Port interfaces to forklift floor scales, recording data in real time
directly into your application in real-time. Yes! Access can do that!
Most people think of Access applications as dull looking grey and black forms, with some nice drop-down lists, but not much more. Access is capable of much more than that. You can develop some
very complex, very robust applications with Access. And they are as solid and dependable as applications developed in any other language.
Microsoft Access is included in every copy of MS Office Professional. It is a fantastic tool for quickly developing small to medium sized database applications.
Some Common Access Myths
Most of the criticisms of Microsoft Access come, believe it or not, from IT Profes-sionals who use competing products - not from actual Access Programmers and Users.
All of a typical IT manager's complaints really sum up to one basic concept: Access puts a heck of a lot of power in the hands of users. Once it grows beyond their
control, we end up inheriting and having to support their mess.
There is, actually, some merit to this claim. Access is very powerful, and in the hands of a power-user, it can become very complex. Especially if these end users are using
the built-in "wizards" to do most of their development. While these "wizards" can be very useful, the truth is they generate awful and practically unmaintainable code.
As soon as you exceed the abilities of the wizards (which will usually happen very quickly), you are stuck, and end up having to get the "experts" to step in and help. But the
"experts" can do no more with this horrible code than you can.
But, when applications are developed by skilled developers using coding techniques that are sustainable, they are as reliable, and as easy to maintain as any other kind of
application, written in any other language. The complaint is not so much with Access, but with Access' built-in "non-programmer tools". And this can be a justified complaint.
1. Access is only good for "small stuff."
2. Access suffers from data corruption.
3. Access databases aren't powerful.
All of these concerns are really, pretty closely related and can be treated all at once. The real culprit here is not Access, but the Microsoft Jet database technology that is behind
native Access Database tables. Jet has some serious limitations regarding size, and scalability & architecture. Jet databases are limited in size, which means if you exceed that size,
you are stuck. And the nature of Jet databases means that all of the query processing is done on the client PC. That means that even though you may have a "split frontend / backend"
architecture, your performance will still suffer. For instance, if your "Data" database is on a network drive, and your "frontend" database is on your machine, and if you join two tables
that each have 100,000 rows, that means all 200,000 rows - the entire contents of the two tables - has to be sent across the network to your PC for processing.
The usual remedy for this is to not use Jet for the database technology, but instead use either linked SQL Server tables, or an Access Data Project (which is a direct link to SQL Server).
By taking this route, the two 100,000 row tables are joined on the server, and only the results are sent across the network. There's just no need to transmit 200,000 rows when all you are
looking for is 100, or 20 or even just one row.
Database corruption in Jet DB tables is still a potential problem, but it is nowhere near what it used to be. Up until Access97, corruption was a constant headache. But from Access 2003 and
on, corruption is far less common. And corruption in the database is very rare when using SQL Server as a backend database instead of Jet.
Corruption can happen in ANY database product. Just ask any SQL Server DBA who has come in one morning to find a database in "Suspect" mode. Or the same for an Oracle DBA. Corruption is
always a possibility in ANY computer product. That's why a good backup and restore policy is warranted for ANY database or application product.
See "Access/SQL Conversions" om the left side menu for more information.