Data Management

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

Microsoft Access may not be right for all situations, but it does have its place -- at least according to database expert Susan Harkins. Here's her take on some of the more common complaints she's encountered from IT pros who like to kick Access around.

Microsoft Access may not be right for all situations, but it does have its place -- at least according to database expert Susan Harkins. Here's her take on some of the more common complaints she's encountered from IT pros who like to kick Access around.


Until Vista came along, Access was easily the most maligned and misunderstood offering in the Microsoft Windows family. While Vista has earned its reputation, Access hasn't. It's true that Access can be a problem child, but with proper discipline, Access performs well and has its place in every organization. Still, professionals tend to spit on the floor when someone suggests Access as a possible database solution.

Note: This information is also available as a PDF download.

#1: It grew!

It's common to use Access for small, noncritical tasks even if a larger, more robust system is available. Occasionally, an Access database grows beyond its original purpose. After a year or two of use, an Access database can become vital to the organization. It doesn't happen often, but it does happen. At this point, the IT professional faces a challenge -- revamp the Access database or upgrade, and both require a lot of work.

Some IT professionals grumble... the original developer should've predicted the future and used a more sophisticated platform. That attitude is counter productive. Successful databases evolve over time. It is difficult to predict, with any certainty, the future use of a database that begins in the lower levels of an organization. Most never move up. They continue to serve their original purpose or users cast them aside.

If an Access database works its way up to the department or enterprise level, celebrate its triumph instead of trashing its creator. After all, if the database evolved and grew, your organization is growing, and that's good news for everyone.

#2: I have to support what?

Non-programmers create most Access databases. Administrators, managers, assistants, and clerks use Access to store and analyze data, without any help from IT. While the database might get the job done, it's usually inefficient and difficult to maintain. Invariably, these innovative folks end up calling IT to help resolve a bug or add a new feature. Some IT professionals simply refuse to help, and understandably so. That kind of problem-solving and debugging is difficult and time-consuming.

If the database comes from a company honcho, the IT department might have no choice but to support the database. In an effort to be proactive, some IT departments have banned Access from their organization. Unfortunately, that drastic step doesn't benefit end users. They'll use Excel, or worse, they'll call IT when they need a database. The real solution is to understand how Access fits into your organization.

#3: Access isn't a professional database

Access has the reputation of being a toy and not a real database. It's undeserved, but a lot of IT professionals simply won't use it.

Many Access developers came from a non-programming background. They found a hole and they filled it. Unfortunately, some professional programmers look down their noses at these enterprising folks, and thereby at Access. Despite the snub, Access is the most popular desktop database on the market.

#4: VBA isn't a pure language

Access uses Visual Basic for Applications (VBA) as its development language. Technically, VBA is a subset of Visual Basic (VB), so many IT pros believe Access has less value than VB. It's cockeyed thinking because one has nothing to do with the other. Professionals have many tools and they apply them efficiently, if they're smart. A subset of VB, used appropriately, is no less valuable that VB itself.

#5: It's too easy

The most ridiculous reason for avoiding Access is that it's just too easy to use -- bul...I mean, rubbish! That's exactly why the smart professional uses it. Don't hamstring your organization, support it. If Access can solve a problem quickly, use it, even if the database is just a temporary fix that frees you up to flex your robust system muscles with a real database.

#6: If you build it, something will corrupt it

Admittedly, corruption is one area where Access does drop the ball. Regardless of the source, corruption is a real problem. For some IT professionals, it's ammunition.

But the truth is, used correctly, Access rarely falls victim to corruption. Unfortunately, the majority of Access users don't always use it correctly. The debate goes on, but many IT pros simply won't support Access because of its vulnerability to corruption. To learn how to avoid Access corruption, see 10 ways to prevent Access database corruption.

#7: Money -- 'nuff said

Access applications are cheaper to build and maintain than the more sophisticated productions of SQL Server and Oracle. If the Access developer promises a $5,000 project and the SQL Server developer wants $50,000 just to get started, who's more likely to get the job? That doesn't mean that Access is the best database for the project, but the SQL Server developer must convince the client that a high-end system and ongoing support are necessary. Otherwise, Access just might rule the day.

#8: Access isn't an enterprise solution

