Quick Queries #56
By Richard Rost
7 months ago
Optimizing Slow Database, Conditional Formatting Date
In this Microsoft Access tutorial I will show you how to tackle a variety of common questions from users, including advice on Microsoft certification, connecting AI tools like ChatGPT to Access, managing memory and object pointers, optimizing data validation, troubleshooting slow databases, conditional formatting for deadlines, and understanding VBA visibility and functions. You will also hear tips on best practices for networking, form design, and hear survey feedback on what new Access topics viewers want to see covered. This is part 56.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries Friday, Microsoft certification, Power Apps, Power Platform certifications, co-pilot integration, OpenAI API, Azure API, VBA memory management, recordset cleanup, pointer vs object VBA, composite indexing, nightly event validation, network performance troubleshooting, front end back end split, conditional formatting expressions, date field comparison, SQL Server migration, VBA public private scope, survey results, layout view issues, database design theory, Excel database transition, Spock Hand reply
Intro In this video, we answer a variety of Microsoft Access questions for Quick Queries Friday number 56. Topics include advice on the value of Microsoft certifications for Access developers, an overview of integrating AI like ChatGPT and Copilot with Access, and best practices for managing object variables in VBA. We also discuss strategies for validating data entry, troubleshooting slow networked databases, setting up conditional formatting with dates, and the differences between layout and design view. Additional viewer questions cover VBA scope, public and private procedures, and common misconceptions about Access.Transcript It's not Friday without Quick Queries Friday. Hey, welcome to Quick Queries number 56 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
What is Quick Queries for those of you who haven't seen one before or haven't watched the first 55 episodes? Basically, I take all the questions that people email to me or post in the forums or share on my website. Oops, someone's beaming in. I am going to put all those together into one video, and we call it Quick Queries. It's queries about Access and stuff. So let's see what we have today.
First, let's go to the mail bag.
Sometimes I get emails from my students and I think that they would make great examples for Quick Queries or even regular videos. But I don't know if the student told me this in confidence or if he or she doesn't mind having this in the video. So I just put students on the bottom because I don't want to get anybody in trouble if their job knows that they are looking for something, whatever. If you email me a question, make sure you say whether or not it is okay for me to use it in the video.
This student said, "I'm considering applying for a Microsoft certification and would like your advice on which path makes the most sense for my goals. I have strong experience with Access and recently began developing Power Apps. I enjoy creating solutions and I'm interested in moving into training."
Good move. The old saying is those who do, do, and those who can't, teach. I disagree. I think those who are sick of doing, teach. I got sick of it.
I currently work at a midsize law firm with both legal and technology responsibilities, and my hope is that my paralegal role can grow into a position focused on training and development with Microsoft products. That sounds fantastic.
Now, me personally, thanks for reaching out, students, to pick my brain about Microsoft certifications, but I have to be completely honest. Personally, I never put much stock in certifications. In fact, I have a whole page about it on my website. Everybody always asks me, "Do you offer Microsoft certification? Can you teach me to pass this test?" No, not really.
I have always found that if you are looking for a certification because your employer wants it, or if you are looking for a job where they want someone who is certified, then yes, it makes sense. But for me personally, I have been self-employed for 30 years, so I have never really needed it.
Now some of the moderators on my website have gotten Microsoft Office Specialist Expert certifications. Basically, you take a test from Microsoft and they give you the specialist badge, and then you get the badge on my website too. As to whether or not it has actually helped their careers or their businesses, that would be a question for them. Feel free to post something in my forums on my website and I am sure they would be happy to chime in.
In a corporate environment like a midsize law firm, certifications can be helpful, particularly if you are eyeing a move into more formal training or a development role. HR usually likes to see something official on a resume.
But personally, I have hired people in the past when I used to have employees. I have hired people with certifications that didn't know squat that I then had to teach everything. I have also hired people that didn't have any certifications like me that knew more than I did. To me, certification doesn't necessarily indicate that you know what you are doing. But it also is like a first level for an employer to be like, "Okay, at least they've passed a certification. They have to at least be this good as a base level."
Since you mentioned branching into Power Apps, which I am still learning myself, you might want to look at the Microsoft Power Platform certifications. They have Power Platform Fundamentals and some other Power Apps-focused designations. Those might be a little more specialized than a broad MOS exam.
Post something in the forums if you do not mind your name out there, and maybe the people who actually have certifications can give you a little more information. But that's my take.
Next up, question from the forums on my website. The forums on my website are public, so if you post there, then I know you're okay with your name being public.
So Ray, one of my ten-year students: Can you use co-pilot integration like you are using ChatGPT in Access? If so, which is better, pros and cons?
Well, co-pilot is built into Word, Excel, PowerPoint, and a couple other apps, but of course not the red-headed stepchild, the literal red-headed stepchild, which is Access. It is literally red-headed. I do not know why they did that, maybe intentionally.
So if you want AI inside of Access, you have to connect with a service that uses API calls. I have several videos that show how to do that. If you want to learn how, start here. It's not hard. It's like five or six lines of code, but you do need to set up an account with OpenAI. It does cost a little money. You have to get an API key that you put into your Access database. So it's not free, but it's not expensive either, unless you use it a lot. Like I use it a lot, so I spend a few bucks a month on it, but it's not bad.
I have been using ChatGPT since it came out, and the OpenAI API, which is basically you are talking to the engine behind ChatGPT through code, just because it was the first major AI that was available, works well with VBA, very simple calls.
Microsoft offers its own API through Azure, which basically is the same as the GPT models, but it's managed inside of Microsoft's ecosystem, so you have to use their servers. So it mainly comes down to convenience versus enterprise needs. OpenAI is quick and easy and it's great for individual developers like me. Azure co-pilot, they call it, is better suited for larger companies that need Microsoft 365 integration, compliance needs, security controls, that kind of thing.
Both can work in Access, but since there is no built-in support, you have to wire it up yourself like Scotty with the engines. But I teach you how to do it. So is there any benefit to using Microsoft? No, not really. There are a lot of other AIs out there that have similar APIs that you can connect to, but I think ChatGPT is the best. That is my opinion and I am sticking to it.
Maybe once Microsoft gets around to actually integrating co-pilot into Access at the application level, then I will start recommending it. Until then, no.
Next up, one of my favorite things is if you set it, you have to forget it. In other words, if you open a recordset or set an object variable, you should clear it when you are done by setting it to nothing. Here's an example: we set a couple of recordsets here for food and log and stuff, and then at the end we set them to nothing.
That is because this memory should be cleared by Access automatically when the function exits, but it doesn't always. Especially if there is an error thrown or something. There are a million reasons why over time Access has what is called memory creep. In other words, your Access database is not the best at cleaning up after itself. So it's always good to reboot your database daily if it is running constantly.
A couple of sharp-eyed students, and I got more than one comment on this, believe me, noticed that I do not always do that with "set DB = CurrentDB" when I make a database variable. You are not wrong to point that out. It is not a bad idea to do that, but it is not really necessary either, and I do not like going over this unless it's a really advanced lesson because the reason why is really technical and I do not want to confuse beginner programmers.
It's a good rule to follow: if you set it, you have to forget it. But this is basically a pointer. For those of you who care, here is the technical jargon.
A recordset and other types of object variables like that, they are actual real objects in memory. They hold space. They reserve space. They hold rows, fields, buffers, sometimes even locks on database pages or records. If you do not close them, those resources can stay tied up until VBA eventually decides to clean them up. Sometimes it doesn't, and that can cause problems. So a recordset is an actual block of memory address. It is an actual block of space in your system's memory.
What you get back from this is actually just a pointer to the database itself. It doesn't reserve a lot of memory. It is not setting aside a block of space and saying, "Hey, we are going to call this first name or we are going to put the records from this set of records here in this space of memory." It just goes to the database.
In some other languages like C, C++, and other low-level languages, you can declare variables that actually reserve a block of memory or you can create a pointer that just points to something else, and that pointer by itself doesn't take up much space. It just references the real thing.
So a recordset is like an actual chunk of memory reserved for data, a copy of the records that you are working with. The database reference from CurrentDB is just a pointer to the database engine, and once a procedure ends, the pointer disappears. Even if it doesn't, it doesn't take up much, a couple of bytes.
Now, VB and VBA do not really have pointers, but they do something similar if you are familiar with passing variables by ref or by val.
Let's say you have a variable like a global variable or whatever. You have first name in it. That takes up, what, seven bytes, let's say. You want to send this into a function or a subroutine, but you want to work with a copy of that data so you do not accidentally mess up the original. You send it by val. You declare a function and then by val, that's a string, so S equals a copy of that data. Now you are taking up 14 bytes of memory roughly, and that is roughly speaking. Now the default actually is by ref. If you do not specify anything, you get by ref, which means you are actually passing a pointer to that memory address to the function. So if you change it, you are changing the original, but in here it does not take up more memory, it just points to this stuff up here. So when you pass S as a string, you are not taking up 14 bytes of memory; you are taking up 7. But changes to S in there will change first name. If you do not believe me, try it. In fact, I have a whole video on this topic on my website. It is a five-year-old TechHelp video, so go watch it. It's pretty good.
So when you reserve a recordset, when you create a recordset, you are creating a copy of a bunch of records in memory. When you make a pointer to a database object, you get that. If you are pointing to CurrentDB, you just get a pointer to the database. That is why it is not critical. If you declare it, oops, that click, critical... my slides are loaded up! It's not critical if you do not clear it. You should, though. It is not a bad idea to get in the habit of doing it.
My best practice rule of thumb is still, if you set it, you have to forget it. There is nothing wrong with putting "Set DB = Nothing" down here. It is a good habit to be in.I just don't always do it because I know in the back of my mind, you don't have to. So you might catch me not doing it. But I should do it when I'm teaching this stuff. I really should. All right, it's not critical, but it keeps your code consistent, especially when you're trying to teach people. And you don't run into trouble later if you change that database object, because this is only valid if you're working with current DB. If you set up a database pointer to a different database object, you might reserve a whole different block of memory.
Again, we're getting into really complicated stuff. But if you're just working with current DB, that's not an issue. What is an issue is that you can get your own coffee mug that's got that on it right there. I'm a merch store. I got mine sitting on my Access shelf right next to my book and my MVP award. So yeah, get yours today.
Next up, one of my students, Mika, asked how to deal with situations where you want to make sure users don't enter illegal combinations of data in a form. For example, let's say you got two things that can be paired together, but not every possible combination is valid. The question should be, do you build the form that never allows a bad combination or is there another way?
Now this, of course, assumes that basic indexing or even composite indexing doesn't solve the problem. And it's going to take a ton of complicated VBA that might slow down the data entry process because you've got to search for thousands and thousands of other records in the database to make sure that the one you're entering now is valid. That's a situation that doesn't come up a lot. Once a month, you might have a rare instance. There was a whole thread with screenshots and my moderators like Matt are doing a great job of helping. This went back and forth for a while.
But the solution to this problem isn't necessarily what I want to talk about. What I want to talk about is sometimes it's ok to allow possible invalid data to keep things flowing if it's something that doesn't happen forever.
So, let's start with the database, and especially when you're dealing with really, really big databases, especially if you're dealing with networking information or stuff that's over the web, you're working with SQL Server. To update a record, if you've got to go and do a bunch of DLOOKUPs and find things that might conflict, but it only rarely happens, one of those rare instances. But every single time you add or edit a record, it's got to do this lookup that takes three minutes.
You have to sometimes balance the ease of data entry with that kind of a security lookup to see if it's a valid combination or not, especially if it's not critical.
I had this one client where they shipped a bunch of products. But from the time the order went in the system to the time they actually shipped, it had to be produced and all this stuff. There was a week or two in there where they had time to discover if there were any problems with the order. The solution that I recommended was: let your people put the orders in so they don't have to sit there and slow their work day down with lookup after lookup after lookup. Then, once the order is in the system, since we've got at least a couple of days until it goes into production, let's run a nightly event.
Have the nightly event at three o'clock in the morning check through all the new orders that were entered and make sure they're valid. Then the next morning when you come in, it could flag it and say, you know what? This customer placed an order that's invalid based on all of these other reasons. So instead of slowing down the sales reps and the people on the floor while they're entering in an order, the nightly event can do it.
I have something similar on my website. If a user signs up, let's say they sign up as a silver member on my website, and then they go away for a couple of days. They come back, they're not logged in. They forgot they signed up, and so they sign up again. Well, my order form that you use to sign up doesn't do a database check to go and see, hey, you're already in there. Yeah, I probably should, but I flag it at night with a nightly event because it's two separate systems. This way, just at night, three in the morning, it runs through all these checks. That's one of them, and it will at least tell me. So the next morning, I'd be like, oh, hey, you already have a membership. So we'll just take what you paid and put it toward the next month, or I'll give you a refund. It's that kind of thing. I don't want to slow down their data entry.
So it's these kinds of things you can do. You could do it in a nightly event instead of slowing down all of your workers. That's just something I wanted to share.
So do you have to make every data entry perfect? No, as long as you can check for it later. Of course, this assumes that it's not critical, and it assumes you've got some time for the nightly event to catch that problem. If it's something you need to know right away, like you're shipping immediately, then you have to do the lookup then. Or at least you could have it as a background process, so that your server does it in the background while you can continue doing other work. Then it could pop a message back to you and say, hey, nope, can't ship that order.
So there's always a balance in this. This big, long message thread where they're going back and forth with stuff kind of reminded me to talk about this because I don't ever think I've talked about it before. That's what these quick queries are for, for me to just brain fart against everything for you guys. Just sharing my decades of experience with this stuff. I've had to do this for a couple of clients.
Ok, let's head over to the YouTubes.
Bish, Kiddell says: great video, learned a lot. I also can't find the links of the next steps mentioned in the video. Can you please share the link? This one comes up at least every couple of weeks. I've got to point people to the "where are the links" video. It's not your fault, though. YouTube does a good job of hiding them. There's a description box down here and you have to click on "show more" and then that'll open up and you'll see all the links in there.
Next up, I get this comment once in a while. Shafferra: Thank you for your dedication and enthusiasm to share your expertise in the field. You're welcome. I have one question though. I've noticed some of your videos are reserved only for paid users. However, some of us can't afford to do so, like in my case, or we come from less privileged countries. He's from Ethiopia. Anyway, you might extend your generosity to cover such a scenario.
Well, thank you for your kind words and for sharing your situation. I completely understand that in some parts of the world, the US dollar doesn't stretch very far and I never expect everyone to pay. That's exactly why I put so much time into creating free content like this video. There are over 1,100 free TechHelp videos on my channel, plus free beginner courses and lots of other stuff that you don't have to pay for. There are literally hundreds of hours of training available to anyone everywhere, free of charge.
At the same time, this is what I do for a living. So I do need to reserve some lessons and some videos and create some extra value so that people will pay for memberships and my courses in order to support what I do, which is producing videos. If I didn't generate any revenue, then I wouldn't have the time to create all this material and I would have to go get a real job. Then there would be zero videos on my website and my YouTube channel.
I really appreciate your enthusiasm for learning and your message does mean a lot to me. Please know I will continue to make as many high quality videos as I can for my channel so that everyone, no matter where they live and no matter what they have for income, can still learn from my materials. But I do have to have some extra stuff that is for members only and for paid students because I have to pay my bills too. So thank you for your comment.
It would be nice if I could just do free videos on YouTube and earn enough from the small amount that I get from YouTube advertising dollars, but it's nowhere near enough. 90 percent of what I do is Microsoft Access, which is what I love and what I'm an expert in. But the pool of Access users is much, much smaller than people that do cat videos. While there are a lot of YouTube content creators out there making millions of dollars a year, trust me, I am not one of them. I make enough and I'm happy that I can do this as my full time job. But what I get from YouTube advertising is just a drop in the bucket compared to what I need to survive every month. So I rely on paid memberships and my paid courses to pay my bills.
Next up, Ross Zeke 300 says he created a database and after six or seven months, it's very slow on the network. So please give me some suggestions to make it go faster.
Well, definitely, make sure your network is up to par. Make sure it's a wired network connection. Access performs very poorly over Wi-Fi. Check your network speed. Make sure that's solid. Use cat 6 cable and quality switches and routers. Do a network test, ping, traceroute, all that stuff. Look for latency, dropped packets. There are tons of free network software tests out there. Just download a utility and try it. See what's changed. Make sure no one else is saturating the network with large file transfers, video streaming, or gaming.
I used to have that problem with my employees back in the 90s. I used to set up a network in the office to run our database for the business. At lunchtime, people would be on there playing Duke Nukem and downloading stuff. That was before the internet was really high speed. As soon as someone got online, I knew about it.
As far as, you know, past your network, check your Access database. Make sure it's split properly. You got back end and front end. The back end's on the server. Front end is on every machine. Every local machine has to have a copy of the front end. You don't share the front end on the network. That's a recipe for slowness and disaster. It's much more easily corrupted.
Perform a compact and repair on the front end and the back ends. Review your indexing. Since you said it ran fine at first, but after six or seven months, it's slowing down, that tells me also that your database is growing. As you're adding stuff, now it's time to start looking into running things more efficiently. You have your database working, but now you have to take some time and optimize it.
Check your indexes. Any field you do searching and sorting on needs to be indexed. If you're opening a continuous form that's sorted by first name, last name, both of those fields need to be indexed. Look for design inefficiencies, pulling a select star, pulling all the fields down when you don't need them from big tables, calculated fields, lots of DLOOKUPs that don't need to be there.
Check how much data you've actually added. Access works up to a two gigabyte limit for your back end data. If you're approaching that, I don't know what kind of stuff you're feeding into this database.But if your database is growing in size, it's going to get slow. Like I always say in my classes, a big database is a slow database. So if you're getting big, it might be time to start thinking about SQL Server, whether you install it locally in your network or get an online version.
And of course, that backend machine, all your machines really, the bigger your database gets the more processing power you need. Make sure you have decent hardware - RAM, CPU, all that stuff, and plenty of extra hard drive space. If there's not enough hard drive space, if you're running low on drive space, there's swap file information that has to be written and read to the hard drive. So make sure you have plenty of drive space.
If you've got any anti-virus software that you've installed lately, aside from what comes with Windows - the Windows Defender or Microsoft Protection, whatever they call it today, used to be called Windows Defender - that's all you need. If you have McAfee or any of these other weird virus scanners that are out there, Norton, whatever, get rid of it. They're unnecessary. They'll slow you down. Some of those do real-time scanning of your ACCDB file.
I had this problem with Norton Antivirus years ago. Client's database worked great for a year, and then all of a sudden they call me - the database slowed to a crawl. I go in there and look and ask if they installed anything recently. "Oh, yeah, we just installed Norton on all of our machines." Oh, okay. Here is your bill. That's what's causing the problem. I disabled Norton and everything went back to running perfectly fine because that virus scanner is scanning that ACCDB file, and it's slowing down the read and write to it.
Make sure all of your users are on the same version of Access. I've seen performance problems where most people are running the newest version 365 and one person in the back is running Access 2016. That can cause problems and slowdowns, believe it or not.
So thanks for the question. After rattling off all of that off the top of my head, this could probably make a good list for my website. So I've got the troubleshooter, and this guide is for problems specifically with Access itself. What you're telling me, Razik, is probably more of a network issue, I'm guessing, but I do not know, maybe not. If it ran fine for six months and now it's starting to run slow, actually run down this troubleshooter too. There should be something on there, maybe.
I've tried to list the stuff in the order that it becomes more likely. The most likely stuff that'll fix it, believe it or not, is just restarting Access. That fixes so many problems. Oh, my database is working weird. Okay, restart it. Oh, now it's working fine. Reboot the computer. That kind of stuff. Reboot the PC. Weird stuff sometimes is fixed by just rebooting everything.
If you haven't yet, shut down your whole network and restart it, including your switch. I had a problem in my own home a little while back where all of my wireless devices were acting weird. Alexa was not answering and light bulbs were turning on. I just rebooted the Wi-Fi router and everything went back to normal. So look for the ON-OFF switch. When in doubt, three-finger salute and restart the computer.
I think I'll turn this into another checklist or maybe I'll just add these things to the pile. I'll add these to the troubleshooter.
Anyways, thanks for the question. Next up, question about conditional formatting from Michael. This says, I'd like to use an expression that will show a deadline is approaching within so many days. I need to know that I'm within 30 days of a deadline and the field be highlighted. Let me know. I've tried field value is less than date plus 30 as suggested somewhere else, but that does not work. That should work. I've also tried greater than and flipping the minus to a plus in all combinations. Is 30 not the right number to use?
What I'm thinking happens is a very common problem that people have with conditional formatting. Let me walk you through it real quick. I think I talked about this in the video or one of my conditional formatting videos, but if not, here's the most common thing that trips people up.
All right, here's my TechHelp free template. You can get a free copy of this off my website if you want to. Here's a customer list that's got a Customer Since field on it. Let's pretend that's the field you're trying to put conditional formatting on.
Okay. Customer Since - go to Format, Conditional Formatting, New Rule, field value is, and then in here, go less than, and then in here, you're going to put in date() + 30, or you can do less than or equal to, whichever one you want. Drop this down, let's put yellow in there, hit OK, hit Apply, hit OK, close it and then open it. Now most of these will show up at it. Let's put one way in the future. Let's put in here 2027-11, and that one is not formatted.
So that absolutely should work. Now, what usually throws people for a loop is if you come in here and you put in just date + 30 like that. Sometimes, see, that's not working because if you look at it and come here, what Access does behind the scenes when you're not watching, it makes date a string, it puts it inside quotes, which we don't want. We want the date function and make sure it's just two parentheses - open parenthesis, close parenthesis, right next to each other. No extra spaces inside there, and that should do it.
I see you do have the parentheses right. I tried it with spaces around that plus, that's not a problem. So I do not know, try reproducing the steps and see, try it again. I have a couple of different videos on my website about conditional formatting too, but you are definitely on the right track. Just go through the steps again, try it with a different field.
Oh, another thing I just thought of, make sure this field is a date field, make sure it's not a string field. I've seen people try to do that before too - they had their whatever field has the date in it as a text field. So make sure in your design, you are dealing with an actual date/time field. The easiest way to tell for sure is that in the table, the numbers will line up on the right side, whereas text will line up on the left.
I've seen people before take dates and then in a query format them, and then the format from the query will treat it like a string. See, look, it's working fine now. But let's say I made a query (Create, Create Design). Let's say I used the customer table and I'll just bring in a few fields - First Name, Last Name. Then for the Customer Since, let's say I make this field a new date and I say Format(Customer Since, "mm/dd/yyyy"). Using the format function, when you run this now, it looks like a valid date, but notice it's on the left side of the cell. That means it's a text value.
Always reserve your formatting for the very last step. Do not format queries and then try to work with this as a date because now look what happens if you have new date. Save it as - I'll just say this is CustomerQ. Now, let's take this customer list, design view, and get rid of Credit Limit because we do not have it in there, and State. Base this now off of that CustomerQ that we just made. And now Customer Since is missing, so let's change this to that new date. Copy, paste the name, save it, close it, open it, and look. There's my value from the future, it is still showing up as yellow because this is not a valid date field and you cannot compare that to a date in your conditional formatting because it's not a date, it's text.
So there are a couple of reasons why it might not work. Hopefully one of those solves it for you. If not, post another message. Do not post replies to this because I almost never see replies to original posts on YouTube. Post it in the visitor forum on my website if you're not a student already. I check my website more, and the moderators on my website are fantastic. They answer stuff a whole lot faster than I do.
You said you tend to trial and error. I save you hours. Unfortunately, the only way you can help is to like and subscribe. Thanks for everything you do. That's all I ask for. Like and subscribe helps to promote my videos and I'm happy with that. Thank you. If you cannot afford to be a paying member, that's fine with me. I appreciate you. Thank you.
I just want to send a quick thanks to everyone who filled out my survey. Once a year or so I do a survey to all my subscribers and students, just to put my finger on the pulse of what you want to see for the next year or so. Let me share the results. We got about 113 responses after three or four days of this thing being online.
So it's what I expected. Most of you want to see Access developer stuff. That's never going to change. That's my forte. That's my expertise. That's what I'm always going to do.
SQL Server is in second place. I have some SQL Server stuff available for taking your database online, but I want to do a series of videos on setting up SQL Server in your office. There are a whole lot of benefits to that too. It makes things a whole lot faster just on your network.
I'm surprised database design and theory is in third place. Look for more lessons on that. I'll do some videos on just design theory, get into some nerdy stuff. I have my old database design and practice books off the shelf that are collecting dust. There's some really good stuff in there. We'll do some.
I think we did a video on bubble sorting earlier. There's all kinds of insertion sorts and stuff I learned in computer science class in college. We're going back to the early 90s here.
I see a lot of you are interested in AI tools. That's good. I have a bunch more ChatGPT type stuff coming out. Power Apps and Dataverse - I'd like to get more involved in that too. Power BI, Python - absolutely. I've been using Python myself for a year or two now.
Word and Excel. I see a lot of you are into Excel. I'm assuming you want to learn more Excel and Word and how it ties into Access. I'm probably going to redo my Word and Excel videos from the beginner lessons because if I'm going to be promoting Word and Excel videos, I also want to attract new eyeballs, new people.
What my goal really is with Excel at least is to redo my Excel course, but this time I want to teach people, okay, here's how you do something simple in Excel, and this is a great tool and it works perfectly for this. But as your needs expand, as you get more data, as your spreadsheet is now 5,000 rows, it might be time to start considering a database.
So I'll be evangelizing Access that way - Excel is great for what it does, but you cannot grow it.Just like you can outgrow Access as a database engine. Access, in my opinion - and I talk about this all the time - is by far the best desktop rapid application developer for building an interface to work with.
The database engine behind it, once you upgrade to SQL Server, is so much more powerful than Access by itself. But that's a whole separate series of videos.
I have seen not too many of you are interested in PC hardware, so I'm going to throw all those videos - I'm just kidding. I don't have any. I have very few videos on hardware. Although I do get a lot of people asking me for Windows networking.
I'm surprised that this is so low because so many people ask me how to set up a network to run my Access database on. I'm probably going to do a video on that. It's not hard, but as I mentioned earlier, you definitely want to be using a wired network for Access. If you've got someone on wireless, set them up with a remote login. Set up a virtual PC somewhere for them. Don't let them run Access over the network unless you're using SQL Server.
Because with SQL Server, the server is handling making sure you don't drop packets and stuff. The way Access normally works if you're just using Access without a file server is that each front end is reading and writing to that ACCDB file. Any one of your front ends can corrupt that back end if it's in the middle of a write and that wireless signal drops, and you could have a corrupted database. Then you're going to be compacting and repairing every three days.
I know because years ago, I tried running my Access database on my laptop from my kitchen and it would, once in a while, nope. So, got to go compact and repair and restart everything.
Anyways, there's the survey results. You can tell the days on the Quick Query Fridays when I don't have anything going on later and I'm just sitting here and talking. Some Fridays I'm like, all right, I've got somewhere I have to be in two hours so we have to make this snappy. Today, nope.
I also wanted to mention, sometimes when I see your comments and I just give you the Spock Hand, it is because I want to let you know that I've read your comment and I acknowledge it and want to give you a reply. Also, when I go to YouTube and look at my comments, I can say just show me all the ones that are unreplied to so I know which ones I have yet to at least read and reply to and stuff like that. Please don't think the Spock Hand is rude. It's me saying, "Okay, gotcha."
G-Gonchens says, "Strange design by Microsoft that you can move a field or a label in layout view without the corresponding piece moving with it." Yeah, the attached label and text box. I can see why you don't prefer it. I'm not a fan of layout view. I never have been.
A lot of features when they first came out were extremely buggy, including things like the tab controls and just other things like that. Now, I just get a bad taste in my mouth from them because they didn't work initially, so I just don't use them, like layout view. I'm the same way - I don't like layout view. I've had layout view completely corrupt forms because you switched to layout view to maybe tweak where a label was and move a text box over and it just blew up your VBA.
Now that might be my prejudice from when it first came out; that bug might be gone, but I just don't like layout view. I just like to switch back to design view, move things around, and then go save it, close it, and reopen it because that way you make sure your design changes are saved. If you get problems in your VBA code, it doesn't lock your database up, and you haven't lost all your form design changes too.
So yeah, I don't like layout view. I will never use layout view. I think I used it for one thing in one of my videos - so I showed you how to easily take a bunch of controls and line them up together, but no, I don't bother.
Next up, Felgons says, "Hello sir. I want to learn the details of how and where to use public, private, or sub functions and, along with it, brackets, watching your videos daily. But this is really magic when you use it, especially in the Fitness series. See, I told you guys the Fitness series is awesome. Lots of tips in there. I learn a lot from your videos but need more details from your videos."
Well, keep in mind, my TechHelp videos are really designed to just go over the topic that I'm covering that day - that one topic. The Fitness series is an exception. It's a series in which we're building a database from scratch, but I'm not trying to be extremely comprehensive with it. The fact that I'm showing it includes cool tips and tricks, but yeah, there's a lot of foundational stuff in there that you might need previous videos for. I mention that up front, like make sure you've watched my Intro to VBA video.
Here's another video you can watch on scope and visibility if you want to learn about things like variables, public versus private, like you mentioned, dim, procedure-level scope, that kind of stuff, subs and functions. Here's a video on creating a function and you can learn 90 percent of this by just poking around in my free TechHelp videos.
That's something that lots of people do. But that's also why I have a complete Developer Course where you don't have to jump around from a bunch of different videos. You can just start with level one and follow the course all the way through, and I teach things in the order that they should be learned. We have our introduction, we have variables, we have if-then statements, and then it just builds on the stuff from the previous lessons, unlike my TechHelp videos, which just jump around because that's what they're designed for. They're designed to answer questions and to teach specific things that might not be covered in my course.
So if you need more details, then go to my website and search for the topic that you're looking for and chances are you'll find a topic for it. If I don't have a video that covers it, let me know. Maybe I'll make one. And if not, well, I've got the full course that fills in all the gaps, let's say.
Next up, we've got a comment from who I believe is the author of an article that I did a video on a few years ago. He wrote an article, The Five Limitations of Microsoft Access, which I reviewed and I just called him out for being blatantly wrong about Access. His comment says, "Guess who wrote that article? Oh, hi, how you doing? Are people still using Access? Yes. Definitely, yes. It is a very cool, era-defining software, yes it is, but I think the world has moved on somewhat."
Well, respectfully, first of all, thank you for the comment. I appreciate you stopping by. I tried to revisit your original article to give it another read-through, but it looks like your site timed out. Sorry to hear that.
As for the world moving on, I'd say the world has added new tools, but Access never left. For small and mid-sized businesses that need a solid database for office users, there's nothing better. Pair it with SQL Server and you've got a world-class solution.
So yes, there are lots of great new software applications and tools, and things like web-based databases and things for mobile, but on the desktop, for desktop PCs, for office workers that actually use the database in their business, Access can't be beat.
I challenge anyone to come up with a better desktop-based rapid application development tool that's network-ready, integrates with Office, integrates with SQL Server, and is as easy to use and learn as Microsoft Access. But again, thanks for your comment and thanks for stopping by.
And with that, we're done with Quick Queries 56. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you on Monday.
TOPICS:
Advice on Microsoft certification vs real-world experience
Overview of Microsoft Office Specialist certifications
Introduction to Microsoft Power Platform certifications
Comparison of OpenAI API and Azure OpenAI integration
How to connect ChatGPT to Microsoft Access using API calls
Pros and cons of using AI integration in Access
Explanation of object variables and memory management in Access VBA
Why you should set object variables to Nothing in VBA
Difference between object references and pointers in VBA
Passing variables ByVal vs ByRef in VBA
Guidance on when clearing CurrentDB object references is necessary
Techniques for preventing invalid data combinations in Access forms
Using nightly events to validate database records
Balancing real-time and post-entry data validation
Optimizing Access database performance on a network
Importance of using wired connections for Access databases
Proper splitting of Access databases into front end and back end
Regular compact and repair of Access databases
Checking and improving indexing in Access tables
Dealing with Access database size limitations
Identifying hardware and network issues affecting Access
Effects of antivirus programs on Access database performance
Version compatibility considerations in multi-user Access environments
Troubleshooting slow Access network performance
Configuring conditional formatting with date calculations in Access
Troubleshooting issues with conditional formatting expressions
Difference between formatting date fields and text fields in Access
Potential pitfalls using formatted fields for conditional logic
Best practices for conditional formatting in Access
Views on Access layout view vs design view
Risks associated with Access layout view corrupting forms
Public and private scope for VBA procedures and variables
Where to use subs, functions, and brackets in VBA
When to use public vs private scope in the Access Fitness series
Responses to common misconceptions about Microsoft Access limitations
COMMERCIAL:
In today's video, we're talking about all kinds of Microsoft Access questions in Quick Queries Friday number 56. We'll discuss whether Microsoft certifications really matter for Access developers, compare ChatGPT and Copilot integration in Access, and talk about when it's important to use "Set ... = Nothing" to free memory. We'll cover strategies for validating data entry without slowing down your users, offer tips to fix a slow networked database, and troubleshoot common mistakes with conditional formatting on date fields. You'll hear about why some videos are reserved for paid users, survey results on what viewers want to learn next, and even a quick take on layout versus design view and best practices for using public, private, and sub routines in VBA. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. According to Richard, when do Microsoft certifications make the most sense for someone to pursue?
A. Only if you are self-employed
B. If your employer requires it or if you are seeking a job that requires certification
C. When you want to start your own business
D. If you want to learn more about using Microsoft products for personal projects
Q2. Regarding connecting AI to Access, what is the main way to use ChatGPT or similar AI tools inside Microsoft Access right now?
A. Use the built-in AI menu in Access
B. Connect with a service that uses API calls
C. Install the co-pilot add-in directly from Microsoft Store
D. Only available through manual data export and import
Q3. What is Richard's best practice regarding setting object variables in VBA?
A. Only set variables but never clear them
B. If you set it, you have to forget it — set the object to Nothing when done
C. Use only global variables so you do not need to clear them
D. Let Access always handle clearing all variables automatically
Q4. In VBA, what is the difference between a recordset and a database object assigned with Set DB = CurrentDB?
A. Recordset is only a pointer, and DB is a full object in memory
B. Recordset reserves memory for data, DB is just a lightweight pointer to the database engine
C. Both take up large chunks of memory
D. There is no difference, they behave the same
Q5. When passing arguments to functions in VBA, what does passing by reference (ByRef) mean?
A. You send a copy of the data to the function
B. The function gets a pointer to the original data, so changes affect the original
C. It prevents the function from changing any data
D. It uses more memory by duplicating variables
Q6. Why might it be acceptable to allow users to enter possible invalid data combinations in a database form and check for errors later?
A. Because it is never possible to prevent invalid data
B. If the validation is resource-intensive and errors occur rarely, checking later (such as with a nightly event) is more efficient
C. To avoid having to run any validation at all
D. Only if the data is never used outside the database
Q7. What are some recommended steps to improve the performance of a slow Access database on a network?
A. Use Wi-Fi networking for all users
B. Ensure each user runs the front end locally and maintain a split database with the back end on the server
C. Store all data in a single table and avoid indexing
D. Never compact and repair the database
Q8. Why is it problematic to run Access databases over Wi-Fi?
A. Wi-Fi is faster than wired connections
B. Wi-Fi increases the risk of dropped packets, which can result in corrupted databases and poor performance
C. It is required for SQL Server integration
D. Access does not work on wired networks
Q9. In the context of conditional formatting in Access, what is a common issue that prevents date-based formatting from working?
A. The field must contain unique values only
B. Using a date value that is saved as text instead of a true date/time field
C. Using colors other than standard ones like red or yellow
D. Formatting queries before creating forms
Q10. According to Richard's survey results, what type of content is most requested by his viewers?
A. PC hardware reviews
B. Access developer content
C. Windows networking setup
D. Cat videos
Q11. Which of the following is considered a good practice when designing forms for data entry in Access?
A. Always prevent users from entering any invalid data, no matter how resource-intensive
B. Balance immediate validation with workflow efficiency, and consider handling validation at a later stage if errors are rare
C. Avoid running validation altogether
D. Only validate after months of data entry have passed
Q12. What is Richard's opinion about layout view in Access for designing forms?
A. He always uses it for everything
B. He prefers design view since layout view has caused form corruption and is buggy
C. Layout view is mandatory for all modifications
D. It is more secure than design view
Q13. What can cause an Access database to run slowly after several months of use, even if it was fast at first?
A. The layout of the forms changes on its own
B. The network speed always improves over time
C. Database growth, inefficient indexing, overloaded network, outdated antivirus software, or running mixed versions of Access
D. Access databases automatically optimize themselves
Q14. What is needed to connect Microsoft Access to the OpenAI API for AI features?
A. Only an Access license
B. An API key from OpenAI and a small amount of VBA code
C. Only a Microsoft Office 365 subscription
D. No account or subscription is necessary
Q15. What does Richard say about the relationship between Excel and Access as your data needs grow?
A. Excel is suitable for all database-level work forever
B. You should move from Excel to Access as your data grows and you need more scalability
C. Access is never needed if you already use Excel
D. Access can only be used for basic databases
Q16. According to the video, what is one reason antivirus software can slow down an Access database?
A. It speeds up file transfers too much
B. It can perform real-time scanning of the ACCDB file, slowing down database operations
C. It improves database reliability
D. It is required for network connectivity
Q17. If you see a field value aligned to the left in a datasheet or form in Access, what does this usually indicate?
A. The field is numeric
B. The field is a date/time value
C. The field is a text value
D. The field cannot be formatted
Q18. Why does Richard maintain both free and paid content on his AccessLearningZone site and channel?
A. He only wants to teach paying students
B. He wants to provide as much free learning as possible but needs paid content to sustain his work financially
C. Free content is a legal requirement
D. He plans to make all content paid eventually
Q19. When hiring, what did Richard say about the value of certifications?
A. Certifications always guarantee high skill
B. Everyone with a certification knows more than those without
C. Certifications are a baseline, but real knowledge and ability matter more
D. He refuses to hire anyone with a certification
Q20. What is the purpose of splitting an Access database into a front end and back end?
A. To make it more difficult to use
B. To ensure the back end contains data and the front end contains logic/forms, improving stability and performance on a network
C. To store all data in the front end
D. To prevent multiple users from accessing data
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-C; 14-B; 15-B; 16-B; 17-C; 18-B; 19-C; 20-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I'm answering a wide variety of Microsoft Access questions sent in by students, forum users, and visitors to my website. For those not familiar with Quick Queries, let me explain how it works. I take the questions people email me, post in the forums, or submit through my website, and I put together a collection of thorough, to-the-point answers all in one place. So let's see what came in this week.
First, I want to address a question about Microsoft certifications. A student asked for my advice because they are experienced with Access, just getting into Power Apps, and considering a shift toward training and development, possibly at their current law firm. They're curious which Microsoft certification path would be best.
Here's my honest perspective. Personally, I have never placed much stock in certifications. I've always been self-employed, so obtaining them wasn't necessary for me. If your employer wants you to have a certification, or if you're seeking a job where it's a requirement, then it makes sense. Otherwise, your actual skills often matter more than a piece of paper. I've hired people with certifications who didn't know as much as those without.
That said, in a corporate environment, especially if you want to move into formal training, having a certification like Microsoft Office Specialist or something on the Power Platform can help with HR. Certifications act as a baseline, showing you meet at least some standard. For someone getting into Power Apps, I'd look at Microsoft's Power Platform certifications, which are more targeted than the broader Microsoft Office exams.
If you want more feedback, I encourage you to post in the forums on my website. Many of my moderators have taken those exams, so you can get their direct perspective.
Next, a question about AI in Access: can you use Copilot integration in Microsoft Access, like you might with ChatGPT? If so, which is better?
The short answer is, Copilot is integrated into Word, Excel, PowerPoint, and a few other apps, but not Access. If you want AI features in Access, you need to wire things up yourself using API calls. I have several videos that walk you through connecting to the OpenAI API, which powers ChatGPT. The process is straightforward with some simple code, though you will need to set up an account and get an API key. It does cost a little, but unless you're making thousands of requests, it's affordable.
Microsoft also offers AI models through Azure, which are essentially the same as the GPT models but come with enterprise-level security and integration. It's more about what's convenient for you. OpenAI's API is quick and easy for independent developers, while Azure makes more sense for larger organizations. Both will work with Access, though neither is built-in at the moment. Until Microsoft provides true Access integration, my recommendation is to use the option that best fits your needs, with ChatGPT currently being my preferred choice.
Another great tip came up about resource cleanup in VBA. Many students notice I clean up object variables, such as recordsets, by setting them to nothing when I'm finished. This is important for memory management because sometimes, if there's an error or interruption, Access won't always clean up memory right away, leading to performance problems over time. I always recommend closing objects and setting references to nothing, especially after working with recordsets.
With database pointer variables—those you set to CurrentDB—it's not as critical because it's just pointing to the existing database engine and doesn't take up much memory. Still, it's good practice to tidy up, especially to keep your habits consistent and avoid problems if you start referencing other databases.
A rather advanced question came in about validating form data: what if you need to check that combinations entered in a form are valid, but checking every time would slow performance considerably? Sometimes, it's better to allow rare invalid entries and check them later, especially if the situation seldom occurs and immediate validation isn't crucial. For example, you could allow fast data entry and then run a nightly batch to flag invalid records. This keeps your workflow efficient while still maintaining quality control.
I use a similar system myself. If someone on my site signs up for a membership more than once, my immediate signup form doesn't bother to check previous records. Instead, my script runs overnight, flags duplicates, and I follow up the next day. As long as catching those errors isn't urgent, deferring these checks to a scheduled process avoids bogging everyone down.
Moving along, I frequently get questions about where to find links mentioned in my videos. YouTube often hides the description box where I place all related links and resources, so you may need to open that up to find them.
One viewer from Ethiopia commented on some videos being for paid members, and asked if I could make all content available for free. I appreciate the request and the kind words. The reality is I offer over a thousand free videos and resources to help as many people as possible, but I also need revenue from paid courses and memberships to support myself and keep creating new materials. Without that support, I wouldn't be able to produce the volume and quality of free content that I already do.
A question also came in about slow Access databases after several months. If your database runs slow on a network, follow these steps. First, make sure you're using a wired network, not Wi-Fi, and check for any network issues using diagnostic tools. Make sure the database is split: the backend with the data is stored on the server, and each user has a local copy of the front end. Don't share the front end across the network. Regularly compact and repair both front and back ends. Review your indexes so search and sort fields are optimized. Also, anti-virus software can sometimes slow things down if it's scanning your database files in real time. Make sure everyone's running the same version of Access. Often, just restarting Access or rebooting your network hardware solves strange slowness problems.
Another popular question: conditional formatting to highlight records when a deadline is approaching. For example, if you want a field to change color when you're within 30 days of a given date, use conditional formatting rules with proper expressions. Make sure the field you're formatting is a real Date/Time field, not a formatted text field. If your conditional formatting isn't working, check for these common issues. Often, the problem is trying to compare values as text instead of dates, or formatting dates in a query, which turns them into strings. Always do your formatting last, so you keep your data as dates for as long as possible.
There was a survey I ran recently, and the results show that Access development is still the main focus for most of my audience, followed by interest in SQL Server, database design theory, and AI integrations like ChatGPT. I'm working on more content about Word, Excel, and integrating those tools with Access. People are also asking for more networking guidance, since running Access over a wired network is so important for stability.
A brief note: if you see just the Vulcan salute emoji as a reply to a comment, it's my way of acknowledging your message. It's a quick way for me to mark comments as read.
Several comments also came in about Access features like Layout View. I personally avoid Layout View and stick with Design View for moving and aligning form controls. Early bugs in Layout View made a bad impression on me, and even if it's improved, I still prefer the reliability of Design View for making changes.
There was also a request for more details on using public, private, or sub functions, as well as understanding brackets and scope. My TechHelp videos are focused on specific topics, but for more in-depth and structured learning, my Developer courses guide you through these concepts in the correct order. For focused help, search my website or check out the Developer lessons.
It was amusing to hear from the author of an article I reviewed years ago about the limitations of Access. Yes, people still use Access, and in my view, it's still unmatched for desktop rapid application development. Pair it with SQL Server and you have a formidable business database platform.
That wraps up Quick Queries 56. As always, you can find a complete video tutorial with step-by-step demonstrations for everything I've discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Advice on Microsoft certification vs real-world experience
Overview of Microsoft Office Specialist certifications
Introduction to Microsoft Power Platform certifications
Comparison of OpenAI API and Azure OpenAI integration
How to connect ChatGPT to Microsoft Access using API calls
Pros and cons of using AI integration in Access
Explanation of object variables and memory management in Access VBA
Why you should set object variables to Nothing in VBA
Difference between object references and pointers in VBA
Passing variables ByVal vs ByRef in VBA
Guidance on when clearing CurrentDB object references is necessary
Techniques for preventing invalid data combinations in Access forms
Using nightly events to validate database records
Balancing real-time and post-entry data validation
Optimizing Access database performance on a network
Importance of using wired connections for Access databases
Proper splitting of Access databases into front end and back end
Regular compact and repair of Access databases
Checking and improving indexing in Access tables
Dealing with Access database size limitations
Identifying hardware and network issues affecting Access
Effects of antivirus programs on Access database performance
Version compatibility considerations in multi-user Access environments
Troubleshooting slow Access network performance
Configuring conditional formatting with date calculations in Access
Troubleshooting issues with conditional formatting expressions
Difference between formatting date fields and text fields in Access
Potential pitfalls using formatted fields for conditional logic
Best practices for conditional formatting in Access
Views on Access layout view vs design view
Risks associated with Access layout view corrupting forms
Public and private scope for VBA procedures and variables
Where to use subs, functions, and brackets in VBA
When to use public vs private scope in the Access Fitness series
Responses to common misconceptions about Microsoft Access limitationsArticle Welcome to Quick Queries 56, where I tackle a collection of interesting Microsoft Access questions and practical issues that have come up from students and community members. Whether you are a beginner or an advanced user, this is a great place to pick up tips, clarify concepts, and deepen your understanding of Access and related technologies.
A common question I receive concerns Microsoft certifications. If you are already experienced with Access and are dabbling in Power Apps, you might be considering whether pursuing Microsoft certification is right for you, especially if you are interested in shifting toward training or development roles. My perspective is that certifications are most valuable when an employer requires them, or if you are applying for positions where HR filters candidates based on official qualifications. Personally, as someone who has been self-employed for decades, I have never needed certification. In my experience hiring others, holding a certification does not automatically mean someone is highly skilled, and lack of certification does not mean someone is unqualified. Certifications can be a helpful resume boost, especially in larger organizations, but experience and proven ability often matter more in the end. If you are interested in Power Apps, specifically look for Microsoft Power Platform certifications, such as Power Platform Fundamentals or certifications more focused on app design.
Another common question is about integrating AI services into Access—can you use co-pilot, or do you need to use something like ChatGPT? Currently, Microsoft has built co-pilot integration into other apps like Word and Excel, but not into Access. To add AI-powered features into Access, you need to connect to an AI service via API calls. The easiest way to do this is with OpenAI's ChatGPT API, which is simple to script in VBA. While there is a small cost for API usage, it's quite affordable unless you're making a very high volume of requests. Microsoft also offers similar AI APIs in Azure, which can be better for enterprise needs, tighter security, and integration with the rest of Microsoft 365, but they're more involved to set up. In regular Access VBA, you need to set up an API key with OpenAI, then call the service using code. If you want more details, check out my OpenAI API tutorials, where you can see the exact VBA steps.
A principle I want to reinforce is: If you set it, you have to forget it. This means, when you declare and set object variables like recordsets in VBA—such as Set rs = db.OpenRecordset("SomeTable")—you should remember to clean them up with Set rs = Nothing when you are done. Access and VBA do not always clean up memory perfectly, especially after errors, which can lead to memory creep and resource leaks. Although Access will normally dispose of objects as a procedure ends, there are edge cases where leaving objects hanging can cause problems, especially with recordsets that lock resources in memory. Even with variables like Set db = CurrentDB, which is actually just a pointer to the database rather than reserving significant memory, it's still good form to clear it with Set db = Nothing at the end of your procedure.
When you declare a variable as a recordset, that creates an actual chunk of memory holding data and possibly locks on database pages. Failing to free that memory means you might tie up resources for longer than you realize. With Set db = CurrentDB, what you're really getting is a pointer to the current database object, and it's much less critical for memory management, but in more advanced work—especially if you assign db to an external database object—forgetting to clean up can cause bigger issues. Therefore, get in the habit: if you set it, forget it (by setting it to Nothing).
Sometimes, performance or validity requirements create a conflict between building forms that never allow incorrect data and forms that allow mistakes to speed up data entry. For example, you might have two fields where not every combination of values is legal. If checking the entire database for conflicts every time a record is entered slows down the user experience, especially if conflicts are rare, consider a different approach: allow the entry, but use a scheduled (nightly) process to check for invalid combinations and flag them for review. For example, at night the system could scan for any new entries that do not meet the required rules, and in the morning, you can notify users of mistakes. This way, users are not slowed down during busy times, but you still catch problems before they matter. This is a practical compromise, especially when you can tolerate delays in validation or when there is a window before data becomes critical.
On the technical side, some users run into slow performance issues after several months of using Access on a network. Here are the top steps to improve your database speed:
First, ensure your network is healthy. Access databases work best on wired networks; wireless can introduce interruptions, slow performance, and risk corruption. Use Cat 6 cables and reliable hardware. Run network tests (ping, traceroute) to check speeds and for any connection issues. Make sure your network is not saturated with unrelated traffic such as large file transfers or video streaming.
Next, double-check your Access database setup. Make sure your database is split: the back end (tables) should reside on the server, while every user gets a dedicated local copy of the front end (queries, forms, reports, code). Sharing the front end file from the network is a sure way to slow things down and risk corruption.
Compact and repair both your front end and back end regularly, especially once the database grows. Check and optimize your indexes—fields you sort or filter by need indexes to ensure quick lookups (e.g. first name, last name, date fields). Review your queries, avoid unnecessary SELECT * (which pulls back every column), and try to avoid using calculated fields or DLOOKUP in places where they are not essential.
Note the Access file size limit: backend files can grow to 2 GB. If your database is getting large, consider migrating to SQL Server, which lifts many of those limitations and improves multi-user performance.
Check your hardware as well. Enough RAM, CPU power, and disk space are essential for quick performance. Also, some antivirus software can slow down Access considerably; programs like Norton or McAfee can lock Access files during scanning, making normal operations painfully slow. Rely on built-in Windows security tools, which are sufficient for most environments.
Finally, make sure everyone is running the same version of Access. Old software on a single workstation can cause compatibility and speed issues.
Users often ask about conditional formatting based on dates, such as highlighting fields when a deadline is approaching. To do this in Access, for example, to highlight a date field when you are within 30 days of a deadline, open your form in design view, select the field, use Format > Conditional Formatting, add a new rule, and use the condition Field Value Is Less Than Date() + 30 (make sure to write Date() with parentheses and no extra spaces). Apply your desired formatting, such as a yellow background. If this does not work, confirm your field is of type Date/Time and not text. Using the Format() function in queries (e.g., Format([MyDate], "mm/dd/yyyy")) returns text, not a date value, and this can break conditional formatting rules. Always store and process actual dates; only format numbers as strings for final reporting or display.
If you want to learn more about the public, private, and sub-function distinctions in VBA, understand that "Public" exposes objects (subs, functions, variables) throughout your project and other modules, while "Private" restricts their access to the module they are declared in. Subs perform actions and do not return values; functions can return a value. Scope and visibility are foundational—if you want deeper learning, follow a structured developer course to build up step-by-step rather than hopping from one video to another, though there are many free resources covering these concepts.
Occasionally, users complain about Access's layout view, where moving a field or a label does not necessarily move the attached label or data field. While it seems like a design flaw, it is an aspect of layout view. Many advanced users prefer design view, where you have more control and fewer problems with VBA code corruption or accidental misalignments.
To sum up, Microsoft Access remains a highly capable desktop database tool. While the technology landscape is always evolving, Access still stands out uniquely for small and mid-sized businesses needing solid, office-integrated database solutions that work well on desktop PCs. If you want to go beyond the basics, pairing Access with SQL Server for your data storage gives you the best of both worlds—easy rapid-app development and a scalable, robust data backend.
If you are struggling to afford paid courses, know that there are hundreds of hours of my free content available on YouTube and my website. This is my full-time job, so member-only options exist to help support the channel, but the free content is there for everyone worldwide.
For ongoing questions, suggestions, or troubleshooting, check out the forums and troubleshooting guides on my website. Feedback through surveys and comments helps me tailor my lessons and videos to suit the needs of the community, so keep sharing your input.
If you are setting up a networked Access solution, always use a wired connection for reliability. If you need to allow remote access, consider setting up a remote desktop or virtual machine, since Access does not perform well over unreliable connections unless using a server backend like SQL Server.
That wraps up this session. I hope these explanations have helped clarify some of your questions about Microsoft Access, VBA best practices, performance, and practical design choices. Continue learning, and do not hesitate to share your own questions or feedback. |
