Quick Queries #65
By Richard Rost
2 months ago
Windows Features That Can Make Your Database Slow In this Microsoft Access tutorial, we'll talk about common Windows settings and features that can slow down your Access database, including issues with file sharing services, opportunistic locking, offline files, SMB version mismatches, power management, file indexing, antivirus software, and using Wi-Fi. Plus, I'll answer viewer questions on Access performance, job search tips for Access developers, date formats, string manipulation, opening related records from a search form, and login password recovery options. This is part of my Quick Queries Q&A series. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, opportunistic locking, file leasing, offline files, Sync Center, SMB version mismatch, network power management, file indexing, exclude folder from indexing, antivirus exclusions, disable Wi-Fi backend, split database, indexed tables, compact repair, SQL Server backend, unbound forms, pass-through queries, recover access password
Intro In this video, we'll talk about common Windows features and settings that could be slowing down your Microsoft Access database when running in a shared network environment. We'll discuss issues such as cloud file sharing, opportunistic locking, offline files, SMB version mismatches, network card power management, Windows file indexing, and antivirus software, along with tips for optimizing Access databases and why running Access over Wi-Fi is a bad idea. Plus, we'll answer viewer questions about Access developer jobs, best date formats, string functions, search form events, and password recovery in Access.Transcript Today we're going to be taking a look at some Windows features that might be slowing down your Microsoft Access database.
Plus, we've got your questions from YouTube and my forums, and some emails, and some other stuff. Yeah, it's Quick Queries Friday. Folks, let's have some fun.
Today we're going to start off with a question from Greg in Portland, Oregon, one of my platinum members.
Greg says, "I'm having a weird performance issue with my Access database. It runs great when the back end is on my local drive, but as soon as I move it to the server, everyone starts complaining that it's super slow. I check the network speed and it looks fine, so I don't think it's a bandwidth problem. Is there something specific to Access or Windows that could be causing the slowdown?"
Greg, assuming you've checked all of the Access stuff, and I've done many videos on all these different topics, make sure your tables have a primary key, that they're indexed, including your foreign keys are indexed. Make sure your queries are optimized. You don't have any wildcards in there, any asterisks, you're pulling in all the fields when you only need two fields. You've got a split front and back end, which you said you already have. Make sure your forms and reports aren't bound to large tables. Use a query instead to filter the results.
Don't try to pull all 300,000 customer records you have into your customer form. Filter that beforehand if you can. Make sure your combo boxes and list boxes aren't loading thousands of records. If you get 100,000 customers, you don't want to load them all into a customer combo box on your order form. That will definitely slow things down.
You don't have DLookup type fields in your queries or in your continuous forms. DLookup, DSum, DMax, DMin, DCount, all those things. Use aggregate queries instead if you can. Your subforms aren't requiring entire data sets. You don't have a customer that's got 10,000 orders and it's pulling all of them in.
Of course, it goes without saying you've compacted and repaired lately. Do it at least once a week. I'm not a big fan of attachments, images, or OLE objects stored in your database. Don't be doing that.
I'm guilty of this one myself. Don't use excessive VBA recordset loops where SQL would work. So, assuming you've checked all of this stuff and your database is properly optimized, there might be some Windows issues causing your slowdown.
Now I'm not going to have time in this video to go over all of these things, but I'm going to at least give you a starting point. Something you could search for, do a Google search for, and that might lead you down the right path.
If any of you want to see me do a more in-depth video about any of this stuff, post a comment down below.
First off, and I have done a video on this one, never run your Access database from a cloud file sharing service like Google Drive, OneDrive, Dropbox, any of that stuff. These tools are great for Word docs, spreadsheets, whatever, but they constantly try to sync and update the files in the background which does not jive well with how Access works in a shared environment.
Two people opening the same back end file at once will cause duplicate copies, sync conflicts, even total corruption. Access needs direct real-time communication with the database file, not something that's being mirrored or uploaded every few seconds. If you want to share your Access apps through the back end, use a proper network drive or move it to SQL Server.
To learn more about this, I have a previous video you can go watch, Access with file sharing services, don't use them. It's okay if it's just you, or maybe you and one other person you can say, "Hey, I'm using the database now." I used to have an office I would drive to and I kept my Access database, which was a small file, in a Google Drive backup folder because when I got to the office, I'd open it and I was sure no one else was using it. When I closed it and I was done, by the time I got home, that file synced up. It was relatively safe and I kept it a small file. I think it was like maybe 30 or 40 megs, it was tiny.
But do not use it with a file sharing service like this and expect multiple people to be able to work with it.
If you do want an easy-to-use almost zero setup solution where multiple people even in different locations can use your Access database, check out Access Database Cloud. That's a great solution as well.
Moving on, we've got opportunistic locking, also called oplocks or file leasing. Let me do that. I'm completely forgetting to bold my slide lines. What am I doing here? It's like I've never given a presentation before.
It's a hidden Windows feature called opportunistic locking or file leasing. It tries to speed up file access by caching data locally instead of constantly talking to the server. Again, it works great for programs like Word or Excel because they're usually open by one person at a time. But for Microsoft Access, it's bad news.
When multiple people share the same database, Access needs every read and write to happen instantly across the network. If Windows delays that to try to optimize performance, it can actually slow everything down or even cause corruption.
So if your shared Access database feels laggy or unreliable, look up how to disable opportunistic locking or file leasing on your server. It's a quick tweak that can make a huge difference. And I have not done a video on this, so if you want to see a video on how to do this, let me know.
Offline files, or the Sync Center, is another Windows feature that can cause big problems for Access. It's something called offline files, also known as Sync Center and not the kind of sink you buy at Home Depot. It's meant to help laptops by keeping local copies of shared files so you can still work when you're not connected to the network. And again, great for Word and Excel. Terrible for Access.
If Windows is quietly keeping a cached copy of your backend file and then syncing it later, you're going to have problems. All right, delays, conflicts, corruption as multiple users are working on out-of-date copies of the back end.
So if an Access app is stored on a shared drive, make sure offline files is turned off for that folder. It's easy to check. Just search Windows for offline files or Sync Center depending on the version of Windows you have to find that setting. And again, I don't have a video on this, but if you want one, post a comment down below.
Next up is SMB version mismatch. SMB stands for Server Message Block. It's a protocol that Windows uses for file and printer sharing over the network.
In simple terms, it's how one computer can say, "Hey, can I read and write that file on your drive?" and the other server goes, "Yeah, here you go."
Now, if you've got a server or—usually I see this with NAS devices, network attached storage—all right, that little hard drive on a box, basically, it's sitting on your shelf. If they happen to be using different versions of the SMB file sharing protocol, you're going to have a problem. Server and Windows uses SMB version three, but older devices might still be using SMB one or two. And when they don't match, Windows has to keep retrying and renegotiating the connection, and that can cause major slowdowns.
I had a client not too long ago who had a modern Windows laptop, but he's got a network attached storage device that's probably like 10 years old. It still works, it's still good, and I had to tell him, "Don't put your Access files there because it's going to cause issues." It's fine to store all the old video files you have and pictures from the kids from 2004, but don't run an Access database off of that.
One thing you'll see with this is not just slowdowns, you'll get random disconnects, database-in-use errors. It's not something you can actually see happening, but if your database is lagging on a network share, check your server or your NAS documentation and make sure both servers—both sides, the server and the workstation—are using the same SMB version, preferably version three.
This is another sneaky culprit: Windows power management on your network card. To save energy, Windows can throttle or even put your network adapter to sleep when it thinks you're not using it. That might be fine for browsing the web, but it's a disaster for Access, which constantly talks to the back end file. If the network card pauses or goes to sleep, Access just loses connection and it looks like it's frozen.
This fix is simple. Just go into Device Manager, find your network adapter on both your workstations and your server, and disable any power saving options. That keeps your network connection live and your database running smoothly. You should not have that on anyways on any wired network connections. We'll talk about wireless in a minute.
Windows file indexing is another performance killer for Access. The indexing service constantly scans files to make searching faster. Again, great for your Word documents. You want to find that Word document that you had, you know, grandma's recipe for peach cobbler. You search Windows for peach cobbler and it can find those documents, but that's not good for Access.
Every time you open or save data, the indexer jumps in and tries to catalog the file, which slows down queries and can even cause lockups and all kinds of weird stuff. The fix is simple: exclude your database folder from Windows indexing. That's an easy one. Again, if you want a video on it, let me know.
Next up is antivirus software. As you know, if you've been watching my videos for any long period of time, I am not a fan of antivirus protection. Norton, McAfee, all that stuff. Get rid of it. The Windows Defender, whatever they call it, Windows Security, I don't even know. Whatever comes with Windows is good enough. Just don't be downloading sketchy files from weird sites.
Most antivirus programs, including Windows Defender, have real-time scans, which means every write to your ACCDB file gets inspected by the antivirus software before Access can work with it, which drags performance into the dirt. It's fine to scan that folder occasionally, like if you want to do a weekly scan, but don't put any extra files in your database folder. Just your ACCDB files. Don't put downloads there. Don't do any of that stuff. Access needs direct uninterrupted access to its file and not have a security program hovering over it every second, like a helicopter mom. Turn that stuff off, at least for your database folder.
And finally, I want to add not using your Access database over Wi-Fi. It's not really a Windows setting or a problem, but just don't do it. It's a bad idea. Now, there's a caveat to that.
In fact, this came up this morning in my forums on my website. One of my users, Thomas, mentioned that Microsoft hasn't kept up with Access to let it run over Wi-Fi. And another one of my students emailed me like a week ago or so. I don't usually advertise this. I'm going to advertise it now, obviously, because I really don't want people running Access over Wi-Fi.
Since Access is file-based, even a small hiccup in Wi-Fi can cause corruption. And not only that, but if you've got one user on Wi-Fi, and he's slowing the database down because of read/writes, it's going to slow everybody else down too.
Don't let people use Wi-Fi unless you have SQL Server as a backend. The way Access works with just Access by itself, it's a file-based system. So it's got to read/write directly to that file. Wi-Fi will slow that down incredibly.
But if you're using SQL Server and you handle the data properly using unbound forms, pass-through queries, and so on, then Wi-Fi is fine within reason. Don't be moving huge amounts of data. Work with one record at a time or a small page of records. Like if you want to show a continuous form, 10 or 15 records, okay, fine. Don't try to load 5,000.
When you open up a customer record, just pull that customer record into an unbound form. Then when you want to save it, give them a Save button, it writes it back to the server. If you do that, you're okay. I've done that myself just fine.
I had one client that definitely wanted to use Wi-Fi. I said, "We can still use Access on your Windows devices, your laptops, your Windows tablets and stuff, but we're going to use SQL Server so we don't have any conflicts that way." And it works great; still using it today. I built the software for him probably 10 years ago. They use it for inventory management. They've got like 15 people that do inventory, and they've got three or four people in the front office that are wired, but it works without a problem as long as you're using SQL Server.
And yes, I will be showing how to do this in my upcoming SQL Server course. This is all coming up.
But there you go, Greg. That's all the things I would check. Again, check your Access stuff first, check your Windows stuff, check all those settings, Google what you don't know. And if any of you want to see me cover anything more specifically, post a comment down below and I'm happy to do it.
All right, let's head over to the YouTubes.
S Southcom said, "This video hasn't aged well from my experience. Haven't been able to specifically land Access developer work either as an employee nor contractor. I've yet to find a job posting for an MS Access developer."
I find them occasionally. In fact, on my website where I have this video at the bottom, whenever I come across it, I've got a Google alert set up for Microsoft Access. Whenever I see someone post a job, here's all the ones I've found in the past six months or so. There's not a lot of them, I'll be honest.
As I said in that video, I think your best bet is to hang out your shingle as an Access developer and be a consultant versus trying to find a job with someone else that needs an Access developer, because not a lot of places advertise for Access developers.
Here's a funny one I found—someone put as a security guard, they listed Microsoft Access as a need in the job requirements. I just had to have fun with that one. But there's a bunch of real ones. I find them every now and then. I post them in here when I see them.
You have to be a little creative. You have to market yourself. Get out there and talk to small businesses. The problem is that businesses know they need a solution for their data needs. They don't know they need Microsoft Access.
They're not looking for a Microsoft Access developer. They're looking for a software developer or a database developer, or even a website developer. When in reality, they've got a small business and they've got three people in a front office that need to share customers, orders, and that stuff. That's where a Microsoft Access database is fine. But they don't know that's what they need.
They don't need this giant cloud service-based, 500 concurrent users, blah blah blah. They just know that they have a data need and they're sick of QuickBooks. So that's what you have to find. That's the tough part, it's not the easy part. That was the hardest part when I was running my consulting business was finding new clients.
Fortunately, I had my Access videos on YouTube that would bring clients to me. That kept me going for at least 10 years until I could just do videos. So you have to get out there. Marketing is the number one thing with any business. You have to know how to market yourself.
Jonathan says, "Month a year is so much better. Who says eight May 2025? You say May eighth 2025. That makes way more sense."
Yeah, that's how I was raised too. Here in the United States, we say "May eighth 2025." In other parts of the world, they say "the eighth of May 2025." So this is regional. This is all depending on where you're from.
That's why I say, and longtime viewers know what's coming, the only good date format is the ISO date format, year-month-day. OK. I'm glad we all agree.
Next up in the "way to make me feel old" department, Bloodlord72 said, "This was literally before I was born, but thanks, needed it. Just don't like the old design of Access."
Yeah, Access has an old school look to it. I actually like that, though. I prefer it, just like I like old school web design. Look at my website. For those of you who are curious, this video came out in 2008. So yes, there's a good number of my videos that are older than the people who are watching them. OK. I need to go take a nap now.
rtap1 says, "Oh, it makes me smile that I remember maybe about a year ago that after all your years of Access VBA development, you were shocked to find out about mid string comma N, meaning to get the remainder of the string starting at character N. And yet you still use the right and Len combo."
Yeah, it's a bad habit to change. I've been using the right and Len combo since I was, what, 10 years old? So 20 some years now. Your comment just 10 days ago has made me try to remember that. It's one of those things where the idea hits you in your head two or three times, it starts to stick. When I first learned it, I was like, wow, it blew me away. Then I forgot all about it. It's like when you meet someone new and they introduce themselves, you have to say their name three or four times, then finally it sticks. Well, your comment 10 days ago just helped to make it stick.
I think I just did a video a day or two ago where I remembered to use mid to get the right side of a string. It's such a time saver without having to use right and Len together. Thanks for jarring my memory.
I want to tell everybody else watching this, if you see me use that right and Len combo again to get the right x characters out of a string, yell at me and tell me to use mid instead, because I'll forget again. I will. I'm honest.
WhatTheShelf321 says, "Is there a simple way to double click on the customer from this search form and open up the customer form for that customer?"
Well, this search form video that you're talking about uses a list box. You can put a double click event in that list box that would very easily open up the customer form. It's going to involve a little bit of programming though. Literally one line of code, but you have to know where to put it, in an On Double Click event.
Or if you don't want to get into programming, you could make a little button under the list box that says "Open Customer" and it will open the customer that you're on. You can do that with the wizard. I'm pretty sure it works with the wizard and it works with a continuous form. Give it a try. Watch my Blank Template video; it'll show you how to do it.
Later on in this series, maybe three or four videos in, I go into contacts and invoicing. In one of these videos in this series, I show you how to make a customer list form. In that customer list form, it's a continuous form with a double click event right here in the ID. Double click there and it opens it up. You can do the same thing with that list box in the search form. One line of code. That's why I say learn a little VBA, folks. A little bit of VBA gives you all kinds of stuff. It opens up your database to a whole new world. I didn't just start singing that.
Finally, today we've got Richard who says, "I have searched all over to find a recover password in a login system for Access, but nothing works the way it's supposed to. It actually does not work at all when I can find it. Do you have a system that works? I can log in, I can change password, but I can't recover a password."
So this completely depends on what kind of security you have in your Access database. Now Microsoft Access by itself has not had any password security in it whatsoever since before 2007. That's when Microsoft Access had the workgroup file. You could create users and stuff like that. But since then they got rid of it because Microsoft wants you to use SQL Server for secure stuff.
You have to build your own security into your Access database and you can make it really rock solid and tight. I have a whole seminar on securing your database. But I can't tell you what to do with your database because I don't know how it's built. Whatever developer built your database is the one you're going to have to ask about how to recover a password. For example, it has to be stored somewhere, probably in a table. It might be encrypted, I don't know and I wouldn't know without looking at your database. This might be something you're going to have to hire a consultant for.
If that's the case, if you want help, check out my Access Developer Network on my website. I've got a list of different consultants and developers and tutors and people you can contact and they'll be happy to help you out with your needs. Just check them out. You'll find a bunch of people on here that can help you out.
All right, so that's going to do it for another Quick Queries, ladies and gentlemen. I hope you learned something. Enjoy your weekend. Live long and prosper. I'll see you next time.Quiz Q1. Which of the following is a recommended practice to improve Microsoft Access database performance in a shared environment?
A. Storing the Access backend on a proper network drive instead of a cloud file sharing service
B. Storing large attachments and images directly in the database using OLE objects
C. Binding forms to full, unfiltered tables with hundreds of thousands of records
D. Using unindexed tables and queries without filtering
Q2. Why should you avoid using Access databases with cloud file syncing services like Google Drive or Dropbox in a multi-user environment?
A. These services increase database security, which slows performance
B. They constantly sync and update files, causing conflicts and possible corruption
C. They automatically optimize query performance, leading to unpredictable results
D. They prevent Access from saving any changes at all
Q3. What is the potential issue with opportunistic locking (oplocks or file leasing) on Windows servers for Access databases?
A. It encrypts data, making Access queries unreadable
B. It keeps files locked at all times, preventing simultaneous access
C. It can delay read/write operations across the network, causing slowdowns and possible corruption
D. It deletes unused tables to optimize performance
Q4. Why is the Windows feature "offline files" or Sync Center problematic for Access database backends?
A. It prevents files from opening on laptops
B. It caches local copies that sync later, leading to delays, conflicts, and data corruption
C. It increases the sharpness of images stored in the database
D. It automatically marks all records as read-only
Q5. Server Message Block (SMB) protocol mismatches between devices can cause what problem with Access databases?
A. All tables are automatically deleted if versions do not match
B. Password protection is disabled
C. Random disconnects, database-in-use errors, and major slowdowns
D. The database will auto-upgrade itself to the newest format with each use
Q6. What setting should be disabled in Device Manager to prevent Access database connection issues related to the network card?
A. Power saving options
B. Hardware acceleration
C. Bluetooth settings
D. Color management
Q7. What does the Windows indexing service do that negatively affects Access databases?
A. Adds password protection to your database files
B. Prevents ACCDB files from being copied
C. Scans changed files to make searching faster, which can slow Access operations and even cause lockups
D. Disables macros in Access databases
Q8. What is a recommended approach to antivirus scanning and Access database files?
A. Set the database folder as excluded from real-time scanning, but run periodic manual scans
B. Install two antivirus programs for double protection
C. Move the database file to the C:Windows folder to avoid scanning
D. Ensure all file types including downloads are saved in the database folder
Q9. Why should you avoid running Access database backends over Wi-Fi?
A. Wi-Fi provides faster speeds than wired connections
B. Even small network interruptions can cause corruption or slowdowns affecting all users
C. Access is specially designed to take advantage of Wi-Fi optimization
D. Access cannot be installed on devices with Wi-Fi
Q10. How can you safely use Microsoft Access with Wi-Fi connections for multiple users?
A. Only if the backend is stored on a USB stick
B. Only if the database is less than 1 MB in size
C. Only if you use SQL Server as the backend with unbound forms and pass-through queries, avoiding large data transfers
D. Only if you run the database on a Mac
Q11. What is a reason NOT to load thousands of records into a combo box or list box in Access forms?
A. Access cannot display any records in combo boxes or list boxes
B. It may cause significant performance slowdowns in the form's loading time
C. Combo boxes are encrypted and do not allow data loading
D. It will erase all records from the source table
Q12. What is the suggested way to filter which records are shown on your Access forms?
A. Bind the form to the full table and then use Excel to manually filter records
B. Use a query with criteria to filter records before loading them into the form
C. Only use macros with no queries at all
D. Filter records after all are loaded into the form using VBA loops
Q13. Which of the following options is NOT recommended for dealing with aggregate functions like totals or counts in your Access database?
A. Use aggregate queries (GROUP BY, SUM, COUNT) where possible
B. Use DLookup or DSum functions excessively in continuous forms
C. Optimize queries to minimize field retrieval
D. Index your foreign keys
Q14. Why might Microsoft Access security and password recovery depend on your database's specific implementation?
A. Microsoft Access includes centralized user management in all versions since 2007
B. Microsoft Access has no built-in password recovery, so custom solutions are required and implementation varies
C. All Access databases are automatically backed up to the cloud
D. SQL Server controls Access security for all databases
Q15. When trying to market yourself as an Access developer, what is a key challenge mentioned in the video?
A. Most businesses are specifically searching for "Microsoft Access developer"
B. Finding businesses that know they need an Access database, as most seek generic solutions and do not realize Access could meet their needs
C. Competing with thousands of Access consultants in your local town
D. Access databases cannot be used by small businesses
Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-A; 7-C; 8-A; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-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 Quick Queries video from Computer Learning Zone, I want to discuss some Windows features that could be causing your Microsoft Access database to run slowly, along with answers to questions I've received from YouTube, my forums, and email.
First, let's tackle a question about performance issues in Access from one of my platinum members, Greg. He noticed that his database works great when the back-end file is stored locally, but the moment he moves it to a server, users start to complain about sluggish performance. Network speed appears normal, so Greg suspects there might be an Access or Windows-specific issue at work.
Greg, before blaming Windows, make sure your Access database is fully optimized. I've covered these basics in several other tutorials, but let me run through them. Ensure every table has a primary key and that keys, including foreign keys, are indexed. Optimize your queries so they only pull the data you need, rather than bringing in all fields unnecessarily. Use split databases with a separate front-end and back-end, and try to keep forms and reports from binding directly to large tables. Rely on queries to filter the results displayed in forms—avoid opening massive datasets, such as thousands of customer records, all at once on a form or in combo boxes. Loading huge lists into list boxes or combo boxes, like your entire customer table, will bog everything down.
Also, avoid placing DLookup, DSum, or similar aggregate functions in your queries or on continuous forms. Substitute them with properly designed aggregate queries wherever possible. Make sure subforms do not retrieve massive numbers of related records. If you have customers with large order histories, pull only what's necessary. Don't forget regular database maintenance—compact and repair your database at least weekly. I also discourage storing attachments, images, or OLE objects in your database as these inflate file size and cause issues.
When you handle calculations or data manipulations, stick to SQL as much as possible, rather than excessive recordset loops in VBA. With all of this in check and your database structure optimized, if you still notice slowdowns in a server environment, it's time to look at how Windows could be involved.
While I can't go in depth on every possible culprit here, let me outline several key areas you should investigate further. If you'd like an in-depth tutorial on any of these, just let me know in the comments.
One major issue is related to running your Access database from a cloud file-sharing service such as Google Drive, OneDrive, or Dropbox. While these platforms work well for Word documents and spreadsheets, they continuously sync files in the background, which conflicts drastically with the way Access operates in a multi-user environment. Sync conflicts, duplicate files, or even corruptions can occur if multiple users attempt to open or write to the database simultaneously through a syncing service. Access expects real-time, direct communication with its back-end file. If you're sharing an Access backend, always use a proper local network drive or consider migrating to SQL Server. Avoid using cloud sync services for anything beyond solo access.
Now, if you want a straightforward way to let users in different locations share an Access database, consider a hosted solution, such as Access Database Cloud.
Another common Windows feature that creates trouble for Access databases is "opportunistic locking," sometimes called oplocks or file leasing. This is a background mechanism in Windows that attempts to boost speed by caching file changes locally before writing them to the server. While beneficial for files used by a single person, such as Word documents, it's disastrous for Access databases, where multiple users need instant, real-time reads and writes. If you find your database feeling slow or unreliable on a network, search for instructions on disabling opportunistic locking or file leasing on your server. Adjusting this can greatly improve performance.
A similar culprit is Windows offline files, also known as Sync Center. Intended for laptop users, Sync Center keeps local copies of files in case you disconnect from the network. While it makes sense for documents and spreadsheets, it can create huge headaches for Access databases. If Windows is keeping a cached version of your back-end and multiple users are working with out-of-date data, the result can be major lag and file corruption. To avoid these issues, make sure Sync Center is disabled for your database folders when working in a shared environment.
Next, consider the version of SMB (Server Message Block), which is the protocol Windows uses to share files over the network. Mismatched SMB versions between your workstation and server—or especially with aging NAS devices—can introduce network slowdowns and random disconnects. Access calls for the most current protocol, preferably SMB version three. Ensure both your workstation and your server or NAS use the same, up-to-date SMB version for best results.
Another subtle factor is Windows power management on network adapters. By default, Windows might turn off your network card when idle to save energy, but Access depends on a solid, always-on connection. If your adapter goes to sleep during a session, Access can lose its connection without warning. Head into Device Manager on both workstations and the server, find your network adapter, and make sure to disable any power-saving settings there. This will help prevent dropped connections.
Windows file indexing is another service that can degrade performance for Access databases. Designed to help you find files quickly, the indexer scans and catalogs files in real time, which is excellent for searching recipes but not for shared database operations. Every time you read or write data, indexing can interrupt or slow the process. To fix this, simply exclude your Access database folder from Windows indexing.
Antivirus software is a notorious performance drag. Most third-party antivirus programs and even Windows Defender scan every attempt to read or write your database file, slowing things down considerably. I believe the built-in Windows Defender is all you really need, but even that should be told to ignore your database folders during real-time scans. You can set scheduled scans for extra safety, but don't let antivirus software constantly monitor your Access files as it only slows things down.
Lastly, avoid running Access databases over Wi-Fi unless your backend is SQL Server. By design, Access is file-based, and Wi-Fi interruptions can lead to loss of connection or even corruption. Even a single Wi-Fi user can bring your database to a crawl if the backend remains an Access file. If your environment must use wireless, pair Access with SQL Server, use good database design practices like unbound forms and pass-through queries, and only work with small subsets of data at a time. In my upcoming SQL Server course, I'll cover this approach in more detail.
To summarize, Greg, once you've checked all of your Access database optimizations, look into the Windows settings and features I mentioned. If you want more step-by-step instructions on any of these, let me know.
Now, moving on to some other questions from my community.
Someone mentioned difficulty finding jobs specifically listed for Access developers. It's true, you won't find many explicit positions advertised that way. Most companies know they need help managing data, but don't realize Access is the solution. You'll have to brand yourself as a database developer and network with small businesses directly. That's how I ran my own consulting business for years—by reaching out to clients and marketing my skills, not just waiting on Access-specific job postings.
There was also a question about date formats. Here in the United States, we usually say "May eighth 2025," while other English-speaking countries use "the eighth of May 2025." My preference is always the ISO standard: year-month-day. It's unambiguous and sorts properly.
A viewer recalled an old tip about using the Mid function instead of a Right/Len combination to get the end of a string. Old habits die hard, but I now try to use Mid more often for this task. If you catch me reverting, feel free to remind me.
Another question was about quickly opening a customer form from a search form. The solution is to use a double-click event on your list box that triggers the customer form to open with the selected customer record. This takes a simple bit of VBA in the On Double Click event. Alternatively, you could add a button below your list box to do this through the wizard interface, but learning a little VBA is always worth the payoff in expanding what your database can do.
Finally, someone asked about adding a password recovery system to an Access login routine. That depends largely on the security model you used in your Access database. For the last decade and a half, Access has not included built-in user-level security—the old workgroup security was phased out back in 2007. Any password or user system in your database would have to be custom-made and stored, likely in a table. Without knowing how your system was built, it's hard to offer a one-size-fits-all answer. If you need help, consider reaching out to an experienced developer or consultants through my website.
That's it for another Quick Queries session. I hope you found these tips and explanations helpful. For a complete, step-by-step video tutorial on everything I discussed today, visit my website at the link below.
Live long and prosper, my friends.Topic List Access database speed issues on network vs local drive
Optimizing Access table indexing and primary keys
Using query optimization to improve performance
Filtering forms and reports to reduce data load
Preventing large recordsets in combo and list boxes
Replacing DLookup and aggregate functions with queries
Preventing loading of entire data sets in subforms
Importance of regular compact and repair
Avoiding attachments and OLE objects in Access
Minimizing excessive VBA recordset loops
Risks of using cloud file sharing services with Access
File syncing conflicts and Access back end corruption
Best practices for sharing Access databases over network
Disabling opportunistic locking (oplocks) for Access
Turning off Windows Offline Files Sync Center
Resolving SMB version mismatches for network storage
Configuring network adapter power management
Excluding Access folders from Windows file indexing
Managing antivirus real-time scan for Access folders
Avoiding use of Access databases over Wi-Fi
When and how to use SQL Server back end with Wi-Fi
Using unbound forms and pass-through queries with SQL ServerArticle If your Microsoft Access database is running slowly when the back end is stored on a network server instead of your local drive, it is important to consider both Access-specific optimizations and certain Windows features that can cause performance issues. Let's walk through what you should check to make sure your Access database runs smoothly in a shared environment.
Start by looking at the basics inside Access itself. Each table should have a primary key and any fields used frequently in searches or relationships (including foreign keys) should be indexed. Look at your queries to make sure they are pulling only the necessary fields—don't use SELECT * if you only need a couple fields. Always split your Access application into a front end (forms, reports, and queries) and a back end (tables and data). This way, only the data travels across the network. When viewing records with forms or reports, avoid binding them directly to large tables. Instead, use queries to filter the data so that only the relevant records load. Never try to load tens of thousands of records into a form, combo box, or list box. If you have 100,000 customers, for example, you do not want all of them to appear in a dropdown—filter first.
When building queries or forms, avoid using domain aggregate functions like DLookup, DSum, DMax, DMin, or DCount, especially when displaying lists of records. These functions can slow down performance because they execute repeatedly for each row. Instead, use aggregate queries built in SQL, which are far more efficient. Similarly, if you have subforms, make sure you are limiting the recordsets to only what's necessary.
Make a habit of regularly compacting and repairing your database to avoid unnecessary bloat and corruption—once a week is a good rule of thumb. Try not to store file attachments, images, or OLE objects in your tables. Store file paths instead and keep the files themselves in a dedicated folder.
If you use VBA in your application, minimize Recordset loops when you can perform the same work with an SQL action query, which is almost always more efficient.
Once you've optimized the Access side and still experience slowness or unreliability when the back end is on a shared drive, Windows features are often the root cause. For the best performance, the Access back end needs a simple, direct, and uninterrupted connection to the server files. Several Windows features can interfere with this.
First, don't store your Access database in cloud-synced folders such as Google Drive, OneDrive, or Dropbox. Cloud syncing tools constantly monitor and upload changed files in the background, which disrupts the access patterns Access expects. In a multi-user environment, this leads to conflicts, duplicate files, and almost certain corruption. If you must sync files for backup purposes, be sure only one person is using the Access database at any given time and the file is allowed to fully sync before and after use.
For proper multi-user Access databases, put the back end on a standard network file share, not a cloud drive. An alternative is to migrate your data to a proper database server like SQL Server, which plays much nicer with simultaneous connections, even across locations if set up correctly.
One major hidden culprit for poor Access performance on networks is a Windows feature called opportunistic locking, or oplocks (sometimes called "file leasing"). This feature caches data to try to speed up access for single-user files like spreadsheets or Word documents, but with Access it delays communication between multiple users leading to slowdowns or corruption. You can resolve this by disabling opportunistic locking or file leasing on your file server. How you do this depends on whether your back end is on Windows Server or a NAS, so check your server documentation or do a web search for disabling opportunistic locking for your particular setup.
Another Windows feature to watch out for is called offline files or the Sync Center. Windows tries to keep local copies of shared files for use when network links go down—again, great for Office documents, but this can wreak havoc on shared Access databases. Users might unknowingly edit cached copies, and when Windows tries to resync later, you get conflicts and corruption. Make sure offline files (Sync Center) is turned off for the folder containing your Access back end. Search Windows for "offline files" or "Sync Center" and turn off the feature, at least for your database folder.
The protocol used by Windows to share files over the network is called SMB (Server Message Block). There are several versions of SMB (1, 2, and 3), and mismatched versions between your server and your workstations—or especially with older NAS (network-attached storage) devices—can cause slowness, random disconnects, temporary lockups, and "file in use" errors. Windows 10 and newer servers use SMB 3 by default, but many NAS drives default to SMB 1 or 2. Try to ensure all your devices are using the same and most recent supported version, preferably SMB 3, for best reliability and speed.
Next, look at how your network card or adapter is configured. To save energy, Windows sometimes puts network cards into low power or sleep mode if it thinks the device is idle. This interrupts Access's constant read/write process, looking at the back end, and can cause the application to freeze or lose connection. The solution is to go into Device Manager, select your network adapter, and disable any power-saving or sleep options. You should do this for both client PCs and servers, especially for wired Ethernet connections.
Windows file indexing is another feature that can slow Access. The Windows indexing service runs in the background, scanning files to make future searches faster. If your database folder is being indexed, the service can lock the file or slow down normal Access operations every time you open or save data. To fix this, exclude your database folder from Windows indexing. Open the Indexing Options from the Control Panel, locate your Access folder, and remove it from the list.
Antivirus and anti-malware software can also slow or interrupt Access operations, as these programs often scan every file every time it is opened or changed. This is especially harmful for Access databases, where every read and write can be delayed. The safest approach is to configure your antivirus to exclude the Access folder(s) from real-time (on-access) scanning, though it is still a good idea to do periodic manual scans. You do not want downloads, random files, or other unrelated materials placed in your Access folder—keep it clean, just for your database files.
A big warning: Never run your Access database over Wi-Fi unless your data is stored on a proper database server (like SQL Server) and accessed via proper client-server techniques (such as using unbound forms and pass-through queries). Wi-Fi connections are more susceptible to brief disconnections and variability in speed, which can corrupt your Access database or cause sessions to drop unpredictably. Even if only one person uses Wi-Fi, it can affect the entire database's performance for everybody. If you move your back end to SQL Server, and your Access front end is written properly to only pull a small number of records at a time, Wi-Fi can work, but stick with wired connections for standard file share back ends.
Once all of these factors are addressed, your Microsoft Access app should perform close to its best possible speed on a shared network. Make sure you check both the Access-related and Windows-related settings. If there is something you are unsure of, search online for the feature and how it interacts with Access, particularly in shared environments.
Now, let's touch on a few Microsoft Access troubleshooting questions that come up often.
Suppose you want to double-click a customer in a list box and open their detail form. This is easy to set up in VBA. For example, if you have a list box named lstCustomer on your search form, you can go to its Double Click event and put this code:
DoCmd.OpenForm "CustomerForm", , , "CustomerID=" & Me.lstCustomer
This opens the CustomerForm filtered to the record whose ID is selected in the list box. If you are not comfortable with VBA, another approach is to create a button under the list box using the Command Button Wizard; set it to open the form related to the selected record.
Another topic is handling password recovery in Access. Since 2007, Access does not have built-in user-level security. Any login system you have is custom, so its logic depends on how the original developer set it up. Usually, user credentials are stored in a table. If you forget a password, you might have to reset it manually as there rarely is a true password-recovery system unless one was coded in. Access's table data can be encrypted or stored as hashes, further complicating recovery. If you need help building or repairing such a system, you may want to consult a developer familiar with Access security and VBA.
Finally, if you are looking for Access developer jobs, note that there are not many listings for "Access developer" specifically. Businesses often do not realize Access is their solution—they look for a "database developer" or "software developer" instead. If you want to build a career or find side work with Access, market yourself creatively to small businesses who have data needs but do not require enterprise solutions.
A handy VBA tip: to get a substring of a string starting at character N, you can use the Mid function instead of combining Right and Len. For example, instead of
Right(strValue, Len(strValue) - N + 1)
use
Mid(strValue, N)
This is simpler and easier to read.
In summary, optimizing Access performance on Windows comes down to carefully configuring both Access and your Windows environment, avoiding cloud sync folders, disabling or tweaking features like opportunistic locking, offline files, file indexing, network card power saving, and antivirus scanning, and using SQL Server for advanced setups or where Wi-Fi is a must. Keeping these factors in mind will help your Access databases run smoothly, reliably, and quickly for all your users. |