Most organizations have different levels of responsibility. That level often determines how IT fulfills a need (or if at all). For instance, most databases have one purpose and often, just one user. The database sits on the user's system and no one else sees it, uses it, or even knows it exists. For these users, Access is a flexible and quick solution. At the top of the organization are the enterprise needs, which are more critical and require more sophisticated and powerful tools. The organization depends on these solutions and they're usually complex and expensive to develop and maintain.

No one demands that an organization use Access at the enterprise level (although I have seen it used, and expertly so, at this level). So why insist that lower levels use an enterprise database system? It's an unreasonable demand. Use the right tool for the job, no more, no less.

#9: File-server applications are inferior

Technically, Access is a file-server application and not a client-server application. That means that Access does all its processing on one server. Client-server applications process on both sides of the network. While that arrangement is more flexible, it also comes with overhead.

File-server applications aren't inferior; but they are different. Used appropriately, they're efficient and capable of outperforming client-server applications.

#10: Access is hard to deploy

Most Access applications need Access, just as an Excel spreadsheet requires Excel. That complicates deployment somewhat. First, Access is a huge application. Second, there are several versions. (I have more versions of Access than pairs of shoes in my closet!) However, if your organization uses Microsoft Office, most of your systems probably have Access installed. On the other hand, upgrades can also be a nuisance, if not outright challenging.

Still, you're going to run into these problems with any application, not just Access. In addition, if you want to maintain Access and avoid deployment issues, consider turning your databases into Web-based applications. For more on this, see 10 reasons to turn your Access applications into Web-based applications.

#11: Poor security

While most Access developers swear by its security model, the truth is, Access security simply isn't as robust as you might need. You can password-protect and even encrypt data, but Access doesn't offer the same level of security as SQL Server. (Unfortunately, the security model isn't even available in Access 2007.)

A compromised database quickly becomes a problem for IT to solve. You didn't create the mess, but you'll get to clean it up. At best, educate users not to use Access for confidential or sensitive data. (Well... you can try.)


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

88 comments
maxidentist2014
maxidentist2014

 The type of database depends on what type of field or career that you plan on pursuing. I plan on going onto the medical field and some practices might use access but a majority of the hospitals, clinics and emergency facilities have a databast that is linked so that no matter where you go you could pull up anyone information as long as they are in the system. A field is just a column or area that has the same information, a record is the information of a single individual, a table is something that you are working in, this could include appointments, customers and services this just depends on how you have it set up. The database is all the information on a single sheet that could be broken up into different tabs. The advantages of using Access to Excel depends on the person using it, a person could like Excel better than Access because they know the program and are afraid of change. But it depends one what the organization you are working for prefers cause you would have to learn either Access or Excel to succeed in your profession.Access is an amazing multi use program. Learning about Access has helped me to create a schedule for my daily routine, I also made a catalog of all my cloth, makeup, shoes, purses, and I love it. As a person who is a fanatic of organization I’m in love with Access. Before learning about Access I had no idea how useful this program was, also it made me rethink about how much it would of help me with my old assistant manager job at an employer company. I would have been able to categorize all the employees and their hours work, paying them would have been a piece of cake, the Access would have made my job so much easier than by file.


sashafroyland
sashafroyland

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.

MichaelX2012
MichaelX2012

I have used MS Access since version one. Access has evloved into an elegant indispensible platform for me. I develop Access and SQL Server applications. I use Access for data imports, quick data scenario testing, reports, exports. Access can do everything a developer wants. Access is NOT an enterprise level database and it wasn't meant to be. Anyone who points out the flaws of Access and those flaws are enterprise database related are not getting the point. I have used Access in situations that saved a company hundreds of thousands of dollars. The real and most important consideration of ANY application is "Does it work? Can it perform the way it was deisgned to perform?" The answer for Access is "Yes!". For all of the naysayers out there... you all truly do NOT know what you are missing. In this competitve development world, Access is a powerful tool in my arsenal. My question for the naysayers is this: "Have you actually used or even tried to use Access to resolve a problem?" If you answer is "No" then that is probably why you feel the way you do. Access, just like any other devlopment tool, must be properly used. When properly used Access is the best in it's class.

PatHartman
PatHartman

I am disappointed in this article and others by Susan because she has had many opportunities to clear the mud from the waters and hasn?t taken advantage. Comparing Access and SQL server is simply meaningless. SQL server simply doesn?t provide any way to create a user interface so I don?t know why people keep trying to compare the two and Access is not a database engine!!! It is a tool for creating a user interface. Jet IS a database engine. Yes, it is true, the Access application is ultimately stored in a Jet database but what has that got to do with the price of tea in China? VB.net code can be stored as plain text files. Does that mean that you should compare notepad to visual studio because they can both be used to edit the code? Access is a development environment and supports VBA as its embedded language. Jet (or ACE) is a database engine. They are NOT the same!!!! Comparing Access to SQL Server is like comparing Excel to Word because they both let you create table like data structures. A semi-valid comparison is Jet to SQL Server or Oracle since both are database engines. However even that comparison is weak because Jet is a file server and SQL Server and Oracle are database servers and they operate differently and provide different functionality. If you want to compare Access to something, compare it to Visual Studio with VB.Net or compare it to Oracle Forms. All the drawbacks mentioned in this article are limitations of Jet/ACE, NOT limitations of Access. Please, Susan, make an effort to clear up this confusion rather than contributing to it. If you can make IT folks understand the two tools and the fact that Access is in no way tied to Jet/ACE (except as a container to hold Access objects) or even to SQL Server, perhaps Access will be able to gain its rightful place in the toolboxes of developers everywhere. I?ve created hundreds of Access applications over the past 15 years and fewer than 20% actually use Jet/ACE as their data repository. The rest use SQL Server, Oracle, DB2, Sybase, Pervasive, MySQL, and more. Some use multiple back end databases. Please ? stop the madness. Access IS NOT Jet!!!!

juddly
juddly

When Access tries to overwrite my own SQL and gives back an error on it's own changes, there is something wrong. Then it won't even let me open the SQL to fix the problem. Ever had an error "Invalid Bracketing" on a simple subquery? It should allow me to disable this auto-coder or error to check the SQL. Now I'm stuck with query objects that work fine but can't be edited. Garbage!

RayJeff
RayJeff

I've asked this question over the years on several other sites as it relates to developing user interface applications with Access. The typical response has been that it comes down to personal preference. When I started the developing the user interface for the Access database I created, I went with VB6 for many reasons. One, was I had no experience using VB.NET. Two, was that I was already much more familiar with VB6, so the learning curve wasn't there. Three and most importantly, I had no experience with VBA. And while I'm sure the learning curve would not have been an issue, time constraints with the project I was working on didn't allow me the time to learn VBA. It's interesting that one of the database specialists I consulted with on the development of my database developed the user interface application for the database at her institution using VBA. It was apparent the specialist has much experience with VBA. But, when I asked about the same or similar parts of her application to mine and to transpose them, well, I couldn't get any help because she had no experience with VB. So, I would say from my view, using VB or VBA with Access depends on several factors, with the main factor depending on comprehensive the application will be. Not to say that a VBA application cannot be very comprehensive, because I was in awe while reviewing the database specialists applications using VBA. But, when you enter the limitations VBA has, it comes down again to personal choice.

RayJeff
RayJeff

I haven't read the comments yet, but I had to get my experience out with Access. I was hired to work for the Education division of a small liberal arts college in 2003. My main duties were to provide help desk/end-user support of the division, students, other faculty and run a computer lab. What I didn't know at the time of being interviewed that the Teacher Education program was goign through a national accreditation. One of the parts of the accreditation is that the program information had to be transferred to an electronic storage system-basically all information has to go from paper to a database. Now, during my interviews for the job, my former supervisor mentioned offhandedly about needing to have a database to store student data. Within the time of my last interview until I was hired, I came up with a quick template based on the little bit of information I got. While my IT experience was basically in tech support, in my last job before it, I created log tracking "databases" Excel and I messed around with creating a small Access database to store the combined data from the Excel spreadsheets. But, what help me out was I was in school at the same time and I had just finished a 2 class sequence in Java. So, my mindset was already in programmer/developer mode I had to use Access for many reasons. What I didn't know about the accreditation process at the time is that it's 5 year process. I got hired at the end of year 3; SO, I had alot of catching up to do. There was no kind of documentation in place or available. Because I worked in a division of non-technical people, they didn't know what to do. They knew that they needed a database, but didn't know how to even begin to setup requirements for the database, much less the actual information going into it. So, the role of programmer was the last thing; so, I truly became a developer. With all of these things, Access was the only system I could use in the time I had left to develop and use. Oh, did I forgot to say I had to develop a user interface at the same time for the faculty to use, eventually down the line. And one last thing; I had no one to work with me on this. I did all the work myself. There was no group of developers/programmers/database people to work with me or assist me. I did correspond with and visited with database specialists at other schools who had gone through the accreditation process, so that helped me greatly. What also made Access perfect was because actual information going in. The students going into the Teacher Education Unit (program) each year is very small. Students are tracked from the moment they declare Teacher Education a s a major until they have graduated with their degree and are actually teaching. Now, while in college, the actual number of students declines from the time students declare the major for whatever reasons ( changing majors or into enrolled at the college). So, Access is perfect for that as far as the small number of students entered into the database. But, the eventual problem is the exponential rise of information for each student. So in that case, a database system to accommodate that was necessary in the near future. And if i was still at the college, I would've made the move to SQL Server. Well, to make a very long story short, was the accreditation process, with all of its issues went great and the program got accredited. And one of the main reasons why because was able to get a database setup up and running, because the database had to be reviewed by the accreditation body. Don't get me wrong, I made many mistakes, but they were the mistakes of a person who was a first time database developer, and I have long learned my lessons.

gordon
gordon

Reason #1^H 0: It uses VBA. Let's say you need to check the date of a file before importing it. You use FileDateTime(path). Now, if the file does not exist, it throws an exception. OK, you say; I'll test for FileExists(path). Wrong! There is no FileExists function in VBA. Let's say you want to use a hash to hold key,value pairs, so that you can address things like col = hash("name"). VBA has a crippled, brain-dead implementation called a collection. You can write hash.add "name",3. But if "name" already exist, it throws an exception. Think you can check if hash("name") exists? WRONG! See #1 above. Let me remind you that this is Version 6.5 of VBA. 6.5 Major Versions and they haven't figured out that people need to check if things exist in a language that throws up when they don't??? #1 VBA error messages Have nothing to do with the actual error. #2 Microsoft SQL: Will NOT tell you where in your long SQL command it gets lost. It knows. It just won't tell you. #3 Microsoft Help Let's say you want to do something you *know* is in Access or VBA, but can't remember it's name. For example, let's create a constant. Open the VBA help, go to the index and type "constant". No help found. OK, type "constants". Still no help. Now type "const". Bingo! You found it. Of course, if you knew what it was you wouldn't be looking in the help! And what kind of help can't find the plurals or singulars of common words? Does MS think that if I ask for help on "loops" I'm not interested in the loop statement? ARRRRGH!!!! Access and VBA are crimes for which Microsoft should be prosecuted.

roaming
roaming

What happens if it is stored on the local drive which has just died and they want it restored? As far as cost being a concern what about PostgreSQL and MySQL?

ferreirah@webmail.co.za
ferreirah@webmail.co.za

With Access 2007 you can deploy the runtime engine without having to buy Access. Also, this is now my second job where we are using SQL Server as a backend and Access as a front end. You get the stability of a client/server database with the ease of use of front end development. If you can't afford SQL Server use SQL Server Express or MySQL or Postgres. We got databases on SQL Server as well as AS400 which we manage thru Access. I dont think Access 2007 got multi user features but you can combine it with a client server database.

Tink!
Tink!

Personally I like Access. But that's because I've always worked in smaller companies where the user base and deployment is very small. Because of this I am able to customize the Access Database to meet the required needs exactly. I've created several Access databases now that have been used exclusively within the companies I work(ed) for. Including 1 for taking messages at reception, 1 for keeping track of a bridal registry and am currently using 1 for tracking inventory with barcodes, and for tracking raw material costs. For larger applications and companies I can understand not wanting to use Access. It's a rather primitive and cumbersome application compared to other softwares. But for the smaller instances, I think it fits just fine.

djnewman
djnewman

I support and develop in MSAccess daily. When Access is used properly it can be a very robust solution and TCO can be very low compared to SQL Server or Oracle for the right projects. Things to consider: 1) MS Access is the front end application layer. It can connect to any number of databases from many vendors, SQL server, Oracle, DB2 etc. Pretty much anything that can use ODBC can be used with Access. I dare you to come up with a reporting solution that is as easy to work with as Access and can create as complex a report in as short a development time frame. Crystal Reports is no where near as capable, and even SQL reporting services is not there yet. With the added benefit of stored queries and VBA, it's a solution that should be considered for any Non Enterprise problem. 2) Jet databases are separate from Access and can be used with many other front ends such as Visual Basic, ASP, and even .NET. Jet databases are fine as long as you don't cross network boundries and you can live with minimal security. For example, they make great local databases for storing program components like queries and reports. Put the data in a secure place like SQL server where it belongs. If you step outside of these boundries, you are asking for trouble. Finally, any company that lets the secretarial staff create databases for any purpose is asking for trouble.

jmgarvin
jmgarvin

It's a nightmare to deal with. It's got too many issues with security and with scaling. The biggest problem is that the users can do things (like delete a db) when they shouldn't be able to. There is no reason to use access unless you want to federate all your data.

50-50
50-50

In my former life I supported end-user computing, both user-developed database applications and end-user reporting from enterprise application data. Now I'm a DBA, looking at database applications from the other side. The biggest problem with MS-Access from my current point of view is the lousy way it behaves when using ODBC or OLE to connect to a remote database. Access has a bad habit of turning the remote database into a single-user-at-a-time resource. It isn't very smart when generating SQL. It often prefers to do operations locally that would be better done inside the remote RDBMS. However, because it comes "free" with Office-Pro in many companies' standard PC image while real report writers cost real money, it becomes the default solution for creating "simple end-user reports" even though it's not a good report writer and many end-user reports are anything but simple.

blarman
blarman

#1. Growth. The problem with this mentality is that you have someone who develops an application using Access that did it without other approval (especially input from IT). This bespeaks an internal management/controls issue much more than an IT issue. #2. See #1 above. The issue here is the company's approved internal development policy. #3. It isn't. Get over it. Access is for SMALL workgroups with a VERY low number of concurrent transactions (2/second) and for databases

Jimmy Lin
Jimmy Lin

