By Richard Rost 9 months ago
Addressing the Common Criticisms of Access
Hi. This is Richard Rost from Access Learning Zone. As you probably know, I'm a strong proponent, supporter, and evangelist for Microsoft Access. I think it's one of the best, if not the best, overall database solution for businesses of all sizes. By itself it can manage the data needs of most small to mid-sized businesses. Couple it with SQL Server, and you have a strong, secure database solution that can handle pretty much anything.
Since I posted my first video, I've received several emails from people saying that Access has this problem or that problem. Sure, Access does have some weak points. So in this video, I'm going to address some of the common criticisms that people have about Access.
In addition, while doing my research for this video (and the first one), I found several web sites put up by people claiming to be database consultants. It was very easy for me to tell that they didn't have much knowledge of Access and what its capabilities are, or they are consultants trying to sell some super-expensive "enterprise-level" database solution as an alternative to Access. I'm not surprised.
So whether you're a fan of Access and just arguing with your friends, or you're trying to convince the boss that Access really is good database, or you're having to deal with the IT guy who absolutely refuses to let you install Access on your PC, or you've got the expensive so-called "consultant" giving you false information to push a high-priced solution, this video is for you.
Problems For All Databases. Now, I'm not including criticisms about things that are problems for all database platforms. For example, one person complained that you have to make sure that you backup your Microsoft Access database files. Duh. You need to make sure you have good backup solution for any database system. And of course, I've got free videos showing you how to properly backup your Access database. https://599cd.com/Backup
Upgrading Issues. Someone else complained that you could encounter problems upgrading from one version to another. He said he experienced all kinds of issues migrating from Access 2003 to 2016. Well of course. That's a huge difference in versions, and you're going to encounter issues like that with any software application, especially if you wait 13 years to upgrade. The move from Access 2003 to 2007 was a major change. Aside from that, I've had very little problems upgrading from one version to the next. That's one of the reasons why I love the new Microsoft 365 subscription model. You get constant updates as they're released, and you don't need to wait for the next major version to come out.
Access Discontinued. The biggest complaint I always get, and I see this quite often, is that someone told me that someone else they talked to read online somewhere that their cousin said that Microsoft is discontining Access. No. Nothing could be further from the truth. Microsoft has repeatedly said that it has no plans to retire Access any time soon. It will be here for the foreseeable future. I've got a whole video covering this: https://599cd.com/AccessGoingAway
Web Apps. Microsoft did retire Access Web Apps which was their attempt to take Access and move it up to the web. It didn't work well and they got rid of it. I've actually seen some other websites refer to this as "Microsoft is discontinuing Access," which is untrue and misleading. Complete details and links are in my other video.
You Have to Pay for Access. Someone actually complained to me that Access is a fee-based program, in other words you have to pay for it. Yeah. Sorry. It's a business application. While there are other programs out there like MySQL or even SQL Server Express that are free, the old adage "you get what you pay for" is certainly true. For all of the extra benefits Access provides, it's worth the money. And if your business can't afford $12-something per month for a professional-quality database application, then maybe you should go back to Excel. Plus, remember, only ONE person on your network has to pay for Access: the Developer. Everyone else can use the FREE Runtime Edition. https://599cd.com/Runtime
2 GB Max Per Database. This criticism is true. The maximum size of any one single Access database is 2 GB. However, you can link together multiple files to have virtually unlimited size. This then makes the maximum size of any one table to be 2 GB, but if you've got tables that large you need to either do some scrubbing, optimize them, split them, or perhaps it is time to upscale to SQL Server for your back-end, which is actually then a plus of Access. The vast majority of Access users will never need more than 2 GB of space for a single table. I've never seen it in 27 years of teaching and consulting. https://599cd.com/Split
Limited SQL. The SQL in Access isn't as "robust" as Oracle or SQL Server. That is true. However it's good enough for the vast majority of the needs of users. Again, I've never run into a circumstance where I couldn't do something in Access because of limited SQL. However, again, if you do ever decide to upgrade to SQL Server, you can use something called a pass-thru query which allows you to run a query on the server, taking full advantage of the server's language and power. https://599cd.com/SQL
Multi-User Limitations. The maximum number of users connected simultaneously to an Access database is 255. Realistically, that number is actually closer to 10 to 20, depending on network conditions. As I've said several times, Access is a great solution for small and mid-sized companies. However, even if you've got a giant corporation with 10,000 people who need to work with your data, a Microsoft Access front-end is the perfect tool for building an interface for an SQL Server back-end. Some developers complain that it's a pain to run around and install new copies of an Access database front-end on 10,000 machines. Yeah, it is. But, I've got a tool that makes it super easy to distribute your updated front-end to all of your users with just a single click. https://599cd.com/Updater
Access is Slow on a Network. This can be a valid complaint if you've got a very large database. Even if you've only got a couple of users on your network, an Access database can still be slow if you're running very large reports or generating complex queries with many thousands of records. It's true. Access isn't a database server like SQL Server. What this means is that if you have two computers sharing the same database file, one of those computers is acting as a de-facto server, but it's not really a server. All of the processing of the data happens "over the wire." So if the user on the 2nd PC decides to run a query to see just customers from Florida, that PC still has to pull down all of the records from the "server" PC and then figure out which records to display. In contrast, SQL Server figures out what records you need and only sends those few customers over the wire. The result is a much faster response time. So if you've got a lot of data that's going to be transmitted over the network, you may want to consider using SQL Server along with Access. Now, speaking from personal experience, this has almost never been a problem for me. I've build lots of Access-only solutions for dozens of clients that run just fine without a server. In my own office, I have 2 PCs running Access. I've got over 50,000 customers, and my two systems run just fine. But again, if you start out with Access and decide later that you've outgrown it, it's very easy to upgrade to SQL Server later. https://599cd.com/AccessSQL
Images and Multimedia Files. Someone complained that Access isn't good for storing files. It is true that Access doesn't store images, videos, documents, or other multimedia files well. However, it's not supposed to! You shouldn't store files like that inside your database. you should store them in a file server, in a dedicated folder. Then you store the location (path/filename) of each file in the database. https://599cd.com/Image and https://599cd.com/ABCD5
Weak Security. It is true that out of the box, Access doesn't offer much security. You can assign a database password, hide the Navigation Pane, and even distribute an encrypted front-end ACCDE file to your users. However, if you need to make sure that users can't get access to data they shouldn't see, then it takes a little extra knowhow and configuration to properly secure your database. In my Security Seminar I show you how to lock down the "program" part of your database. This will prevent people from seeing/chaging the design of your forms, reports, and VBA code. I also show you how to take measures that are "good enough" for keeping people out of your data. These techniques are good enough for the average office worker. However, if you need hacker-level security for your data, then just move up to SQL Server for your back-end. Problem solved. https://599cd.com/Security - https://599cd.com/SimpleSecurity
Only Available for Windows. There is no version of Access for Linux or the Mac. Yeah, you've got me there. However, my right-hand man, Alex, says it's possible using Parallels. And of course you can use a remote desktop solution or a virtual PC to connect to from your Mac.
High Learning Curve. If you're comparing Microsoft Access to a program like Excel, then yes, there is a steeper learning curve involved. It takes more knowledge to properly set up an Access database than it does to just open a blank Excel spreadsheet and start banging away at the keyboard. However, if you're comparing Access to other database applications, then Access is by far the easiest to learn how to use. I've worked with many of them. Believe me. Access is a piece of cake.
Coding Too Complex. Someone actually complained to me that programming for Microsoft Access was too complex. Again, if you're comparing Access VBA to other programming languages like C/C++ or something along those lines, then VBA is a walk in the park. Plus, you can build a really good Access database without a shred of VBA coding. I've got 9 Beginner lessons and 32 Expert lessons where I don't use any programming at all and I'm still able to teach you how to make some pretty cool databases. However, even if you only learn a little tiny bit of VBA, you can really take your databases to the next level. But complex to program in? Hardly. If you know how to program in anything else, you'll find that VBA is simple by comparison. You just have to learn the little bit of Access-specific add-ons to the language like OpenForm, GoToControl, and so on. Watch my Intro to VBA video to see just how easy it is. https://599cd.com/IntroVBA
No Web Version. That is a valid complaint. There is no easy way to put your Access database on the web for other people to use. There are lots of different options for sharing your data, and I've got videos covering most of them. However they all involve a little bit of setup. You can use a remote desktop solution to share your database remotely, however any serious sharing over the Internet involves moving your tables up to SharePoint or SQL Server. Again, it's not hard, and I've got videos showing how to do it step-by-step, but it is something that Access can't do by itself. Just keep in mind that when your database grows to the point where you want to share it online, a solution is available. https://599cd.com/AcOnWeb - https://599cd.com/AccessOnPhone - https://599cd.com/SharePoint - https://599cd.com/AccessSQL
Difficult to Support. A few IT helpdesk guys complained to me that they hate Access. It's their job to support the users in their company. The users build these monstrosities in Access. They then go to the helpdesk guy for help. Now the poor helpdesk guy has to try to fix the mess. Well, the problem here is simply that these users (the IT guy included) lack training. If they knew how to build databases the right way, they wouldn't be getting into such messes, and going to the helpdesk guy for support. This isn't a problem with Access. It's just a lack of education. But, it's a complaint I do hear from time to time. IT guys hate Access because they see what untrained office workers build with it.
Access Corruption. Some people have complained to me that Access database files can get corrupted very easily. If you have a situation where a user gets disconnected from the network, or is in the middle of editing a record and the power goes out, for example, the ACCDB file can become corrupt. Yeah, this is a possibility for any database, really. As long as your network is fairly reliable, it shouldn't be an issue most of the time. The first thing to do is make sure you have good nightly backups. That's very important. If your database does become corrupt, try a Compact and Repair. In my nearly 30-years of working with Access, I've only had it happen once or twice where Access couldn't repair a data file, and that was before 2007. Since the new ACCDB file format was released, I haven't had a single lost database file. https://599cd.com/Compact
Proprietary. Access is designed by Microsoft and their format is proprietary. Some people want open-source solutions so they can modify them. OK. That's a valid criticism. However, Access is extremely customizeable as it is. You have complete control over the design of your database. As far as the Access program itself, there are very few things I would want to change if I could. Oh, I do have a list, but most of those things are minor nit-picks. Besides, most people want to be able to customize their database, not build a completely new database program.
Not a "Real" Database. A lot of software engineers and database "purists" say that Access isn't a "real" database. It allows things that most professional databases wouldn't. For example, multi-valued fields. Those go completely against the rules of a normalized relational database. I agree with that point. Multi-valued fields are evil. In fact, I have a whole list of things that I consider evil in Microsoft Access. You can find the full list here: https://599cd.com/Evil. I also consider using spaces in your object and field names evil. So is using the Hyperlink, OLE Object, or Attachment data types. So yeah, there are some things in Access that you shouldn't do. Microsoft added some of those features to make it easy for beginners and database newbies to do certain things that are normally pretty complex. You can use a multi-valued field to create a drop-down list of options instead of properly creating a relational combo box using data from a 2nd related table. Although you shouldn't do these things, they're possible. Again, this just comes down to proper training. Don't blame the software. Blame the user. PBKAC. Error ID-10-T.
Can't Be Distributed as an EXE. This one is a valid complaint. You can't take the Access database that you just spent the past year developing and easily distribute it in a form that an end user can install by simply clicking an EXE installer. You can release your database as an encrypted ACCDE file, and have your end users install the free Access Runtime edition, so they don't have to pay for a copy of Microsoft Access, but it's still not the same as a professional installer application. Access used to come with something called the "Developer Extensions" that let you do this. I believe Microsoft stopped releasing that in 2003. In the past, I have used programs like InstallShield to create distributions for Access databases that would package up all of the needed components and make a nice installer for you. However, I haven't used that in years. You can create a distribution package with Windows Installer, but it's not easy to do. A quick Google search shows that there are some other 3rd party solutions available, but I haven't used any of them, so I can't recommend one. In my opinion, this is something that Microsoft could definitely improve upon. Adding the option for Access developers to quickly and easily package and deploy their databases for distribution would be a nice feature. https://599cd.com/Runtime
Scheduled Jobs. One person emailed me complaining that Access doesn't have triggers, or the ability to schedule jobs to run at specific times. Uh, yeah it does. You just have to know how to program a Timer event. Here's a video on it: https://599cd.com/ReminderPopup
Conclusion. While there may be other high-end database platforms out there, Microsoft Access is easy to learn and inexpensive, but is also very capable of growing with you. It's extremely customizable and scalable, so as your business grows, your database can grow too.
Learn More. Want to learn more? Visit my web site using one of the many links provided on the topics discussed in this video, and of course if you have any questions, please feel free to contact me or post them in the comments section below this video.
Free Training. Also, be sure to watch my free 4-hour long Access Beginner Level 1 course. It will teach you all of the basics of getting up and running in Microsoft Access. https://599cd.com/ACB1
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, microsoft access criticisms, complaints about microsoft access, pros and cons of microsoft access, problems with microsoft access, issues with microsoft access, disadvantages of microsoft access
You may want to read these articles from the 599CD News: