Access has Its Place

There were other points you made that struck a chord also. It seems almost universal that any developer I meet will gag and guffaw at the mere mention of the word "Access", and I never understood why. There can be no argument that a database application developed with Access, whether back-ended by an enterprise-scale RDBMS or not, shouldn’t be deployed to multiple offices within an organization. The packets generated by Jet when interacting with its data seem to have a lot of overhead, which is bad when the forms and data are on two different servers, and there are other inefficiencies and vulnerabilities that only the most experienced Access developers would even be aware of much less know how to avoid. In a nutshell, regardless of how carefully developed, an Access application will scale badly, and what database application ever made it to production without its user base eventually growing beyond the original requesting group?

I see Access as an incredible "one-stop-shop" development tool; enabling you to create a solution including full-featured UI, database and reporting in a single, cheap, easy-to-learn environment, and in a fraction of what a typical development cycle would be. But on the other side of the coin, all those things have also become Access’ curse. It has become extremely easy to create an extremely horrible database application with Access, and I have seen it many times. I’ve seen "vigilante" Access databases sprouting up like weeds in an organization when a few folks with a smattering of Access savvy decide they’ve outgrown Excel. And almost 100% of the time I was eventually asked to scrub, correct, merge and/or migrate the data in these applications into something that worked. I think there are very few well-qualified Access developers out there, and they are far outnumbered by folks who "give it a shot" because they’ve taken a class or got something to work on the tail end of a wizard a few times.

I attribute the beginnings of my understanding of how relational databases work, how to build an intuitive, ergonomic UI and how to make some really slick reports to MS Access, and I even think that for small companies or small groups within mid-sized companies it has great potential as a development tool. Its undoing however, is the all-too-common scenario where a supervisor or manager wants a "cheap and dirty" solution for 20+ people fast and the semi-proficient Access guy says, "I can do that!".

Twenty years ago I cut my programming teeth on FPD/FPW 2.6; I credit the tool with having contributed significantly to the formation of my career as a developer. Its foundation is certainly responsible for the understanding of good database design principles that I’m proud to count as a part of my playbook.

The IDE isn’t quite as good as Visual Studio’s, but it’s certainly better than Java on Eclipse 😉

One thing I sorely miss, after having switched to VB.NET about 2-3 years prior to Microsoft’s announcement of VFP’s discontinuation, is the small and wonderful community that surrounded the product. It was absolutely second to none. To put it plainly, it was like a large family—one was very much among friends there. (It’s still active, but greatly diminished.)

Before I go into two scenarios that made an Access solution the BEST choice (over winForms, Web), I wish to clarify an often over abused and misrepresented argument against MS Access. Many people speak of Access’ inherent “bound” data handling (and subsequent data-locking) as a common issue for performance and multi-user access issues. This is true, but this is merely the inherent simplistic approach that makes Access easy to use by the masses. However, even .NET tries to offer this kind of simplistic approach in some of its WYSIWIG design interfaces. This by no means is a limitation of Access. By using simple VBA.NET objects one can easily “detach” forms from the data they are displaying/updating in the same way a winForm or web interface is detached. Sure there is an overhead for the developer as all data access and updates must be managed manually through SQL statements in the code, but this is not much different than developing under .NET through VS. What’s more performance is increased because ADO command objects can precompile SQL requests, much like a stored procedure in SQL Server, and the data is no longer being perpetually locked by bound forms. I have even developed “logical” data locking algorithms on business data entities within an Access DB so that I can manually manage things like concurrent data updates etc. so that data integrity is maintained from the VBA code.

One other important bi-product of this approach, is that the often encountered performance issues of trying to run an Access front-end against an Access split-file data store elsewhere on the network is all but removed; the data access is managed through VBA data connections and no longer via Access’ built in and cumbersome linked table mechanism. In fact there should no longer be a need to even have a table or linked table on the front-end (other than for configuration information) as the data connection is managed manually; Access merely becomes a convenient, and powerful, interface coding tool.

Two classic examples that I had to implement concerned both a small not for profit organisation and a large multi-national private Swiss Bank:

