Things I wish I knew about Microsoft Access before I started using Microsoft Access

Microsoft Access is one of the most powerful databases for small business available on the market. Not only does it have its own form and reporting development environment built in; the wizards and design views allow the user to quickly create data centered applications. This ease of use, coupled with the simple fact that Access is included with Microsoft Office has lead to widespread usage in both the small business and corporate environments.

I have worked with Access for several years. In my experience, I have come to several conclusions about Access:

Multi-User Company Wide Applications should never be built using Microsoft Access.
Multi-User applications in Access use the file locking system built into Windows. What this means is that after the first user connects to the database, there is a significant slowdown in performance. MS Access supports up to 256 connections, the actual performance limit is much lower. I have seen access applications come to a crawl with only 10 users. Instead use SQL Server with a .NET front end.

Set the Focus On The Field
One of the painful things you must do in MS Access when doing applications is that you must set the focus on the field before you can get the value the user typed in.

MS Access Corrupts Databases
Because of the file locking and users disconnecting due to lockups in Windows, MS Access Databases will sometimes become corrupted. Try these things if it happens to you:

1. Do a repair and compact.


2. Restore from your backup. If you don’t have a backup, you need to back up your database nightly.


3. Create a blank database and import it in.


4. Buy a tool that repairs access databases.

Easy Applications Are Super Easy, Hard Applications are Super Hard
Access tends to be incredible at building quick and dirty database applications. If there are a significant amount of business rules involved, Microsoft Access simply becomes counterproductive. It is easier to build a business rule intensive application in .NET than to build it in Microsoft Access.

So what is Microsoft Access good for?

• Microsoft Access is great when you have a bunch of spreadsheets that you need to import and run some reports on.


• Microsoft Access is great to store simple things at home like your recipes or DVD collection.


• • Microsoft Access is great for small business until the business is ready to afford a full time dedicated IT person to do .NET development.

Finally, get yourself an Access Database Comparison Tool, you make copies of your Access Database, employees will change things and then you will wonder what changed. The best tool for comparing Access Databases is AccessDiff.