Monday, April 01, 2013

Please, Stop Creating Microsoft Access Databases!

It all starts very simply and innocently with someone needing a place to store data that is a little bit more than what is convenient to store in Microsoft Excel. She thinks, "It's just a couple of tables and I already have MS Access on my desktop", so this shouldn't be too hard. The bad news is that if this database is "successful" it will likely draw others to it forcing the SadBA (self appointed database business analyst) to consider granting access to her desktop stored database, developing forms, and producing reports. Even worse is when new opportunities present themselves and she decides to create additional MS Access databases. She only calls in IT if she needs something scripted such as more advanced forms or jobs that can load and transform new data.

Flash forward a few years and consider if this behavior is repeated across multiple organizations and locations and you have a classic database mess. IT will probably be asked to perform heroics when a desktop fails and there isn't a sufficient backup, or when there is an MS Office upgrade being planned and these databases need testing, or when the SadBA is leaving the company and no one understands how to support these databases.

As big of a database mess this is, the underlying data mess can be a daunting maze to unwind. Consider even a single database, a trained DBA would need to understand the underlying data model, document any scripts or procedures loading data, and itemize reporting needs. If any forms were developed and especially if multiple people are using the database as part of a workflow, then you'll need a Business Analyst and possibly an Application Developer to consider how these business processes are accomplished.

Perhaps you've never had to read someone else's code?


Rebuilding a database when it likely has poor naming conventions, missing data relationships, and a complete lack of referential integrity requires a DBA with the skills of a linguistic anthropologist. Now tell this DBA that there are multiple databases that contain duplicate and related data and they'll need some special software tools to normalize the data model, load in data from multiple sources, and match, merge and de-duplicate records,  before even considering how to replicate existing functionality.


Why is this a Big Concern?


Even smaller companies are recognizing the benefits of analytics and Big Data processing. It's relatively easy for a business user to perform analysis on a single data source, or even a handful if the data relationships are understood. This can easily be done in MS Excel or even better, by selecting and correctly leveraging a self service BI tool. But if there are numerous databases stored all over the place with undocumented data dictionaries, unknown data quality, and little understanding of how to relate data sources, then it is virtually impossible to perform broad analytics on it. It is part of the company's dark data - data that exists but can't easily be analyzed for intelligence or insight.

Is this your company's sales data, customer data, marketing data, or financial data? More likely, the answer is yes because it's this data that business users work with the most. If the business user needed to perform a quick analysis and IT wasn't accessible, available, or had the necessary agility to solution, then it is likely that a SpreadSheet Jockey or a SadBA established a solution.

What is the first step to solving this issue? Please, stop creating MS Access Databases!