1. Not for profit
The not for profit could afford neither the IT infrastructure nor the support personnel to run enterprise level systems. Before the Access solution, they were used to being able to work through multiple Excel sheets and Word documents that they could easily take home and update out of the office without any fancy Citrix or even VPN connection back to the corporate network. There were about 8 permanent users in the office and several others that would occasionally need to access the system. Using the “unbound” data approach above, the performance amongst even hundreds of concurrent users would have been virtually instantaneous, because only small subsets of data needed to be accessed through rapid connect/disconnect operations within the forms’ VBA code, with all data locking handled manually through the data access logic layer all within the VBA. This was great, but where Access really shone was when a user wished to work from home. Without needing any special setup on the users laptop (i.e. SQL Server, supporting runtimes etc.), I created a manual data synchronisation algorithm permitting the user to take a full copy of the data portion of the Access database to work unplugged from the corporate network. Upon return the user simply resynchronised the data with the master data file permitting them to add all their changes and receive any changes made since they last synchronised. Any conflicts of the same data object being modified both in the master and in the synchronised copy were signalled to the end-user so that they could choose how to update the data object.

By not using the native mechanisms offered by Access for data-access and data-synchronisation, yet still relying solely on VBA native mechanisms to replace Access’ own means, all performance, multi-user and data integrity issues were solved, and a simple to distribute solution (two files) could both be used onsite and offline.

2. Multi-national private Swiss Bank
A custom made web-based CRM solution was implemented through .NET web technologies for use by all portfolio managers in multiple branch locations within the banks internal network. This was met with such positive feedback that it quickly went through multiple iterations making it available to en even wider user base within the internal departments of the bank. One of the three founding partners of the bank, saw the potential for managing some of his personal “contacts” that, for security reasons, were not managed though the banks internal databases. Again a criteria being that he could take the data offline on his laptop. Using a similar implementation as described in point one, Access came to the rescue with a simple online/offline solution.

Sure Access isn’t the right tool for solving all problems, but it certainly has its place. If used correctly, it offers a powerful development tool offering virtually all primary interface and data functionality from within a single file, requiring no additional supporting infrastructure. Even with the ease at which enterprise level architectures can be implemented today, the two above scenarios are classic examples of the continued need to have such a tool at the developer’s disposal.

It’s not just gossip. Back in the day, about the same time that you were developing in Fox, so was I. Since I’ve always approached dev from an architectural point of view, I found FoxPro (as it was known then) to be less than satisfying — it didn’t support many-to-many relationships. As a matter of fact, I found a paragraph in the Fox dev manual regarding many-to-many relationships, which read (paraphrasing from memory now): "…since many-to-many relationships rarely occur in real life, we will not discuss them at this time."

So I switched to Access 1.0, which DID support many-to-many relationships. Following the Microsoft buyout of Fox, I noticed that the Access Jet engine got REAL fast, so I asked my friendly MS inside person if they had integrated Rushmore. Yes, it’s true…Fox was purchased in part for its Rushmore Technology, which was quickly integrated into Access, and later on into SQL Server. And there you have it, the other side of the story.

P.S. During all those years that I developed in MS-Access, I loved it! It was fragile, didn’t offer transaction protection like SQL Server with its transaction logs (the Access pessimistic locking was a huge overhead!), wouldn’t run as a service, causing monumental data transfers across the LAN (Access may have been instrumental in more companies upgrading their network capacity than we’ll ever know) — the list goes on and on. But, I could do SO MUCH SO FAST! Access was fabulous for prototyping — in a day or two I could put a prototype UI in front of my clients, and if this were a (small) departmental application they could go into production with it. Up-sizing to SQL Server was a little tricky, and — in order to support enterprise-level transaction activity — inevitably required a re-write of the UI into something like VB — web was still pretty primitive in the early and mid 90s.

Access was such fun, such a great platform to work in for a database developer, and incredibly useful as long as it was a replacement for linked spreadsheets, limited to no more than 3-5 concurrent users, with a database no larger than 2 GB. I wonder how folks view it today? Will have to read all the comments.