THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

OT: Motorcycles and Access

So, there's a bit of a shouting match over at Brent's place, which got me thinking not how Access sucks is lacking, or how to use it as a piñata (which is admittedly fun!) but instead why DBAs have such hosility toward it. Why the crazy twitch? Why the lashing out? The answer, for me at least, is only half related to Access itself.

Many emergency room nurses do not like motorcycles. Is it because motorcycles are "evil?" Or because they are poor quality, inferior products? No, it's mainly because if you spend your time stitching people back together who have fallen off of motorcycles, and that is your only contact with motorcycles, it changes your viewpoint.

Many DBAs have been called in to rescue people, or teams, or projects who have mission critical Access applications gone horribly wrong. It's very unpleasant, especially the typical discussion we have to have with the Access afficionado -- who probably authored the thing, and is at that moment feeling quite defensive and more than a little worried -- that explains why it's important when you outgrow Access to ditch their entire app, move the data to an RDBMS and rewrite the UI from scratch. A new UI, incidentally, that they can't write because it won't be VBA. It's no fun. I think this experience, oft repeated, is the source of most of the hostility.

I will go out on a limb here, and even though I suffer the same twitch from rescuing people in that very scenario, I will say that there is a place for a simple desktop, or small workgroup, all-in-one database app where no coding is required. Further out on this limb, I will admit that when people ask me which one they should use, my answer is always FileMaker Pro, not Access (Stop laughing! I'm serious!). Why? Because it has a much better user interface, and it genuinely meets the need: a fairly technical person not capable of or interested in coding, with no "IT Department" can make a small scale, decent little database and do useful things. If you outgrow it, you throw it away - without also throwing out thousands of lines of custom VBA code. There's less expectation that you can run a big team or a huge database on FileMaker. And yes, SQL Server Express is awesome, but I am talking here about having a full, authoring UI for a non-coder to make useful forms and data and get immediate value from his PC.

So, for me the real issue with Access is its hole-digging tendency. Someone starts down the Access road and only later, after a huge investment of time, learning and resources, realizes they've dug a hole and jumped in. When it doesn't work any longer or won't scale, it's just so painful. There's no real upgrade path (only the illusion of an upgrade path, which makes this even more bitter) and the "SQL Server integration" options are terrible. Ultimately, they have to call in the Emergency Room DBA. That's where the twitch comes from.

 

Published Friday, February 12, 2010 10:19 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

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.  :-)

February 12, 2010 1:38 PM
 

Brent Ozar said:

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.

February 12, 2010 1:56 PM
 

merrillaldrich said:

Hi Aaron - I hear you, but I think there's one key difference: the pain involved. Anecdotally, at least, the right answer when Access breaks down is often "chuck it." The answer to a SQL Server problem usually is less extreme. A few stitches (upgrade an edition) instead of an amputation.

February 12, 2010 2:24 PM
 

AaronBertrand said:

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."

February 12, 2010 2:28 PM
 

Adam Machanic said:

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.

February 12, 2010 3:12 PM
 

Alexander Kuznetsov said:

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.

February 12, 2010 4:34 PM
 

AaronBertrand said:

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.

February 12, 2010 4:49 PM
 

Alexander Kuznetsov said:

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.

February 12, 2010 5:13 PM
 

merrillaldrich said:

I like both points: Alexander indicates, correctly, that you have to be constructing something relevant, that people care about and use, and if workload is high - good for you! At a certain point the elegance of a technology solution is only interesting to IT people, except when it really is profoundly broken. It's an extension of the Shipping is a Feature idea (Spolsky) -- a beautifully architected, elegant application that no one cares about won't succeed on its looks.

However, Aaron is right, too - *if the performance of the thing* is essential to its/your success (a public web site, for example) then you have to build it correctly, or you'll fold when it folds.

Frustrating, though, that SO MANY groups have gone through this successful idea + broken Access + no migration option, and still we have no decent solution. Example: this might not be practical at all, but what if there *were* a tool that would convert all your Access forms to .NET winforms, Access reports to SSRS, and migrate all your Access data to SQL Server Express (and I mean a real, working tool). Hmm. Maybe that can be my first $1 mil.

