Access Is Underrated: Your Hatred Of Microsoft Access Is Largely Unjustified
by Luke Chung
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/
Related posts:
Gewern
Jun 7th, 2011
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!
stan3
Jun 7th, 2011
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…?
Tony D'Ambra
Jun 7th, 2011
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.
Nathan Reyes
Jun 7th, 2011
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.
Richard Love
Jun 9th, 2011
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.
Luke Chung
Jun 9th, 2011
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.
nonlinearly
Jun 13th, 2011
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.
admin
Jun 17th, 2011
Wow. And you always hear people praising MySQL for its speed and performance. That’s interesting to hear.
Alex
Jun 22nd, 2011
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
Joe Buonocore
Jun 23rd, 2011
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!
Rudy Lambert
Jun 23rd, 2011
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
Debra Tope
Jun 27th, 2011
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.
Dalkeith
Jun 29th, 2011
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.
akyatbahay
Jul 25th, 2011
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.
Luke Chung
Aug 2nd, 2011
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.
Luke Chung
Aug 2nd, 2011
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.
patrick
Sep 19th, 2011
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.
Hughie
Oct 13th, 2011
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
Margaret Bartley
Oct 18th, 2011
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.
Victoria Suominen
Nov 7th, 2011
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
john frederick
Nov 22nd, 2011
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.
Brian Watson
Dec 12th, 2011
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.
Cc
Feb 12th, 2012
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
Jim Flood
Feb 22nd, 2012
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.
alan
Mar 6th, 2012
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.
Paul
Apr 19th, 2012
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#)
Luke Chung
Apr 19th, 2012
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…