OT: Motorcycles and Access

Hey Merrill, great post.  But there is a little bit lacking in the logic of at least the first half of your post.  I have been called in to rescue mission critical SQL Server applications that have gone horribly wrong as well.  In that case I do not suddenly view SQL Server in a poor light: I reserve that for the architects and/or implementers who used a good platform to make a bad application.

I think there are competent developers out there who can make a decent application with Access.  The problem is not about the quality of the application or the code, but is more about my biggest beef with Access, and is similar to one of Brent's points - why bother starting with it if you know it can't scale?

I think people have the ability to do the same kind of hole-digging with SQL Server.  "We'll use SQL Server Express; it's free!"  Then their database hits 4 GB and they have to scramble to recover from downtime and point the app at a new instance.  The difference between SQL Server and Access in this case, of course, is that to move your app from Express to a "real" edition, you just change the connection string.  The Access migration (even to SQL Server, a platform made by the same vendor!) is, as you suggest, a lot more painful.

For an app that is only ever going to service a 3-person team, and when the in-house experts only know VBA and such, it probably is the better choice.  But take the same sized team and app, and no expertise whatsoever either way, I'll take Express every time.

Also, I don't know how off-topic your post really is.  :-)

HA! Great post.  I'm not so sure about FileMaker, but I see where you're coming from.  When people ask me for tiny apps like that, I tell them that they've got three options.

First, ask yourself, how many things do you want to build?  Is building this thing just the first step in a new career as a programmer?  I can usually tell pretty quickly if somebody's building this because they're curious about programming and they wanna do more, or if they're a business user who has a desperate need to solve a problem.  If they only wanna solve a problem, I suggest that they either store the data in a spreadsheet - because users love spreadsheets - or else hire somebody to build it for them.

If you want to keep building things, though, do yourself a favor and pick up something that will scale.  No, SQL Server isn't as friendly as Access, but you can take your skills to amazing places.  You can't take your Access skills to NASDAQ or Ebay.

It's not just a program you're building - it's your career.

Yep, I think we're saying the same thing; I said: "The Access migration (even to SQL Server, a platform made by the same vendor!) is, as you suggest, a lot more painful."

Brent,

To be fair regarding the "it's your career" line, I know a couple of Access consultants who probably do a lot better financially than anyone on this thread. You can make a lot of money if you have a niche, no matter what that niche is, as long as there is some demand. And there is plenty of demand for Access skills--though certainly not as much as for SQL Server skills, and the projects are very different.

Like Aaron, I've been called in to help fix up SQL Server problems, and I've been called in to help with Access to SQL Server migrations when Access could no longer scale. And sorry, Brent, but as a SQL Server person I don't hate Access in any way. I think of it as a tool, and just like SQL Server it's often improperly used. There is no reason to spread FUD around its use.

Brent,

Putting on my business hat, an Access app that has many users and does not scale, that app is a smashing success. It does what people want, that is why people use it.

More to the point, because the scenario when an Access app has grown and needs to migrate to a more scalable platform is very common - that common scenario shows a proven way to succeed in business.

Have someone who knows what customers/users want, and have them build a solution with a technology they are most comfortable with. Later, when there is money, we can hire someone to upgrade and use proper technologies.

The key point is to have someone who knows what the business needs. The choice of technologies is less important at this stage.

Such a project, quickly done in Excel or Access,  may have a better chance to get to profitability than a project done by a highly skilled team of software engineers, who fail to communicate with the business well enough and build a perfect solution, but not for the problems they needed to solve.

Alex, a counterpoint: Friendster.  They built a simple social networking site (and yes, I was a member).  When it got popular, it started to crumble under the load.  By the time they reacted, hired good enough people to scale out and migrate to proper technologies, they lost all of their business.  The site is decent now, but when was the last time you heard of Friendster being mentioned in the same sentence as social media?  I'm simplifying the course of events obviously - you can read more about it here:

http://www.nytimes.com/2006/10/15/business/yourmoney/15friend.html?_r=1