February 12, 2010 5:21 PM
 

AaronBertrand said:

>> 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.

February 12, 2010 5:23 PM
 

Alexander Kuznetsov said:

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.

February 16, 2010 1:39 PM
 

Anne Onimos said:

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

February 19, 2010 10:58 AM
 

David W. Fenton said:

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.

February 20, 2010 12:41 PM
 

merrillaldrich said:

Hi David - thank you for the thoughtful reply. I do have two observations - first, to your point about drawing conclusions and Bieber's error, I hope that a closer read of my post will reveal that I agree with you completely. That was my point. The people who clean up the messes, only, can have a skewed view. If I didn't make that point, my fault for poor writing. The Access bashing reference I lead with was intended as a joke.

Second, I will agree that Access works OK for very small applications, for example in a 3 or 5 person micro-business, where complex development or scalability are not a concern.

The problems that people point out often are less about that fact, but more about the fact that Access is so often stretched way past that practical limit, whether it be because the 5 person firm grew, or because an Access expert, not knowing how to implement other technologies (that does not mean everyone, that means specific Access developers who have just one bag of tricks) continued to recommend it in the face of *genuine* problems. And I am deliberately including both the Jet engine and the UI components in that concern. I have worked with many small organizations in exactly that scenario.

To me the distinction between the Jet engine and the UI development halves of Access, while technically true, isn't really important, because in my experience you hit the various data engine limitations and the very critical UI development limitations at some point when you outgrow the whole platform. Simple example - it's completely impractical to only have only one developer work on a bigger, more complicated app, and have all the code locked up in a single file. In my experience any large application with a bigger user base will outgrow BOTH the data engine and the practical limits of the UI features. An access front end with a SQL server back end might solve some of the problems, but often ignores and/or creates elaborate workarounds for the real UI development issues. Many (again, not all) Access power users I have encountered in my work have this tendency to downplay all those workarounds, I think because they have not been exposed to all the things you *don't* have to do in a full development environment.

Real case: I did some consulting work for a firm where one of the two owners created an Access app on which their whole business was based; he was what I would call an amateur developer savant - he knew a little VBA, enough to get by, and he is VERY smart by all other measures. It was a success - they did ultimately go through the awkward pain of porting the tables to a SQL Server, and so on, and I tried to help. There are precious few features in Access to make this a smooth transition, and it was difficult, complicated and painful (try, for example, to unplug an Access UI full of linked tables, pass-through queries in VBA, linked queries and so on from a prod SQL database and into a development copy, in order to work on the code, as this type of amateur dev).

Here's how it went: the guy, by himself, had to do all his dev work at night (when his staff was not on the system) and then deploy first thing in the morning, often by copying and pasting code, hoping not to have any serious bugs. He did the dev work against production data, so he also had to be VERY careful not to make any mistakes, and was severely limited in what he could actually test without affecting the business. In addition, he had to employ several consultants like me at (to his small business) great cost, because of the gaps in his own expertise. It's worth noting that through all this *he was trying to do just what you describe* - not use the Jet engine, and use the Access platform for the UI. But he was way past Accesses practical limits as a UI development platform and as a database engine. That, I guess, is my point. Yes, there's an engine and separate UI tools, but the distinction doesn't matter much to someone outgrowing the entire platform. Was this the "motorcycle accident?" I'm not sure. It happens so often.

So, yes, we who are asked to climb this scalability wall over and over do have a different view, and, yes, we don't see the success stories. That does make us tend to generalize against Access. True. Still there are real problems by objective measures, too, and as you also point out it's important to see, understand and be clear about those.

So perhaps we really agree?

February 20, 2010 1:33 PM
 

David W. Fenton said:

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?

February 21, 2010 1:35 AM
 

merrillaldrich said:

Thanks David, it sounds like you've had some great success. I wish you all the best!

February 21, 2010 11:49 AM
 

Merrill Aldrich said:

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

December 31, 2010 8:27 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement