Articles

Now I Know Why Microsoft Access Sucks

In rant, tech on June 6, 2007 by karan Tagged: , ,

So I spent nearly a whole day chasing a seemingly simple solution to a Microsoft Access problem, and I’ve finally found the officially sanctioned solution. Here it is.

Now, the non-geeks can go read the posts below, or wait for the next one. This post isn’t for you.

Have a quick read, my fellow software developers, especially the part about creating a form to supply parameters to a query. You’ll note the following steps:

  1. Create a new form and add the controls needed for entering the parameters.
  2. Modify your query so that it obtains its parameters from the controls on the form.
  3. Add an OK button to the form that runs the query and then closes the form.
  4. Add a Cancel button that closes the form without running the query.

Yes, you’re modifying the query so that it obtains its parameters from the controls on the form. This is a test to see how much attention you were paying in your software design classes (or indeed if you had any).

If you fail to see the problem, then clearly… you’re an Access developer (dare I say Microsoft Developer?). I now have nothing but disdain for you, and all Access MVPs. I’d assume someone would stand up and point out to Microsoft how bad it really is, but clearly you never realised.

If on the other hand, you’re now looking on in sheer terror, welcome to my frustration! What manner of infernal coupling is this?! Could it be that there’s no method to pass a variable?! Yes, dear friend, Access doesn’t let you pass a parameter to a query. Oh no, you must either bind a database element (the query) to a GUI element (the field on the form), or rely on the user to put in the value through a series of generic input dialog boxes.

Reuse? Bah!

Access is not so much a database system as an evil, evil lie.

</rant>

Advertisements

45 Responses to “Now I Know Why Microsoft Access Sucks”

  1. Cannibalism; it’s whats for dinner. Access is to SQL Server what VBA is to Visual Studio and .NET. I don’t think there is a such thing as a pleasant Access experience unless you’re an absolute beginner to relational databases.

  2. Haha,

    Couldn’t resist!

    I’m no more of a fan of Access for solid DB applications than you are. But… It does have its place.

    I worked for six months in an organisation (which shall remain nameless) where they stored their data (which was relational in nature, and rather complex) in EXCEL!!!

    Then they wondered why the whole thing collapsed in a heap… Now I know access has flaws as an environment – but dear god! It would have been so much better stored in Access than stored the way it was…

    As for the query thing – instead of the built in query generator, I always compiled it from SQL on the fly in the code, and executed that way. The JET 4 engine behind access is actually pretty good for < 500,000 records… So for “small scale” business needs, it does the job…

    I’ve also seen it used for massive telecommunications reconciliations with 50 GB data sources – needless to say that’s the wrong platform… Had that query running for 2 weeks straight before the analyst gave up and restarted the machine LOL!

    Eugene

  3. It’s not so much the database component – that’s relatively straightforward, actually! It’s the presentational component that they’ve laid on top of a reasonably lightweight and functional database that gets on my goat. People (i.e. the Non Technicals) see that part and say “well it looks easy enough right?”, when really, delving into the GUI component is a path through a forest of briars. at night. with a carpet of poison ivy. barefoot. naked. being chased by rabid dogs.

    Excel is a terrible idea to have as a database, but at least the formulas et al can be bent to work. VBA in Access is a horrendous horrendous mess that I will firmly steer clear of in future.

    (and it’s not like it’d get any better doing the GUI component up in VB, given that Access doesn’t let you pass parameters to the query in any normal way shape or form!)

    • karan wrote:

      (and it’s not like it’d get any better doing the GUI component up in VB, given that Access doesn’t let you pass parameters to the query in any normal way shape or form!)

      Access does let you pass parameters to a query.

      Dim db As DAO.Database
      Dim qd As DAO.QueryDef
      Dim rs As DAO.Recordset

      Set db = CurrentDb
      Set qd = db.QueryDefs(“qryCustomersSelect”)
      qd.Parameters(“Year”) = 2011
      qd.Parameters(“Month”) = 4
      Set rs = qd.OpenRecordset

      It’s not as sophisticated or flexible as SQL Server or Oracle, but then again it was never claimed to be. It’s a desktop database system and up until the horrible UI changes in 2007 it was very good for what it was designed for.

  4. Haha,

    yes, i certainly agree about it looking easy, and therefore leading to the proverbial forest of briars… lol!

    I just try to use the engine underneath which is perfectly functional… especially when certain places where i’ve worked don’t let you use anything else :)

  5. The solution proposed above for parameter queries is targeted at non-developers. In Access VBA, you can easily pass parameter values in code to parameter queries. Access is terrific for small and medium-sized projects since it allows for rapid application development. I’ve been using it since 1994 and I’m making much more than most developers. Most developers hate it because they love complexity. That’s why many of them are so broke and unemployed. I use both Access and ASP.NET/SQL Server. You can compare Access to a light truck and ASP.NET/SQL Server to a heavy truck. Why should a business buy a heavy truck when a light truck will do the job? Only buy a heavy truck if you need one.

  6. Why Access 2007 sucks from a user not a developer.
    We updated our entire computer system so we could all run the same software “Office 2007” We paid big money to a programmer to build a database and convert another. Well it does not network worth a darn. If you have more then one person in the system I might as well be using 3×5 index cards. Our Programmer thinks it’s the network. Our network guy thinks its the software. They are both pulling their hair out.
    We have so much money invested in this thing so far were stuck

  7. Access Developer, there’s no need to buy either truck – free and flexible open source options render it entirely unnecessary to do so where you aren’t limited by company policy. The LAMP stack makes it very easy to get up and running with a multi-user database-based webapp, and it’s been proven a hundred times over.

    Harry, I’m 99% certain it’s a limitation of the system – Access simply doesn’t do that kind of stuff as expected.

    • Karan, if a senior executive at your workplace / client’s place wants a certain report generated by lunch time or by the end of the day so that she can use it to press her case at that vital meeting, the Access developer will get the job done faster than the LAMP theorist. And that’s what counts.

      • I think you miss my point above – this post was drawn from a requirement to create an application, and multi-user one at that, not a one-off report. It’s not sustainable development, and the requirement to do it with Access when there are better (and cheaper) technologies available is a mad one.

        Furthermore, if you’re using Access as a primary database for enterprise-level data, you’re just asking for a world of hurt. It’s why Microsoft themselves offer SQL Server. The only reason to use Access should be because of resource limitations, particularly money and expertise.

      • Karan, of course with a multi-user application and enterprise-level data, I’ll use asp.net/sql server instead of Access. But the bulk of the database applications in large organizations are actually small single-user ones. For example, there’s a telco I know with over 400 small single-user Access apps! How did such chaos unfold? It happens when a senior executive demands some complex report by the end of the day or even by lunchtime for that important board meeting. When the small apps grow into unruly teenagers, I have a utility that quickly converts them to asp.net/sql server. Secondly, the cost of an Access license is nothing compared with the usability hell and tech support costs associated with “free” open source.

      • Having worked in an organisation with 80,000 employees globally, and another much smaller scale one, I’d be inclined to disagree. Perhaps in certain contexts single-user app-databases make sense, but in any reasonably large organisations a “single user” database is very shortly unlikely to remain so, especially if it’s useful.

        My argument here is not about particular applications for Access, it’s with the coding style it not only encourages, but requires. It’s not good coding practice, and it’s simply horrifying to those that care about standards. Again, the prompt for this was while developing a “medium term” solution for a particular segment of the 80k+ organisation which would have up to 20 users; I refused to continue working the context of Access. Saner heads prevailed and the app was built on an open framework (MySQL & JSP).

        And finally, I’m afraid you’re just plain wrong the final point. I presume your income is tied closely to the use of Microsoft products, which is why I’ll let you make your case, but your arguments are out of date and pure FUD from Microsoft, particularly around long established open frameworks.

      • Karan, Access allows for rapid application development which make us Access developers very popular with senior executives who need that urgent report. A partner at a major law firm which bills clients $500 or even $1,000 an hour (eek!) doesn’t care how that complex report was generated. The judge needs it by 2pm today! Of course for a core banking platform you need something solid like Oracle.

  8. I agree Access 2007 sucks. However, parameter passing from forms to queries (or any other object) is rather simple. Use global variables to pass parameters. The only weakness of global variables is that if you have an unhandled error then the database forgets the global variable.

    You can readup on global variables here:
    http://www.blueclaw-db.com/access_pass_parameter_global_variable.htm

    • Joe, I’ll have to direct you to a decent and reasonably recent computer science/software engineering course – global variables may work in a small, focused solution, but they do not scale well and are widely discouraged as bad programming practice.

  9. Access is great when used correctly. I have programmed with Access to support business since version 2.0. We are using Access 2003 now so I can not comment on Access 2007. I would not use Access 2007 since the new features are stupid.

    Access is great for programming and interacting with Office applications thrrough automation. As far as queries go, passing parameters through the interface or other methods has not been a problem. If you look at the time and money spent supporting Access versus what it can do, the investment is a good. Shareware and freeware databases lack the same support structure and ease of use, so unless you are an expert at that program, you will spend more time solving problems and less time getting work done to support your business.

    • I make the point elsewhere in the comments that the only reason you’d use Access is because of resource limitations, be it money or expertise – often, a business will already have Access installed as part of the Office suite, and that’ll be the first place to look for a database solution. I’ll freely admit it’s easier to use than free solutions, but for sustainable and evolvable development I don’t think Access is the right solution.

  10. I get frustrated with access all the time. I’ve had to completely scrap access on some queries because the work I do often exceeds the capabilities of Access, but I am forced to use it because it’s the company standard. One typical query executes as an ODBC passthrough on an SQL server and returns around 600,000 records. Typically, access locks the system (2.4 Ghz 64 bit dual core intel) for 4 hours at which time it displays an out of space error and drops the data . I rewrote the query in Perl by using the ODBC interface module and the same query now runs in the background without slowing the system and completes in about an hour.
    What really frustrates me, however, is how each new version seems to be less functional than the last. I currently need to convert a unix epoch date time string to a mmddyy string. In Access 2003 this was a simple task of adding an offset to convert the epoch date and copying the integer into a DateTime field.
    Access 2007, however refuses to do the conversion, and I have yet to find the magic help question that even hints at a way to do this.
    Microsoft is always accusing linux of requiring the uses to scour the internet for help, but Access has that built right in to its help system. type in any seach criteria into the help query box., and you will be presented with millions of links to web articles that tell you what you already know and i fyou are lucky, buried within those results are 2 or 3 articles that actually address your problem.
    I am studying Russian profanity so I can vent my frustration at this crap without getting fired for offending my coworkers.

  11. Access 2003 sucks big time. You cannot get the ##$%$%%$$ switchboard to run, let alone autorunning without coding.
    Doesn’t Microsoft know that any company that can afford to hire programmers will not be using an Access database.

    • @char

      I actually spent $153 on a legit copy of Office 2003 Pro the other day just so that I can cost-effectively get my hands on Access 2003, by far the best database in the planet. Don’t use the switchboard. Customize the menus instead. Post a project for free at Elance.com or Guru.com and you’ll get assistance for a pittance. There are also hundreds of Access 2003 programming jobs, even on Wall Street, at monster.com alone. It’s impossible for someone to produce a solution faster than me from scratch using anything other than Access 2003. You see, the president or VP of a company wants that complex report produced very quickly before some important meeting and really doesn’t care how it’s produced. Can you program a cash flow statement in a few hours using anything other than Access 2003?

  12. AccessDeveloper keeps praising the value of Access as a way for semiskilled computerists to generate those complex reports, known in some circles as ad-hockery.

    ONe problem is that these complex reports are often completely wrong due the the fact that the person creating the report failing to understand the relationships between the tables and how the query builder is actually interpreting the query design form.

    On queries with relatively simple data and less than 5 tables, it’s not too bad, but I’ve seen access queries combining 7 or 8 tables that produced completely innaccurate results because the user did not understand the data.

    The fact that someone with little training can throw together impressive looking reports in a short time doesn’t automatically mean those reports are useful. It’s kind of like putting a brick facade on an outhouse: It looks pretty but it still stinks.

    However, open source databases also have gui query builders and report generators available, but they act as a separate application and are not tied to the database as n access. this makes them much more flexible.

  13. Absolutely agreed Nik – good database design is hard, and without that performance and extensibility suffers. With it, the “average” user will find it difficult to construct a query that does anything useful.

  14. @Karan and Nik

    Do you know why many companies have Access developers making good cash? It’s because some strategies can change on a daily or even hourly basis, and there’s no faster way to whip something up than by using Access. For an app that hardly changes in several years, e.g. the core banking application of some dull commercial bank, then of course you’ll go Oracle.

    Your Access prejudices (Accessism LOL!) are bad for your wallet.

    • My wallet’s just fine thank you very much. I’d be pretty willing to bet a DBA makes more than an Access developer, and as for rapidly shifting strategies it’s easier to build and deploy a SQLite or MySQL database with a php front end onto an intranet than to set up the convoluted sharing controls necessary for multiple users to access a system. A LAMP or WAMP stack is almost trivial to set up these days, and any web dev worth their salt knows a bit of SQL to boot. I’ve seen it in my previous organisation – a major international bank – where an Access database built over 3 years with utterly broken workflow was replaced in 3 weeks with an intranet-accessible site built by a couple of interns. If we’d paid the Access developer more than peanuts we got ripped off.

      • I think popular development platforms like the ones you’re talking about have cut-throat competition and above average tech unemployment. I know developers who are much more skilled than me but are really struggling in this economy. If only they could swallow their pride and look for “unglamorous” tech work, then they wouldn’t be languishing at home watching daytime telly. At the end of the day, a dollar is a dollar so long as it’s legally obtained.

      • I’m sorry to hear that but there’s no lack of employment here in Australia. In any case, that people are employed to deal with a system does not make it a good technology system.

  15. Gosh darn!
    Access is like a woman with premenstrual syndrome. You never know when it’s going to start not doing things the way it should for no apparent reason.

    Now it doesn’t change the filter property of a form. I’ve rebuilt everything from scratch and it still doesn’t work for some stupid reason.

    I’m completely frustrated and I came here just to complain to see if I get some psychological relief (there’s nothing else I can do)

    I wouldn’t advice even my worst enemy to use access.

  16. Karan,
    Something else I find extremely annoying an MS Access is that it lacks any equivalent to the “dbload” and “dbunload” utilities found in informix and other enterprise grade DBMS systems.

    For readers unfamiliar with “dbload” and “dbunload”, dbunload generates plain text file of SQL commands and data that can be used to recreate all the elements in the database including tables structures, forms, reports, macros and data. The resulting text files are sometimes called “flattened” databases. The dbload utility can read in a flattened database and reconstruct it in the database format.

    Some of you that are unfamiliar with dbload and dbunload are probably thinking “So What!”. Thse two utilities are very useful for archiving databases and may also be used to recreate a complete database under a different database engine. I suspect the latter use is why Microsoft has intentionally hobbled this functionality om Access.

    For archival purposes, being able to output all the database to a plain test format has many advantages. First, a flattened database is about 1 eigth the size of an mdb or accdb file and it compresses better, making it possible to store more data in less space. Second, flattened databses use open standards, so a database create in Informix on a Unix system can be easily moved to a standards compliant database format on any platform.

    Of course Access has the documenter tool. But the documenter is broken by design in that it will only work correctly through the GDI print module, which converts the data into a graphic format that can’t be used to reconstruct the database. If you try to export the documenter report to a text file, it is possible to export each object type individually to text files, but when you do this, property names are truncated making them useless for reconstructing the Forms and Reports.

    As for the dbload functionality, Access is unable to run an SQL script. Individual queries must be defined and called from a macro. It is posible to create tables by pasting individual ddq into the SQL view of the query designer, but some versions of Access use non-standard SQL.

  17. In business it’s not about what you like; it’s about what the market likes. A restaurant owner will sell fizzy drinks despite hating them. Access, just like any other product, has it’s fans and critics. I use Access 2003 every day because it’s the fastest (and therefore cheapest) way for me to develop single-user database apps. At the end of the day, a dollar is a dollar so long as it’s clean money.

  18. Access Developer

    Obviously you have never had to use Access 2007 in a distributed environment.

    Here what ahappens.

    The boss calls you up and says “I need this new report ASAP!”

    You have an access database that links to the data via ODBC, so you open it up in Access 2007, and use the report wizard to quickly generate the report. Then you preview the report and find tha the stupd wizard-created form has date fields which are too narrow to display the date, instead of shoeing “05/07/2008” it shows “######”.
    So you switch over to design mode with the intention of modding the report. Should be simple, just scoot the fields over to make room for the date field to be stretched out. This worked in every older version of Access.
    But in Access 2007, you can’t move fields in a wizard generated report. You can resize them, but that is useless because then it overlaps the adjacent field.

    Not all of can be on the Microsoft payroll and pretend to be a fanboy defending a really crappy product.

    In business it about what works best and is cost effective. but while you may believe that throwin together something quickly that fast and cheap is better than something that is robust and accurate, I definitely would NOT want you working for me.

    • I’m totally in love with Access 2003 but have never liked 2007. I’ve even donated an Access 2003 license to my employer because they won’t buy me one. Ha ha.

      I have an Access 2003 program that generates asp.net / sql server apps from specs in Access 2003 tables.

  19. It must be nice to work for an employer that lets you pich your own development tools.

  20. Access Developer is a retard. That should settle the argument. I mean, my God, Access? When I use the bathroom better software comes out.

  21. yes access sucks

    its limited
    its not scalable
    sql in it sucks
    multi user is nightmare

    yet i just accepted a access job and i am already frustrated. I gave them 2 quotes and a recommendation of going wamp mysql route.

    yet they want to stick to acess because “thats what we know”, even though they currently have problems with another access db that they are using.

    i blame people hiring manager who are in charge of IT decisions without knowing 1 anything of IT, 2 dont do research, 3 believe they know more than an expert and 4 refuse to get a second opinion

    anyway

    just ranting as i am already looking for solutions on access problems!!!

    i should have said no to the job :(

    nice blog and greetz

  22. As much as Access 2000 – 2003 sucked, they are all stellar products compared to Access 2007, which is an abortion.

  23. There is nothing wrong with Access, itself, per say. The problem is version 2007 and beyond… The interface SUCKS beyond description. It is pretty much useless. I reverted back to 2003 and wont look forward until Microsoft corrects its mistake of making it look the way it does. Its horrendous. As far as Access itself, it has a place and I have dozens of rock solid systems at various clients, earning me nice money, and they’ve got great systems to use. NONE of them will be upgraded to 2007 or beyond.

  24. I have just experience my share of frustration with both Access itself and with ADO.NET. Access itself if far, far worse. At least VB.NET with ADO.NET allows you to pass parameters to queries and parse the results, with somewhat more difficulty than with PHP/MySQL. Not so, Access.

  25. My situation is comparable to the employee. My teacher needs my report by the end of the week. I am not able to develop my application both rapidly and accurately at the same time with my limited experience. Just to give you an example, I have a query that concatenates the first name and last name of students into a new field, while ordering them by last name followed by first name, yet if I convert this query to a report, the ordering is lost. Access does not let me easily modify the report so that the ordering is restored to exactly the way I want it. Fortunately, I could probably get a decent grade as it is since the teacher’s requirements are not that strict, but if I were in a real job and the boss demanded the data displayed in a particular way, I wouldn’t know how to do it. Access should retain the order when converting one type of object to another and not make it harder than it has to be.

  26. haha, this thread is funny

    I used Access 2003 once, there was a programmer that used it to build a shipping metric tool for this big company, lol

    It was a nightmare, but because this company (politics), we had to use that junk

    This was like 7 years ago. Then I switched to a real enterprise development, they use Oracle 9i at that time, but since switch to 10g and 11g. To make those reports, fancy or not, all you need to know is good SQL skills and some function, knowing how to SubQuery or how to use the WITH Clause helps too.

    Usually one crunches a lot of data to get little data back for that Fancy Report.

    You can dump the raw data into Excel and format it quickly to make it nice – if you repeat that same step many time, you can record some Macro code (for simple stuff) or write some VBA if there will be more dynamics involved.

    I routinely work with up to 6 tables in a JOIN with 40,000,000 plus records and I can execute complicated queries in under 15 minutes with the right Hints.

    The average Access Pro, don’t even understands a CROSS JOIN, so go figure and they don’t understand how to properly join the Tables together, so that the data makes sense for that fancy report.

    I have Access, I like Excel.

  27. I meant to say, I HATE Access, I like Excel.

  28. Ok so I’m coming late to this party, but you guys have no idea what you’re talking about. There are things that Access can do better and faster and more cost efficiently than anything else. The bottom line is that there are a lot of small businesses that need creative solutions but cannot afford what it would cost to have a program developed from scratch. You just have to understand its limitations and then appreciate the fact that unlike other database systems, Access lets you easily step out of the box and combine disparate technologies in order to do what is most important in business: get the job done at a realistic cost.

    1. It can do everything that any other database can do, precisely because you can use it with any other database, or any combination of databases. Access is NOT a database, it’s a RAD tool. JET is the database that Access uses by default, but you don’t have to use it. I use SQL Server and Postgres for storing data and writing stored procedures and views. Then I link those tables and views to Access. And this is something that no other system can do like Access. I can have tables and views from 3, 4, 5, however many different database I want interacting within the same Access application. I can pull data from the SalesLogix SQL Server and an ERP with a proprietary database, some local tables, and whatever else I want.
    2. It’s a great data viewing tool. If you use Access for nothing else, it’s worth its weight in gold for it’s ability to allow you to create very quick queries (say I just want to see all the invoice header records for a cetain date) with drag and drop, and then open up a datasheet where I can do quick on the fly sorting and filtering. It’s certainly better than running a select statement in SQL Server and then having to rewrite the SQL to sort what I want and what not
    3. It’s super easy to create multi-user applications that have ALL the power of bigger database and more by leveraging the power of those systems. I store all the data in an SQL server, link the tables in Access, and then I can either have multiple (up to 10 reasonably) in the same Access application, allowing Access to handle most of the record locking and such, OR I can put a copy of that app on everyone’s local machine, and let SQL Server handle all the record locking, and by doing things I can have HUNDREDS of people using an Access application with no concern.
    4. It allows for EXTREMELY fast interface design. This is especially useful if you need to create a basic form that allows an employee to update certain records without having to export them to Excel and then reimport them into your database
    5. BOUND FORMS are GODS GIFT to RAD. Have you ever tried to develop a quick .NET application that uses data from Postgres? Ha! You’re going to have to create all the insert and update statements.. just to edit or add a simple record from a form. FUGHEDABOUTIT.

    I can pull data from 5 different databases, link it all up, and show it on quickly designed forms so that people can see what’s on order, what’s currently in the shipping system, add notes to records from other databases, stuff you just can’t do with anything else..

    The problem is you’re trying to compare Access to something like SQL Server or Oracle, and its genius is not even in that realm. It’s a TOOL, that’s all!

  29. The point you’re missing Matt is that from a programmer’s point of view, Access is a total pain. From a maintenance point of view, it’s a total pain. From a pure database point of view, it’s a total pain. Access is most definitely not a RAD tool – RAD tools enable you to turn a rapid prototype into a ongoing application easily, which Access will not do. It’s a base level tool to manage simple databases, and it is roundly abused for purposes for which it was not meant.

  30. Strictly from a programmer’s point of view, that may be the case. However I think that one challenge that programmer’s have is the intersection between the intellectual sphere of programming, and the spheres which create the demand for it. If developed right, applications can be created on it that can be maintained by a very large population of people, that are ongoing and stable. This is from the perspective from someone who is in small to medium business, where I have applications that handle 40 or 50 users, that have been in use for 4 or 5 years.

    Many businesses are presented with a choice: Spend a ton of money on a compiled application that will also be costly to continuously customize, employ a theoretically halfass, but usable access applications at a low price, or do neither and leave a process manual. For many many businesses, the only choice is really between the last two. It may not be best practices, but there is nothing wrong with using it for ongoing applications.

    Intellectual purity is not all its cracked up to be lol

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: