10+ reasons why IT pros hate Microsoft Access (but really shouldn’t)

[asather]

Susan Harkins from Tech Republic wrote this article on MS Access and I'd be interested in hearing the die-hards (and the haters) chime in.

I love Access but I limit my use to quick calculations/reporting...Generally, stuff that isn't going to be used again. I learned my lesson regarding the Monsters we can inadvertently create with software like this awhile ago.


Andrew

 

[Banana]

She's quite astute in her analysis, IMHO.

That said, I do feel that Access could be much more better.

For starters, it would be less of a problem child if it was designed to encourage good programming habits from the novice programmers instead of being fast & loose with what is given which can get any IT professional fuming in no time.

It also would do a lot of good if they had a good Excel-to-Access exporter that also helped the users re-orient the spreadsheet thinking they inadvertently bring to Access, and is also available when creating a database from scratch. At least, this will bring users' attention to the fact that there is a database theory to know about. They don't have to understand 3NF to keep a simple contacts database. There are some wizards that does sort like that, but I don't think there's enough of those nor do I think they make the point clear. (Has that changed in 2007?)

Then there's data types and programming conventions. Access certainly does not help when it names controls the same name with the control source it is bound to (at least in 2003... 2007 may be different?), and subforms are source of confusions for new users. I sometime wonder how many and what kind of errors IT professional see with loose coding where variants are used that could be avoid if Access made a good habit of showing the users proper data types in VBE for a given datatype (e.g. more tighter binding of datatypes when referencing a control rather than defaulting to a variant for example).

There's probably more, but I believe that if they addressed those thing, it will help Access a bit and make the upsizing process more bearable when it comes into IT dept's realm.

 

[Pat Hartman]

"Then there's data types and programming conventions. Access certainly does not help when it names controls the same name with the control source it is bound" - I'm going to predict that this will change based on the questions that Clint is asking in his blog. Nothing is certain and this is a little late in the development cycle but if not for the next version, perhaps the one after that, Access will name the controls according to some agreed on standard.

asather, You should probably widen your reading and include articles written by Luke Chung (available at www.fmsinc.com). Yes, the untrained can create some really bad databases, but they only rise to the attention of IT if they become mission critical. Of course IT grouses - this should have been done by a REAL programmer with a REAL developer's tool. However, in all cases, when the app was conceived and developed, NO IT resource was available to do it because it wasn't deemed important enough.

Is the solution to prevent the use of Access? I don't think so!! That will simply drive the users to make worse solutions with Excel or some other tool such as FileMaker Pro. As long as IT is not willing to support the small, initial needs, they don't really have the right to complain when it blows up in their faces. A better solution is to provide better training and small amounts of support such as reviewing all schemas. Once the schema is correct, the application rates to be more successful and easier to convert if it ever becomes necessary to do so.

Also, and Susan's piece is no exception, too many articles start with the comparison of Access to SQL Server but anyone who knows Access will tell you that you are comparing Apples and Red Peppers. Access is NOT Jet no matter how many people make that association. Jet is a database engine and SQL Server is a database engine. SQL Server CANNOT create applications, Access CAN. So, if you wanted to compare Apples to Apples, you would compare Jet to SQL Server and you would find that each has its place in the world and that they are more complimentary than competative. One isn't better than the other. They are DIFFERENT and they serve different needs.

Access includes Jet as a convenience to allow developers to create desktop applications, not to limit them to that paradigm. In fact, at least 90% of the applications that I develop use non-Jet back ends. Access is an excellent RAD tool for use with DB2, Oracle, SQL Server, MySQL, etc and if you make some smart decisions early on in the development, there is nothing to prevent a SQL Server back end being swapped for the original Jet back end when the amount of data or number of concurrent users puts a strain on Jet.

Bottom line - Access has no Web capability (DAP pages are being depricated) but if you need a client/server solution, you won't create it faster with a different tool than Access.

 

[georgedwilkinson]

I gotta go with Pat's bottom line on this. I've tried JDeveloper, Oracle Forms, VB, C++ with various application frameworks, Cobol, JSP, ASP, Pascal, C, ASM, and probably others...there is absolutely nothing faster for database application development than RAD with Access (though JDeveloper is pretty good), especially if you normalize your design before creating forms/reports.

As a person on both sides of the argument, poorly written Access is just as bad and no worse than poorly written anything else...and there is plenty of both. In fact, it's easier to correct Access problems than other compiled solution problems.

I'm not sure if Access being available to non-developers is as big an argument as people would let on. I've seen real live developers with their brand new MIS degrees who don't have a clue about how to create a database application. They can muck things up worse than end users do when developing in Access.

I am a bit disturbed by the trend towards keeping the ownership of these poorly designed solutions with the person who did the bad job developing them. I can't say how many times I've gone into a consulting situation where I said the database needed to be normalized and the original programmer said it's impossible. Don't get me started on that.

 

[Banana]

I gotta go with Pat's bottom line on this. I've tried JDeveloper, Oracle Forms, VB, C++ with various application frameworks, Cobol, JSP, ASP, Pascal, C, ASM, and probably others...there is absolutely nothing faster for database application development than RAD with Access (though JDeveloper is pretty good), especially if you normalize your design before creating forms/reports.

Thanks for confirming what I've suspected. You've obviously tried much more than what I've tried, and I was wondering about Oracle Forms and other where there weren't trials available.

The only closest competitor and that's quite a long shot, mind you, is Kexi. The concept is great, but I don't foresee it being a serious application anytime soon; and likely not five years from now, even though I am totally tickled at the idea of having a open source application where one can reverse engineer any objects to behave a certain way and use Python, which is full-on OOP, but right now it's just that- an great idea. And have had tried other stuff including FileMaker, .NET envrionment, and some more that escapes me, Kexi is much more closer to Access than anything else, which speak to how short those applications falls of the standard set by Access.

Access, OTOH, is available and fairly robust for a client/server application with VBA providing so much capability. Couple with the fact that likelihood that Access is already available as a volume license in any given corporation and therefore the costs is already sunken so it's free in this sense. What I've seen at, I can't justify buying those when Access is already had and can be put to use, and I think that says a lot about Access, IMO.

And what's more cool, is that when you couple it with a different backend, you get much more functionality than if you went full-tilt with C++ front-end or web application for just a bit more of work. At least, that is what I've found since moving to MySQL as the backend.

As I said earlier, if Access team put a bit more effort in ensuring that Access implicitly teaches good habit to the end users, it'll make it much easier when it becomes too big for its job and needs to be handed off to IT department. This is probably why I had mixed feelings about the new multi-valued fields in 2007, wondering if they had learned anything from the fiasco that is lookup fields. (But to be fair, Access team aren't alone in doing something like that- MySQL provides SET and ENUM datatypes which are roughly similar in concepts, and I've chosen not to use them because I can't get around the problem that it is basically denormalizing the data)

 

[georgedwilkinson]

Thanks for confirming what I've suspected. You've obviously tried much more than what I've tried, and I was wondering about Oracle Forms and other where there weren't trials available.

Just about every piece of software Oracle sells is available for a free download to developers until a derivative piece is created from it...then you need to pay the license fees. The problem with all of them is the learning curve, though I can't say that Access is a lot easier to learn than JDeveloper...just slightly easier to use.

As I said earlier, if Access team put a bit more effort in ensuring that Access implicitly teaches good habit to the end users, it'll make it much easier when it becomes too big for its job and needs to be handed off to IT department.

That is assuming the Access developers know how to create a production database system correctly.

I was thinking about the Open Office Access knockoff (Base I believe it is called)...has anyone tried developing with that? I'm curious about its viability (just not curious enough to try it). Read the marketing propaganda but it doesn't really tell you much of anything.

 

[Banana]

Blech. Tried it, and bought the shirt.

I still stand by my original assertment that Kexi is by far the best competitor, which is quite pathetic when you consider that it's not even stable and robust!