I think the main misunderstanding about access is that it is not a server applicaion, but a desktop application. It is not designed for multiple people to run it from a server at the same time which is the cause of the corruption problem. The solution we found was to detach the access front-end from the access back-end, upconvert the access backend to SQL Server, and attach the access front-end to the SQL Server back-end via ODBC. We put a copy of the access front-end on each desktop and in doing so have never had a corrupted front-end since and we have been doing this for about 6 years now. This raises the issue of making changes to the access front-end since everybody has their own copy. We chose to use the microsoft installer from visual studio to create an install package for our users to update their computers whenever we make changes to the front-end. You just have to remember that any queries created by users in their own copy of the front-end are lost unless you first import them into the new front-end before creating the install package.

John Dickey
John Dickey

Having been around and used Access since the beginning I have experienced all of the good and bad of it. For myself Access provided and still does today an easy to use tool for RAD and proof of concept at relatively low cost. I have used Access in front end back end combination since the beginning and more recently through my consulting have seen increasing requests to turn applications that companies have developed (Note: most are 50 or less employees with usually 5 to 10 users with revenues of 20M US or less) into web based applications. For the majority of these small businesses the move makes sense. However, when I see that the demand will increase or what they are presently using the database for makes them liable to HIPPA or SOX, I take the time to educate the clients and move them to a SQL Express or MS SQL backend. As to specific points... #1 it grew... true it happens, but on the other hand most organizations that I have worked with would never have commited the resources to a costlier soulution from the get go. #3 ...not professional ... well I once worked for a company where one of the owners though that... but then again he felt foxpro 2.1 was the end all be all too... when I showed him how Access could be used to address dedicated solutions in minimal time he changed his tune #11 poor security ... that one I have to agree with especially in view of the fact that the security model is not even a part of 2007. In light of the very real issues today of data theft and privacy Access should never be used in those situations. All of the above being said, I personally believe Access today still has a position to fill in an organization. The key is to evaluate what it is being used for and why the business model prevents a different solution from being used.

ChrisHyche@AlabamaOne.Org
ChrisHyche@AlabamaOne.Org

...it requires write access to the location it is stored! I can't tell you how many time users had "accidentally" moved or deleted a common use Access database at my last place. Fortunately at the time we had Netware with the excellent Salvage command(pre-shadow copy).

Underground_In_TN
Underground_In_TN

Loading business data from all the various Access databases that build up over time gets tricky, usually takes a lot of time and dollars to develop and are often overlooked simply because only a few people know about them. That negates #7 above, as actual ROI begins to shrink from what it first appeared to be with that $5000 initial develop cost.

marie-noelle.baechler
marie-noelle.baechler

I have seen quite a few cases where the sum of all user needs is far greater than what the IT department can do with its budget (and people). So, there are many cases where the developpment of applications by users is unavoidable. But it seems to me that proposing appropriate solutions for these developpments is extremely important if IT deparmtments want to avoid to face emergencies, when one of these developpment fails or when a critical user leaves. Another important point is that the fact that an application is used by a very small group (less than 10 people) does not necessarily mean that this application (and the corresponding datas) are not critical for the whole company (administration, NGO, ...). I used Access in similar situations in the past and I saw several very important drawbacks. some of the most important were corrupt databases and failures to migrate access applications from one version of access to another one. It is not very complicated to create an instance of an enterprise databases (with your preferred RDBMS) dedicated to the storage of tables and datas of end user developpments. Thus, user datas will at least be stored and managed according to the guidelines of the company. In addition, the IT department will have an easier job if an optimization or a repair is needed. It will also make easier to handle problems related with data integration, concurrent access, security and so on. But it leaves the "user knowledge" on the desktop in a fragile container. I have obviously no miracle solution to this problem. I just notice that there are alternatives to access (for example ORACLE Application express, FileMaker, 4D, and so on), which can also be used with a remote database. It may make sense to examine these alternatives in order to see wether one of them would be more appropriate for the needs of a given company. Introducing it may be easier if the IT department is able to give some support for applications developped with it.

CharlieSpencer
CharlieSpencer

I've seen this a few times. Someone thinks they need to manage some related data and uses Access to create a database. They require others to enter data, occasionally going to the effort to create entry forms. After a couple of years this person leaves. Sometimes his replacement will ask for changes to the database or forms, requiring IT to allocate resources. More frequently the replacement has no use for the data at all, and those formerly required to enter the data start popping the champagne corks.

Tony Hopkinson
Tony Hopkinson

#1: It grew! Actually, I've seen this a lot and to that I developed some self defense techniques to ease the transition. Just simple things like if you have a standard entity identifier in the 'full' systems, use it. Access is a very good prototyping system, try and get as much reuse out of it as you can. All it takes is an hour or so with the relevant people before you crack one off. Get your super user in the loop, saves extraordinary amounts of effort later on. #2: I have to support what? So make it easy on yourself, set up a culture where IT facilitates access's use. #3: Access isn???t a professional database Professional, yes. Client server no. The thing to remember is epecially if you've been stretching acess's envelope, the optimisations for a dsktop an are near inverses of those you would do for client server. #4: VBA isn???t a pure language Well personally I think VB sucks. VB.net is better, but it could hardly be worse. As for VBA, again it's the generally amateurish nature of the code that's the problem, not the choice of language. Can't really point fingers, I haven't seen a language yet that someone hasn't wrote poor code in. #5: It???s too easy Easy isn't the problem, dumbed down is. There are many access developers who's knowledge of SQL is well limited, some none at all. #6: If you build it, something will corrupt it Used correctly. Indeed, but all systems worth maintaining eventually stretch the envelope. The problem with access is it will snap with no built in facility for sensible recovery. #7: Money ??? ???nuff said The cost of the DBMS is neglible in a high end system, in fact with PostGre or MySQl it (apart from an admin) it can be nil. To compare the price of a single copy of access and a bit of diskspace, to clustered server farm is disegenuous at best. Now if you were going to say compare SQL Server Express, a mid range server and a .Net developer, and you are already rolling out access, then maybe a point. But even then I could argue the ongoing support costs sucessfully, if the IT resource was already there. #8: Access isn???t an enterprise solution No it isn't. The only way to get round that is to hide it behind client server functionality. That's either re-inventing a lot of wheels or doing a web based front end which is re-inventing a lot of wheels. By enterprise level I mean number of simultaneous transactions, or data volumes in the giga btye range. #9: File-server applications are inferior Eh, access does all it's processing on the client. Client server was invented for a reason, not just to make file server based apps look silly. They are inferior if you need client server functionality. You can try to cope, but again scalability will slap you right across the face. #10: Access is hard to deploy Web based is meant to avoid all sorts of deployment issues. Deploying access vs an inhouse application, six of one, half a dozen of the other, depending on the size of the app. #11: Poor security The lack of control in terms of privileges is inherrent in it's design, aside from hiding it if you need this, don't use access. Acess' reputation with 'erm 'professionals' is not from their lack of understanding of it's uses, but from repeated abuse of them by the general run of it's users. Lend them some help, see access as a protyping type RAD tool with minimal resource costs to IT itself, it can be a boon. A nice neat set of umambiguous requirements, what more could a pro ask for? As soon as you see your access system start to fail, start amassing your arguments for using a higher end tool. If your solution is a success there should be no problem at all in doing so.

