Microsoft Access for an Enterprise System - Why Not?

Question

Imagine this: a prototype data entry system has been created using Microsoft Access and VBA. The entire application is the Access file; it holds the front-end forms implementation, the business logic and the backend data storage.

A business meeting is being held where they are discussing the future of this prototype. Some individuals are arguing that this Access prototype should be used as the long-term solution.

What points would you raise to argue against this? Note that you are the only technical person at the meeting, so you have to word the answer so that reasonably intelligent non-technical people can understand it.

Planned usage - about 5 simultaneous users, a small number of entries each day (less than 10). Other departments may want to develop their own systems that pulls information from the database.

There may be reasonable arguments as to why Access IS OK. They might be interesting arguments, but imagine you're on the negative team.

Answer 1

I would point out that Microsoft Access can only go so far in meeting your application needs. It is entirely possible that you would want to do something that can't be done in Access. Here's where you'd need an example, like "launching a web browser to link to documentation is impossible." But I don't know if that specifically is true, but it needs to be something that could be useful in the scope of your project.

Answer 2

• Not web based (not as easy to maintain/not scaleable)
• Not easily extended--can't connect easily to share data

I guess it boils down to the purpose of the application. If you plan on automatic sharing of the data/pumping somewhere else, you would need to get something else. Perhaps Oracle APEX or something.

Answer 3

I have to say that I worked with Access in the late 90s and I think it gets a worse rep than it deserves.

I think that a good developer (not just anyone who picked an Access-in-5-days book) can make good and impressive applications with it. There are just so many people in the in-5-days group that it affects the perceived quality.

That being said, around Access 97 these were the main problems, don't know if they're still relevant

• The engine is not particularly strong and not very compatible with ANSI SQL. However, it was not difficult in my experience to run Access against an MS SQL server, if you can afford one.

• Running Access applications used to require an installation of Access or the use of an expensive EXE creator. That was a major showstopper if you didn't have the expensive installation of Office, etc. Not sure if it changed.

• The language was very close to old VB (more scripting than OOP). Now you can create nice VB applications easily with VB.NET, I am not sure if the Access language evolved. At some point I switched to doing my front-ends with VB.

• Support for non LTR languages in forms and reports was abysmal. I was coding applications that involved Hebrew and it was a mess.

• Not really web based.

• People tend to use the database and code in the same MDB file. There's an automatic tool to separate, but few people bother to.

• People used to host the database on their local machine. When small teams used it, network file sharing was key but wasn't perfect.

• A mess if people use other operating systems or cellphones to communicate with the database.

• Forms used to resize fairly badly and look different on different resolutions and screen sizes, not sure if it's resolved.

My view is that Access is a good way to go about what you are trying to achieve for a prototype. Build the database and application in a day, see if it does what you need. Then try to export it to a real database engine if you have access to one while keeping the access front-ends. Then if you really need to, build web-based on VB based interfaces.

Answer 4

I believe the main problem is with concurrent access to the DB -- to the best of my knowledge, the Jet DB engine doesn't handle transactions, meaning that corruption can occur if multiple users attempt to make concurrent changes. But someone with more experience should confirm this.

If your 5-10 users won't be making simultaneous changes, I doubt you'll have a problem. But you need to be sure they won't.

[UPDATE: It seems that from Access 2000 at least, these concurrency problems have disappeared. Thanks to commenters jle and Rex M.]

---

[UPDATE #2: There's actually quite a bit of interesting stuff in MS KB articles:]

From How to keep a Jet 4.0 database in top working condition:

• By default, writes are locally buffered for 5s
• Require Win2000 SP3 or later to avoid data loss due to server oplocks

From Using Microsoft Jet with IIS:

• Always use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver, as the latter is not thread-safe and has stability issues -- "unpredictable results may occur" if concurrent access is attempted with it.

From How To Synchronize Writes and Reads with the Jet OLE DB Provider and ADO:

• Writers must perform updates between Connection.BeginTrans() and Connection.EndTrans() (fair enough)
• Readers must call JRO.JetEngine.RefreshCache before reading; however, partial results of in-progress transactions may also be visible (this is very unfortunate).

Answer 5

It's hard to answer this question without it sounding like "MS Access bashing" (Access is a perfectly legitimate solution in many cases) and you've asked specifically for the "against" argument, so let's look at it pragmatically...

One potential issue I'd bring up is that there is a much smaller pool technical people you can hire & retain in order to maintain/update the MS Access application. This makes it a little bit more costly from a headcount perspective.

There may be some security considerations to consider (after all, it's easy enough to zip and email a single file).

Edit... I'm assuming that they've already taken into consideration the licensing costs for MS Office?

One thought is if they would consider the cost of updating Office (and thus the DB). I remember hearing a few issues coming from upgrading older Access databases to the newer versions of Office.. it might be worth consideration.

Those are the few points I can think of. Unless you can see the number of users growing, or if you don't forsee any future development work (i.e. functionaltiy Access can't support), it might be hard to come up with more compelling reasons against (without providing more information on the scope of the application).

Answer 6

Have any of the users had any complaints? Currently, its only a prototype, but probably more than one person is using it. Not sure if moving to 5 will make a big difference.

The other departments that want to link to it and run their queries may bog things down (Select * from this table left outer join {every other table in the database} where date = [since the beginning of time]).

You really need to explain the security limitations that have been mentioned.

Answer 7

For me, the main problem of Access is that it does the exact opposite of "Make the right thing easy to do and the wrong thing awkward to do." Complete separation of data and presentation is awkward in Access and requires good discipline.