Access Is Underrated: Your Hatred Of Microsoft Access Is Largely Unjustified

At one time, Microsoft Access was the most popular database platform. While not as popular as Excel, Access still dominates the Windows desktop database market and as part of the Microsoft Office family, it’s still in many organizations.

It remains in use by my many people but is not as popular for a variety of reasons. However, it can still be very helpful.

Strengths:

Non-developers have the ability to create database solutions without resorting to professional developers. This offers:

  • Empowerment: Ability to create what you really want without going through someone else (people don’t use other people to write documents or create spreadsheets any more)
  • People are creating huge unwieldy Excel spreadsheets that really should be databases. Access offers the next level up that end-users and power-users can leverage (learning .NET and SQL Server is too far a chasm).

Connects to many data sources: Ability to get to data in other platforms like SQL Server, SharePoint, ODBC and other Access databases

Multiuser support: automatically lets multiple people edit the same data without collisions

Scalability: Access databases can contain up to 2 GB of data which is much more than people can type. Can also connect to SQL Server databases for unlimited database sizes.

Potential for Professional Solutions: Someone skilled with Access can take an existing solution and transform it to a very professional one

Low cost: No need to get extra budget to do this.

Many organizations don’t like Access applications because people create many of them and some get dumped on IT departments who react with “Who created this crap?”, “We could have done this better on a more ‘professional platform’ if someone come to us earlier”, and “That’s it, no more Access databases!”

What we’ve found over the years is that this approach is wrong and reflects a lack of understanding of the overall database strategy of an organization:

  • Most Access applications (over 90%) are created and die in Access without ever needing help from centralized IT. That means the IT department never had to be involved in all these small projects.
  • An IT department will require $50K-$100K to even consider an application development project. That’s fine but has some implications:
    • Should people not create solutions worth less than that?
    • If so, does that mean those business opportunities are given to competitors?
    • Sometimes a small opportunity turns into a big one because one tried. That $25K profit opportunity may turn into millions. Baseball analogy: Without a cheap way to profitably get an “at bat”, one would never know. Making lots of small singles is a legitimate strategy because it generates a run and every now and then, it can turn into a home run.
  • We’ve seen organizations ban Access, then people went out and bought FileMaker. Not because FileMaker was better but because the problem didn’t go away (IT didn’t help create any of the solutions they needed, they just took away a tool).
  • Bad applications are created on all platforms whether it’s Excel, Access, SQL Server, .NET, Java, Oracle, SAP, etc. Bad applications are hardly tied to technology.

Solution:

Database/application evolution exists. It is subject to the random forces of Natural Selection. Solutions live and die based partly on their qualities but often based on externalities like the changes in the economy, government regulations, new customers, new products/services, competition, etc. To predict which 5% of this year’s new Access databases will need IT support 3 years from now is very unpredictable

Organizations should recognize end users and line of business managers can create a lot of solutions on their own and are best equipped to do so (like giving bullets to infantry).

These solutions should be considered tactical (special forces) not strategic (nuclear weapons) and should not be held to the same standards. Get things done quickly and moving on is the key to being nimble.

Given the inability of most IT shops to handle the workload already on their plate, organizations should be looking at ways to leverage the knowledge of information workers by supporting Access in tiered levels:

  • Bronze: Make it easy to install and deploy Access databases while ensuring the system administrative functions (backups, etc.) are properly managed which is what IT departments are great at.
  • Silver: Establish a training program and help desk to assist end users get their Access work done
  • Gold: Offer programming assistance to enhance an existing Access application and give it back to the author
  • Platinum: Take over an existing solution and offer full services to enhance or migrate it to another platform.

Considered properly, the Access databases created by workgroups should be considered a revenue model for the centralized IT staff. What they need is an understanding of the evolutionary forces in power, and adapt rather than resist change:

  • People will constantly need new databases that should not require IT involvement.
  • People will continue to create crappy solutions that need professional assistance.

Getting it into the budget and planning is what’s critical. Anticipate a small percentage of Access database will need professional help each year and provide it. The military provides “cover” when requested. IT departments should do the same thing. What one never does is blame the infantry for getting into the mess. They were just doing their job and following orders.

Luke Chung is president of FMS Inc. He’s written a paper on this topic, which you might want to check out: DATABASE EVOLUTION: MICROSOFT ACCESS WITHIN AN ORGANIZATION\’S DATABASE STRATEGY. FMS also offers commercial products for Microsoft Access professionals and system administrators http://www.fmsinc.com/MicrosoftAccess/

