Quick Queries #80
By Richard Rost
13 days ago
Often Overlooked Optimization Most Developers Miss
In this Quick Queries video, we discuss an Access form optimization that can speed up read-only forms by setting the Recordset Type to Snapshot. We also talk about automating billing statement emails, RAM recommendations for Access and SQL Server, setting custom database icons, combo box design for linking tables, handling VAT rates, why you should not run Access databases from Google Drive, database size and user limits, and more. I respond to viewer questions about form printing, running balances, Access and cloud storage, cancel button behavior, and Access Day event details.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp QQ Quick Queries, AccessLearningZone.com, sluggish form, read-only form snapshot, optimize forms, mailbox automation, email billing statements, combo box concatenate names, requery combo box, recordset loop, StatusBox function, reverse running balance, autonumber recycling, multiple VAT rates, Google Drive database issues, SQL Server migration, RAM for database PC, form print PDF margins, mobile access alternatives, concurrent user limits, Azure SQL Server
Intro In this Quick Queries video, we discuss an Access form optimization that can speed up read-only forms by setting the Recordset Type to Snapshot. We also talk about automating billing statement emails, RAM recommendations for Access and SQL Server, setting custom database icons, combo box design for linking tables, handling VAT rates, why you should not run Access databases from Google Drive, database size and user limits, and more. I respond to viewer questions about form printing, running balances, Access and cloud storage, cancel button behavior, and Access Day event details.Transcript Ever have a form that feels sluggish for no obvious reason, even though the data is simple and you're not editing anything? Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today, we're going to talk about an often overlooked optimization that can speed up your Access forms. It's a simple setting, easy to miss, but it can make a noticeable difference, especially on lookup or dashboard-style forms. We're also going to cover things like emailing billing statements automatically, how many users Access can realistically handle, RAM recommendations for a new database PC, why you shouldn't run databases through Google Drive, and whether you can recycle autonumbers.
Let's jump in.
Right now, off today, we've got posts in the forums from Kevin, one of my moderators, and I recently did a captain's log post about setting up a custom database icon. Sometimes I post a little tip or trick or something in the captain's log, which is why everyone should subscribe. Application icons, your database icon, can just give your database a more polished, branded, fancy look. Kevin jumped into the thread and shared some VBA code that he uses to automatically set the icon when his database starts up, and here it is right there. I'll put it on the screen for everybody.
I like to spotlight stuff like this because I love seeing the stuff you guys come up with. Setting a custom icon is one of those small touches. It makes your database feel a little more like a real application. A lot of people ask me how to hide it from looking like Microsoft Access. This is one of the ways. In fact, I've got whole separate videos on that topic.
Kevin took it a step further, and he built a little routine that sets the app icon property on startup. This is great if you're deploying multiple front ends or you're updating your branding or the icon file moves. You want to make sure it's there. That kind of stuff is you can easily throw a check in there to see if the file exists.
Thanks to Kevin for sharing that. Like I said, I love it when you guys share your code with me, your tricks, your little utilities you built. When you do, I try to pass them on to the rest of the community because that's how we all get better. In fact, half the ideas in my courses come from students like you. In level one, someone asks a question. I think that's a great idea; I'll cover it in level two. Same with the TechHelp videos.
Next up, we got Julian, one of my Gold members. He's buying a new PC, and the seller asked how much RAM he needs to comfortably run Microsoft Access and SQL Server, along with his usual stuff like Sage 50, Office, and Adobe. So, he wanted to know what a safe, practical recommendation would be for a real-world database.
That's a great question, Julian, because this is one of those things where the wrong answer can cost you performance for years. For Access by itself, honestly, it's pretty lightweight. You're going to run Access on 8 gigs, no problem. Once you start layering in SQL Server, accounting software like Sage, Office apps, maybe Acrobat, with a couple things open at once, that's where the memory starts to matter.
I made a solid recommendation: 16 gigs is kind of the modern sweet spot. That gives you breathing room for SQL Server, your background services, and Windows itself, which loves to eat RAM. If you're going to 32 gigs, great, that's kind of future-proofing. But for most Access developers and power users, 16 gigs is the practical minimum that I recommend on any new machine today.
Matt brought up a great tip to leave RAM slots open so you can upgrade later without replacing everything. That's a smart move. If you don't know how much memory you've got in your machine, here's a little tip for you. You can find out in a couple of different ways. You can go into System Settings or Control Panel. I like to just bring up the Task Manager. It's Control Shift Escape to bring up the Task Manager.
Normally, you see these processes, things that are running. Access is right here, if you have to kill it from time to time, like I always have to do. Go to Performance. If you don't see the menu, just hit the little hamburger menu. Go to Performance, and under Memory, it'll tell you right there how much memory you've got. I've got 64 gigs because I do a lot of video processing and higher-end stuff on this machine, so I beefed it up when I bought it. There's your processor, 2.3 gigahertz, your hard disk, GPU, all that good stuff.
Alright, next up, we got Lee, one of our beginner students. He's building a sports card database - pretty cool. He wants to create a combo box that shows players' last name, comma, first name. This led to some confusion about whether he needed a separate player ID table, how to structure his keys, and then how to refresh the combo box after adding a new player without closing the form.
This is a great discussion thread. Donald and Kevin both jumped in with some solid advice. All kinds of good stuff in this thread. I'll put a link to it down below so you can read the whole thing.
In a nutshell, you don't need a separate table just for the player name and ID. Your player table should have basically your autonumber primary key - that's your player ID - and then you'll use that as a foreign key in your card table. So, you end up with a playerTID - Player Table ID that's linked to your card table's ID in a nice, clean one-to-many relationship. That way, you can have one player who's got multiple cards.
For the combo box, Donald's SQL example is perfect. He gave - you select your ID and then you concatenate those two strings together in the combo box. Or you can do this in a query. I do this in the TechHelp database using a query. We got this query here called customerLFQ that puts the first name and last name together, and that way you can see this in the combo box. All that is, is a field that puts together last name and first name, just like Donald did.
If you've got it in a query, you don't have to keep rebuilding it every time that you do a new combo box. Now, the refresh issue: when you add a new player and it doesn't show up right away, that's just a requery problem. When you close the player form, you need to requery the combo box or the calling form. Donald suggested using a requery in an event. Kevin mentioned you can use an IsFormLoaded function; both are great patterns. Once you start getting into a little more VBA, if not, just close the form, reopen it, or hit F5; that'll refresh the box as well.
Again, all their code and all the stuff that they recommended is right on this thread. I'll put a link to this thread down below. But the big takeaway is one autonumber, primary key per table. Use foreign keys for your relationships. When the data changes behind the scenes, you just have to requery it. You can do it manually or with a tiny bit of code. Great question and lots of beginners ask this exact same stuff. I cover this kind of thing in my beginner series.
Next up, Ronald, one of my longtime students, asked if billing statements can be emailed out to customers automatically from Access, or if they have to be sent one at a time manually. That's a great question, Ronald. The short answer is yes, you can absolutely automate that, but it's going to involve a tiny bit of code. Like I always say, learn a little bit of VBA and it'll make your databases a whole lot better, so you don't have to sit there sending statements one by one.
What most people do is build a little VBA loop that runs through a list of customers, usually filtered with just the ones with open invoices. It'll generate their statement or send their invoice email automatically. You'll have to learn something like working with recordsets; that's what I recommend you do it with. You generate the reports or PDFs, hand those off to Outlook or whatever email system you're using, but once it's built, it's a huge time saver. You click a button, everybody gets their statement. You don't have to do it manually.
It's definitely doable. I cover how to do this and a lot more in my email seminar. I teach everything you need to know about sending emails using Access, even HTML emails. I have a TechHelp video that shows how to send email as well, and in the extended cut I show the members how to do it with a recordset loop and send it as a PDF attachment. Check these out if you want to learn more. I'll put links down below.
And speaking of links down below - a little intermission while I've got your attention here - if this video's helping you out, make sure you hit like and subscribe and click that bell so you get notified every time I release new videos. It really helps me out, and I appreciate it. Thank you.
Next up, we got Steve, one of my Gold members, who asked how to clear the status box message on the main menu. That sparked a little side discussion about how the StatusBox function works and whether you need to reference it with Me, and why sometimes the code behaves differently depending on the version that you're using. That's another long thread here.
First, a clarification for anyone watching who doesn't follow along with all my videos: The StatusBox we're talking about is a little utility function that I wrote that displays messages on the main menu. Here's a video that explains how it works. Basically, it's on the main menu or even on any form you want to. You can put a little box - instead of popping a message box up, you just display it in the StatusBox.
I like this because if you do anything that's automated at all, message boxes will stop your code. It just sits there waiting for you to respond. So you can put things in a message box, which you can see, or if you're not watching it, it'll just display the message and move on. It just adds into that box whatever you want to say as status.
Now he wants to know how you can clear that. There are a couple of different ways you can clear it. If you're on the box - if you're on the form with the box itself and you want to clear it before you do stuff - well, by the way, I switched to dark mode. Some people love it, some people don't like it. I'm going to leave it on for now because it's much easier on my eye - my one good eye that works - and I think it's just cleaner looking. Tell me down below, what do you think? Do you like it like this, or do you like the old-school white background better? I think this is better myself, but I'll go with whatever the majority of people comment.
Anyway, here's the code for the StatusBox. If you want to clear it before you issue something to it, just say StatusBox = "", just like that. StatusBox is the name of the control. There, that's the StatusBox. So now, when you're on it, it'll clear it before it does anything, so it doesn't stack on top of it.
If you want to clear it from another form - let's say you're on the Customer form, and you've got something that's doing it, like the Order form - here you just have to call it by its full name. So you have to say Forms!MainMenuF!StatusBox = "". And then status in an opening order, so whatever, the orders. Now, from this other form, move it out of the way so you can see it, when you click Opening Orders, it blanks the box and then puts Opening Orders in there. That's totally up to you if you want to do that.
Of course, this led to the whole Me discussion, which normally you don't need Me if you're referencing a field. If you don't say Me.StatusBox, Access will assume you want the StatusBox control on the current form. The current form is Me. You do generally need Me when you're referencing properties. For example, if you want to change the form's Caption property up top, it's Me.Caption. That being said, a lot of developers use Me anyway for readability, so you can tell that it's a form control versus a variable or something like that. It's totally optional. Stylistically, it's a personal preference.
I can always tell when someone didn't learn Access from me because they use Me everywhere. There are a lot of things, like how you guys name your variables - I can always tell if it's one of my students. They come in with strFirstName, or they use underscores or spaces in their field names. Don't get me started.
Next up, we got David, one of my Gold members and a Trekkie - he's got the Trekkie badge. David wanted to know if it's possible to do what he calls a reverse sum in Access - basically starting with a fixed amount like $1,000 or $10,000 or whatever and then subtracting payments or deposits from it over time so you can see what balance remains instead of what's being added up.
What you're describing sounds fancy, but it's really just a running balance flipped upside down, and I do have a video on running balances, which is right there - it's my check register video. I'll put a link to this down below. Instead of starting at zero and adding transactions, you basically start with your maximum amount and then subtract as activity happens - same math, just reversed perspective.
Once again, Donald chimed in with some awesome ideas - great practical suggestion using a continuous form with the starting balance in the header and then subtracting the sum of the transactions in the footer. That works great for a quick visual. Here's some code that GBT recommended, which works fine too. Whether you call it a reverse sum, a remaining balance, or debit card math, it's all the same core running total concept.
Next up, Jean Pierre, one of our Gold members, has two related design questions. First, can you recycle deleted autonumbers to fill gaps in a table? Second, how do you handle multiple VAT or tax rates and invoices instead of just a single percentage? Donald once again nailed both answers.
Let's start with the autonumber question because this comes up all the time. Short answer: no. Do not recycle autonumbers. They're not meant to be sequential business numbers; they're just unique record identifiers. Donald pointed to the correct video. Autonumbers are not for you. I'll put a link to this video down below. They're for your computer, they're for Access; you should not worry about what the autonumbers are at all. If you want to use sequential numbers, you can come up with your own sequential numbering system or your own customer code.
The only time I would restore an autonumber that you deleted or somehow got rid of is if it was an accident and you need to get it back. There are tricks - I'm not going to go into them now - but basically, don't rely on them for anything.
As far as the separate VAT goes, there are lots of different ways to do it. I show in Access Expert 9 how to use multiple sales taxes. Essentially, in Expert 9, we set up multiple tax rates, so you can have a different tax rate for each customer - that's their default tax rate. Each order can have its own sales tax rate, and each item on an order can have its own sales tax rate because sometimes you might have a customer who is tax exempt and so all of their orders are tax exempt.
You might have a customer who is not tax exempt but has one thing they're buying that is. Sometimes states will do a tax holiday where certain items are charged at a lesser rate, or there are all kinds of weird exemptions and exceptions to the rule. That's why I did it that way. You can set a different tax rate for each item on the order if you want to. You could also use a combo box tied to a tax rate table for each country. There are all kinds of things you could do. You could pull a percentage into the invoice. I've set up all kinds of systems for people based on different tax rates. It definitely makes your database fun, having all these different tax rates floating around, but it's definitely doable.
Alright, heading over to YouTube. First up, Tony is basically asking why not just use Google Drive instead of setting up a local Windows file share. Isn't that easier or maybe more reliable than SQL or shared folder? I think that Tony's talking about my SQL Server video that I did, which followed up this Windows file sharing one. The point of the Windows file sharing was to make sure that your computers are talking to each other on your local area network. Computer A is talking to computer B; you got a folder that's working between them. That's the first step in making sure that your network is good for SQL Server, which comes after this, or if you want to just share a Microsoft Access database between two machines.
Google Drive has its benefits. I use it myself. It's great for cloud backup, remote access, sharing files between locations. I used to use it all the time. Way, way back in the day, I used to actually have an office and wanted to make sure I could get any of my documents, spreadsheets, or even Access databases through Google Drive. But it was a 30- or 40-minute drive between my house and my office, so anytime that I put files in there, by the time I got to the office, they had synced - hopefully.
Never run an Access database out of a Google Drive folder. Copy it down locally. Ideally, move it so that someone else - if you have multiple people - doesn't try to open it up also from the Google Drive folder, and then when you're done, put it back. This works fine for small databases, but not for big ones. I have a whole separate video on why you should not use a file sharing drive like Dropbox for your Access databases.
This Windows file sharing video specifically is about direct LAN file sharing inside a local network. It's good for an Access backend, and eventually the goal is to move to SQL Server. I wanted to make sure that people have their network set up properly first, because if you can't share folders between two computers, then you're going to have issues with SQL Server, too.
You definitely don't want to run databases through Google Drive or any sync service. You introduce latency, file locking issues, possible corruption, all kinds of problems. A properly configured local network share is far more stable for live database work. Cloud storage is great for backups and file distribution. It's not great for running databases.
If you want to learn more about why you shouldn't use Access or SQL Server for that matter with file sharing services, go watch this video. See, even BJones agrees. He says great tutorial. I split my time between two countries, so I have a little more techy setup because I'm not on a wired network. My caveat with using an Access database over a connection like mine is - one, don't; and two, backup, backup, backup.
I used to use an Access database between two locations like that, but it was a small database and I would drop it in my Google Drive folder, drive to the office, and by the time I got there, it had synced up. Then I could copy it down to my desktop on that machine, work with it, and when I was done, copy it back up to Google Drive again, and that worked fine. It was maybe a couple hundred MB, so it took a few minutes. High-speed internet on both sides - it was not a big deal. But Google Drive acts as a good backup and you don't want to run stuff out of there because it will cause all kinds of issues.
Next up, we got Bruce asking one of those "it depends" answers: maximum or practical limit for how many line item records you can store in an Access table before you should move that data to SQL Server. It depends. There's the theoretical limit and there's the real world practical limit. Access can technically handle millions of records, but it's not really about record count; it's about database size and record complexity. A table with just names or just a couple of key fields is tiny, and a table with tons of fields, attachments, large text, is much, much bigger.
The tipping point usually comes down to performance, multi-user load, how many items you have, and how big each one of those items is. It's all dependent on a bunch of factors. But the bottom line is, once the database starts slowing down with either lots and lots of data in it or multiple users using it across the network, that's when SQL Server starts to make sense. Everyone always points out the two gig file limit - yeah, but that's per database, and even then you can break up tables. So it's not really so much about the size of the data. I would say it's more about the speed that you're getting out of the database.
Next up, Adrian says he's not able to attend Access Day 2026, which, by the way, is coming up on March 27th in the Redmond, Washington area. If you're able to attend, get there. Unfortunately, he says he's not going to be able to attend. I get it - especially if, like me, you live in Florida. It's not exactly right around the corner. But I also go because the MVP Summit is the same week. It's right in front of that, so they put it right next to that. Still, it's a great event to get to if you can. I highly recommend it.
He wants to know if I can provide a summary rundown of the topics and maybe some contact info afterwards. Access Day is intentionally an in-person only event. The official agenda and speaker list are always posted on their website. They're not doing virtual sessions because the whole point is networking and hallway conversations and getting to meet people.
That said, like I did last year, I will absolutely give you a high-level recap afterwards. I'm not going to be taking detailed transcripts or notes about everything that was said, but I'll cover the interesting nuggets - things that are worth passing along. I'll coordinate with Armand, the guy who's putting it on, to make sure that what I'm sharing is appropriate. If you can't make it, I'll do my best to keep you in the loop, and hopefully you can make it to future events.
Next up, we got Juan - hold on, my Spanish is not good yet. Juan says he likes the videos, but he would prefer them in Spanish. I'm working on it. Ever since I moved to Florida 13 years ago, I've been trying to learn a little bit of Spanish. I'm up to about 10 words, so I'm getting there. I know a lot of different languages, but they're all computer languages. I learned Latin in high school. You'd think that would help me - but no, it really doesn't. Maybe sometime around the year 2050, when I'm finally fluent in Spanish, I'll be able to teach in Spanish. I can't right now.
What YouTube does have available is live captions and the auto dubbing. There's an option in YouTube where you can turn the videos into Spanish if you want to. I've listened to some of them. I don't understand what they're saying, but they sound pretty good. Check that out, but I can't help you aside from that, because I only speak English. Sorry. No habla español.
Next up, Maniche. He built the form in Access using A4 paper size. It looks fine in print preview, but when he prints or exports to PDF, some of the data spills out onto the second page and he says he can't shrink the labels or text boxes any more.
First of all, you said form, but you probably mean report. If you built it as a form and you're trying to print it, that's your problem right there. Don't try to print forms. We print reports - forms are for on the screen. But I know you probably said "form" because people think forms, they think pieces of paper.
Make sure you use a report, and print preview can look perfect, but the actual printer driver or PDF engine can shift margins just enough to push content onto the second page. Even a tiny difference in top or bottom margins can do it. Here are a couple of quick things to try. First, double check your printer margins and make sure they match what Access is using. Second, leave yourself a little buffer space at the bottom of the report instead of designing right to the edge - and also left and right. In fact, in my videos when we do labels, I say you have to move that right side in just a little tiny bit. You measure out 8.5 wide by 11 high, bring it in just a little bit less than 8.5. Third, make sure you're using the same printer or PDF driver when testing, because they all render slightly differently.
Try that stuff. Hope that helps.
Next up, this is a comment on one of those conversation starters I did a while back - what's one feature that you wish Microsoft would improve about Access? He says they need a mobile app in the worst way. It's not 1984. I get this one a lot. Access is great for rapid application development on the desktop. For what it's built for, it's the best at what it does.
Microsoft did experiment with Access web apps years ago and then quietly retired them - mostly because demand wasn't there, and also because they just weren't done right. I love Access, but web apps were a disaster. If you need true mobile access, you're usually looking at a web-based front end. Power Apps will do it, or something custom that talks to SQL Server on the backend. I write all my stuff in ASP. I do have an ASP course available. It's a little older, but I am thinking about revising it. It's not as slick as a lot of these new modern web design platforms where you just give it a couple of sentences and then the AI builds the mobile app for you, but if you want to learn how to do this stuff yourself, ASP is really great. ASP is very similar to VBScript, which is very similar to VBA, so if you know how to program in Access VBA, you can pick up ASP really easily.
Next up, this is a video I did a while back about lottery numbers. You type in the lottery numbers that hit and you put in all your numbers that you played and see which ones won. Every now and then I get someone here like Alexander who says he developed his own lottery number system that gives him a 28 percent chance of picking the correct numbers, and that the traditional hot-cold tracking methods don't compare to his approach.
It's the legendary "I've cracked the lottery system." I love spreadsheets as much as the next nerd, and tracking numbers, highlighting matches, analyzing patterns - that's all fun. But mathematically speaking, every draw is independent. The odds don't change based on what happened last week or what numbers are hot or cold. If you truly had a 28 percent chance of picking winning numbers, you wouldn't be in my YouTube comments. You'd be on a beach somewhere buying islands. By all means, track numbers if you enjoy it. Just treat it as entertainment and not predictive science. It's the same thing with people that say they got this foolproof way of predicting the stock market. Great. Don't brag to me about it - go do it, make your billions. I'll believe it when I see it. One of my favorite quotes from Carl Sagan is that extraordinary claims require extraordinary evidence.
Next up, VJ is asking how many users can be in an Access database before you start running into locking errors. He's got about 50 to 60 users; now tables are locking and people can't update data. Yeah, 50 to 60 users is getting up there. You're getting near the practical ceiling for Access, especially if a lot of them are in there at the same time editing data. That's where you start to see real record locking, table locking problems, performance slowdowns, all that fun stuff.
Access can handle small teams great - 5, 10, 15, maybe 20 people. But at that scale, it's time to start thinking about moving the backend to SQL Server. Concurrent users and network speed make a big difference too, but your symptoms are classic time-to-upgrade signals. That means it's time for you to start watching my free SQL Server for Microsoft Access Users course. It's right here on YouTube, it's on my website. There's the link. SQL Server Express is free, this course is free - at least Beginner Level 1 is, which is all you need to get you up and running. Go check it out.
Next up, Artat says that you can hide the Cancel button, but if the Cancel or Default property is set to true, it will only respond to the Escape or Enter key if it is both visible and enabled. Yes, that is 100 percent true. You are absolutely correct.
What I talked about in this video was you can assign the Escape key to pressing the Cancel button, and I said you can hide the Cancel button if you want to. What I meant, and I didn't clarify this in the video, is hide the Cancel button unless the loop is running. We have the Start button that starts a process, and then you can hit the Cancel button to cancel it. What I meant by hide the Cancel button is have this thing not visible, and then when you start the loop, make it visible so it will respond to the Escape key. Then when the loop is over, hide the button again. That's what I meant. I wasn't clear about that in the video, I apologize. Yes, the Default and the Cancel buttons have to be visible and enabled for them to work. What I meant to say and didn't say in the video was yes, you make it visible once the loop starts and then hide it again when you're done with it, so it's not there all the time and people won't see this menu with this big Cancel button. Only show it when you need it. But thanks for bringing that up. I should have been a little clearer in the video.
Next up, Tina says, I wish you would make some Azure courses. Good suggestion. When they add that 25th hour to the day. For anyone not familiar, Azure is Microsoft's cloud platform. It's basically their giant online ecosystem of services, everything from virtual machines to web apps to cloud-hosted SQL Server. Instead of running SQL Server in a box in your office, you run it in Microsoft's data center and that way anyone around the world can use it.
Full transparency, my hands-on experience with the broader Azure ecosystem is pretty limited. I've worked with SQL Server hosting in the cloud. In fact, I've got an entire seminar showing you how to set up SQL Server with a hosting provider like Winhost. This is basically SQL Server in the cloud, but on Winhost servers instead of Microsoft's. Like I mentioned a minute ago, I've got a course that I just started on teaching you how to set up SQL Server in your office and not in the cloud - it's on your LAN. Could I do Azure-specific content in the future? Sure, especially around SQL Server in Azure, which I have worked with before. I've set it up for some of my clients before. It's not that different; you just have to get used to Microsoft's web interface and their tools and management stuff. If there's enough interest, I'll seriously consider adding it after I get my current SQL Server course underway to the point where I'm happy with it. If it's something that you want, drop a comment down below - if you want to see me do some Azure courses, let me know. Squeaky wheel gets the grease.
I can't remember if I gave Artat credit for this last week or not. Usually when I mention someone in a Quick Queries video, I at least give you the little Spock hand in the YouTube comments, but I came back to this one, I just saw it and didn't have a comment on it. So Artat's the one who taught me this, I'm pretty sure, or else someone else did - one of you guys did - because I didn't know this. It's one of those features they snuck in. If you have an empty sub and you hit debug compile, it gets rid of it. So that's really cool. I did not know that before last week when you guys taught me this.
See, I learn new stuff all the time. When I really learned Access back in the '90s or 2000s, when it was relatively new, a lot of these features didn't exist, and the Microsoft team has just kind of snuck them in. So it's like, what? When did that - what? How? Thank you again, and please, by all means, keep teaching me stuff, guys. I will turn around and teach everybody else.
Finally, tonight we got one from zigzoo Hugh. Okay, I still don't know how to pronounce that. He's asking if your data in your form is read-only anyway, why not open it as a snapshot to improve performance? This is in regards to my sluggish form optimizing form performance video. Yes, that's actually a solid suggestion.
If the form truly is read-only and users aren't editing anything, then using a snapshot can absolutely help. A snapshot recordset is read-only by design, so Access doesn't have to worry about all the record locking or write permissions. That usually means less overhead and sometimes better performance. The key is making sure that you really don't need edits, because once you switch to snapshot, it's locked down. But for reports, dashboards, lookup forms, it's a great optimization trick.
If you're wondering how to do it, go to a form like the customer list form. You can edit the records in here, but let's say you only wanted people to look stuff up in here. Go into Design View, go into the properties, go to Data, and then right here, Recordset Type, you can just switch this to Snapshot. Save it, close it, and when this form opens up, it's going to be read-only. See, I'm trying to edit stuff and I can't. But these records will load faster. There's no record locking. You don't have to worry about conflicts. So it may load a lot faster, especially over a network.
Before you go, don't forget Access Day 2026. It's on the calendar, March 27th, 2026 in Redmond, Washington. About the Redmond area - I went to last year's event; great time, lots of solid presentations, great conversation with other Access folk. You know, it's kind of like Star Trek conventions and all the Trekkies get together - well, this is all the Accesses, whatever you want to call us. He's got a list of presenters on there. I'm not one of them; I'm just an attendee. I'm just going to hang out and learn some stuff myself. But I'm definitely looking forward to it. If you can make the trip, put it on your calendar and check it out. Registration is open. There's the link.
Don't forget to stop by my website - check out what's new. Got my SQL Server course kicking right now, but I'm always adding new videos, updates, templates, random bits of Access goodness over there. Check it out. While you're there, don't forget to subscribe to my mailing list. You'll get notifications every time I release a new video or at whatever frequency you like. You can set it to weekly, monthly, quarterly, whatever you want. Don't forget to check out the captain's log where I post my thoughts about whatever I happen to be thinking about that day. Sometimes it's about Access, sometimes about science, sometimes sales. Usually there's some Star Trek involved. Whatever happens to be on my mind that day. Yes, my favorite thing lately has been putting myself in pictures with Star Trek scenes and stuff. It's just cool. I'm a nerd. Leave me alone.
Speaking of nerdy stuff, check out my merch store - hats, t-shirts, mugs, all that good stuff. Grab a copy of my book. It's on Amazon. Enough said.
Today we looked at that often overlooked optimization - try saying that three times fast. Often overlooked optimization. Oh boy. It's been a long day. Anyway, that can speed up your forms, especially when they're read-only. We talked about automated billing emails, when it's time to move your backend to SQL Server, and why cloud sync folders aren't the best place to run a live database.
Do me a favor - post a comment down below. Let me know what you thought of today's video and how you might use this optimization in your database. Of course, drop any questions you want me to cover in next week's Quick Queries video.
That's going to do it. That's your Quick Queries video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time. Enjoy your weekend.Quiz Q1. What is one often overlooked setting in Access forms that can improve performance, especially for lookup or dashboard-style forms?
A. Changing the form background color
B. Using snapshot recordset type for read-only forms
C. Adding more subforms
D. Increasing font size on labels
Q2. Why should you use a snapshot recordset type for certain Access forms?
A. It allows multi-user editing
B. It enables cloud synchronization
C. It provides read-only access, reducing overhead and improving performance
D. It increases the database size
Q3. What should you NOT do when working with Access databases and Google Drive?
A. Use Google Drive for cloud backup only
B. Work with the database from a local copy
C. Run the database directly from the Google Drive folder
D. Copy the database down locally before using
Q4. What is the recommended minimum amount of RAM for a modern Access or SQL Server development computer according to the video?
A. 4 GB
B. 8 GB
C. 16 GB
D. 64 GB
Q5. In an Access database, what is the main purpose of autonumber fields?
A. Generating sequential business document numbers
B. Providing unique record identifiers for tables
C. Storing customers' VAT values
D. Displaying numeric IDs to users
Q6. What should you do if you add a new record to a table and it does not immediately appear in a related combo box?
A. Requery or refresh the combo box
B. Restart Access
C. Delete and recreate the combo box
D. Compact and repair the database
Q7. When should you consider moving your Access backend data to SQL Server?
A. When your database file size reaches 100 MB
B. When you have hundreds of concurrent users and performance issues
C. When you exceed the two gigabyte file limit every day
D. When you want to redesign your user interface
Q8. What is the best practice when handling VAT or sales tax rates in Access?
A. Only use a single flat tax rate for all orders
B. Store tax rates inside the order details as needed
C. Set up tables or queries that allow for multiple tax rates per customer, order, or item
D. Never change tax rates once set in the database
Q9. Why is it not recommended to recycle deleted autonumber values in Access tables?
A. It breaks queries using those numbers
B. Autonumbers are only for Access to ensure unique record identification
C. It improves application security
D. It could increase report load times
Q10. According to the video, what is the practical maximum number of simultaneous Access users before common issues arise?
A. 100-200
B. 30-40
C. 5-10
D. 50-60
Q11. What feature in Access can you use to display process status messages without interrupting code execution?
A. MessageBox function
B. StatusBox utility/function
C. Immediate window
D. Error handler
Q12. What best describes the purpose of the "Me" keyword in Access VBA?
A. It always refers to the underlying table of the form
B. It refers to the current form instance, mainly for clarity and referencing form controls or properties
C. It is required for referencing any control on a form
D. It automatically refreshes all combo boxes
Q13. What should you use in Access for printing or professional output instead of forms?
A. Exporting the form as PDF
B. Reports designed specifically for printing
C. Snapshots of tables
D. Copying form layouts to Excel
Q14. What is NOT a good reason to choose Power Apps or ASP for your Access backend/front-end?
A. To provide mobile access to your data
B. To produce sophisticated web-based forms
C. To avoid learning SQL basics
D. To create custom web front-ends for SQL Server
Q15. Which statement is TRUE about record locking errors in Access databases?
A. They never happen, regardless of user count
B. They typically occur as concurrent user numbers rise beyond practical limits
C. Only occur if the backend is on a cloud storage service
D. Only happen with forms in design view
Q16. When does the Cancel or Default button in an Access form work with keyboard shortcuts like Escape or Enter?
A. Only when it is invisible
B. Only when it is both visible and enabled
C. Only when used on continuous forms
D. Only with mouse clicks
Q17. Why did Microsoft discontinue the Access Web Apps feature?
A. Security concerns
B. Low demand and incomplete implementation
C. High server costs
D. Lack of mobile device support
Q18. What is the recommended way to automate emailing billing statements from Access?
A. Send emails manually using Outlook
B. Use VBA with a loop and recordset to generate and send emails
C. Export reports to Word and copy-paste into an email
D. Print statements and mail them physically
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-A; 7-B; 8-C; 9-B; 10-D; 11-B; 12-B; 13-B; 14-C; 15-B; 16-B; 17-B; 18-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 AccessLearningZone.com, I address a range of questions and share useful tips and optimizations drawn from recent forum posts and viewer comments. I'm your instructor, Richard Rost.
Let's begin with a frequently missed way to improve the performance of your Access forms. You might notice that some forms feel sluggish even when dealing with simple, non-editable data. Often, the solution lies in a small but effective setting: changing your form's recordset type to Snapshot. This works especially well for lookup forms and dashboards. By making a form read-only with Snapshot, Access does not have to manage record locking or edits, resulting in faster load times. Remember, use this only if no editing is needed, since Snapshot makes the form strictly read-only.
Next, I want to highlight a helpful post from our forums about customizing your database's application icon. Adding a custom icon to your database is a great way to give it a polished and professional look. One of our moderators, Kevin, shared a method using VBA to set the icon automatically on startup. This helps when deploying multiple front ends or updating your branding. It's always enjoyable to see creative solutions like this from our community, and I love passing these tips along, as much of my course content comes from great questions and ideas you all provide.
Another common topic is computer hardware for database work. Julian, a Gold member, recently asked about the amount of RAM needed for running Access and SQL Server alongside other business applications. My recommendation is a minimum of 16 GB for most modern systems. While Access itself is light on resources, running it alongside SQL Server, Office, and accounting software like Sage 50 means you'll benefit from extra memory for smoother multitasking and future-proofing. If you can, leave some RAM slots open for easy upgrades later.
If you're unsure how much memory your computer currently has, you can easily check this using the Task Manager in Windows by pressing Control Shift Escape and looking under the Performance tab.
Forum discussions also addressed how to structure tables for a sports card database. Lee wanted to create a combo box showing "Last Name, First Name" for players. There was confusion about whether to use a separate player ID table, how to create relationships, and how to refresh the list when new players are added. The key takeaway is to use a single player table with an autonumber primary key, which links cleanly to the card table as a foreign key. To display "Last Name, First Name" in a combo box, create a query that concatenates the names, and set the combo box to use this query. After adding new records, use a requery command so that updates appear instantly.
On the subject of automation, Ronald asked if it's possible to send out billing statements from Access automatically, instead of emailing each statement individually. The answer is yes - with a little bit of VBA, this process can be fully automated. A VBA loop can email statements to all customers with open invoices by generating the required reports or PDFs and sending them through Outlook or another email system. Once set up, sending all statements is as simple as clicking a button. This approach saves a lot of manual effort. For those interested, I have an email seminar and videos that demonstrate this process, including how to attach PDFs and use recordsets.
Another question from Steve regarded clearing the status box message on the main menu. I use a utility called StatusBox to display messages within forms instead of relying on disruptive message boxes. To clear the message, simply set the status box text to an empty string. If you need to clear it from another form, reference the control with its full name. We also discussed the use of "Me" in VBA: while not strictly necessary for referencing fields in the current form, some developers use it for readability.
David had a question on calculating a reverse sum in Access, where you start with a fixed amount and subtract transactions to see the remaining balance. This is just a variation of a running total, but inverted. Start with your initial amount, then subtract each transaction as you go. There are various approaches, such as displaying the starting amount in the form header and subtracting the sum of transactions in the footer.
Jean Pierre asked whether it's possible to recycle deleted autonumbers to fill in gaps, and how to support multiple VAT or tax rates on invoices. Autonumbers are not meant to be reused or sequential - let Access manage them as unique identifiers. If you want sequential numbers for invoices or customers, create a separate system for that. For supporting multiple tax rates, set up tables and relationships so each customer, order, or item can have its own associated tax rate, allowing for flexibility with exemptions and varying regional rates.
We also discussed why you should avoid running Access databases through Google Drive or other cloud sync services. While these tools are excellent for backups and sharing files, they are unreliable for live database work. Running a database directly from a cloud sync folder introduces latency, file locking issues, and a high risk of corruption. Always work locally for active databases and use network shares for multi-user access. Move to SQL Server when you need more robust performance or handle larger data sets.
Bruce posed the classic question about the maximum number of line items or users Access can handle before needing to upgrade to SQL Server. While Access can technically handle millions of records, practical performance is limited by data complexity, record size, and the number of concurrent users. Problems such as table locking, slow performance, and data corruption are clear signs it's time to migrate to SQL Server, which I cover in a free course.
Adrian asked about Access Day 2026, an in-person event packed with valuable sessions, networking, and expert presentations. If you can't attend, I will provide a summary of major topics afterward, but the key experience is participating in person.
Juan asked for videos in Spanish. While I'm still learning Spanish myself, YouTube does offer live captions and automatic dubbing features that can be enabled to translate videos.
Maniche encountered layout issues when exporting reports formatted for A4 paper to PDF, resulting in data spilling onto an extra page. The solution is to design your reports with a bit of buffer space on the page edges and verify that printer and PDF margins match what Access expects. Always use reports, not forms, for printed output.
Viewers often request a true mobile version of Access. Microsoft discontinued their Access web apps, so mobile front ends today typically rely on other technologies such as Power Apps or custom solutions using ASP. I have an ASP course available for those interested in building web-based or mobile-accessible database front ends.
On the lighter side, I responded to a viewer claiming a new lottery number prediction system. While it's fun to experiment, odds do not improve with pattern tracking for lotteries or similar independent random events.
VJ wanted to know how many users can work in an Access database before running into locking errors. Once you reach around 50 to 60 concurrent users, Access often struggles due to record locking and slowdowns. This is a sign you should switch your backend to SQL Server, which is free and robust for higher user counts.
Artat pointed out that for command buttons with the Cancel or Default property set to true, those buttons must be visible and enabled for keyboard shortcuts to work. I clarified that the intent is to show the Cancel button only during long-running processes, then hide it otherwise, to keep the interface clean.
Tina requested Azure courses. While I have some experience setting up SQL Server in the cloud with hosting providers, my Azure content is limited for now. If there is enough interest, I can consider creating more tutorials focused specifically on Azure and cloud services.
I continue to learn new things from this community. For example, if you have an empty subroutine and run Compile, Access will automatically remove it, which I didn't know until recently. I welcome and appreciate all tips you share, as teaching and learning are truly a collaborative process here.
Finally, zigzoo Hugh asked about using Snapshot recordsets for read-only forms. This is a solid way to boost performance for lookup and dashboard forms where edits are not required. By selecting Snapshot as your form's Recordset Type in the Data tab, you ensure the form loads faster and remains read-only, which can be particularly effective over a network.
Don't forget to check out Access Day 2026 if you can attend, and keep an eye on my website for new videos, updates, templates, and helpful content. Subscribe to my mailing list to stay informed or visit the captain's log for my thoughts on Access, science, and the occasional bit of Star Trek.
Today we've discussed an often overlooked optimization for form speed, automatic email billing, when to move to SQL Server, and why cloud sync folders are best used only for backups, not active database files. Let me know in the comments how you might apply these tips in your projects, and drop your questions for future Quick Queries episodes.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Setting a custom database application icon with VBA
RAM recommendations for Access and SQL Server PCs
Checking installed system memory using Task Manager
Structuring player and card tables with proper keys
Creating combo box with concatenated player names
Refreshing combo box contents after data changes
Automating emailing of billing statements in Access
Building a loop to send emails to multiple customers
Clearing and updating a custom form StatusBox control
Referencing controls with or without Me in VBA
Calculating a reverse sum or remaining balance in Access
Handling multiple tax rates in Access invoices
Assigning tax rates per customer, order, or item
Setting up combo boxes for country-based tax tables
Why not to run Access databases from Google Drive
Difference between LAN file sharing and cloud sync
Access table size and record limits before SQL Server
Determining practical user limits for Access databases
Refreshing form data when new records are added
Optimizing form performance using snapshot recordsets
Setting form Recordset Type to Snapshot for read-only
Troubleshooting printing issues with report margins
Default and Cancel button behaviors on Access forms
Visibility requirements for form Cancel button
AJAX: Not included, as not covered in detail
Power Apps as alternative for mobile Access solutionsArticle Have you ever found your Access forms acting slow for no clear reason, especially when they only display simple data and you are not doing any editing? There is an easy optimization that often gets overlooked but can make a noticeable difference in the speed of your Access forms, especially on lookup forms or those used as dashboards. The key is setting your form's Recordset Type to Snapshot if it is truly read-only. When you do this, Access knows it does not need to handle record locking or prepare for edits, which can reduce overhead and speed up form load times, particularly over a network.
To put this into practice, just open your form in Design View, go to the Property Sheet, find the Data tab, and set Recordset Type to Snapshot. Now, when users open this form, they will be unable to edit, add, or delete records, but the form will load and scroll through data more quickly. This works well for list forms, lookup forms, or dashboards where you only need to display data and not modify it. However, keep in mind that you cannot edit any data while in Snapshot mode, so use this only when you are certain the form is for viewing purposes alone.
Another small way to polish your database is to set a custom application icon. This gives your work a more professional, branded look and helps hide those Microsoft Access visual cues if you want to make it feel truly custom. If you want to set your application's icon every time the database starts up, you can use some basic VBA like the following, which runs in the AutoExec macro or in your startup routine:
CurrentDb.Properties("AppIcon") = "C:\\Path\\To\\IconFile.ico"
DoCmd.SelectObject acForm, "YourMainForm", True
DoCmd.Restore
This assigns your specified icon to the database window. Always check that your icon file actually exists, so throw in an error handler or simple Dir() check if you need to.
When building Access databases, questions about hardware often come up. For Access itself, you can run perfectly fine on 8 GB of RAM, but if you routinely use SQL Server, Office, and maybe some accounting software all at once, 16 GB is a more practical real-world minimum. It leaves room for Windows background services, and if you want some future-proofing or plan on a lot of multitasking, 32 GB is nice to have. When you buy a new PC, try to leave open RAM slots to upgrade later. To check how much RAM is installed in your system, just open Task Manager using Ctrl+Shift+Escape, go to the Performance tab, and look under Memory for a clear readout.
When designing tables, always use an autonumber primary key for each table and use the autonumber as the foreign key in related tables. For example, a Player table would have PlayerID as an autonumber, and your Card table would use PlayerID as a foreign key, resulting in a clean one-to-many relationship. You do not need a separate table just for names and IDs; keep your structure normalized but simple. If you use a combo box and want to display Last Name, First Name, you can create a query that combines the fields with an expression like:
FullName: [LastName] & ", " & [FirstName]
Then use this query as the row source for your combo box. If you add a new player while the form is open and do not see them appear in the list, just use a Requery method on the combo box after adding a record. You can do this with a bit of VBA, such as:
Me.MyCombo.Requery
This will refresh the list with the latest data.
If you need to automate tasks like emailing billing statements to customers, Access is fully capable of doing that with a bit of VBA. You can loop through customers with open invoices, generate reports as PDFs, and send each out by email using Outlook. Here's a simple outline of how such a process might look using recordsets:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Customers WHERE [HasOpenInvoice]=True")
Do While Not rs.EOF
' Generate the statement/report for the current customer
' Export it as PDF
' Send email using Outlook automation
rs.MoveNext
Loop
Automating this means you can process hundreds of billing statements with the click of a button instead of manually sending them one by one.
When it comes to printing, always use reports, not forms. Forms are designed for on-screen interaction, while reports are built for printing and exporting. If your report spills onto a second page unexpectedly, check the margins set in both Access and your printer or PDF driver and make sure you have left enough buffer space on all edges. Even small margin differences can cause a layout to shift during printing or PDF export.
If you ever want to clear out a status message box (for example, a text box on your main menu that displays status messages), you can empty it by assigning an empty string in VBA:
StatusBox = ""
If you want to reference this box from another form, use:
Forms!MainMenuFormName!StatusBox = ""
This approach makes sure that each time you update the status box, you start with a clean slate instead of piling messages on top of each other.
On the question of autonumbers, it is never recommended to recycle deleted autonumbers to fill gaps in your table. The purpose of the autonumber is to provide a unique identifier; they should not be relied upon to give you sequential, business-ready numbers. If you need a sequential invoice number, set up your own logic for those codes and let the autonumber do its main job quietly in the background. Only recreate a deleted autonumber if it was lost by mistake and you have a special reason.
For handling orders or invoices with different tax or VAT rates, you can set your database up so that each tax rate is stored in a separate table, and each order, customer, or item can reference its appropriate tax code. You might let customers have a default tax rate, but let each order or each line item override it as needed. Some companies or regions have complex tax structures, and a well-designed database will factor that flexibility in with a combination of lookup tables, combo boxes, and per-record overrides.
If you are sharing an Access database with multiple users, never run it directly from a cloud-synced folder like Google Drive or Dropbox. Copy it locally to your desktop, do your work, and move it back when finished. Shared folders synced across the internet are great for backup, but terrible for active, multi-user Access databases - they can introduce latency, file locking, and corruption. For networked Access, use a traditional file share on your local network, and when your database or user count grows, consider moving your backend data to SQL Server.
As for performance limits, Access can technically handle millions of records, but it is rarely the record count that causes problems. It is more a matter of total database file size, record complexity, and especially the stress caused by multiple users editing at the same time. If you start seeing lots of locking errors, forms lagging, or more than about 10-20 users in the system at once, consider migrating your data to SQL Server. SQL Server Express is free, and Microsoft provides tools to help you upsize your Access data to it for vastly improved concurrency and robustness.
A recurring question is about keyboard behavior on forms, especially with the Cancel or Default button properties. For the Escape or Enter key to trigger the Cancel or Default action, the control must be visible and enabled. You can make your Cancel button not visible by default and only show it (set its Visible property to True) when a lengthy process is running. Hide the button again when finished.
Access does not have an official mobile app, but for web and mobile scenarios, use Power Apps or develop a custom web front end linked to your database backend, perhaps using ASP, which is very similar to VBA if you already have experience with that.
Lastly, the Snapshot setting for forms is a great but simple way to speed up Access forms that are meant to be read-only. For any form where no edits are needed, open its properties, go to Recordset Type, and choose Snapshot. This setting tells Access it only needs to read the data, not prepare for changes, which means faster loading and less network overhead. You will not be able to edit, insert, or delete anything on the form, so make sure this matches your intended use.
In summary, always tune your database for real-life use: use snapshots for read-only forms, automate repetitive tasks with a bit of VBA, structure your tables cleanly with autonumbers and foreign keys, set custom icons for branding, do not use cloud-sync folders for live databases, and plan your next hardware purchase with enough RAM for your typical multitasking load. If your user base is growing or your database slows down, learn SQL Server and consider migrating your data there.
I hope these tips help you work more efficiently and give you ideas for new ways to optimize your Access databases. Keep learning and sharing with the community, and do not hesitate to experiment with these optimizations in your own projects. |