Regarding developers knowing the database theory... again, Access was primarily designed for information worker, not for DBAs (though it does a good job for DBAs) and therefore was supposed to not encumber those workers when all they want is a simple contact database. This is partially why I argued that a good Excel-to-Access exporter would do a lot of good in helping those kind of folks transitioning to Access and helping them understanding the theory practically without requiring them to go to a website and asking why they can't do so and so and get hammered by multiple posters to read up on normalization.

 

[asather]

Pat,

I'm honestly honored to have attracted your attention on the matter. I've been using this forum for years (I was formally mtairhead); you and a few other users have pretty much taught me anything I know now about database development, outside of one class in high school and a couple foundation courses at my undergrad.

As for my "monsters" comment, allow me to explain: I have no qualms helping users fix and expand databases they've built, and I'm happy they've taken the initiative to attempt a solution themselves. At times I've been on the wrong side of these projects, however. Poorly designed Access databases have evolved into multiple user (multiple locations) systems that the original designers either no longer understand, or rely too heavily upon IT to maintain. (Or worse, they've left, and you're expected to finish development.)

That's not to say I would discourage the use of Access, but I have a few basic rules before I agree to help "database entrepreneurs."

Andrew's Few Basic Rules Before He Agrees To Help Database Entrepreneurs

1. As soon as it becomes important enough for IT to help you support it, we store the data the way we would store any type of important data: in a safe and redundant manner. No more relying on "FS1 Backup, Week 3" tapes every time the dang thing becomes corrupt. Exporting the data to a robust backend and linking it back to the database file is not out of the question, especially in a multiuser situation.

2. Clear, precise (and written!) plans will be formed before I begin work. I've learned that engaging in water-cooler conversation regarding improvements is a fool's errand. It leads to feature creep and miscommunication. (This is an obvious step for software developers...Not always so obvious for IT staff in small(ish) organizations, in my experience.)

3. If the employees I'm aiding cannot at least fundamentally understand what steps I've taken to expand/correct/entrench/etc the database, they should strongly consider taking classes in MS Access or allow me to instruct them on sight. (I've offered the latter for previous organizations.). If it's become so important that they're designing production systems with it, it's important enough to understand.

The reasoning behind the final step is to create buy-in. The last thing I want is complete possession and responsibility for the system. So long as the proper safeguards are in effect, I believe in empowering intelligent users.

The final step rarely occurs, however there are certain coworkers who exhibit unnatural desires for unruly amounts of reported information, which provide no insight to the operations of an organization. Without enough Access education, they are dependent on you to provide a solution (IE Create the report, right after you add this field and that). There have been moments in my work experience when I am faced with an individual who is so excited about the possibilities of these unwarranted reports that they literally gasp for air when they complete their wish list. ("What-if-we-found-correlation-coefficient-and-compared-last-qtrs-market-segmentation-study-results..." ...Sorry, whaaaa, now?)

The Data-Gasm
I long ago defined a term for such moments as a "data-gasm." This occurs when a user realizes the potential of the data and filters none of their ideas for practicality. Data-gasms can last months. I now follow such datagasms with a tactful soapbox on business statistics (though sometimes a simple "and what would that do for you?" works)...But, if the user has the capability to do it themselves, I don't have to disappoint them and determine that their needs are less important than others'. Since data-gasaming employees often require additional fields, queries, etc., I'm happy to teach them how to do all these things and let them experiment for themselves.

In Summary
I think it would be difficult for me to dislike Access, as it was the first database system I learned and the easiest. I also do agree that it's the fastest. I can sympathize with those who are stuck with the MDB (Monster Database), however. Much of what I practice regarding database development (And any pessimism I might express regarding MS Access) stems directly from the politics of system use and development, not the technology.

 

[Banana]

Just realized something: Pat already referred to FMS Inc... But I think this article goes deeper than Susan's piece, and was published long ago. Worth a read, IMHO.

 

[Len Boorman]

Okay my comments are as a person who has nowhere near the knowledge of the previous posters but does consider themselves as a reasonably good developer in the areas they tackle.

Yes I understand normalisation, Ansi Sparc Architecture, Referential Integrity, Associations and those things that in my view are essential to developing a robust application.

So Access how do i see it.

There are a huge number of situations where smaller companies can use Access to provide efficient, worthwhile applications that are of great assistance to the running of the business. Access is cheap, fast to develop and provides multi user support to a level.

I look on it as a tool and being an Engineer (5 years in training many years ago) I know that you need training on how best to use a tool. Now unfortunately Microsoft have made it too easy for people to believe they understand Access. An Excel book and a Table to the untrained look similar. They are totally different as we know.
This is where teh IT people tend to focus their attention to the untrained person believing they understand Access producing crap applications. Not too long ago I was asked to review an application created by another employee. I took the diplomatic approach pointing out the technical deficiencies and the potential effect. What I should have said was that it was a totally crap application produced by a person who did not know enough to understand how much they did not know.

That is the problem. Ask any experienced Access Developer and I am sure they will admit that they do not know it all. Okay I would bow to the likes of Pat and some others who have such vaste knowledge and experience that maybe they are the exception.

This forum demonstrates this so clearly where you have a variety of posters answering questions. Sometimes you can pick out people who have in depth knowledge in particular areas.

So in conclusion. I have met some really poor IT people, also some very good IT people. To comment constructively on a subject you must know the subject. So often IT people comment on a subject without actually knowing too much.

All tools have their particular niche where they excel, other areas where they area bit of a patch and also totally unsuitable areas. the real expert knows what to use when.

Thats my comment, maybe a bit middle of the road but honest.

len

 

[stopher]

Just realized something: Pat already referred to FMS Inc... But I think this article goes deeper than Susan's piece, and was published long ago. Worth a read, IMHO.

Banana (& Pat), thanks for the link. I think this hits the nail on the head.

When reading Susan’s article, one the one hand I was agreeing with the points, but on the other hand I felt that they were all business issues poorly addressed not issues about specific development tools.

The key point to the Luke Chung article is about business strategy. This means having the vision to decide which tools are right and at which point in time to meet current and future business requirements. Then implementing the strategy so that teams have a clear vision about what is/will be supported and how systems will develop/migrate. This takes away all the in house arguments mentioned in Susan’s article. With good strategic management, out-grown mission critical Access based solutions will have been replaced well before they become an issue.

As Pat says, Access is what it is and it is number one in its genre for good reason. It’s RAD and with that comes compromises (which Susan highlights) but it’s those compromises that allow it to be what it is. If organisation has seen fit for someone to develop something on Access, then the organisation also accepts the latitude for creativity. Knowing what tools are capable of is core to understanding how a business will implement its business information/IT strategy.

Finally, I do agree with Len that businesses make the mistake that Access is as easy to develop as Excel and therefore individuals are allowed to steer their own course without a real understanding of how Access (and databases in general) really work. Maybe Access should come with a government warning

Chris

 

[Pat Hartman]

I like the idea of a government warning - STEP AWAY FROM THE KEYBOARD ....

In my not so humble opinion, the best thing that businesses with users developing Access applications can do is to provide on-site consulting support to help the users find the correct path. The problem is keeping this person from being sucked up into a development project. If they become a doer, they will never get out from under.

PS, Banana, I don't post the specific link for a reason. If I post the link, people will go to the article and read it but they probably won't look for others. Whereas if I post only a link to the website, they can still find the article pretty easily but they'll see the list of others and hopefully take a look at one or two - but I forgive you

 

[Mike375]

Finally, I do agree with Len that businesses make the mistake that Access is as easy to develop as Excel and therefore individuals are allowed to steer their own course without a real understanding of how Access (and databases in general) really work. Maybe Access should come with a government warning

Chris

I don't think it is a case that Access is harder than Excel but more a case that you simply have to do a lot more because you are making a data base. The analogy I give people is that if the road from knowing zero to being a guru is 10 miles long for Word, Excel or Access then with Word if you get a 1/4 of a mile down that road you can probably do most things you will want to do with Word. Excel might need 2 miles and with Access you probably need to get at least 5 or 6 miles down the 10 mile track to be able to make something that is of use.

 

[Banana]

Mike375, somehow I don't think this works. I certainly understand your point that one has to be fairly proficient with Office to make use of Access, but it's not the proficiency (or lack thereof) that's the problem. It's the paradigm. To make a proper database, you have to know the database theory and normalization, whereas with Word or Excel, you could pretty much organize data any way you wanted and it'd work out in the end.

More often than not, they bring "spreadsheet paradigm" over to Access. Even worse, it seems to work at first. If all they needed was a list of names and emails, then one table approach would work wonder for them and they could do search, filter, and whatever. (Yes, they can do all that in Excel, but I think they also can see how they can have more controls). As soon as the pointy-headed boss come along and say, Hey, this is cool. Let's make it a mission critical app! only then the user has realized that he's in deep doo-doo because nothing will work beyond that one simple table approach. It's that delayed kick in the nuts that gets people in a bind, and hence now & then a thread pop up here with title "OMGZ! HELP M3 OR I CAN HAS FIR3D!!1! LOLLERSKATES!"

Pat, you're probably right about not linking a specific article. I would be so ticked if everyone could go to FMS Inc and read everything to be read over there. Would do everyone a bit of good. I suppose I just had this lightbulb when I realized that Susan's article was essentially saying the same thing that Luke already did much earlier and with much more details.

 

[Mike375]

To make a proper database, you have to know the database theory and normalization, whereas with Word or Excel, you could pretty much organize data any way you wanted and it'd work out in the end.

Unless you are keeping a simple list of names and address I think it is not too long before Customer and Orders etc. surfaces

In my experience the vast majority of people who have a go at Access do not last very long. You will see examples of this on the forum. 99% of the members don't get past 10 posts.

With the benefit of hindsight I think from a financial aspect it is much better to get the professional developer on board. I started with Access 95 and came across from the DOS Q&A data base. Looking back at previous copies of my data base it probably took me about 3 years before I could do what I wanted to do and I would have been far better off concentrating on insurance activities

My advice to others with respect to Access is to either get a developer or forget it or prepare for a long journey.

Having said that I think people (and companies) would benefit if they had a basic knowledge so as to have some idea of whether a change to be made is like replacing 50 tiles on the roof of the house as compared to only 3 bricks in the foundations

 

[DCrake]

Simple Software Solutions

I have been sat here for a few days monitoring the comments that my learned friends have said. Anyway for people who read the tag lines that accompany a members post you may see that mine reads "How much will it cost? and how long will it take?". These are the two main questions that I am asked when being apporached by a prospective customer. They comment on this after handing me a spec written on one side of a sheet of A4 paper (if I'm lucky). My image shows a man being run over by a ball of string. Subliminally indicating "well, how long is a piece of string?".

In my early days applications were written in dBase II on a Schneider Euro PC with no hard drives. Things got better with dBase III+, then with the introduction of MS Access 4.3 (Access 2) my whole world changed. I have progressed through every uprade of Access, however I am not confident about 2007 enough to move on. Considering all the issues I read about on this forum.

Access boasted that you could have a table containing up to 1 billion records or 2gig in size. The former was achievable if you had 1 table in an mdb with one field in it that was a bit type field. Yes you can, but what the hell can you do with it? My major gripe about Access is when a version is updated and nice little features that your previously enjoyed are no longer available in the latest version. I quote the ability to have bold headings in message boxes by using the @ symbols.

With regard to the finished article I tend to press the customer into telling me what reports he wants and what does he want to analyse in order to define a table definition. You can spend many a day programming in facilites to capture a multitude of data items only to find out that they are only kept for cosmetic reasons and are never used once entered. I agree a paperless office is in many compaines policies but where do you draw the line.

In conclusion, I like Access but as a rule I tend to use VB where possible as you can control the actions more concisely than Access and of course you don't have to worry about creating and distributing MS Access runtime versions.

CodeMaster:

 

[georgedwilkinson]

Love the 1337, Banana. Very funny!

 

[The_Doc_Man]

I am one of those persons who came from a different direction (or two or six) before getting to Access. In the early 1980s, I learned about relational databases through something called DataTrieve, a DEC product. I tried a few little MS DOS databases. (Yep, I'm that old...) Settled on Paradox.

Then Windows came about and I found that Paradox for Windows 3.1 was the most ungainly, nearly unusable product I have ever had the displeasure to fight. I happened on one of these little Access 2.0 cross-product upgrade offers. (If you run any of these.... {big list of products}, we'll sell you a copy of Access for $99 as an upgrade!) OK, I tried it. In two days, I had converted my Paradox DB that was totally worthless into a fully functioning collection-tracking, report-generating, query-managing database.

When I got involved in this forum some time later, I knew DB theory from college and the DataTrieve work plus some serious reading about theory, but I really learned by doing. My experience could be a "war story" about how a little bitty departmental database suddenly grew into a division-level, mission critical database and had to be "grown" into another platform.

I inherited a database of the computer systems our department managed. It was a mess, but the boss said we needed it and I was the only person left who knew anything about Access. Sad to say, obviously more than the person who wrote the original database. I can't TELL you how badly denormalized that puppy really was. But here's the hint - it used to be a spreadsheet that grew in the telling.

At one point we had 400+ servers on the list. The database had lots of features including dynamic help on-form based on whichever control had focus. That control's name (the associated label control) changed color. If there was an error on the form when you tried to save it, the control background changed color. The things you could do the entry were based on control buttons. If some things were disallowed, the buttons didn't grey out - I made them invisible - and realigned the remaining buttons to show no gaps. The reports were well received. That was when the higher-ups said, "It's time to turn this into a REAL database." (I bit my tongue more than once, so bad that if I were into to such things, I could have installed the piercing jewelry without having a professional puncture job...)

Since nobody ever saw the tables or raw queries, I was able to put at least some auditing under the action forms. That way, we could slap wrists when we needed to, and could tell when a thing went away. I learned a lot from the forum about controls and forms, but now (per my signature), I'm somehow considered an expert on the subject. I must admit that the nomination came at me from out of the blue.

Katrina hit our area hard enough that the database fell into the muck. We have changed a lot and don't use it any more, but I'm just waiting for the day when someone will remember.

The point of this rambling discussion is that the referenced articles make exactly the points that I've seen here. Database growth in requirements, importance, content - like the line from Field of Dreams... If you build it, they will come. (Doc's addition: And if the bleachers don't collapse right away, they might even come back.)

The readers who see this thread should understand that the referenced articles DO describe real-world factors, opinions, and attitudes. The stuff that the authors describe in those articles - it's for real. If you work for a larger organization or if you hope that one day your business will BECOME a larger organization, the references in this thread are GOLDEN.

 

[stopher]

PS, Banana, I don't post the specific link for a reason. If I post the link, people will go to the article and read it but they probably won't look for others. Whereas if I post only a link to the website, they can still find the article pretty easily but they'll see the list of others and hopefully take a look at one or two - but I forgive you

Pat,
That sounds like my University lecturer coming back to haunt me . I was lazy then. But now I'm lazy and busy. Please, please give the more direct link where possible

Great tip about reading the other articles though. Still trawling through them and can't stop reading.
Chris

 

[Pat Hartman]

My very first computer professor made a point that has served me well and as you can see by my style, I took it to heart. The field is growing so fast (and this was in 1968 when you needed at least a million dollars to buy a computer that was less powerful than our desktops today) that there is no way that anyone can possibly know everything they need to know so the only solution is to know how to find the answer. All tests were open book since that is the way the real world is and many exam questions could be answered with a book and page number reference.

I do have one more thing to say on this topic and that is that Microsoft does the business community a disservice by insisting that Access is so easy a Cave Man can do it. Just because you give a knowledge worker a word processor doesn't mean that you would expect him to produce the sequel to War and Peace in their spare time. By the same token, you can't give Access to a knowledge worker and expect him to produce a database application. Because so many people actually can produce anything with Access says everything about its excellent, logical design and intuitive tools.