Comments (44)

  1. Gewern

    I just got MOS cert in Access 2007. It was the 1st ever for the testing site. I was surprised just how rare this cert must be!

  2. stan3

    But how about vendor lock in? MS Access has even no documented basics of the file format, unlike of standard-based formats like ODF or even Excel 2007+. I am assuming it’s implementation-defined. Don’t you think standard-based client-server solutions address modern needs of the networked era…?

  3. The scenario painted here is fairly accurate, but MS is partly to blame, by complicating the integration of Access Web Services. Why implement on Sharepoint and Office 365, when IIS is available?

    A good example of where IT depts. screw-up: a sophisticated Access client-server Events Management database which cost peanuts to build was rejected in favour of a ‘serious’ web-based solution which after spending $50K still doesn’t work.

  4. Very nice article. I’ve been supporting this point of view for some time within my organization, even to the point of requesting to my managers that a separate Access “fire-fighting” team be formed in order to meet the rapidly changing requirements generated by all of our functional groups (inventory, quality, maintenance crew, etc.). I’ve been supporting a rather large database that I wrote for about three years now for my engineering group and can attest to the power of fighting fires quickly and effectively. There is just now way our IT staff could dedicate the sufficient manpower needed to support our needs.

    The big problem is with the lack of understanding among the higher-ups as to how adaptable access can be. Im 28 yrs old and have been developing in Access for about 3-4 years. It has been my introduction to most things programming related and I enjoy it very much. My only hope is that it continues to be supported by Microsoft and continues to push the boundaries of client based database design.

  5. Richard Love

    Luke’s use of “Hatred” is sadly merited. Simply put, ignorance often manifests as an extreme view. I’ve not met a knowledgeable IT professional however who upon understanding the potential (and limitations) of Access has not integrated it into an effective information management strategy. Many IT folks however are the equivalent of plumbers and electricians, not architects – hence the gap in knowledge and perspective. Those who understand the game in terms of solving a business problem/increasing productivity, not in terms of pipes and wiring, see Access as an extraordinarily valuable tool.

  6. I’m glad so many of you found my article worthwhile. I think we’ve proven over the years that one can create great Microsoft Access applications with the proper training, processes, tools, and system administration.

    There are definitely some organizations that get it, and use it very effectively internally and against their competition.

    Here’s my recent blog on a related topic: Microsoft Access Database Scalability: How many users can it support?

    http://www.fmsinc.com/blog/post/Microsoft-Access-Database-Scalability-How-many-users-can-it-support.aspx

    Hope you like it.

  7. nonlinearly

    Yes you are write… I have an example that ridicules mySql odbc drivers:
    I have an access front-end application with linked tables to a mySql database (back-end). The link is through mySql odbc drivers. The simultenoious users are 5. When the data in the master table became about 15.000 then the odbc drivers had very poor performance. I would said unacceptable. I changed the back-end database to Access and evrething was OK.

  8. admin

    Wow. And you always hear people praising MySQL for its speed and performance. That’s interesting to hear.

  9. Alex

    Hi there! I love access. I believe it is absolutely efficient. Now it is 12 years since my team started working on X-Pro, an ERP-system for moldmakers, plastic injectioners, and similar companies up to about 200 employees. We covered every organizational need and our users are happy, from the office worker up to the managers. They enjoy super fast, partially very complex queries, which provide all answers about the business. We also have a satellite program written in VB.NET – both front ends use the same huge MS SQL db. When I compare VB.NET and Access, then I am confronted with development time 5-10 times higher in VB.NET than in Access. That really makes it worth to stick to it – not nice graphics create the efficiency, but the fastest way to a solution for our (end) users. How nice to get happy users for a fifth of the regular expenses of such systems…Of course, there are some smaller disadvantages – but overall there is really no reason to underrate Access

  10. We went through this exact scenario with the admissions office of a mid-sized university. We presented our proposal for developing a complete student recruitment, activity scheduling, application processing system in Access. When they went to IT for approval, their response was “we could do this for you” and generally turned their collective nose up at Access.
    Nevertheless, our system was developed an installed and has been in use for 10 years!

  11. Luke,

    We have an incredible amount of legacy code using DAO and Jet databases. We are looking at porting code to use ACE and it is straight forward to do so. But our decision is also based on what ultimately the maximum capabilities of 64 bit ACE database is. The 32 bit database limits are well published (2GB size, 32,768 objects, etc.). Can you direct me to where the 64 bit maximumns are?

    Regards,
    Rudy Lambert

  12. Debra Tope

    Great article. For a number of years, we have successfully deployed MS Access as a front end for SQL Server. While our enterprise systems are web based, the MS Access front ends are ideal for the needs of individual departments. As a result, we have full data integration at reduced expense.

    The Access Front Ends are definitely ideal for the small scale ‘tactical’ applications that change rapidly in response to new needs or business strategies. It’s also easier to migrate them to a web based environment when the occasion demands, as users have already gone through many iterations of working out the details of what they really want.

  13. Dalkeith

    Good article Luke – I linked to it from the Access Web log and guess what? There was a Troll at the bottom someone just absolutely demonstrating your point.

    I’m an accountant who has learn’t database design through Access 2003. I really would be lost without some kind of complete Database IDE now…

    It excells at the really complicated jobs for which you are unlikely ever to have more than 5 or 6 users that change quickly but nonethless have to constantly be repeated and are highly valuable.

  14. akyatbahay

    I am spoiled by the flexibility of .net and visual studio ide.
    From my perspective, access is good for newbies and light weight requirements.

    But it is very limiting when it comes to more advanced requirements: web services, dynamic html rendering, etc.

    If i was to develop a rich client based front end, i would go towards windows forms (c# or vb.net)

    I think access gives this false expectations that it will provide all solutions, but it does not.

    In the end:
    I rather develop on web based front end than use access based front end.

  15. Rudy:

    Regarding the features and limitations of Access 64-bit, I would say that if you’re considering databases larger than 2 GB, they shouldn’t be in a Jet database. Use SQL Server to host the data. Access can still be the front-end.

    The performance difference between the 32 and 64-bit versions are not that significant. We ran some tests with our Total Access Statistics product, http://www.fmsinc.com/MicrosoftAccess/StatisticalAnalysis.html, and found about a 30% performance improvement with 64-bit with some very intensive numerical calculations. Regular database work which is usually constrained by hard disk performance, will not have much impact going to 64-bit. At least not now.

    Hope this helps.

  16. akyatbahay:

    The .NET developer is not who Microsoft Access is targeting.

    There are very few developers compared to information workers, who need to create solutions without the cost or inflexibilty developers offer. Just like people no longer use secretaries to type their letters, lots of people can get their data analysis and reporting done without having to rely on a programmer.

    Sure, they aren’t creating websites, web services, or dynamic html rendering. But they didn’t want to do that and will probably never do that.

    Comparing .NET to Access is not appropriate in this case. The question is: Given the problem that needs to be solved, is Access the appropriate tool? In many cases, it’s the right answer.

  17. patrick

    Thank you for the article. I’ve been defending Access for years. We have people who want “professional” tools, but throw out an Access database that’s working great and under our control and getting some onerous tool that suits no one. Access has its place and fast development for teams is what I love about it.

  18. Great article Luke. I’ve been making my living from Access DB’s since version 1.0 and 1.1 , and agree with almost every word you have written.

    Access might not be perfect, but no product is. It does allow even relatively casual developers, or people who understand their own data needs to put together highly functional and commercially successful applications for workgroup sized applications.

    It is a shame that it does suffer from this exagerated predjudice from certain of our own colleagues, but hopefully your article will help dispel some of the negative vibes we all sometimes encounter.

    Keep up the excellent work

  19. Margaret Bartley

    One thing you didn’t mention is that many IT people are not particularly skilled in Access deveopment, and do not understand that it is full programming environment.

    Like many MS products, there are levels of usability, and it is possible to become productive without a lot of knowledge. But there is a huge engine in the background that can do many wonderful things, but many people in IT do not know it.

    I worked for a small platform group in a local government once, and the manager said to me, “Too bad Access doesn’t have error trapping”.

    He was the one assigning which projects are done with which tools, and he did not know what Acces was capable of. This is not uncommon, since it is not taught in most universities, and most college kids stick to what they are familiar with.

    Also, I don’t see MS evangelizing what Access can do.

  20. Victoria Suominen

    I was THE FIRST Visual Basic Programmer in Michigan. Period. There were many of my clients who had been castigated by the Corporate IT Dept – usually IBM based or EDS – and it was impossible for these clients to get their work done efficiently. Imagine an engineering department unable to enter data and make important calculations because updating Excel datasheets was just too time-consuming (not to mention Lotus 1-2-3 back in the day…)

    Access was a pretty good product even back then. Prior to Access, the Apple IIc was a popular business computer. You could make HUGE LANS, which EDS called “Vikki’s Little Toys”, but those “toys” saved corporations tens of millions of dollars and those “toys” worked! Back then, the LANs had various servers – CORVUS was popular . The programming was done in Pascal and you have to write some low-level code to make sure the Pascal programs called the server file semaphores, otherwise records would only lock at the application level and that meant total LAN freeze-up if 2 users tried to write at the same time.

    Pascal is very much like Visual Basic in that it is a language which lends itself to structured programming and object-oriented design. Along came Access and slowly disappearing were dBase and its famous clone dBMan.

    One of my first major ACESS/VB projects came when the State of Michigan decided to shut-down their legacy system and fire up their new 100 million dollar system. I think it was called MEPS. Well, the new system did NOT work and the IT Dept later estimated it would take 50 million dollars to get it working. The problem was that the Dept of Mgmt & Budget for the State of Michigan could not write any purchase orders because it was part of the new system. So, I set up an Access database and cranked out some VB code, which loaded all the Vendor information from the mainframe. I set up some data entry forms, print forms, and VIOLA!!! Out came the first purchase orders. The data from the purchase orders were stored in an Access table, which the IT Dept could read *someday* when they got their 100 million dollar mistake up and running.

    Thanks to Access and VB, the Dept of Mgmt & Budget could function in the midst of a crisis.

    It took 1 day to set up the Access Database, write the code to download the Vendor file, 1 day to create the code to handle forms and purchase order data handling, and 1 day to generate a respectable printed purchase order. So, in just THREE DAYS, a system was designed, implemented, and tested.

    The best part is this: The final system WORKED!!! Total cost – less than FIVE HUNDRED DOLLARS. (consultants back in the early 90s were paid by the hours and I think I charged the state about $20/hour ;)

  21. john frederick

    FLYING UNDER THE RADAR WITH MS ACCESS

    Why is it that MS Access continually flies under the radar? So many BI products, report writers and database tools bombard the airspace with reputed heavy guns. Is there another with the full functionality that is inherent in MS Access?

    The functionality in MS Access is as follows:
    1. Application development
    2. Query data (query by form)
    3. Ad Hoc reporting
    4. Custom reporting
    5. Pivot tables
    6. Pivot charts
    7. Reports and sub reports
    8. Forms and sub forms
    9. Data cleansing

    In order to offer this full functionality, two items are the key. First of all, MS Access has the ability to create temp tables, and secondly, macros for the ease of execution. Most report writing authors (developers) back off MS Access when they realize the power to develop has a learning curve (tables, queries, forms, reports and macros)……BUT, one must invest time in order to save time. As compared to most other products for report writing, these deal solely with a band type of report writer. One must realize that within these other products, this band report writer is the end result, visible front-end and the back-end is laden with SQL statements and stored procedures that build the data for the report writer. How many times have you tried to join tables and found that it could not be performed in one query or the query became so complex that it would never be changed again?

    Using Access you can build temporary tables with multiple queries joining the temporary data to your schema and then write a macro for the user to click to execute. All of this without writing any VB code results in the execution of a complex report with a single mouse click.

    Take a serious look at those spreadsheets with Vlookups, subtotals and conditional formatting or take a look at that complex VB code or stored procedures that build other reports and then have a real look at a tool that does it all with power and ease. To be a programmer or not to be a programmer, that is the question.

  22. I have developed an Access database that is now running from MS Access 2010 32 and 64 bit. I mainly use SQL backend but occasionally use Access backend for smaller clients. It has taken me 14 years now going on 15 years to develop a very comprehensive Operational Management Application. If I did not do it in MS Access I would most properly only be halfway. I have many clients on our application and most are running very smoothly. I only have the odd client that is struggling with their speed over a constrained network. We have developed several web-enabled pages that link to Operation Smart (the name of my application). What I have got right regardless of where we move to in the future, is an internationally competitive blueprint that will be used as our specification for future development possibly for the next 30 years. Without Access we would still be designing many of the modules with no end in sight. Yes, it does have constraints but we have overcome most by using our imaginations and not being scared to experiment. I have recently also been working in Oracle and realised that for our deliverable it’s not necessary.

  23. Cc

    This is a way past due article even as old as it is. So many things to say here…I will start with
    … my organization has been using access since 2.0 and most would be very surprised at the size of the organization I work for as an IT admin and app developer. We have used access for small departmental databases such as po systems and freight tracking all the way up to a a full as400 sync system FE/BE system using MySQL as the back end. Access has worked flawlessly and has been a very simple and inexpensive solution. Case in point, we replaced an $800,000 system from SAP with a access and MySQL system for less than $50,000. No joke. One of the comments in here mentioned something like this and complained about MySQL being too slow. It’s slow because someone is using native access queries instead of pass throughs and stored procedures. We have 240 simultaneous users in a complete field service system for keeping 185 field service techs up to speed and they all swear it is an Amazing system. Using MySQL and msaccess.

    This all just proves the original point made by the author, access (and any other database) gets a bad rap from poor developers and ignorant IT people.

    Again, Luke this has been a great article. More please. Thanks tons. Cc

  24. Jim Flood

    I’m in the consturction field, and retired Navy Builder Seabee, I first started using Access in my last duty station in 1999, and been making my own databases since, if your using Excel, I say, I can do it better in Access! I’m an Inspector now with a large company, and there main business is IT, but with an Engineering department at some locations, I don’t even ask the IT for help, it’s like you mentioned, they have other things to worrry about. My database consolidates everything that I do and makes life easy.

  25. alan

    I can only attest to the fact that yes, I’m in I.T. and I have “pieces of access crap” dropped in my lap that have been left by prior employees that have little to no technical background and am expected to “fix” the problems as they arise. MS Access should never have been invented.

  26. Paul

    As a developer of over 20 years professional expereince.Having extensively used Access, SQL server, Oracle, SYbase, MySQL I still have a ‘soft spot’ for Access.

    The quality of Access based solutions varies greatly, i’ve inherited some awful code over the years then on the other hand, some very well written 900Meg, multi user solutions(20 concurrent) that would make even the most active Access cynic think twice!

    As with all things IT development, spend time planning, establish scope and test.

    For the record…. I think Access is a great ‘general purpose’ desktop database, ideal for quick prototyping and proof of concept, yes it has many faults, all software does.

    Dont pre-judge, be objective and pick the best tool for the task at hand. Access in the hands of a PROFESSIONAL developer is a very powerful tool indeed.
    I am also the first to accept Access is unsuitable for some tasks.

    In conclusion, Think for yourself, be objective and be the best in whatever your chosen language is (vba/c++/c#)

  27. Alan: Thanks for the feedback but that’s looking at it backward. The people who are dropping those databases on your lap did the best they could to get their job done. They probably built many databases, most which are not on your lap.

    There are a few ways to address the problem you’re experiencing:

    1. Train the users so they can do more on their own and do it better.

    2. Hire or train your people to support and enhance Access applications.

    3. Pretend Access doesn’t exist, and start from scratch. That’s the same as banning Access. You and your IT department could then be happy being there at the beginning to create your solution the “right way”.

    The problem with the last approach is you may be surprised how many solutions your end users need and the budget you’ll need to support all of them. It’ll be difficult justifying the expense for the “right” solution when the problem being solved is worth much less. Then what do you do?

    Database evolution. Can’t stop it…

  28. Len Robichaud

    I have been developing in MS Access since 1996 and have long battled the IT management opinion that MS Access is strictly for amateurs.

    As an amateur musician, I recall an old saying that, “… the guitar is an easy instrument to learn how to play, badly.” It is the same with MS Access… MS Access is a database that is an easy tool to learn how to play, badly. However, in the right hands you can produce solid, bulletproof, multiuser applications.

    I’d like to see the MS Access haters try to tell Jeff Beck, B. B. King, Eric Clapton, and the like that their choice of instruments is strictly for amateurs.

  29. Zafar Nadeem

    Whilst the author makes some valid points, overall however, I think he’s painting a far too simplified and somewhat skewed picture.

    For instance, there is nothing about the negatives. Without weighing both, how can you reach a balanced conclusion?

    The negatives are:

    As for some of the general points:

    1. ◦Empowerment: Ability to create what you really want without going through someone else

    This is only true for a small percentage of users, typically what we would describe as power users. In a typical organisation that would be around 5% of the user base.

    2. ◦People are creating huge unwieldy Excel spreadsheets that really should be databases

    True, but the very same ‘positives’ could be made for Excel spreadsheets in the first place and perhaps what could also be said is ‘there are many unwieldy Access databases that should really have their data stored in a suitable and centrally controlled database.’

    3. Scalability: Access databases can contain up to 2 GB of data

    I’m sorry, but 2Gb is peanuts in todays world. You can hardly describe 2Gb as being ‘scalable’ – unless your organisation is one around stamp collecting…

    4. What we’ve found over the years is that this approach is wrong and reflects a lack of understanding of the overall database strategy of an organization:

    The problems facing organisation today are around Information management, and in that context, having a certain number of users creating Islands of Information across the organisation is actually a risk and hinderance to managing an organisations data and information.

    Lets imagine for a second that a CEO suddenly asks for information about a number of metrics, which are scattered/stored across several dozen Access databases. Whose going to be the one to manage and explain to the CEO that they need a month to get that information out?

    Or the usual argument that the power user in a department who has perhaps created dozens of Access databases is suddenly hit by the metaphorical bus?

    There are far too many risks which need to be recognised that unfortunately are not even touched upon in this article.

    In all fairness, the comments about the usual responses from IT are fairly true, but the answer is some form of control which allows for the creation of access databases where perhaps the data is stored in a centralised databases, is backed up and recorded in some information asset register.

    Ultimately, organisations need to make a call based on their size, resources and other business requirements but they should always make decisions which generally avoid extreme positions but more importantly with all the facts (the positives AND negatives)

  30. c.a.

    Yes, Access is a wonderful database application programming tool when used properly. It allowed me to build a comprehensive medical billing and practice management software with EDI generation/translation/import capabilities. It integrates with Sage accounting software and two different web-based medical records systems. It has had automatic updates, systematic error handling, screen resizing for ten years! My six mid-size non-profit clients are all very different and demanding, but they all run the same code base, with a separate mdb that lets them pull custom versions of most reports; some also have entirely unique screens, reports and menus. My largest client has over 800 employees and bills half a million individual services ($24 million revenue) to Medicaid, Medicare, self-paying individuals and private insurance. My oldest, a 250-staff organization, has run my system continuously since 2001 and never experienced an outage of more than a couple hours every couple of years. Of course the back end is SQL Server (data files in the 2-4GB range).

    Go ahead, tell me what software company can do this with any other tool and maintain 25,000 lines of code and support 200+ daily users, with only ONE person doing all the programming as well as taking all the end user phone calls for 11 years. And I’m only working part time on it!

    Meanwhile, I was also doing development in ASP, ASP.NET, and VB.NET. They are incredibly bad for rapid application development, hard to use frameworks with no long-term longevity. Microsoft’s failure to support Access as a serious development platform since Access 2007 is heart-breaking to me (poor documentation, UI control, backward compatibility, lack of progress in user-generated reporting options, push to the unaffordable SharePoint platform). Please, Microsoft, get the .NET and the Visual Studio teams to sit down and study Access. Isn’t it time they catch up to what we could already do in 1997? I’d love to have some of .NET’s advanced features and third-party controls added to my everyday toolkit, but until the basics are covered, Access is still the best tool for my clients’ internal line-of-business apps, given their small IT budgets.

  31. Eldho George

    hi, the article is soo good for those who are unaware of oracle or who needs to satisfy their mind that ” no buddy u still have an chance to fire up ” …. but befor setting your mind so i wish you would go into the following link

    http://database-management-systems.findthebest.com/compare/24-36/Access-vs-Oracle

  32. eldho george

    hi Luke Chung

    i left a comment for you… what happen .. find it hard to digest

  33. Nelson Gonzalez

    This is to answer Eldho George above. It is clear you don’t know what Access is about. You can’t compare a full enterprise business suite in Oracle to a desktop application in Access.

    The article is completely biased regardless of its title. Oracle doesn’t cost $180!!!!! Any mid-sized to big company would spend several hundred thousand dollars implementing Oracle for their enterprise systems. I work for one such company with Oracle 11.5 installed. Access applications solve interdepartmental problems quick and easy with minimum investment compared to Oracle, etc.

    Furthermore, if you use a SQL backend to an Access frontend you can scale your database to hundred of users and you don’t need to use the 2GB jet databases.

    I hope this isn’t all you’ve got…

  34. I’ve been developing Access databases since the beta version of version 1, so that’s just about 20 years. I’m surprised that nobody here has mentioned one of the great strengths of Access – the ability to create bespoke applications using other Office apps like Word and Excel. Through VBA, you can do some amazing things by using Office apps to perform specific tasks. For example, I have been writing an Access system to manage a training department. Apart from the obvious tasks such as tracking training course attendance, the system sends out invitations to the courses via Outlook, sends notifications of new courses to trainers and automatically adds them to trainers’ Outlook calendars and a shared calendar, creates course certificates for each attendee in Powerpoint which are published in PDF format and mailed out to course attendees automatically, outputs reports to Excel and automatically creates pivot tables and pivot charts in a spreadsheet which are then automatically mailed via Outlook to interested parties, plus a whole slew of reports and graphs. This degree of automation and integration is not available in *any other* software suite. Use Access as your data repository, integrate it via code with the other Office apps and you have software systems you *cannot* build any other way.

    I think MS have made a huge, huge error with the new Office 15 in pushing everybody to cloud services and Sharepoint, and I’m extremely disappointed that they do not understand the strengths of their own product enough to develop Access more into what the vast majority of its users want it to be – an application development platform, but one whose data can be distributed in organisations and over an intranet WITHOUT having to pay for Sharepoint or Office 365. Will they never listen?

  35. Nelson Gonzalez

    I’ve got to tell you Andy that I’m baffled at what I’m seeing at the Access Dev forum right now for the upcoming version 15. I created a post asking some questions but haven’t heard from them yet.

    You are right, through Automation, you can build pretty things with the Office ecosystem that no other tool can. But I’m not sure we are now going into the right direction unless the Access dev team decides to revamp what they have done so far for this version. I understand the need to move to cloud and off-premise services but there are a few constraints that make it very difficult:

    1. Many companies don’t use SharePoint so if that’s the only way that Access web apps must be created then it’s pretty much a huge hurdle for Access developers working within enterprise environments.
    2. Access web apps seem to work with macros rather than VBA. I believe this was necessary due to the app running on a web browser but it limits what you can do with the database; meaning that it will be hard to replicate a desktop app on the web using Access
    3. The SQL Azure/Access strategy seems to have fallen from grace – that was a winner in my opinion, specially since no SharePoint is required.

    The Access dev team has had about 3 years of feedback from users on where to go. I can only think they had to surrender to the Metro guys at Microsoft since they know pretty well what the users are doing today with Access.

    The bottom line is that if you want to build competitive Access desktop apps you need to be well versed in a number of technologies, such as: DAO, ADO, ODBC, SQL Server, VBA, and Unbound forms. Right now and as far as I can see in the dev forums, no extra support has been added to any of the above for version 15. More support for SQL back-end database structures could have been a good thing to add.

    Let’s see what happens in the next few weeks with this version. It could very well be one of those versions that everyone skips until 16. Who knows?

  36. Gary

    It’s easy to see why MS is basically killing of MS Access. MS Access is pure brilliance, and could quite easily kill off many other profit streams MS counts on to satisfy its shareholders. Just take a look at the revenues derived from MS Access licenses when compared to SQL Server, Sharepoint, etc.

    Seriously, if you coupled the programming ease-of-use found in MS Access (there is no other system capable of stringing together complex queries like MS Access) with a truly scalable back-end, then you’d have a do-it-all product that would destroy the need for other much more costly “solutions.”

    I’ve been developing MS Access applications for years. I can’t count how many times our IT department has provided an hours and dedicated headcount estimate for a project that needed five times the hours and people for something I could do in one week. They need project managers, IT department head sponsors, BAs, developers, time to test, etc. I just need me, and an Access database. I can get a project done before IT will get out of of steering.

  37. Joseph

    I don’t agree that it’s underrated at all. This article doesn’t address its weaknesses. It’s a collection of sub-par components: a weak, poorly-designed, limited language that is deprecated (Visual Basic). MS declared a few years ago that there will be no more improvements to VBA and won’t license it to anyone else as a scripting language anymore. It’s irresponsible to recommend a dead language. It’s also an IDE, a so-so report generator and a poor, severely crippled database engine (Jet/Ace and a ridiculous 2GB file size limit).

    There are lots of other faults with it, some of which were pointed out by a commenter above. The format is proprietary, and combined with the proprietary language results in vendor lock-in. THIS is one of the biggest objections from IT departments to the use of Access – what goes into Access often stays in Access. Another objection is that it creates a “data silo” – a separate database, accessible to only a few, whose data may bear little relation to similar or the same data contained somewhere else. This is also true of Excel spreadsheets, which many businesses often use to maintain lots of small collections of information rather than having everything in a single data warehouse. This encourages poor managers to go “data shopping”, checking out the various data silos until they find the values closest to what they want. I’ve been in organizations where this happens, and “That’s not what our data says” becomes a popular refrain at meetings to deflect any criticism. I’ve seen data silos ENCOURAGED for just this reason, and a billion dollar company with data scattered on Oracle, DB2 and a beta data warehouse system that was being operated in parallel. And yes, I saw that you could go into the systems and find three different answers to the same question! Excel and Access makes that even worse.

    Access is also single-platform in a new era where that just won’t fly. People want to access their data on different OSes, desktop and server, and on various mobile devices and through a browser. They don’t care that it can output “to Sharepoint”; they want it output to THE WEB in an open standard format. They don’t want to have to engage in yet more vendor lock-in.

    Access really isn’t empowering “non-developers”; it’s encouraging non-developers to never learn anything. To be a *competent* Access user, you still need to understand databases, indexes, normalization, etc. and you still need to learn to program (VBA). The solution to a non-developer wanting a database solution isn’t a bunch of pre-generated templates and point-and-click wizards – it’s bad enough that’s what passes for a Microsoft DBA these days. The solution is for the user to pick up a book and read it. I’ve seen users as diverse as a vice president of supply chain pick up a “for dummies”-style book and teach himself basic programming skills to get answers he wasn’t getting out of standard spreadsheets anymore and actually saying how much he enjoyed it. The same goes for picking up something like “Head First SQL” or something. Otherwise it sounds like one has some unmotivated employees.

    That said, a real programming language, real report generator and real database (not prone to corruption) are always better solutions, ideally paired with a real motivated employee. Back over a decade ago when I was choosing IT solutions for a small company I was part owner of, the solution was Delphi – a full, capable, real, compiled programming language – paired with Crystal Reports, which could also integrate with Delphi. The local database at the time was initially the Borland Database Engine and eventually a product called Codebase by the time I left.

    Today it’s easy to think of solutions that cost absolutely nothing!
    My personal choice for a “database programming stack” would be the python 3 language – very easy to read, maintain and learn, full-featured, yet with many time-saving features ideally suited for data manipulation, the Eric IDE – everything from code folding and profiler integration to a built-in chat server and collaborative editing windows so that users can be working from home around the continent and still work together!, Qt is one needed a heavy-duty UI, the built-in Python GUI set otherwise, BIRT, a Java-based complete reporting solution, and sqlite 3 for local databases and PostgreSQL for client/server needs. I’d toss in a few goodies like the Pandas library for Python, which gives it amazing “data frames” for ETL (extract/transform/load) operations, slicing, dicing, manipulating data, applying functions to columns or rows, filtering, etc. It was developed by someone in the financial industry and has found widespread use both in trading firms and among scientists. I’d also add SQLAlchemy, which gives a higher-level data access layer than SQL and works with many databases so one can switch back-ends and keep the same code and also offers an ORM (object-relational manager) for abstracting the table layout and allowing the user to work with a more natural object layout for the data. Another goodie would be ReportServer, which can allow one to schedule reports to run at certain times, forward/e-mail reports to individuals, only send reports if certain conditions are met (such as only send a report on late shipments if the total late shipments are above 2%), etc.

    A user learning Python, an actively-developed language, would be able to apply that language in many other areas as Python is used as a scripting language in many tools. It is also cross-platform, as is the Qt GUI toolkit (Google uses it for Google Earth). Eric also runs on all the major OSes and BIRT is Java-based so again runs almost everywhere. It can use many different data sources and even join together data internally from different sources, output to the web, be accessed from any web browser, etc. sqlite3 doesn’t have the 2gb data limitation, is public domain and incredibly thoroughly tested (it has to be as it is used in many embedded products so can’t be patched with bug fixes once it’s deployed). Every release sees literally millions of SQL tests performed covering all features to verify the code. PostgreSQL is an amazing client/server database engine available for free with no file/memory limits and many features, including compression, partitioning, and replication, that are only available in the Enterprise edition of SQL Server. Among PostgreSQL’s many advantages is that procedures and triggers can be written in about a dozen different languages – including Python.

    A setup like this could produce everything from simple databases all the way up to sophisticated cross-platform GUI or server software, from small local dbs to massive multi-terabyte client/server dbs. Access doesn’t scale – if you need to trade up to something bigger the code will need to be rewritten, and going from Access to SQL Server can involve a huge cost.

    One could plug lots of other tools into this formula: C# and .Net/Mono for development language and cross platform capability, JasperReports or Pentaho as alternative reporting tools (both Java-based), etc.

    These designs have no vendor lock-in and no upfront cost. Learning any “living” computer language will give one knowledge that can be applied in other areas. Learning VBA only lets you script Access and Excel, while someone who put the time into learning C#, Java, or python can use that new toolset towards other areas of their work they might be able to automate and improve with programming that don’t involve databases. Using serious reporting packages gives you finer control and output. Heck, with python you can use an interactive shell along with pandas and interactively “slice and dice” data, perform statistics on columns of data, filter out rows with blank values, produce a graph of the remaining data, etc.

    Access is like a small Swiss Army knife. Assembling an actively-developed computer language, database engine and report generator is like having a fine set of tools. There’s potentially a greater learning curve, but not much more (especially with python) and the payoff is the ability to do a great deal more things that could never be done in Access and then leverage those skills towards many other areas as well. You also gain freedom from OS/vendor lock-in and don’t have to put up with MS limiting the product to encourage you to “trade up” to their more expensive tools. You can also swap out any of the tools at any time and replace it with another “brand” due to open standards rather than dumping the whole toolbox. With Access all the parts are bolted together and you have to toss the whole Swiss army knife to replace anything.

  38. ronnie valero

    Joseph, all your chits and chats about ACCESS is 95% incorrect and bias. It’s pretty clear maybe that you have no in-depth experience and knowledge on how to use ACCESS as A DB FRONT END DEV”T . TOOL ONLY not as db store. Take note a DB FRONT END DEV’T TOOL. Show as a link for a DB tools same as ACCESS which uses pyton. I love python too but its not ACCESS.

  39. Richard

    Joseph you make some interesting points but I think you over emphasise Access’s weak points when in actual fact a good developer will be fully aware of them and can overcome them through good design, professional coding practices and choosing the right tool for the job (eg. switching to SQL Server when appropriate**).

    ** Did you know that the free Express version can now support databases up to 8Gb!

    Personally, I don’t know of any RAD tool that can be used to seamlessly migrate desktop applications to the web or mobile platforms. The technologies and hardware restrictions (particularly mobile) are just too different. But hey, if anybody knows of a product as productive as Access that can also be used for web apps and mobile, please let me know!!

    I agree about VBA, it is a dead language albeit a very powerful one. One of my favourite languages is VB.net (actually VB 2005 onwards). It’s a modern fully object orientated language pretty much as powerful as C#. I’m fully aware that it runs on top of the .Net CLR and is a managed language but why couldn’t MS update VBA to use the VB .Net syntax? Wouldn’t it be great to have class inheritance, Try-Catch-Finally, instead of the clunky On Error of VBA , and inline variable definition and instantiation (dim myString as String = “abc”) etc.

    But one of your suggested alternatives, Python plus a bunch add-on open source projects just doesn’t cut it for me. I’ve used Python, its okay and as you say it’s free and there’s lots of clever code libraries out there that one can use (Beautiful soup comes to mind). But trying to write a professional grade Access type application using the rag tag collection of stuff you recommend is going to be a bit of a nightmare – all those different library dependencies! And what happens when one of the components is updated and it breaks your project? And I don’t like the fact that the report writers you suggest depend upon Java which is rapidly falling out of favour on PCs and Macs because of the regular security issues.

    The fact is no solution is perfect. Access is still used because really there’s nothing else like it. IT departments with their grandiose strategies can moan on about how it doesn’t fit in with their carefully manicured plans but the fact is that many IT departments are too removed from the business that pays their wages and they can’t react quickly enough to those businesses changing demands. That’s why PCs and Macs became popular in the first place, and that’s why tools like Excel and Access still have their place because often results are needed now, not in two years after endless project meetings!!

  40. Sean

    No. No, no, no, no, no. The last time someone was this wrong, it was 1935 and he’d just stepped out of an aeroplane from Berlin with a piece of paper in his hand.

    Access is lethal to any properly run business. It puts semi-serious RDBMS tools in the hands of people who don’t understand the simplest basic fundamentals of data management. Before you know it, you’ll be trying to run your project from a “database” designed by a marketing graduate who’s figured out how to turn his imported spreadsheet into a form and make the background pink. Just no.

    Any DB professional will tell you that RDBMS are tremendously useful and powerful, but also tremendously fragile and tremendously easy to get wrong. Keep access the hell away from your business, for the same reason you keep power tools away from babies.

  41. John

    Sean,

    Yes, yes, yes, yes, yes, you are an elitist data-snob. We have made MILLIONS of quantifiable dollars by using MS Access solutions in our business. It hasn’t been lethal at all — it has allowed us to grow, adapt, and compete well. Developing in Access lets us rapidly prototype and deploy solutions which would take months and unobtainable budgets through conventional IT channels. And it’s not that hard to get the solution right if you have two days worth of study in database normalization. Seriously, your hyperbolic comments reveal you to be one of the knee-jerk reactionaries that Luke Chung is trying to enlighten. So anybody who advocates the use of Access is Chamberlain? What does that make Access itself? Hitler? LOL… At least you got one thing right — Access is a power tool (what does “semi-serious” mean??? — I have to take that comment semi-seriously) and in the right hands (not a baby’s!) one can tear through some serious work in short order.

  42. bobby

    Imagine in organization where business analysts learn how to code things. Things that database developers do. Imagine IT professionals who were graduates of computer science knows how to code things but only on the basis of schema and flow charts, but business analysts can actually picture out flow charts and schemas without plotting into diagrams…imagine how insignificant IT departments would have been if business analysts can do IT works but IT people would find very difficult to understand the whole process. Accountants knows the business processes and the whole things about software developments, and in my opinion, this is one of the greatest conflicts between these two professions. so, IT would result into security and quality issues to prevent this colliding ideas of non-IT professionals learning access and the traditional IT mindset.

  43. 280 + tables 1700 queries 535 Forms 280 reports 150000 + lines of code runs with an Access or SQL backend. We have over 70 clients some with over 50 users and tables with 900000 records. It all runs fine and our customers love us because when they need something new we can respond quickly and inexpensively to their needs.

    We can slap together something that works in hours, let the customer try it and if it fits their needs turn it in to something completely professional in a couple of days.

  44. Joseph

    @Richard

    >Joseph you make some interesting points but I think you over
    >emphasise Access’s weak points when in actual fact a good
    >developer will be fully aware of them and can overcome them
    >through good design, professional coding practices and choosing
    >the right tool for the job

    There’s the problem. A good enough developer could probably make due with copies of Turbo Pascal and DBase. However, one of the initial premises of the article was that

    >Non-developers have the ability to create database solutions
    >without resorting to professional developers.

    And they’ll run into Access’ limitations if their project scales high enough or the data grows large enough or broader compatibility becomes a requirement.

    >** Did you know that the free Express version can now support
    >databases up to 8Gb!

    On the other hand, PostgreSQL offers unlimited database size, no CPU or memory constraints, and advanced features such as partitioning, compression and mirroring only found on the Enterprise version of SQL Server, which starts at over $25K, at it’s completely free too. :-)

    The DB2 Express edition has CPU and memory constraints but no database size constraints.

    >But hey, if anybody knows of a product as productive as Access
    >that can also be used for web apps and mobile, please let me
    >know!!

    Some Delphi users might nominate it along with its FireMonkey framework, although IMHO it’s rather weak in the web area.

    > and is a managed language but why couldn’t MS update VBA to
    >use the VB .Net syntax?

    There’s lots of indication MS wants to be rid of VBScript but can’t for compatibility reasons. It was gleaned from MS blog posts that the Windows version of VBScript was inextricably tied to Windows. The OS X version had to be completely rewritten for that platform and it ended up being inextricably tied to the PowerPC processor. :-( When Macs switched to Intel CPU, VBA actually disappeared from the OS X version of Office for at least one release. It’s also not on the ARM version of Office for Windows RT. Microsoft clearly doesn’t know how to write portable code (which I guess isn’t a surprise). It would be nice to see them update VBA but that would cause problems with all of the books and libraries and tools that use the existing version. It’s also possible based on all the other compatibility problems that they’d like to get rid of/change it but that it’s also inextricably woven into Excel and there’s no simple way to get it out. :-(

    >Wouldn’t it be great to have class inheritance, Try-Catch-Finally,
    >instead of the clunky On Error of VBA , and inline variable definition
    >and instantiation (dim myString as String = “abc”) etc.

    Don’t forget short-circuit boolean evaluation, which, well, I think *every* other language has had since ALGOL-68! :-)

    >But one of your suggested alternatives, Python plus a bunch
    >add-on open source projects just doesn’t cut it for me.

    Actually there’s a product called Camelot I learned about after writing the original post that combines python, Qt and SQLAlchemy with its own IDE and libraries into an Access competitor, so I’d like to think I’m on the mark with this one. :-)

    >I’ve used Python, its okay and as you say it’s free and there’s lots
    >of clever code libraries out there that one can use (Beautiful soup
    >comes to mind). But trying to write a professional grade Access
    >type application using the rag tag collection of stuff you
    >recommend is going to be a bit of a nightmare – all those different
    >library dependencies!

    Really? Python has become HUGE in the scientific and data analysis communities, with the creation of the SciPy and EuroSciPy conferences and a new PyData conference two years old dedicated exclusively to python and data analysis! This latter conference was due to python finding significant use among “quants” on Wall Street and other financial areas to analyze data. CERN even used it to help hunt for the Higgs Boson! O’Reilly just published a book called “Python For Data Analysis” which addresses Python and some of the libraries in question. The author created the Pandas data frame library and has seen enough success to start his own company now offering financial analysis libraries for quants in Python while keeping Pandas open source. Many of the libraries in question were intended to work with each other so there are no dependency nightmares (something Python really isn’t known for anyway). The last data analysis course I took used R and Python. Python is also used heavily in massively parallel applications. In fact, a new web service, Picloud, exists that lets you, with a line or two of additional Python code, run your algorithms in the cloud, even if you need a thousand CPUs!

    Someone told me recently “Every Python user is either a web developer or a scientist.” :-) While not literally true, you’re significantly underestimating the degree to which Python is being used in data analysis of all kinds today.

    >And what happens when one of the components is updated and it
    >breaks your project?

    I don’t know of backwards compatibility being wantonly broken, but the simple answer is… don’t upgrade that package. :-) We can ask the same question about every piece of software, including Access, which has had its fair share. In addition, virtualenv lets you isolate a python program so that it runs with a particular set/versions of libraries regardless of what else is installed or what other programs are using, so this isn’t a problem even if you do upgrade a library for another project.

    >And I don’t like the fact that the report writers you suggest depend
    >upon Java which is rapidly falling out of favour on PCs and Macs
    >because of the regular security issues.

    Those security issues generally revolve around Java being enabled from the browser and ensuing script attacks allowed to run, not Java running a program on the desktop. But the beauty is one could swap out the report writer for, for instance, Crystal Reports, except you lose cross-platform compatibility to some extent (although CR does produce web output now so on the consumer/client end you’d still have cross-platform access to reports).

    >The fact is no solution is perfect. Access is still used because
    >really there’s nothing else like it.

    Or it “comes in the box” with the rest of Office and no one’s bothered to explore other ways to fill the need. I remember applying for a job once which as one of its requirements stated “ability to produce complex menu-driven applications in Excel”. My first question to them was, “Why in the world would you want to do that in Excel?” :-) Basically the manager didn’t know about any other tools and it was apparent IT wasn’t helping her with her problems either.

    >That’s why PCs and Macs became popular in the first place,

    and we’re going back the other way with web apps and the cloud and Chromebooks now. :-)

    > and
    >that’s why tools like Excel and Access still have their place >because often results are needed now, not in two years after
    >endless project meetings!!

    That’s more a problem with how things are run rather than with IT itself. On the other hand, if there’s a problem, everyone will blame IT for not predicting it and preventing it. “Data silos”, backup and security issues are real problems, not IT imagination.

Leave a Reply

Your email address will not be published. Required fields are marked *