But the point is, let's simplify even further and pretend that Friendster's performance problem was due to building the thing in Access, and the trouble with the migration was all that changing of IIF to CASE, DISTINCTROW to GROUP BY, MEMO to NVARCHAR(MAX), learning how to write stored procedures, etc.  If they had used SQL Express initially, the migration would have been much quicker.

Aaron,

Most startups fail. Most IT projects fail too. There is no clear cut way to success. Yet from my perspective for every project that failed because it did not hold up under load, there are several that used proper technologies but did not solve the problem they were supposed to solve, typically because of poor communication.

What is the point if the app is fast and uses proper technologies, if it is full of bugs and does not do what users want?

Rewriting an Access app which does what users want is not that bad - we already now what the users want. Designing and writing code is usually easy when we already know what to deliver.

>> typically because of poor communication.

>> we already now what the users want.

I fail to see how Access helps you do these things better?  If you can't communicate and you don't know what users want, then your app in Access is going to be just as miserable and useless as it would have been in SQL Server.  In fact, choosing a non-scalable platform like Access only guarantees that if you succeed at the above then you will definitely have work to do in the future.  Your ability to adapt and migrate your solution to more scalable tech can prevent you from succeeding, even if you have mastered the communication issues and knowing what users want.

Aaron,

I meant the case when a non-developer uses a simple tool to come up with a solution. This can be Excel or Access, but it has to be very simple, so that we can develop something working without much learning.

If we need to try out a working prototype ASAP, Excel definitely helps - people who had that idea can do it thmesleves, without having to communicate with developers or learn more complex tolls.

By the way, emergency room slang for "motorcycle" is "donorcycle".

I really thought you were going to use the motorcycle analogy correctly, i.e., pointing out that emergency room personnel never see the millions of people who haven't had motorcycle accidents. Likewise, SQL Server folks who get called in to rescue floundering apps built with an Access/Jet/ACE back end that was not designed to scale are only seeing the "accidents." In the field of psychologists who study homosexuality, this kind of thing is referred to as "Bieber's error." I can't find a good online cite but the <a href="http://en.wikipedia.org/wiki/Irving_Bieber">Wikipedia article on Irving Bieber</a> refers to it briefly:

<blockquote>[Bieber's book] <em>Homosexuality</em> has been criticized for examining homosexuals already in analytic treatment as opposed to non-patient heterosexuals.</blockquote>

Bieber made his conclusions about ALL homosexuals from studying a population of men who were seeking psychiatric treatment for mental illness. Thus, the population he looked at had a whole lot of psychological pathologies and he never saw the healthy homosexuals. So, like the emergency room personnel, the SQL Server "doctors" who are cleaning up after failed Access/Jet/ACE projects are only seeing the apps that ran into the wall, not the ones that drove the speed limit and operated with their helmets on.

A properly designed Access app with a Jet/ACE back end that aligns correctly with the actual requirements of a particular application in terms of user population, security requirements and reliability will never be seen by the SQL Server "doctor" because it isn't "ill," just as the millions of people who ride motorcycles but have never had an accident will never be seen by the emergency room personnel.

So, the view of the "doctors" will tend to be skewed by commonly running into the worst scenarios repeatedly, and never seeing the Access apps with Jet/ACE back ends that never encounter problems.

And, of course, this post once again exhibits the seemingly universal tendency among those who bash Access in favor of SQL Server to fail to distinguish between the Jet/ACE database engine and Access the database application development tool. There are ultimately scalability problems with both, but they are completely different types of problems (an Access app scales fine to any population of users if the back end is appropriately chosen; it's only when the app becomes complex enough that it needs to be maintained by multiple programmers that scalability issues pop up for the development side of things), and Access as front end can be used without any significant problems in a much larger portion of all database applications than Jet/ACE as data store.

And I also note the asymmetry in this "argument" about SQL Server vs. "Access" -- I have not encountered a single Access developer who denies that Access/Jet/ACE is inappropriate for apps at a certain scale, nor any who make the blanket claim that one should never use SQL Server and always use Access/Jet/ACE (which would be the equivalent of what so many SQL Server advocates are claiming about Access). Access developers see SQL Server as one of the many tools available to them to help them build efficient, inexpensive and reliable apps for their client, not as an enemy (or even as any form of competitor).

This is in part because Access developers are not in the database emergency room, patching up the accident victims. But I think there's also something of a weird chip on the shoulder of SQL Server advocates that is inexplicable to me. I wholly endorse the assertion that Access/Jet/ACE is inappropriate for certain database applications. Yet, the corollary of that, that Access/Jet/ACE is also APPROPRIATE for certain database applications, is something that way too many of the SQL Server advocates deny categorically.

It's not that they say "in my experience...", no, they make categorical assertions that have no exceptions. And almost always, they do so while exhibiting confusion on the issue of Access vs. Jet/ACE (confusing the development tool with the database engine). This suggests a superficial understanding of the subject and tends to discredit anything useful they say about Access/Jet/ACE and upsizing.

I think your estimation of the Jet/ACE db engine's scalability is off by a factor of at least 4. I have had several of apps with MDB back ends (I don't do A2007 development, so no ACE back ends) that functioned just fine with 15-20 simultaneous editing/adding users. It's not very hard to design these apps if you read up on how to build efficient multi-user Access apps. A lot of the principles are exactly the same as those you use with a client/server setup (the first law: retrieve only the minimum data needed to get the job done).

I have managed plenty of these projects over the years as a sole developer and have never had issues with apps this size. I know of plenty of Access developers who have much, much more complex apps than any of the ones I've created, and they work just fine.

You also seem to imply that ODBC cannot work, but it works extraordinarily well. There a few well-known issues with ODBC and ODBC-direct that are not easily solved with ODBC alone, but since A2000, when you encounter those kinds of problems you can switch to ADO (which is much more complex, as it's no longer using Access's native easy-RAD techniques), but at least you have options to work around the problems.

I just don't see the issues you describe at all.

I don't think there's anything particularly exceptional about my skillset, other than that I've built it over 14 years of experience (and even that's not really exceptional). My first apps were awful, from my present-day point of view, but there's a boatload of my "version 1" code still out there in production use (I have an "if it ain't broke don't fix it" attitude), and I sorely wish I could fix it, but until I'm asked to do something with these apps that the old code prevents, I cannot justify the time and expense of refactoring the code in the apps to be better structured. If it's working, how can I say "but it ain't pretty!"?

Now, if that code were interfering with productivity, endangering data integrity, or slowing down the users, I'd be the first to hear about it. But it's not! I'm as surprised as anyone, but this is one of the things that Access doesn't get credit for, that even its crap applications are shockingly able to stand up to everyday use, even when held together with bailing wire and chewing gum. My big project this past summer involved taking over an app that dated from 1996 (with very few revisions over the years), and had NOT A SINGLE LINE OF VBA CODE -- it was all macro driven. It worked for these folks for 13 years. It was butt-ugly, and a mass of monstrous spaghetti and duplicate objects with slight variation and some really weird data structures (though actually quite clever in some cases), BUT IT WORKED. If the light switches still work, you don't care if the wiring is substandard. Until you plug in that new halogen lamp, of course, and then you need new wiring, and that's why I was hired.

Maintenance can be a nightmare in these apps, and when that pops up, I always recommend a refactoring of the affected parts of the app to insure that there's only one time this problem has to be addressed (and I always start a project like the one described above by leaving the end user experience unchanged and refactoring out the macros and replacing them with manageable and maintainable code, and only then do I start implementing new features).

But I just don't see the issues you describe, even in my crap apps from c. 1997 or so (two that I started in May of that year are still in production use as the flagship databases of the two organizations involved, though the user population is tiny in both cases; and both apps have undergone multiple major revisions at this point; both would scale just fine, in fact, because of the way they have been revised over the years to get away from non-scalable designs in the original implementations).

So, I'm not sure we agree at all. To me, putting the upper limit at 5 users looks like the conclusion of the developer who really doesn't know how to program a multi-user Access app (if you follow the sample code and sample databases, you'll probably max out at about that level, which is why everybody I know avoids the horrid examples provided by MS). And, yes, it likely requires reading a book or two (or three or four...) to get it right, rather than pointing and clicking your way to an application.

But what development platform does that not apply to?

So, last day of the year, and I can see many people are in a reflective mood. I don’t usually deep dive