40 comments:

  1. What a silly posting. Non-developers create Access databases because they need to get some work done and Access presents an opportunity to move forward. Sure a lot of the time a mess results, but not without benefits.

    What are they supposed to do, wait for the always interested and available IT dept to respond to their needs (ha ha).

    I've unwound many user created Access databases and used them as models for very successful applications. If you don't like dealing with situations like that, find other work. Not every project starts out as a nice neat greenfield project...most messy user created dbs fulfilled a purpose. And many need to be redone, in order to keep fulfilling the need. Good!

    ReplyDelete
    Replies
    1. Thanks for the comment, though I disagree that this is a silly post. Yes, there are ways to allow non-developers to get work done and there are ways to migrate these databases, but not without challenges.

      I've updated this post with responses to other comments. See The Problems with Siloed Databases Part 2

      Delete
    2. Lol either hes a student or he works at microsoft, access is a kids toy, not a tool for competent people who needs the work done.

      Delete
    3. Can't even use an adverb properly but has no problem slinging insults. Face it people. There are plenty of snobs out there, especially when they are trying to pump up whatever Big Data priesthood they adhere to. The rest of us working database designers will keep designing using whatever tool fits the business need and budget.

      Delete
  2. Access is so much less an issue then Excel based on my experiences.

    ReplyDelete
    Replies
    1. Good point - more people have the skills to use Excel, so the volume is often a bigger issue than the depth you find in Access.

      Delete
    2. I currently exist in the 7th circle of data hell. Folder up folder of excel sheets and word documents stretching back years (possibly even forward through time...)

      It's like I'm cleaning up after a category 25 cyclone (highest category for those that don't know is 5)...

      Delete
  3. Please, Stop Creating Microsoft Access Databases!
    Does this mean that microsoft corporation has spent huge time and money to develop and put forward an application which is essentially worthless.
    Maybe the scope of your statement is too wide?
    Just asking, is all.

    ReplyDelete
    Replies
    1. If Microsoft were smart, they'd have tools to help automate the upgrades to Sql Server or .Net - but they'd need workflows to help developers "clean up" the poorly designed ones. Thanks for the comment

      Delete
    2. Yes, yes it does.

      Delete
  4. Well, I respect your opinion. But I agree with Avi, no company would waste so much time and money for a trash application. Maybe there are some issues about it but still, I think it helps people more. Thanks for the post by the way. I'm looking for latest enhancement in Access Database and this is where I bumped into. Respect for the blogger.

    ReplyDelete
    Replies
    1. Thanks for the comment. Not sure if the "company" you are referring to is Microsoft, or companies that have developed databases/apps leveraging Microsoft Access. Either way, I'm not suggesting to trash these applications. IT departments should offer their users alternatives and created roadmaps to migrate the most important ones (hint - tied to workflows) that were already created.

      Delete
  5. Its really good to know about that some facts and other informative points given here are quite considerable and related kind of as well would be so far better idea to look for more of these kind to have better results.

    Construction Service Management Software

    ReplyDelete
  6. I remember the pain and joy of designing and building an Access database to manage my PhD reading lists back in the 1990s, a task which back then Excel couldn't handle, and when I got it to work, revolutionised the way I worked.

    Fast forward into my professional career and I think this allowed me to be creative and demanding of my IT departments in delivering solutions (regardless of platform) that delivered the sort of results that were needed. Would I have done this had I not had the chance to experiment with something like Access? I somehow doubt it...

    Surely the solution should allow us to embrace the experimenters who want to play with Access - and maybe pointing them towards the power of Excel or your BI tool of choice is the way forward - but locking things down just says to me that you're just going to end up building a wall between users and the technology 'owners', which is a situation that will only ever end up in a bad place.

    ReplyDelete
    Replies
    1. I have no issues with experimenting and fully support several self-service BI programs. See 10 Principles of Self-Service BI and Self-Service BI - Drivers and Enablers for my recent posts on the subject.

      The problem with Access is that it has no guard rails. It enables business users to create or enhance data sets with little transparency. If you do too much with it too quickly, it's very easy for an experiment to evolve to a full department workflow capturing new data that is disconnected from other data sources.

      If you have the skills to experiment in Access, ask your IT organization for their recommendation on other tools. If you need to park data for analysis, they should be able to get you better options - and if not, your organization probably has bigger issues.

      Delete
  7. I agree that Access is a poor way to develop databases. Microsoft Office was designed for data of the 70' s. Still Oracle and SQL are made for developers (wait on IT). So what does one who has a deadline do... Use what's good enough... MS Access. Any one has a GUI based SQL back-end database app? I'm in the market for one.

    ReplyDelete
    Replies
    1. Lucas seems to have hit the nail on the head. For us non-IT people who need the power/structure/control of a database over the "wild west" of Excel, Access provides us the graphical design interface that is (as far as I can tell) missing in the database world. And if (that should be IF) the person developing has any experience or training in data structures and program design, it would probably be an OK result.

      Delete
    2. It's better than ok! You can develop beautiful Access databases if you just take the time to think about your data and what you need from the data. The problem I see in most of these posts is that people seem to think that IT and Data are the same professions. Many IT people don't have a clue about data and few IT departments have hired people who are truly talented when it comes to efficiently handling and organizing data and making it accessible.

      Delete
  8. I have found a way to bloxk my client from geting to the back end. Access is a very difficult database to learn, which is why most of the time it gets a bad name.

    Yes, it can create a mess if it is not developed correctly but that can be corrected. Oracle and some of the larger DB are very costly and as some suggested leaves the user waiting on IT.

    I have worked as a Access Developer/Analyst several very large organizations and Access is utilized through out the organizations.

    ReplyDelete
  9. How many articles have bashed Access when the true culprit may be the developer/designer and not the software itself? What other platform provides both a front end and back end system complete with an extensive design toolkit for forms and reports for user interfaces? Plus, don't like Jet? Well, you can connect to any other database system through ODBC. Heck, if Microsoft allowed executable file outputs in Access applications, we wouldn't even need VB .Net.

    Sure, Access has a high learning curve but if you are an experienced user, you can build very robust, stable solutions. It's always funny to read posts like these ones. Rarely, do you read posts bashing the popular Excel when small businesses overuse the flatfile system for unwieldy, buggy application and automation environments. And then, developers tell them to use SQL Server/Oracle, skipping over a middle-range cost-effective solution like Access.

    ReplyDelete
  10. Nice post, but I can say:
    Stop the costs and timing imposed by the IT department.
    I saw Access projects that have led a reduction of 90% of the costs required by IT department to get the same result.
    Access is not a bad product, there are bad programmers.

    ReplyDelete
  11. All those who codemn access do so because they found out late that what it took years to build in java & co. took days to build with access.

    I have used access very very well. My company lives on access like fms and showork does. I have different access applications running on mdb and sql server backends and efficiently handling hundreds of concurrent users with challeges.

    Whenever acces has issues, the fact boils that to bad design and this can happen in any language be it java

    ReplyDelete
  12. As above commenter said. the true culprit is always the developer. I think the OP didn't learn much about MS ACCESS and copied biased opinion from neighborhood developers. Really, its all how big the organisation is and how much they want to spend on IT department as well as what their requirements are.

    Access + ODBC + MySQL is powerful enough for many organisation and probably the cheapest way. instead of paying fancy licence fees/development fees/ development teams etc.

    In my opinion the OP thinks ACCESS is waste because its a locally saved database and incompatible of multi user access. probably he never spent enough time on this development environment. hence he didn't say any word about using back-end tables?

    Every development tool has its own strength and weaknesses. its a question and decision by the organisation which one suits them the most.

    Since All Microsoft office products uses single thread platform, access also depends on single thread to process commands. probably this could be the only major weakness access ever had and has. Apart from that its a very good tool to satisfy many mid range organisations.

    Don't bash ACCESS bash the developers who doesn't know which tool to use to give a solution :)

    ReplyDelete
  13. Definitely it depends upon how much effort you put on developing any database. Everything has its own strength and weakness. For my experience i find access is good database.

    ReplyDelete
  14. Having worked in IT for 30+ years, with the last 15 years (or so) being in the SMB arena, I find the article and the follow-up comments are often revealing, sometimes arrogant, and (I would opine), horribly biased..

    Access is not a "bad" program. It's a TOOL. If you know how to properly use ANY tool, you can improve your productivity and make your workflow easier. If you DON'T know how to use a tool properly-well, you just make things worse.

    The evolution of consumerized IT has helped create this mess-people think it's so easy to do what it takes a trained professional months/years to learn and understand. Which, in turn, creates a definite IT "superiority" attitude that fosters unbelievable arrogance on the part of the majority of IT professionals. It's the arrogance that leads to people adopting the "SaDBA" role. The IT clients would prefer to avoid interaction with the IT staff/department.

    It's correct to point out that database management requires a skillset that most do not have, just as it requires a different skillset to perform business accounting. Would you want your IT department filing your business financial records?

    But it's time for IT to drop the condescension and explain, WITHOUT the smugness or the attitude, that databases increase in complexity as they grow and it would be better for a company to work with a professional database developer/DBA as opposed to trying to "homegrow" an application.

    ReplyDelete
  15. been using msacces since 1996 until today in a multiple user enviroment and will be developing more program using msaccess.whats wrong with you? do you how how to use the tool? learn it!

    ReplyDelete
  16. I note that of the 23 posts, exactly one agreed with Isaac.

    The one person that agreed was the least professional sounding ("Lol either hes a student or he works at microsoft, access is a kids toy, not a tool for competent people who needs the work done.") OK, third graders don't like Access.

    Access has limitations and Microsoft has done a poor job of enhancing this widely used and versatile tool. On the ground, it remains as an important tool. What IS ridiculous is that it's so hard to find a more modern and robust alternative to Access.

    ReplyDelete
  17. I've made a couple million dollars as a silly Access developer. Still make big bucks on it to this day. More than most Java and .net developers because what it all boils down to is - are you a good solution provider. I've been so busy solving people's problems and banking the money that I haven't had time to learn anything new.

    That said, I actually agree with the article if the title changed a little. "Stop creating Access Databases Without a Plan and If You Don't Know What the Hell You Are Doing!".

    As far as the headache for IT - I've spent much of my career fighting IT for their ignorant beliefs about MS Access. They dismiss it out of hand. In the end, I win and solve the problem because I can and they can't - at least not without at least tripling the cost and resources.

    Microsoft is definitely behind on moving Access to the future. But in the meantime, It's going to be Monday and people have to work so solutions have to be made.

    ReplyDelete
  18. I agree with Michael 100% access itself isnt a BAD piece of software, the issue is that it allows idiots to create semi powerful databases that then grow organically without structure or planning and become a behemoth of epic proportions.

    That said, doesn't it still have a 255 concurrent user limit? And the idea of pushing out front end updates to the gui for multiple users, even with a GPO in place is a nightmare. The way data is moving (towards cloud systems etc) I don't see much of a future for access.

    The fact that people make a living from being a "silly access developer" is fascinating and crazy to me. That is not to knock your profession Michael, I am sure you are great at what you do, I just don't see why companies would pay someone to do it, unless they had an organically grown DB that had become unmanageable. More of a tidy, less of a dev?

    But I could be very well mistaken!! Great comments btw.

    ReplyDelete
  19. 100% agree with this post.
    We have several shadow IT departments attempting to report on data from an Oracle environment by extracting tables into Acces/Excel.
    They are crashing due to the large amounts of data, and they think they need faster computers.

    ReplyDelete
  20. Well, ya know everyone has a different story. I think Access has it's place. I do a lot of VBA and can use Access quit well. I learned a long time ago that it helps to be a good programer who is objective when it comes to the users who actually use the application. And speaking of users we had workers inputting data into excel spreadsheets that they had to share. It was a nightmare until I converted the process and put together forms and tables for them to log data into Access - Front end to back end setup. They were thrilled I made their job so much easier for them and removed the "frustration factor". So I guess it all depends on what you are using it for. I love Access and VBA. Have learned and done many things with it to help the business where I am employed.

    ReplyDelete
  21. @cy you are perfectly right. developing a database of significant size for a medium size or even big organization using access and sqlserver requires a good understanding of database rules and some skills in VBA to leverage the power thereof. So my advice is try to learn a bit of programming.

    ReplyDelete
  22. Microsoft Access is the world’s most widely utilized database technology ever because it’s easy to develop and delivers business value quickly which is always important and keeps programmers employed.

    The MS Access technology is sound. The basic foundation has not changed since its release in the early 1990’s. It’s not a sexy technology like C#, Java or .NET but it is a solid workhorse database application technology.

    Where the breakdown occurs is here: as the business process matures and depends more heavily on this critical business function the MS Access database performs, the flaws in the original design become exposed which is why the phase one MS Access database shouldn’t be thought of as a completed work product but rather as an evolving software solution which will need care and feeding to grow. Think of the phase one database as a working prototype. It’s supposed to have certain flaws because the business value hasn’t yet proven it can provide the required financial investment to support a fully compliant IT project.

    Phase one Microsoft Access databases need all the nutrients that large enterprise IT projects require, which are: business architecture, data architecture, application and technical architecture, project management and change management. Knowing when and at what amounts to feed your phase one database application in order to nurture it into a blossoming to becoming a phase 2 database application which is a mature, more productionalized and hardened database application takes careful attention and monitoring. Once a phase two business value has been approved, the process is to look at the phase one database architectures, develop target architectures, design a transitional architecture and roadmap, estimate the level of effort and then go back to the business with your discovery and request signoff before starting phase two development.

    One instance where a MS Access database should absolutely NOT be developed in the first place is when a commercial off the shelf (COTS) or SaaS applications already exists in the market. It’s imperative that a Buy vs. Build decision tree be followed and an exhaustive online product search for a commercial off the shelf project be performed before any coding. However, smaller company’s (say under $10 million gross revenue), back-office workflow processes are most often very unique and not supported by any COTS or SaaS products. These unique processes are what give these smaller businesses their competitive advantage and phase one database application often become roadblocks for their growth when they are not nurtured appropriately.

    In summary, Microsoft Access databases enable unique business processes to be solved and tremendous business value to be delivered where no commercial off the shelf (COTS) or SaaS applications exist. Proliferation should be encouraged within these guidelines: Buy vs. Build analysis completed, the subject matter or data is not duplicated, and lastly, database and application programming standards are adopted and implemented at appropriate amounts between phases. Think of this as IT Asset management.

    I’m trying to build a Microsoft Access database practice. If you would like any consultancies, support or application development, please visit my site at Help4Access dot com.

    Thank you.

    ReplyDelete
  23. Other than few hitches of scalability and security, I can do anything with ms access. Do not be surprised that there are other features, for instance attachment, if well used and integrated with CorelDraw produce workpieces that one cannot believe.
    I tend to think shamming of ms access is just limited to one's limited innovation.
    With little thinking, well programmed frontend my relatively maintain it size at the expense of replications of the backends after meeting a certain size. Furthermore, if u can control any windows application via ms access including MS SQL Server, then don't you realize the toy is truly a killer gun?!

    ReplyDelete
  24. All of the issues you discuss are real, but most of them revolve around poorly designed and maintained databases which can happen with any database tool. I have used Access databases for a lot of purposes and if well planned and executed they are excellent resources and no longer have to be limited to the desktop. Size shouldn't be a problem because of the ability to link multiple databases. Again it all comes down to planning your databases so the design is sound. I think there are a lot of expensive business solutions software peddlers out there who want us to believe that something as inexpensive and accessible as MS Access could not be of high quality.

    ReplyDelete
  25. A one-sided analysis, your article is. Looking at the comments in this post shows how much wrong you are that Access be taken out of the business. I'm coming from 6 years developing Access databases and 4 years as an Application Developer, and I can say that one complements the other. Please do research on papers on why Access has a place in the organization - just so that your argument is not polarized only to your own belief.

    ReplyDelete
  26. It's relatively easy for a business user to perform analysis on a single data source, or even a handful if the data relationships are understood. This can easily be done in MS Excel or even better, by selecting and correctly leveraging a self service BI tool.

    ReplyDelete
  27. Thanks everyone for the comments. I decided to do a follow up post this year covering data silos and database platforms. I hope you will all visit The Database Decision that Will Hurt Your Company's Big Data Opportunities and share your perspectives.

    ReplyDelete
  28. Actually, i used Access 2007, as part of the suggestion..i think the company should implement a training for access basics...theses would helps a lot..even non IT professionals can learn.....

    ReplyDelete
  29. Isaac, With over twenty years experience with Microsoft Access in a Fortune 500 company, I couldn't possibly disagree with you more vigorously! Why don't you read a bit of Luke Chung's writings on the *proper* use of this absolutely wonderful tool you are trashing. I particularly take issue with your snotty sadBA acronym -- I suppose you never found yourself thrust into a role where you were limited to the set of tools management gave you, and then had to figure out how to get results! It sure is a lot easier to sit on the "Big Data" throne and look down your nose at everything else.

    ReplyDelete