blogpost
blogpost

I see two major problems with this article: 1. it fails to distinguish Access as front end and Access (Jet) as data store. Access can be an enterprise-level front end to any database that provides an Access-compatible data interface (ODBC, ADO, etc.). 2. Actually, #1 is the main problem with the article, as the security issue, for instance, is only an issue when you are using a Jet back end. Likewise, corruption. This is precisely the area where most IT opponents of Access are ignorant -- they think of Access as "a monolithic MDB/ACCDB file storing data and application objects" when what they should be thinking of is an MDB/ACCDB storing front end objects that interface with your choice of data store (Jet MDB, Jet ACE, SQL Server, MySQL, Oracle, Sybase, whatever...). -- David W. Fenton David Fenton Associates http://dfenton.com/DFA/

RationalGuy
RationalGuy

I love Access. I agree with most of the reasons you mention, but I still use it all of the time. It fits as the best solution in a variety of situations. For me, it's best when Excel is just too cumbersome, and I'll be doing a lot of VBA coding for functionality. Access allows you to build functioning applications pretty much friction-free, that can grow to be quite complicated and feature-rich. I once wrote an Access database to automated a large percentage of a global GroupWise to Exchange e-mail migration. The platform is very versatile and packed full of functionality. However, Access can fall prey to the things you mention. At that point however, buy-in to upgrade to a better platform is usually simple. The application has already proven its value. The data and structure can be ported to SQL Server in minutes, even with a large number of records. That alone is usually enough to stabilize the app until a new front-end is built to connect with the data. Also, since you've already written the necessary application functions, even if you're porting to a different language, the logic already worked out. Generally, it's just a matter of finding the correct syntax to accomplish the same thing in the new language.

zhenchyld
zhenchyld

I personally love Access. It's allowed me to deploy a 6 user call center solution that's been running the same branch of a business for 3 years, and the initial overhead incurred was something like $200 (lol!). Plus I can go in an customize reporting features for their different clients whenever they need it. The biggest complaint I hear is it's not scalable. However, if you split the backend into multiple db's you have a 2GB table-size limit that's pretty difficult to hit in most situations where a high-end system isn't a more preferable choice. Even though the Jet DB engine is a bit laggy over a LAN, if you keep recordset size in mind during the planning and design phase performance can be passable. The only place I will say Access is lacking is in security. I wouldn't really even consider Access security as 'security' at all in most cases, the only thing I use it for is to log changes by username. But if the DB isn't web-exposed and doesn't have any sensitive data, who cares? In some circumstances (especially circumstances a lot of small businesses find themselves in where they need a db solution but can't justify the cost of a server-based SQL engine and an expensive frontend), Access just can't be beat.

algis
algis

@zhenchyld  Another key benefit of Access is portability. You can have a pretty sophisticated single-user database in one file and transport it anywhere you go on a thumb drive. Try doing that with a C# .Net / SQL Server solution. It's also perfect for very small businesses which do not have a server.

Editor's Picks