Quick Queries #66
By Richard Rost
2 months ago
Why Are Multi-User Databases Prone to Corruption? In this Microsoft Access tutorial, we'll talk about why multi-user Access databases are prone to corruption, including the role of network reliability, file-based architecture, and the benefits of migrating to SQL Server. I'll answer viewer questions about issues like Access corruption, indexing foreign keys, differences with FileMaker, and data backup strategies. We'll also discuss practical troubleshooting tips for common problems, address frequent user concerns, and cover topics like storage media longevity, subforms, report issues, and form events firing order. This video is part of my Quick Queries series. Allen from London, England (a Platinum Member) asks: Why do some Access databases run for years with no problems while others corrupt all the time? I have been an Access consultant for almost 10 years, and I have seen small offices with only a few users that corrupt constantly, while larger setups with 10 or more users never have an issue. What really causes Access corruption, why does it seem so random, and what is the solution? PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, multi-user database corruption, compact and repair, network reliability, SQL Server migration, file-based system, concurrent users, wired network, Wi-Fi instability, backend ACCDB, transaction log, indexing foreign keys, antivirus scanning, network power saving, Windows offline files, storage media lifespan, form events order
Intro
In this video, we'll talk about why Microsoft Access multi-user databases are often prone to corruption, what really causes these issues, and how network reliability and setup can play a major role. We'll discuss the differences between using Access as a file-based system versus upgrading to SQL Server for better stability and security, plus answer YouTube user questions on topics like troubleshooting error messages, best practices for indexing in Access, choosing backup methods, and other database-related issues.
Transcript
Today we're going to talk about why Microsoft Access multi-user databases are so prone to corruption, not like taking bribes corruption, like you see on Capitol Hill. We're talking about corrupted database files, like things don't work. That kind of stuff.
You go to access your database and it's like, uh-uh, can't get in. Nope, not today. Then you have to compact and repair and do all kinds of crazy stuff. It is a pain. I know.
So we're going to talk about that first and then get to your YouTube comments and a lot more. So let's get to it.
Today's first question comes from Ellen in London, England, one of my platinum members. Ellen says, "Why do some Access databases run for years with no problems while others corrupt all the time? I've been an Access consultant for almost 10 years, and I've seen small offices with only a few users that corrupt constantly, while larger setups with 10 or more users never have an issue. What really causes Access corruption? Why does it seem so random, and what's the solution?"
Well, this is one of those things that could have many different reasons. Access corruption usually isn't random and almost always comes from the fact that Access is a file-based system where every workstation on the network is opening and writing directly to that shared ACCDB file on the back end.
That means the quality of the network matters far more than just the number of users. Microsoft says you can theoretically have up to 255 users. No way. I would say concurrent users - 20 is probably pushing it. Now, I've set up offices where they had 30 or 40 workstations that had access to the database, but maybe only 10 people at a time were using it and it ran fine.
I've also seen tiny offices where they were trying to run it over Wi-Fi, and that was far more dangerous than the big office with the solid network. So the best solution is to make sure their network is solid first. That goes without saying. Make sure no one is using Wi-Fi, and the best solution is to set up SQL Server for them if they've got a lot of users.
SQL Server avoids this problem entirely because only the server ever touches the data files, and it uses a transaction log to keep everything consistent. So even if a client drops out, even if they've got someone on Wi-Fi, their computer that's on Wi-Fi isn't directly touching those files on the server; it's asking the server to give it information. That's why one environment can be bulletproof with SQL Server and then there's a mess if people are just talking to Access directly.
And for those of you who don't know how it works exactly, well, Access is a file-based system. That means your database, the ACCDB file, lives out there on a shared network drive, and every user's copy of Access, the front end, opens that same file directly.
So when you add or edit a record, Access goes into the file and changes just that small part. It's like opening a Word document and changing just one paragraph instead of rewriting the whole book. But every user is working with different paragraphs in the same Word document. See how it gets kind of confusing?
This works fine when the file is sitting on your own computer, or on a stable wired network. But if there's a hiccup, like a Wi-Fi dropout, someone losing a connection mid-save, a laptop or network card goes to sleep, or someone kicks their computer during a write and knocks the network cable out of the back, something like that, then Access might not finish writing that change. Since all users are sharing the same file, one bad write can mess up part of the database and cause corruption.
Access doesn't just go corrupt for no reason. It's almost always a networking or a concurrency issue.
Now SQL Server, on the other hand, is a server-based system. Instead of every user opening the data file directly, only the server itself touches the files. Your Access front end, or any other app, just sends SQL Server a command, and then the server, based on that command, reads or writes the data and sends back the results. The client workstations never touch the actual files on the hard drive.
SQL Server also has a transaction log that records every change before it happens. If the power goes out, a connection drops, or even if 50 users are connected to it, SQL Server can still roll back safely, and that's why corruption is extremely rare. Plus, SQL Server has a lot of other benefits, like it can be much, much faster. For example, if you're doing a very big report, your workstation has to get all of the records down from the table before it can find just the ones you need.
SQL Server is also a lot more secure. It has actual proper security, whereas Access does not.
Access is great for quick development on smaller projects where everything stays local. You have two, three, five people using Access on a stable, wired network, you'll probably never have a problem. Like I said, I've installed setups where they had 20 or 30 people connected to the database. It depends on the amount of concurrent reading and writing, but I made sure it was a properly set up network, good network cabling, new switches, all that stuff. I've had setups like that that have run literally for decades without a single corruption issue, more than a basic compact and repair couldn't fix.
But as your database grows, and more people start using it at the same time, or you want to add remote users, now the risk goes up. You've got Joe who wants to work in the break room on the Wi-Fi. You don't want to mind your Access database. As your concurrency grows into the double digits, 10, 20, 30 users, that's when you really want to start thinking about SQL Server for your back end. Technically, Access can still work, but all those computers are constantly reading and writing to the same shared file, and every new connection increases the chance that something will go wrong and your database will corrupt.
The key issue here really isn't the number of users, but how reliable the network is and how heavy each user's workload is. SQL Server fixes that by changing how the data is handled. Instead of letting every workstation reach directly into the file, the ACCDB file, SQL Server stands out in front like a gatekeeper, and each user sends a request to the server, which safely reads and writes the data and returns only what was asked for. If the client drops out, or if there's noise on the network, or if Joe kicks his computer and accidentally turns it off in the middle of a write, that request might get lost, but the server's data remains just fine.
One analogy that I like to use is a salad bar. Think of it like a salad bar. With Access, everybody walks up and grabs the lettuce with their own grubby little hands. Nasty. I've seen people do this too; it's disgusting. In fact, one time I was at a salad buffet, and I saw a kid drop the spoon on the floor, pick it up, lick it off, and stick it back in the salad bar. I was like, that's it. Check please. I'm out.
With SQL Server, you get a server who is the only person that touches the actual food on the salad bar. He's got nice clean hands. He's got tongs. You tell him what you want, he gives you back your tomatoes, your carrots, your ranch dressing. Nobody touches the salad bar except for him. That's SQL Server. He keeps things running smoothly, and there's no kids spitting in the... ugh, gross.
Now, if you want to learn more about SQL Server, I do have one seminar available right now. It's called my Access SQL Server Online. This one is for having an online version of SQL Server that's hosted by a web provider. This is good if you've got people in different locations, like different regional offices or people that want to work from home. You put your data in SQL Server, and everybody can connect with their Access database no matter where they are. This one's mostly dedicated to working with SQL Server Online.
I'm also working on another SQL Server course that is going to deal with working with SQL Server on premises. In other words, setting it up yourself in your office so you can work with your data right there in your own SQL Server. Nothing goes online. You get all the benefits of SQL Server, the security, the extra stability, all that stuff.
If you're interested, get on my mailing list. I'll put a link down below. As soon as it's released, I'll be sending out everybody an email. Of course, I'll be putting some videos here on my YouTube channel and on my website.
If SQL Server is not going to happen anytime soon, walk through my troubleshooter and check the usual suspects. Make sure you have good backups. Make sure you've compacted and repaired both the front and the back end on a regular schedule. Weekly is fine for most users, but it depends on how heavily you use the system. If you're reading and writing large chunks of data, you might need more compacting. Keep images and attachments out of the database; store those on a disk and link to them instead. Get everyone off of Wi-Fi. Replace any flaky switches. Make sure the back end is on a stable, wired network share.
If you do have problems, run down the list. I've tried to put these in the order of things that are more likely to come up. Make sure you don't have antivirus scanning your Access database folder. Make sure people aren't leaving the database open all night long. Try the simple things. Reboot the computer if it's not working. You've compacted and repaired it. Did you reboot the PC? You never know; it could be a memory leak, all kinds of stuff. Run down this list. There's lots and lots of stuff in here.
If you have problems, you can post them in my forums. So there you go, Ellen. Hope that answers your question. Access is remarkably stable. Like I said, I've had clients that have had 10 or 15 people using one of my Access databases for decades without problems. I still have this stuff. They're still out there. They still contact me once in a while. I try to help them out since they were clients for years, but I don't do consulting anymore.
People think that Access just randomly blows up, but there's usually some other underlying reason why your Access database blew up. Access has been around for a very, very long time. It is very stable.
Let's head over to the YouTubes and see what we got today. First up, we got Rivera's Clean Cut Lawn Care. Cool. I always hate it when people use no capitals in it. It's hard; you have to take a minute to decipher what it is. I love my camel casing or Pascal casing or whatever casing you want to call it.
Anyway, Rivera says, "How do you feel about other Access developers making videos on YouTube?" I love it. Short answer: I love it. As far as I'm concerned, the more people there are out there that are excited about Access, the better. I don't look at anyone like that as competition. There's plenty of views, plenty of viewers, plenty of topics, plenty of love to go around. If someone shows something in a different way than I do, that's great. I love learning from them too.
Full disclosure, I love YouTube for science stuff. I have a hard time watching other computer type tutorials. I love Star Talk with Neil deGrasse Tyson. I watch Cosmo, it's another space channel. When it comes to learning computer topics, I'm more of a book guy. I like a good book or an article. I like to sit down and read, experiment, take notes. Even though I produce videos, that's not how I personally learn best. I'm more of a reader.
For computer based tutorial videos in general, I usually see two extremes. On the one hand, you get people with great material, but they're dry or you can't understand them, or you start looking for excuses to go fold laundry instead of watching their video. Lots of those. On the other hand, you've got some creators who are fun and energetic, but they don't really know what they're talking about.
My goal is to try to land right in the middle. I want to be entertaining enough that you don't zone out, and that you actually want to listen to me, but solid enough with my stuff that you can trust what you're learning. I'm not the smartest guy out there, but I try to at least convey what I know in a way that makes it easy for people to understand. That's what I like to do.
Yes, I fully support anyone out there making Access content or any other kind of content. I watch some Excel videos. Mr. Excel's fantastic, and there are a lot of other creators that I like to watch. The more voices, the better. If they teach something in a different way that clicks for you, that's awesome. Not everybody likes my style and I get it. I'm not everyone's cup of tea. Whatever helps you learn is good in my book.
I'm always open to suggestions. If you have ideas for things that I could do to make my videos better, post them in the comments. I'd love to hear from you.
Next up, we've got John who is commenting on the video I did last week talking about the Windows features that might make your Access database lag. He says, "Interesting video on the side that mentions the probable issues, but without solutions."
Thanks for the feedback, but the video was meant to point people towards specific Windows features that can cause the slowdown and explain what to disable or check. Lock locks, SMB mismatch, offline files, indexing, antivirus scans, network power saving, using Wi-Fi. I gave a list of things to check. Each one is a solution, just not a full step-by-step walkthrough because that would make the video two hours long.
If you'd like a deeper how-to on any of them, then tell me which one and maybe I'll make a dedicated video for it. This is supposed to be TechHelp Quick Queries. Hopefully, I'm giving you a list of things that you can check, use the Google machine, and see if you can find instructions on how to do it there. I can't cover every possible solution, so I can just get you started, and that's what I try to do with these quick queries videos.
Next up, it's funny because some of the videos I never thought would have such long tails on them are the ones that I keep getting comments on. This video actually gets a lot of views. It's one that I did for Excel with just displaying lottery numbers.
I wanted to make this public record that Tee-da-hot boy said, "Thanks for the lesson. Once I hit it big, I'm going to look you up and make sure the money circulates." I'm taking that to mean you're going to hook me up once you do hit the lottery. So I'll take you up on that. Thank you. Or at least buy my course.
Next up, chitchatchitch asks, "How have you been out there? Does FileMaker have all the problems like Access?" I have to admit, I am not a FileMaker expert. I've used it briefly, probably 10 or 15 years ago, so I don't know a lot about FileMaker, but I do know it's still a file-based system at heart, so a lot of the same rules apply.
Anything that slows down or interrupts file access in Windows can cause trouble for either Access or FileMaker - network hiccups, Wi-Fi drops, anti-virus scans. The same things I just mentioned can also affect FileMaker the same way they affect Access. Just like Access, once you put the data behind a real database server, instead of having every workstation touch the files directly, most of those problems go away.
Again, I'm not a FileMaker expert, so I really don't know. Maybe someone else who knows FileMaker better than me can chime in, but the basic plumbing is similar enough to Access that the same best practices are going to apply.
Mr. Tom says, "Thank you for all your help, but I only get a SimTax error." It should be Syntax with an N - Syntax error missing operator in query expression. Well, Syntax error means you typed something wrong and since you spelled Syntax wrong, I'm guessing maybe you typed something else wrong in the query expression. So basically it's probably a spelling error. But without seeing your work, I can't tell you.
Remember back in high school when you came up with the wrong answer and the teacher said you had to show your work? I can't tell what you did, so I have no idea what the problem is. I'll need more information to help you. Why don't you post it in the forums on my website and then I'll have a better idea of what you have going on there.
I love hearing your success stories. Galfa Rep says, "Thank you for posting this." This is about my missing index video, Quick Queries 62. Make sure you index your foreign keys. He says, "Before, I changed one index from not being indexed to yes duplicates. I would run a query while using my Access program over a network. It literally took 90 seconds to run the macro. Yeah, I've been in that situation too. Changed one index using your method. When I run the macro now, it takes two seconds. I kid you not." I believe it. That's why I made the video. That's awesome. I'm glad that worked for you. Especially if you're working over a network because your local PC has to pull down all of those records and then sort through them if they're not indexed, so that's really slow. That's why indexing is a good thing.
A follow-up question from Galfa Rep: "If a table has more than one foreign key, do you index each one of the foreign keys in the table?" Yeah, I would. It can't hurt. If you've got an order table and it's got a foreign key to a vendor table and a customer table and whatever, yeah, index all those if you plan on doing lookups. Just remember, for every index, it's going to slow down updates. So if you have a customer as a foreign key, every time you add or edit a customer, it's going to have to update that index as far as that table is concerned. It speeds up searches and sorts, but it slows down updates. You've got to find a balance for your network and for your database.
Next up, Kerasnese says, "External SSDs or hard drives will die faster than DVDs." This is pertaining to my video, "Don't Lose Data." In that video, I was talking about CDRs and DVDRs, the ones that you read and write yourself at home, not pressed DVDs.
CDRs and DVDRs use organic dye that slowly breaks down from heat, humidity, and simple chemical decay, which is why they often fail long before a hard drive or SSD does. Pressed DVDs are a different animal entirely, but in that video, I was talking specifically about recordable discs. Those definitely rot. Hard drives and SSDs can fail too, but when they're stored normally and not abused, they generally keep data far longer than dye-based optical media, which is why archivists recommend migrating old CDR and DVDR backups to modern storage instead of trusting a 20-year-old burn disc. Will it be readable, as I mentioned in that video? I have a lot of old DVDs that I just recently tried copying, and some of them, sadly, are no longer readable, so my old copies of Duke Nukem and Starcraft from way back when are gone. I'll have to try and find them again. Sad.
As far as pressed DVDs go, factory-made ones, if they're stored well, cool, dry, and dark, they can last up to 100 years. They're pretty stable because they use a physical aluminum layer, not the organic dye that's in recordable discs. Still, they can suffer from layer separation or oxidation, but they last a lot longer than CDRs and DVDRs.
Physical hard drives usually last between 3 to 7 years before mechanical wear starts to become an issue. However, if a hard drive is stored on a shelf and powered up once or twice a year, it can usually last 10 to 20 years or more. The mechanical parts only wear out when they're spinning, and so archived drives that are handled gently can often beat the life expectancy of DVDRs. Of course, make sure you keep your magnets away from them.
SSDs, solid state drives, don't wear out mechanically, but their controllers can fail and the flash cells slowly lose charge when left unpowered. In practice, a quality SSD stored at room temperature can hold data for 5 to 10 years without power, sometimes longer. An SSD that is powered on periodically can run well over 10 years with normal use because modern SSDs have huge write endurance and good error correction.
Bottom line: pressed DVDs last the longest, hard drives next if stored carefully, solid state drives for several years. They're not ideal for deep, decades-long cold storage, but they're okay, and recordable CDRs and DVDRs are the worst of the bunch because that dye breaks down relatively quickly, which is the whole point of the original video.
This is why I recommend multiple backups and migrating your data every 5 to 10 years or so. Copy them to something else. Keep a copy in the cloud. Cloud storage is so cheap nowadays. Keep your local copies, and then I upload everything to Wasabi, which is a file storage platform that I use. So I've got backup copies of everything up there and it's cheap. They don't charge you for ingress or egress, only storage. I pay like 7 bucks a month for all of the videos on my website plus all my personal stuff. It's not expensive, and I've got, what, two terabytes with Google Drive.
Sev Faces says, "The way your dragging and dropping doesn't work." Well, if you see me doing it in the video, it worked for me. That's all I can really say. If you want help, I need to know more about what's happening. What are you doing? What's not working? What do you see? It doesn't help me help you, so I need more information.
Subforms: if I know what you're talking about, it's dragging a form off the navigation pane and dropping it into another form as a subform. That definitely works. That hasn't changed in, what, 10, 15, 20 years. I don't know.
New member Lisa says, "Late to the party here, but this is the best six bucks a month I ever committed to." I'm glad to hear that. "Why is my report only showing the first entry though?" Again, I can't tell without more information. I don't know what you did. I don't know what you have going on. I would love to help you. Why don't you post it on the forums on my website with some screenshots of what you did, and I'm sure the moderators and I can help you out. That's not enough information for me to go on to tell you what you did wrong.
Finally, today's Shadow Dragon asks, "Great video, Richard! Do you have a list of events and their firing order? I've done some quick searches, but there doesn't seem to be a one sheet list. Thanks."
Yeah, I do cover this in my Access Advanced Level 1 class. Advanced courses are the ones that deal with events and macros, and one of the things that I do cover, where is it, right here, is the form events. Here is the list of form events and when they fire. You got open, load, resize, activate, deactivate, unload, close. I go over all those different events in the forms, not all of them. There are some weird ones in there, even I never use. But most of them, and I teach which events do which things that you're going to want to accomplish. Which ones can be canceled and which can not, and stuff like that. So yeah, that's in Access Advanced Level 1.
You're right, I've been thinking about, one of these days, putting together a cheat sheet that goes over all of the different events and when they fire, when to use each one. So that's on my very, very long to-do list.
Speaking of to-do lists, this is one more thing I can check off, having finished it today. That's going to be your TechHelp Quick Queries video for today, Friday, November 14, 2025. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next time.
Quiz
Q1. What is the primary reason Microsoft Access databases are prone to corruption in multi-user environments?
A. Access databases are inherently unstable
B. Access is a file-based system where every workstation opens and writes directly to the shared data file
C. Access does not support concurrent users at all
D. Access databases become corrupted due to outdated software alone
Q2. According to the video, which has the GREATEST impact on Access database stability in a multi-user setup?
A. The number of users connected to the database
B. The age of the Access software
C. The quality and reliability of the network
D. The size of the Access file
Q3. Why is it more problematic to use Access databases over Wi-Fi than over wired connections?
A. Wi-Fi is always slower than wired networks
B. Access databases only allow one user at a time over Wi-Fi
C. Wi-Fi connections are less stable, increasing the risk of incomplete data writes and corruption
D. Users on Wi-Fi have fewer permissions in Access
Q4. What major advantage does SQL Server have over Access in multi-user environments?
A. SQL Server allows unlimited users on Wi-Fi
B. Only the server interacts with the data files, using transaction logs to keep data consistent
C. SQL Server stores all data in memory rather than files
D. SQL Server requires no maintenance or setup
Q5. What analogy does the presenter use to compare Access and SQL Server regarding data management?
A. A racecar and a bicycle
B. A public swimming pool and a private spa
C. A salad bar where everyone touches the food versus a server who serves you
D. A fast food restaurant and a home-cooked meal
Q6. Which of the following is NOT recommended to avoid Access database corruption?
A. Make regular backups and compact-and-repair schedules
B. Store all large images directly inside the Access database
C. Keep all users on stable wired network connections
D. Place the backend database on a reliable network share
Q7. What does "concurrent users" mean in the context of Access databases?
A. Total number of unique users who have ever accessed the database
B. Users who are accessing the database at the same time
C. Users who are using different databases but on the same network
D. Users who have admin privileges
Q8. Which of these actions may INCREASE the risk of Access database corruption?
A. Replacing old network switches with new ones
B. Keeping database files on a reliable server
C. Allowing antivirus software to scan the database folder during work hours
D. Regularly compacting and repairing the database
Q9. What is one trade-off of indexing foreign keys in Access tables highlighted in the video?
A. Indexing foreign keys decreases search speed but increases update speed
B. Indexing foreign keys always makes the database smaller
C. Indexing foreign keys speeds up lookups but can slow down updates
D. Indexing foreign keys is only beneficial for single-user databases
Q10. How does Access handle edits when multiple users are working with the same data file?
A. Each user works with their own copy and merges changes later
B. All users edit the same paragraphs in the same file, so interruptions can cause corruption
C. Access locks the file for one user at a time
D. Access syncs data changes in real time, with no risk of conflicts
Q11. Why is regular migration or copying of backups to new storage media recommended?
A. Because Access files require a different format every year
B. Because all storage media eventually degrade or fail
C. Because the cloud is always faster than local storage
D. Because only newer storage media are recognized by Access
Q12. Which type of backup media did the presenter say is generally the LEAST reliable for long-term storage?
A. Pressed DVDs
B. Solid State Drives (SSDs)
C. Hard Disk Drives (HDDs)
D. Recorded CDRs and DVDRs
Q13. The presenter mentions that SQL Server's transaction log helps prevent corruption by:
A. Allowing users to edit the data file directly
B. Recording every change before it happens so changes can be rolled back if needed
C. Requiring only small databases for multi-user environments
D. Making the network faster
Q14. According to the presenter, what should you do if you encounter problems with your Access database?
A. Only use the database during business hours
B. Run through a troubleshooter checking backups, network, antivirus, and hardware
C. Ignore the problem and hope it resolves on its own
D. Convert the database to Excel
Q15. What is a best practice for storing images or large attachments in an Access database?
A. Store all images directly inside the ACCDB file
B. Store images on disk and link to them from Access
C. Only use cloud storage for all images
D. Avoid using images in Access altogether
Q16. Why is it important NOT to leave an Access database open on a workstation all night?
A. It makes the computer slower in the morning
B. It can contribute to corruption or file-locking issues
C. It stops other users from accessing the database
D. It automatically runs queries that add data errors
Q17. Why is compact and repair an important maintenance task for Access databases?
A. It automatically converts the database to the latest version
B. It reduces database size and attempts to fix minor corruptions
C. It upgrades user permissions across the network
D. It increases the number of users allowed
Q18. If you use Access for a small office with several users on a solid, wired network, what is most likely?
A. Frequent, unavoidable corruption
B. Stable operation with rare corruption, especially with good maintenance
C. Only one user can access the database at a time
D. Users must always use SQL Server
Q19. What is true about Access and FileMaker regarding corruption risks in multi-user environments?
A. Both are file-based and subject to similar network-related corruption risks
B. FileMaker avoids all corruption by design
C. Access allows for more simultaneous users than FileMaker
D. Neither can be used reliably with more than one user
Q20. What major benefit does SQL Server provide for remote users or users in different locations?
A. It stores multiple copies of the same file on each user's PC
B. It allows Access front ends to connect to a central data repository securely and reliably
C. It only works if all users are in the same building
D. It eliminates the need for a network connection
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-B; 7-B; 8-C; 9-C; 10-B; 11-B; 12-D; 13-B; 14-B; 15-B; 16-B; 17-B; 18-B; 19-A; 20-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
In today's TechHelp Quick Queries video from Access Learning Zone, I'm addressing a topic that comes up often: why are Microsoft Access multi-user databases so prone to corruption? And to clarify, we are not talking about the kind of corruption you hear about in politics. This is about corrupted database files, the type that can keep you from getting into your data when you need it.
You are working away, try to access your database, and suddenly it's not letting you in. Maybe it says the file cannot be opened or prompts you for a compact and repair. It is a hassle, and I know firsthand how frustrating it can be.
Let's begin with a question from one of my valued members, who asks why some Access databases run flawlessly for years while others, sometimes with only a handful of users, seem to corrupt constantly. What causes Access corruption to happen so randomly, and is there a way to prevent this?
This issue actually does have an explanation, and it is not really as random as it may appear. The root cause is that Microsoft Access is a file-based database system. What this means is that every user on your network opens the same back-end database file directly. The quality and stability of your network play a much larger role in preventing corruption than simply the number of users, although a larger number of concurrent users certainly increases the risk.
Microsoft claims Access can handle up to 255 concurrent users. In practice, I'd say around 20 users is usually the upper limit for a stable, simultaneous multi-user environment - and even that's pushing it. I've deployed systems where 30 or 40 people could access the database, but usually only about 10 were using it at the same time and everything operated smoothly.
The real danger comes from network instability. Even small offices can experience constant corruption if they're using unreliable networks, especially Wi-Fi. I've seen small teams struggle because they insist on having wireless setups, which greatly increases risk. The best solution is to ensure your network is solid and robust, and everyone is on a wired connection. If your environment and user base are growing, or you must support remote users, consider migrating your backend to SQL Server for better reliability.
SQL Server eliminates many of these issues because only the server itself accesses the data files. All data changes are handled through a transaction log, so any failure, such as a lost connection, does not corrupt the database. Client machines just request or update information, and the server takes care of everything. Even if someone has a flaky connection or kicks their power cord, the data remains safe.
Going a bit deeper, with Access as a file-based database, every user's Access front end opens and directly interacts with the same ACCDB file on a shared network drive. When adding or editing records, you're only updating a small piece of the file. This all works quite well locally or on a reliable wired network. But if someone loses their connection mid-save, or a laptop goes to sleep, or a cable gets pulled out, Access could fail to finish writing that change, and since everyone uses the same file, a single failed write can damage the whole thing. Most Access corruption comes down to network problems or too many simultaneous users.
SQL Server, by contrast, is a server-based database system. No client ever touches the data files. The front end (could be Access or anything else) just sends a request. The server processes the request, handles the reading or writing, and replies with only the needed information. In addition, SQL Server's transaction log records every change before it actually happens, giving you safety and consistency even during failures. Corruption on SQL Server is extremely rare.
You also get added benefits: speed, security, and scalability. For example, creating big reports in Access means your workstation pulls back all the records to filter them locally. SQL Server does all this processing on the server and sends only what you ask for. Access does not offer the same level of security as SQL Server.
Access is a wonderful tool for small shops, rapid development, and local networks. As long as you have just a few users connected by a solid, wired network, you probably won't ever have a serious corruption problem. I've set up databases for companies with twenty or thirty users, and as long as I made sure the network infrastructure was good - quality cabling, reliable switches, no Wi-Fi - those databases have run for years or even decades with only the occasional compact and repair.
But once your setup grows, with more users accessing simultaneously, or you want to add remote and wireless users, the risk increases a lot. The fundamental issue is not just user count but network quality and the scale of user activity. SQL Server solves this by making the server act as a gatekeeper to the data. Clients only request what they need, and the server safely manages all changes.
I like to compare this to a salad bar. With Access, everyone helps themselves, touching the lettuce with their own hands, which is messy and unsanitary. At SQL Server's "salad bar," only a server (a waiter with tongs) handles the food. You tell the server exactly what you want, and only the server interacts with the food - nobody else can get near it.
If you are interested in learning more about SQL Server, I have an Access SQL Server Online seminar designed for moving your data to a hosted SQL Server, great for organizations with users across multiple locations or staff working from home. I am also developing a course for running SQL Server on-premises in your own office, so you can take advantage of all the security and reliability benefits without sending your data online. Join my mailing list to get an update as soon as that becomes available.
If you're not ready for SQL Server yet, make sure to cover the basics: keep good backups, compact and repair your database regularly, and store images or attachments on disk rather than in the database itself. Make sure nobody is working from Wi-Fi if you can help it, replace unreliable network hardware, and ensure your back end lives on a stable, wired network share.
Go through the usual troubleshooting steps: avoid having antivirus software scan your Access database folder, be sure users close the database rather than leaving it open overnight, reboot problem machines, and check for memory leaks or misbehaving software. If you run into persistent problems, visit my forums for advice and to get help from other users.
So to sum up, Access is more reliable than most people realize, provided you set up the environment correctly. Most reports of "random" database corruption have an underlying technical cause; Access as a platform is very stable if properly managed.
Moving on to some comments and questions, I received some feedback about tutorial videos on YouTube. I welcome other Access content creators to make videos and share knowledge. I believe having more voices in the community is a good thing, and everyone brings a unique teaching style. Personally, I tend to learn better from reading books and articles than from watching videos, but I appreciate that others learn differently. My goal is to present information in an engaging yet reliable way that students can trust.
If you have suggestions for how to improve my videos, let me know in the comments. I always welcome feedback.
Responding to another recent comment, one viewer felt a previous video about Windows features that can slow down Access databases gave only problems but not solutions. The idea was actually to provide a list of common causes—like SMB mismatches, offline files, indexing, antivirus scans, network power saving features, and Wi-Fi problems—so that you know what to look out for. A video covering all potential fixes would be far too long, so if there's one topic you'd like a complete how-to for, let me know and I'll try to make it in the future.
There were also questions about FileMaker. Although I'm not a FileMaker specialist, it operates with a similar file-based architecture as Access, so it's prone to the same risks when running on an unstable network. Many of the same precautions I described for Access will help with FileMaker as well.
A common query is the infamous "syntax error" message in Access, which often comes down to a simple spelling or punctuation mistake. If you want help solving a specific error, post your question in my forums along with what you tried, so I can better assist you.
Indexing and database performance also came up. If a table has more than one foreign key, I recommend indexing each foreign key to optimize queries. Just remember every index marginally slows down updates, so find the right balance for your workload.
As for data backup, I sometimes hear people asking whether hard drives, SSDs, or DVDs are best for archiving. CDRs and DVDRs, which use organic dyes, can break down fairly quickly and are generally not a safe solution for long-term archiving. Hard drives and SSDs typically outlast recordable optical discs. Pressed factory-made DVDs last longer, sometimes up to a century if stored properly, but the best practice is to migrate data every 5 to 10 years and keep redundant backups, ideally with at least one copy in the cloud.
I also got a question about why certain features, like dragging and dropping forms to create subforms, might not work as shown in my videos. This function has always worked for me, so if you run into trouble, let me know exactly what is happening so I can help troubleshoot.
Another member asked about why a report might display only the first record. This is usually a setup issue with the report or query, but without more information or screenshots, it's hard for me to give a specific diagnosis. Please post your question in my forums for assistance.
Finally, someone asked about a list of events and their firing order in Access forms. I cover this topic in detail in my Access Advanced Level 1 course, where I explain different events, their order, and when to use each one. I plan to create a cheat sheet for event orders in the future as well.
This wraps up today's TechHelp Quick Queries video for Friday, November 14, 2025. For a step-by-step video tutorial on all of these topics, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Why Access multi-user databases corrupt
How Access stores data in ACCDB files
File-based vs server-based database systems
Impact of network reliability on Access stability
Dangers of using Wi-Fi with Access databases
Network concurrency and Access corruption risks
Understanding SQL Server as a back end for Access
How SQL Server prevents data corruption
Advantages of SQL Server over Access for multi-user setups
Transaction logs and data consistency in SQL Server
Performance differences between Access and SQL Server
Security differences Access vs SQL Server
Proper network setup for multi-user Access databases
Best practices to reduce Access corruption risk
Identifying and mitigating network-related Access issues
Why storing images and attachments in Access is risky
Importance of scheduled compact and repair
How antivirus can interfere with Access databases
Why users should avoid leaving databases open overnight
The impact of network hardware on Access data integrity
Indexing foreign keys for better performance over networks
Tradeoffs between indexing and update performance
Strategies for reliable long-term data backup
Comparing lifespan of CDRs, DVDRs, hard drives, and SSDs
Recommended backup rotation and migration practices
Cloud backup options as part of a backup strategy
Article
If you have ever used Microsoft Access in a multi-user environment, you have probably heard stories about database corruption. Here, we are not talking about the kind of corruption you see in politics. Instead, it is the sort where your database file becomes damaged, blocks access to your data, and sometimes requires you to perform compacts, repairs, and various fixes. It can be frustrating, but the causes—and solutions—are usually clear once you understand how Access works under the hood.
Access is a file-based database system. That means your main data file—an ACCDB file—resides on a shared network folder, and every user's Access front end opens the same file directly. When a user edits or adds a record, Access only updates a single part of that file, in much the same way as editing a paragraph inside a Word document. But here is where it gets tricky: every user who opens the database is fiddling with a different 'paragraph' of the same file at the same time.
This model works fine if the network is fast and stable, everyone is on a wired connection, and only a few users are working at once. But trouble arises when the network is unreliable. A Wi-Fi connection that drops, a computer that loses connection during a save, a laptop going to sleep, or a network cable being accidentally pulled mid-write—any of these can interrupt a transaction. If that write never completes, the database can become corrupted. Since all users are tied into the same file, one bad write from any user can spoil it for everyone.
People often ask why some offices experience constant corruption, even with a handful of users, while others can run dozens of users for years without issue. The answer usually comes down to network quality, not how many people are using the database. While Microsoft claims you can have up to 255 concurrent users, reality says 10 to 20 concurrent users is the practical upper end—even fewer if your network is shaky.
A typical small office running Access over Wi-Fi is far more at risk than a larger firm using a rock-solid, wired network. The single most important thing you can do to keep Access databases running smoothly is to ensure the network infrastructure is robust and that no one uses Wi-Fi to connect to the database. Any device that has an unstable network connection can trigger problems if it loses contact mid-operation.
When your needs expand, or you want to allow remote users or support heavy concurrent usage, it is time to consider migrating to an actual database server like Microsoft SQL Server. SQL Server changes the game because only the server itself ever handles the actual data files; workstations just send requests to the server, and the server manages reads, writes, and transaction integrity. Even if a user's computer loses connection or crashes, the server keeps your data safe by managing changes through a transaction log and ensuring nothing gets left half-finished.
The technical difference is huge. In Access's file-based model, every user touches the same data file at the same time. With SQL Server, users never touch the data files; only the server does. This eliminates almost all the risks from network drops or unreliable connections, and greatly reduces the chance of corruption.
A simple analogy is a salad bar. Access is like letting everyone put their hands right into the lettuce, each person grabbing what they want, with the mess and risk that entails. SQL Server is like having a server who prepares your salad for you. You never touch the food—only the server with fresh tongs does, which keeps things orderly and much more reliable.
If you have to stay with Access for now, follow some best practices to minimize the risk. Always keep good backups, and compact and repair both the front and back end of the database regularly—weekly for most offices, but more often if the database gets heavy use. Never store images or attachments inside the database; instead, store those files externally and link to them. Ensure everyone is on wired connections, replace flaky network switches, and keep the data file on a fast, stable file server.
Other things to watch for include antivirus software scanning your database folders, users leaving the database open overnight, memory leaks on client machines, or improper network share permissions. If problems do occur, start by checking free disk space, try rebooting the computer, perform a compact and repair, and make sure the database file is not being locked by another process. Always work through the usual suspects before assuming the database itself is at fault.
People sometimes think Access corruption is simply random, but in nearly every case, it is explained by some event or environmental issue—usually a brief network or system hiccup that disrupts the write process. Modern versions of Access are very stable under good conditions, and well-designed Access databases running on reliable wired networks can handle double-digit simultaneous users for many years, especially when maintained and backed up as recommended.
As your database grows or more users need access, especially remotely, consider upgrading to SQL Server. You can run SQL Server locally in your office or choose an online host to let users connect from anywhere. Either way, you gain transactional reliability and greatly improved stability, speed, and security, since SQL Server can enforce granular security rules and carries far less risk of outright file corruption.
Turning to a few related questions: yes, the same general file-based access issues that plague Access can affect other file-based database systems like FileMaker, as their core plumbing is similar. Any time users' computers directly interact with a shared data file across a network, they are vulnerable to the same types of interruptions, which can result in data corruption. Moving to a server-based solution is the recommended fix in all cases.
If you are trying to speed up queries in Access, one of the most important steps is to properly index foreign keys in your tables, especially if you are working over a network. It is common for query performance to go from painfully slow to nearly instant just by adding one or two properly-configured indexes. Indexes speed up searches and sorts but do slightly slow down updates, since the indexes must be maintained. Balance is key; index every foreign key you regularly join or filter on, but do not index unnecessarily.
For backing up data, modern hard drives and SSDs generally offer much better longevity than do-it-yourself recordable CDs and DVDs, whose data layer can deteriorate within a decade or two. Pressed, factory-made DVDs are much more stable, but still require careful storage. The best approach is frequent migration—copying your important files to newer media every 5 to 10 years—and keeping at least one backup copy in the cloud for redundancy.
For Access users wanting more information on advanced form events and their firing order, there are in-depth tutorials and courses that break down every event, such as Open, Load, Resize, Activate, Deactivate, Unload, Close, and more. Understanding which event fires when and what actions you can safely perform in each is crucial for advanced database development.
Remember, for every problem you run into—syntax errors, forms not displaying data, drag-and-drop not working—it helps to provide detailed information when seeking assistance. Screenshots, clear explanations, and step-by-step details let others quickly spot what may have gone wrong.
In summary, Access multi-user environments are only as reliable as your network. If you pay attention to network stability, avoid Wi-Fi, keep backups, perform regular maintenance, and move heavy-duty multi-user loads to proper server-based databases like SQL Server, you can enjoy trouble-free, fast Access applications for years. If you need more help or want to upgrade your database infrastructure, there are resources and courses that can guide you through each step in the process. And as always, sharing knowledge and asking questions in forums builds a better community for everyone working with these tools.
|
