Quick Queries #93
By Richard Rost
3 days ago
Correcting Missing Field Names in SQL WHERE Clauses In Quick Queries #93, we discuss a common Microsoft Access SQL mistake that can break your queries even when the statement appears correct and walk through why a missing field name in SQL criteria causes unexpected results. We also address questions about opening Access databases in separate memory spaces, quirks in the new Form Zoom feature, usage of API code, issues with inactivity timers and popup forms, split testing YouTube thumbnails, and advice on effective recordset cleanup in VBA. We briefly mention how much API detail you need to understand and upcoming tips for managing multiple open database instances. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, AccessLearningZone.com, SQL missing field name, query criteria referencing form controls, separate memory space, form zooming bugs, recordset cleanup, inactivity timers pop-up forms, separate Access instances, background user activity monitoring, split testing thumbnails, Windows API code understanding
Intro In Quick Queries #93, we discuss a common Microsoft Access SQL mistake that can break your queries even when the statement appears correct and walk through why a missing field name in SQL criteria causes unexpected results. We also address questions about opening Access databases in separate memory spaces, quirks in the new Form Zoom feature, usage of API code, issues with inactivity timers and popup forms, split testing YouTube thumbnails, and advice on effective recordset cleanup in VBA. We briefly mention how much API detail you need to understand and upcoming tips for managing multiple open database instances.Transcript Why does one tiny missing piece of SQL completely break your Microsoft Access query, even though the statement looks perfectly correct? 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 a simple Microsoft Access SQL mistake that almost every developer makes at least once. I know I'm guilty of it multiple times. It's one of those little syntax problems that's easy to miss, but can leave you staring at the query wondering why you're getting no records back.
We've also got questions from comments on YouTube, my website forums, Reddit, emails, and lots of other places. Topics include opening Access databases in separate memory spaces, weird form zooming bugs in the new form zoom feature that they just released, API code and how much of it you really need to understand, inactivity timers and pop-up forms interfering with focus, split testing thumbnail images, and some recordset cleanup tricks that might not be as clever as they first seem.
Alright, let's jump in.
Leading off to bat today, we've got Chuck, one of my Silver members. Following along with the TechHelp free template, he noticed that his search form was mysteriously hiding records with null dates. He was using criteria like "is null" or "between" tied directly to controls in the same form that was loading, and it looked like "is null" just wasn't working.
This is one of those sneaky Access gotchas that trips up a lot of people when they're building search forms. Chuck thought that the "is null" part of the criteria was broken because the records with blank dates weren't showing up, but the real issue was the query trying to pull criteria values from the same form that's based on the query, so it's kind of like a circular reference situation in Excel.
The fix is simple: don't have the query criteria depend on controls from the form that's loading itself, because the form is still loading. Put your search criteria boxes on a separate form like the main menu or a dedicated search form, then open your results form from there.
Once Chuck moved the criteria off the form itself, everything worked correctly. I'm assuming; I haven't heard back from him. But that's also why I usually recommend separating your search forms from your data forms. Anyway, it keeps things cleaner and avoids weird loading behavior like this.
Now that's not to say that you can't use fields on a form to filter the data, but if you're using it to load the data set that the form is based on, then it's best to have that stuff in a separate form.
Next up, we've got Edwin, one of my students from Hong Kong. He shared an interesting experiment with recordsets. He noticed some performance and stability improvements after assigning multiple recordset variables to the same dummy object, the same dummy recordset object, and then closing just one of them at the end. He was wondering if this was actually a clever optimization, or if he was accidentally creating a disaster waiting to happen.
I'll put a link to this thread down below. You can read the whole thing. There's lots to this one. We went back and forth quite a bit. But he was doing stuff like this, where he set up different recordset query variables and assigned them equal to the same object basically. And then it seems to close every one of them.
It's actually interesting because how object variables really work behind the scenes: if you do something like "set RS2 = RS1," you're not making a copy of the recordset. Both variables are just pointing to the exact same object in memory. If you've done any programming in languages like C, you'll know what pointers are. So yes, if you close one, they're all effectively closed because they're all references to the same object.
Technically what he did works, but I wouldn't recommend it as a long-term coding pattern because it gets confusing really fast. Your variable names stop meaning anything at that point because now every variable points to the same placeholder object.
Another takeaway from this thread is that an empty recordset is not the same as nothing. That's the part that trips a lot of people up. A recordset with zero rows is still a perfectly valid open object. If your variable is actually nothing, that usually means the set statement failed or never happened in the first place.
Both Donald and Thomas gave great advice in this thread. The safest cleanup pattern is still the old boring classic:
"If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If"
I know this seems weird. "If Not RS Is Nothing" is the pattern that you have to use and then you want to check to see if it's not that, then close it and set it to nothing. That's weird, I know, but that's just how you have to phrase it.
Sometimes the old boring patterns are boring because they work. They're not elegant, but they do the job.
Speaking of Thomas, next question comes from Thomas. He asked a really good follow-up question about something that I mentioned recently regarding separate Access instances in my "Access Not Opening" video. I'll put a link to this and the thread down below. He wanted to know whether opening multiple databases using the same MSACCESS.exe could still cause databases to interfere with each other internally, even if Task Manager shows separate windows.
Again, big long discussion, huge thread. Bottom line, this is one of those things that a lot of Access developers don't realize. If you just double-click the ACCDB files normally, Windows will usually route them all to the same running copy of Access. So even though you see multiple database windows open, they're all sharing the same Access process, the same VBA engine, and a lot of the same internal resources.
That's why if one database locks up in a startup loop or crashes hard enough, sometimes all the other databases freeze too. And if you try to open another one, it won't open. That was the point of this video. It'll just sit there and look like nothing happens.
So that's why in the video, I explain that if you kill MSACCESS.exe, then it should open up just fine. And you can open them up in their own MSACCESS.exe instances if you set up a shortcut the right way.
Now, Thomas brought up some really good questions about whether separate instances are truly isolated. Yes, if you launch Access with an explicit shortcut to MSACCESS.exe followed by the database file name as a parameter, Windows does create a completely separate Access process with its own memory space and VBA engine. So if one crashes, the others usually keep running just fine. You can test this yourself by doing the technique that I show in the video. If you kill one of those processes, the other database still stays open.
I also mentioned in the thread, this is actually how all the Office apps work under the hood. Word and Excel do similar stuff too. Now just because you can open two copies of the same database, it doesn't always mean you should, especially with unsplit databases. That's where things can get a little spicy.
I mainly use it for viewing records side by side and even then, carefully. Like, sometimes when I create another video, I copy the settings from an old one and I'll open the databases side by side so I can copy back and forth. But you have to be very careful. Only make edits in one of them and the other one should be read-only. So be careful with that kind of stuff.
Next up, I got Peter. He's building a daily calendar form. Couldn't figure out why his SQL query kept returning no records. The query worked fine with hard-coded values, but as soon as he switched to using form values, everything broke. And this uncovers one of the most common SQL mistakes people make in Access.
Can you see it? Here's his SQL statement right here. Do you see the problem? I see this happen a lot, especially with people that are first learning SQL. The problem is he's got it right here in his WHERE condition.
There's the WHERE condition right there. I'm going to copy this over and put it in a Notepad for a second just so we can see it. And yes, I'm in dark mode right now.
Now if we replace this Forms!DatePicker!F_Calendar, let's just replace that with X. Here's X. And then we'll replace this with X + 1. We'll put some spaces in here. Now you can see the problem. Do you see it?
WHERE DateTime >= X AND < X + 1
This is something that trips a lot of people up. You have to have that field name in there twice. A lot of people want to do WHERE DateTime is greater than this field and less than this field or value, whatever. But you have to have that field name in here again. That DateTime has to be in there again.
That's a huge thing. I see a lot of people make that mistake. Alex pointed out you should have some spaces in there, put spaces around your different field names and stuff like I just spaced this out. Sometimes you don't need it. Sometimes Access and SQL Server will be very forgiving with the spacing, but it can't hurt.
Kevin nailed it: missing the field name before the less-than operator. Alex pointed out that "DateTime" is technically a reserved word so you really should have brackets around it. I try to avoid "DateTime" if possible, try to avoid any reserved words.
Interestingly enough, Peter eventually got it working by switching to variables and building a QueryDef, which is perfectly fine, but that's the tougher solution. It would have worked if he had just corrected the SQL statement. But hey, if it works, it works. That's all you need to know.
Hey, while I've got you here, if you like this stuff, go ahead and make sure you have already hit that like and subscribe button for me now. Do it. Do it.
Got some more questions on that opening Access in a separate memory space. Symbicat says, does opening an ACCDE make any difference? Honestly, the best answer sometimes is go test it. Go try it yourself and see. If you're curious, that's one of the best ways to learn.
In this case, I don't think it matters whether it's an ACCDE or a DE file because the important part is how Access itself gets launched. If you're explicitly launching MSACCESS.exe and the database is specified as a parameter, then Windows creates a separate Access process first, then Access loads whatever the database file is. At that point, DB versus DE probably doesn't change the instance behavior at all. The ACCDE changes how the database itself behaves, but not how Windows launches Access.
But hey, go test it. I haven't tried it myself, but if anybody tests it and finds some weird edge case, let me know because Access always has surprises hiding somewhere.
Next up, someone asked whether it matters if the database itself is stored locally or on a server when you're launching Access in a separate instance. No, I don't think the location of the database file matters at all. It can be on your local drive, a network drive, a UNC path, or on a server somewhere. Again, the important part is how Access itself gets launched. If the shortcut explicitly launches MSACCESS.exe first and then passes the file name as a parameter, then Windows creates a separate Access process in its own memory space.
And the actual recommended setup for multi-user Access databases is that each user gets their own local copy of the front end running their own Access instance, and they're all connected to the same shared backend on the server. That's the safest and most stable way to run and deploy a multi-user Access database.
Next up, impossiblepudding says they've been using Access for years and often wish they could work in another database while a long process was running. They asked the obvious next question: is there a setting somewhere to always force separate Access processes? As far as I know, there is not. There isn't some hidden checkbox or command line switch that says always open every database in a brand new Access process.
By default, if Access is already running and you double-click another ACCDB file, Windows actually routes it right into that same running copy of Access. If you want guaranteed separate instances, use the shortcut trick. Honestly, if you regularly work with multiple databases at the same time, it's worth making separate shortcuts for each of the ones you use most often.
That's what I have on my desktop. I've got like five different databases that I'm constantly going back and forth throughout the day. I've got a shortcut to each one of them. The databases themselves are in my database folder that I make sure gets backed up and stuff, but all the shortcuts are on my desktop that launch those different databases.
Doing it this way saves a lot of headaches when one database decides to go full warp core breach. It doesn't crash the other ones along with it. If I do want to open a separate copy of one of them, you can. Again, as I mentioned before, it's dangerous, but you can do it. Just check your taskbar first before you open it so you make sure it's not running.
I actually have another video coming out. I've got it slated for Wednesday the 27th to check to see if your copy of your database is already open or not. If so, it'll give you a warning message because I do that on a couple of my things so that I don't accidentally open up multiple copies of the same database. We'll talk about that next week.
Next up, this one came in an email from Seth, one of my Gold members from Cleveland, Ohio. He said, I've heard you say with some of your complicated code, especially Windows API stuff, that you don't necessarily have to understand every little detail under the hood in order to use it, but isn't the whole point of learning programming that we want to understand how all that stuff works?
Yes, absolutely, Seth, but there are levels to it. It kind of compares to learning math in school. First you learn arithmetic, then algebra, then trig, then calculus. You don't start off with differential equations on day one. Even later on, there are formulas you're going to use all the time without necessarily understanding every microscopic detail of where they came from mathematically.
Programming is the same way. There's the stuff you're going to use every day, and you should absolutely understand that deeply, things like loops, variables, queries, DLookUp, recordsets. I can write those in my sleep, basic VBA logic. That's the stuff that you're going to be writing day in and day out. So you should understand all of the nuances, all the parameters, how it all fits together.
Now some things, especially these Windows API calls that I use from time to time, they're more like pulling a reference book off the shelf and just copying some stuff out of it. Even I can't memorize every API declaration or every parameter for every Windows function that I've ever used. Nobody does. You look it up, you understand generally what it does, you understand how to use it safely, and then you move on.
It's the same thing with anything. Take cars, for example. It's useful to know how to change your oil or maybe replace a battery or, if you get into it, a timing belt, if they still have those. I don't know if they do or not. But do you need to completely disassemble and rebuild the engine from scratch in order to drive the car effectively or even do some basic maintenance? No.
That's how I look at programming. You don't need to reinvent every wheel. You just need to understand enough about the wheel to use it properly. Know when it's about to fall off and how to change a tire, maybe.
I've been doing this for 30 years. Do I remember every single Windows API function that I've used? No, absolutely not. I go look them up. It used to be a Google search. Now you can ask your favorite AI for it and it'll give it to you. Copy it, use it, understand it. Generally, I try to give the overview: here's the big picture of what this does, what to give it, and what to expect back. Even things like "Sleep," stuff like that. But you don't have to understand every single variable inside that API function and what it's doing and all that.
But yes, use what you need. Remember the stuff you do every day.
Alright, head over to YouTube. BJones noticed that when I zoomed in the form on the forum zooming video down to 70, some of the button text started wrapping into the second line instead of resizing cleanly. You're definitely going to run into little quirks like that right now because this is still a brand new feature and it's not fully polished yet. They're still working on it. I'm sure the Access team is aware of a lot of these issues. At least I hope they are by now.
Honestly, this is one of the reasons why I usually don't jump all over new features the second they come out. Early versions almost always have weird little artifacts and edge cases and quirks and I like to let other people find the bugs before I start building production systems around it and teaching the stuff. But this is a feature that I know a lot of people were excited for, so I wanted to put a video out on it relatively soon, as soon as they released it.
They actually announced it last year at the MVP Summit. So it's been a year in the works and I'm happy with the results. They've still got a lot to go. But overall, I think form zooming is still a fantastic addition to Access and I'm pretty confident they'll smooth out those little UI glitches over time. Just be patient.
Joseph mentioned that one problem with inactivity timers is that if a dialog box is open somewhere, it can steal away focus from your inactivity form and break the logic. He said in his own system he eventually moved the timer into the background instead of relying on popups. That's a really good point.
Anytime you're building inactivity systems or timers in Access, dialog boxes and popup forms can absolutely interfere with the focus and timing behavior. That's one reason why I usually prefer background timers and internal tracking instead of relying too heavily on visible prompts. Remember, if you open up a form dialog, all processing stops in the database while that dialog form is open. Just keep that in mind.
And then of course, the more complex your environment gets, especially with Citrix or Remote Desktop environments, the more weird edge cases you start running into. Sometimes the simplest and most reliable solution is just quietly monitoring activity in the background and then handling the shutdown automatically if needed. That's why I always tell people test the stuff in the real environment your users are actually working in because Access behaves very differently once you get outside a simple user desktop setup.
Another thing you can also do, and I've done this for clients before, is have a separate app that runs even in the background to check for user activity. In fact, I did a part four for the "Are You There" series which comes out on Tuesday. If you're a member, you can watch it now. In part four, I teach you how to query Windows itself to see if the user is still there. Whether it's separate from Access, you could make a separate little app or even run a separate Access database, run it hidden, and just query Windows to say, hey is the user still there or are they gone, and has nothing to do with the database. If they're gone then you can have that app kill whatever processes you have to or shut things down. There's so much stuff you can do. I'm just scratching the surface of it. This is really more of a Windows thing than an Access thing anyway.
But you have to be careful with dialog popup forms that are waiting for the user to do something and then close them and you have to watch out for message boxes, which is why I also have the message box timer video. Instead of popping up a regular message box, always pop this guy up and you could work this kind of timer logic into your dialog forms too. If you're going to open a dialog form, which I use - I have several of them in my databases - don't just leave that sitting there and let the user walk away from it. Put a hidden timer in the background of that thing too. So your background form might not still be ticking down if you've got a dialog or a message box up. All things to consider.
Alright, so a quick follow-up on the thumbnail poll that I did from last week's Quick Queries. I mentioned that I was split testing five different thumbnail styles across multiple videos to see if any of them performed noticeably better.
So far, after about a week and about three to four thousand views for these videos, it's honestly too close to call. They're within a few percentage points of each other. I don't think it's statistically significant. Try saying that ten times: statistically significant. They're all performing basically about the same.
I think what this tells me is that the people who normally watch my videos are probably going to watch them regardless of the cover image. I also got some really thoughtful comments that you filled in on the survey itself. So I'm just being honest. A lot of people love the Star Trek themed thumbnails, which I totally expected, but several people also pointed out that from a business standpoint if someone doesn't already know me they might be confused why a Microsoft Access video has a Starfleet captain on the cover, which is honestly fair.
So I think I'll probably keep the Star Trek stuff for more fun little things inside the videos instead of using it as the main branding. As far as the thumbnails with women in them, a few people mentioned that maybe I should use less provocative women because it can come across as they're just being used to grab attention. I understand that point completely. In fact, one person commented "sex sells but caveat emptor," which honestly made me laugh because that's pretty accurate.
My intention wasn't to be provocative. I actually told ChatGPT, "Give me an attractive but professional business-oriented woman." Since ~90% of my audience is male, I figured, alright, let's test it. I chose red because that's the color I associate with Microsoft Access. If I did Word I'd use blue, if I did Excel I'd use green, but I was also trying to keep it consistent so it looked like the same person from thumbnail to thumbnail. Once it generated the first woman I was like, okay, here's what I want to use for the future ones, and just in case anyone is curious, this one here is actually a picture of my wife. I used her as the template for this one, so she was pretty happy about that, and she actually is tracking a few percentage points higher than the other woman, so she's happy about that too.
At the end of the day, like a lot of you said in the comments, I follow the data. So right now the data basically says it doesn't matter very much either way. I think going forward I'm just going to let the video itself dictate the thumbnail. Sometimes a screenshot from the video is actually the easiest and honestly works the best because I can just take a screenshot right out of the video itself or I've already got the database up, just stage something nice. Other times if it's not a walk-through video, if it's an explainer topic where a screenshot doesn't make sense, maybe I'll go back and use a person or some kind of AI-generated image, we'll see.
So like I said, I'm going to let the split test run a couple more weeks but I think we have our results: it's pretty much let the video dictate. I appreciate all of you who filled out the survey and gave me some comments, and thanks a lot.
Speaking of that poll, ShadowDragon says I left out an option: a penguin wearing a red Starfleet uniform. We have a winner, folks. That's the one we're going to go with from now on. That's it. I love it.
Alright, before you go, don't forget to stop by my website and check out what's new. I'm always adding lots of videos, updates, templates, all kinds of good stuff over there. Don't forget to check out my Captain's Log where I post all kinds of cool stuff, whatever I happen to be thinking that day. Sometimes it's Access, sometimes it's science or sales, or whatever happens to be on my mind, or some cool stuff that might have happened to me.
Like how I recently got almost $200 because I didn't get rid of an email that I thought looked like spam and I actually filled out a form and yeah, they sent me some money. And a review of some peanut butter-like substance that I would rather eat the empty jar. Just stuff like that.
Hey, grab a t-shirt or a hat or a coffee mug or whatever and show your Access Learning Zone pride. While you're at it, pick up a copy of my book, it's on Amazon, and stop by my forums on my website. If you're looking for help with Access, there are developers and tutors and all kinds of other people on my website that are available to help you. Make sure you get on my mailing list while you're there as well.
So today we learned that one tiny missing field name in an SQL statement can completely break your Access query, even when the syntax looks - there's another tongue twister - when it looks perfectly fine. Sometimes the smallest SQL mistakes are the hardest ones to spot because you could stare at it for an hour and then what?
That's honestly one good thing that AI is really good for. If I have code and I'm like, what is wrong with this? I can't figure out a block of code I've written, sometimes because it's two o'clock in the morning and the coffee has worn off and my brain stops working, I copy and paste it into GPT and I'm like, what's wrong with this? "Oh, I'm missing a period" or "I spelled something wrong" or something - usually something dumb. That's one good thing that AI is good for: troubleshooting and checking for your mistakes. I use it all the time for correcting my spelling and grammar because I'm really, really good with spelling and grammar, but it takes a lot of effort to go back and edit stuff. So I'm just like, alright, I'm done writing it, now here, check for spelling and grammar for me. But spelling and grammar check has been around forever since, you know, early versions of WordPerfect.
Anyway, we also learned how to launch Access databases in separate memory spaces, how new features like Form Zooming still have a few quirks to work out, how pop-up forms can interfere with inactivity timers, and why you don't always have to understand every single line of Windows API code in order to use it effectively.
Post a comment down below, let me know how you liked today's video, and send in your questions for next week's Quick Queries video.
That's going to do it, folks. That's going to be 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 and enjoy your weekend. In fact, enjoy your long weekend because it's Memorial Day on Monday, here in the States at least.Quiz Q1. What is a common mistake that can break a Microsoft Access SQL query even when the statement looks correct?
A. Using reserved words without brackets
B. Forgetting to include the field name in the WHERE clause multiple times as needed
C. Not using enough spaces in the SQL statement
D. Using hard-coded values instead of variables
Q2. Why was Chuck's search form hiding records with null dates when using "is null" in the criteria?
A. The database was corrupt
B. The form's criteria controls were being referenced from the form itself as it was loading, causing a circular reference
C. "Is null" does not work in Access
D. He was using the wrong data type for the date
Q3. What is the recommended solution to avoid circular reference problems when filtering data based on form controls?
A. Use hard-coded values instead of form controls
B. Place search criteria controls on a separate form, not the form displaying the data
C. Use "is not null" instead of "is null"
D. Restart Access every time you run the query
Q4. When assigning multiple recordset variables to the same object in Access VBA (e.g., Set RS2 = RS1), what actually happens?
A. A new independent recordset is created for each variable
B. All variables point to the exact same object in memory
C. The variables only share their parameters but not the data
D. The objects become disconnected after closing one recordset
Q5. What is the safest recordset cleanup pattern recommended in the video?
A. RS.Close
B. Set RS = Nothing
C. If Not RS Is Nothing Then RS.Close: Set RS = Nothing
D. If RS Is Nothing Then Set RS = Nothing
Q6. What is the purpose of launching multiple ACCDB files using explicit shortcuts to MSACCESS.exe with the file name as a parameter?
A. To launch Access in read-only mode
B. To create completely separate Access processes, isolating them in memory
C. To enable autosave features
D. To avoid using Task Manager
Q7. What problem can occur if multiple ACCDB files are simply double-clicked to open?
A. Only the first file opens
B. Windows routes them through the same instance of Access, sharing internal resources and possibly causing interference
C. Access switches to safe mode
D. You are always prompted for credentials
Q8. In the problematic SQL example mentioned with Peter, what was the actual syntax error?
A. Not putting spaces between operators
B. Not repeating the field name before both conditions in the WHERE clause
C. Using a bracketed field name
D. Using an ACCDE instead of an ACCDB
Q9. Why should you avoid using reserved words like "DateTime" as field names in Access?
A. They will always cause syntax errors
B. Access cannot handle any reserved words at all
C. They might conflict with SQL or Access internal keywords, leading to unexpected behavior
D. They are case-sensitive
Q10. When launching Access databases in separate instances, does the database file location (local vs. network) matter?
A. Yes, only local files allow for separate instances
B. Yes, only network files support this feature
C. No, what matters is how Access is launched, not the file location
D. Yes, UNC paths are not supported
Q11. What is the recommended setup for multi-user Access databases?
A. Each user has their own local copy of the front-end connected to a shared backend on the server
B. All users share the same ACCDB file on a network drive
C. One user acts as host and others connect remotely
D. All users share a split database front-end
Q12. Is there a setting in Access to always force new processes for each database opened?
A. Yes, in the Access options menu
B. No, there is no built-in setting; you must use explicit shortcuts to force separate processes
C. Yes, but only for ACCDE files
D. No, it's not possible to have separate processes in Access
Q13. Why do new features like Form Zooming in Access sometimes have quirks or bugs?
A. Because they are not supported on all operating systems
B. Early versions of new features usually have edge cases and artifacts that need to be worked out over time
C. Only the Enterprise version supports them fully
D. Form Zooming is incompatible with SQL queries
Q14. How can popup forms or dialog boxes interfere with inactivity timer logic in Access?
A. They slow down the VBA engine
B. They steal focus, preventing background timers from properly tracking inactivity
C. They trigger auto-close behavior
D. They delete records automatically
Q15. What is the primary principle for learning and using Windows API code according to the video?
A. Memorize every detail of every function
B. Only use APIs that you have written yourself
C. Understand generally what the API does, how to use it safely, and refer to documentation as needed
D. Avoid using Windows APIs altogether if possible
Q16. What was the outcome of the thumbnail split testing described in the video?
A. One style performed dramatically better than the others
B. The cover image had a minor impact; viewers watched regardless of the image, indicating it was not statistically significant
C. Thumbnails with people were always better than screenshots
D. Red Starfleet uniforms attracted the most viewers
Q17. What is a practical use of AI mentioned in the video for Access or coding in general?
A. Generating new databases from scratch
B. Troubleshooting code and spotting small syntax errors when you cannot see them yourself
C. Automatically writing all VBA code for you
D. Creating optimized SQL statements by itself
Q18. What is the main lesson about small SQL mistakes in Access?
A. They are easy to spot in the query editor
B. Even minor mistakes, like missing a field name, can cause perfectly fine-looking statements to return no records or break completely
C. Access will automatically fix minor syntax errors
D. SQL mistakes never affect performance
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-B; 9-C; 10-C; 11-A; 12-B; 13-B; 14-B; 15-C; 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 Access Learning Zone, I'm tackling a series of questions and common pitfalls that Microsoft Access developers often encounter. The main focus is a subtle but critical SQL mistake that almost everyone makes at some point. It's the classic scenario where your query appears correct, but it returns no records, leaving you puzzled as to where things went wrong.
Let me start with a story from Chuck, one of my students. He was working with the TechHelp free template and noticed that his search form wasn't displaying records with null dates, even when he used criteria like "is null" or "between" based on controls on the same form. The problem stems from relying on search criteria drawn from controls located on the very form that's being loaded by the query. In effect, it creates a circular reference, similar to issues you might see with Excel formulas referring to themselves.
The solution is straightforward: avoid designing your queries so that their criteria depend on controls from the form being loaded. Instead, place those search fields on a separate form, like a main menu or a dedicated search screen, and then open your results form from there. Separating search and data entry forms avoids these conflicts and keeps your database behaving reliably.
Next, Edwin shared an experiment involving recordset variables. He assigned multiple recordset variables to the same object, then closed just one of them, hoping this might offer some performance benefit. Technically, when you do this in Access VBA, all those variables point to the same object in memory. Closing one closes them all. While this works, I would not recommend it as a best practice, since it quickly becomes difficult to track what each variable is really managing. The go-to best practice remains the tried-and-true cleanup pattern: always check if your object variable is not nothing, then close it and explicitly set it to nothing.
Thomas followed up with a question about running multiple databases in isolated memory spaces. Many users assume that opening two databases in separate windows means they're truly isolated, but that's not the case by default. If you just double-click your ACCDB files, Windows typically routes them to the same MSACCESS.exe process. This can cause trouble if one database crashes or hangs because it can affect all open databases in the same process. To run them in truly separate instances, you must explicitly launch Access using a shortcut to MSACCESS.exe and point to the database file as a parameter. This creates a distinct process for each instance, isolating them both in memory and with separate VBA engines.
Peter ran into another classic SQL mishap. He built a calendar form and found that while his query worked with hard-coded values, it failed as soon as he substituted form values. The underlying problem was a subtle bit of syntax: in SQL queries, when you want to specify two conditions (like finding records between two dates), you must repeat the field name for each condition. For instance, "WHERE DateTime >= X AND DateTime < X + 1" rather than "WHERE DateTime >= X AND < X + 1." This is a small detail, but forgetting it means your query just won't return the right records, no matter how logical your statement looks.
Another common question I get is about the impact of opening ACCDE files (compiled Access databases) in separate instances. The file type itself doesn't matter; what counts is how you launch Access. Using the shortcut method to specify the executable and the database file ensures proper isolation, independent of where the database file lives. Whether it's on a local drive, network share, or server, the launching technique is what matters.
Some users ask if there's a way to force Access to always open each database in its own process. As far as I know, there's no hidden switch or setting that automatically launches new database files in separate processes every time. By default, double-clicking another ACCDB routes it right into an existing Access process if one is running. The best approach here is to make dedicated shortcuts for the databases you use most often. That way, you're protected against one database crash taking down everything else.
I've also touched on the topic of API code. Seth asked why, if the goal in programming is deep understanding, I often say you don't need to know every inner detail of every snippet of Windows API code you use. My answer: there are layers to learning. You need to truly master the basics and the things you use every day, like loops, DLookUp, and recordsets. When it comes to more advanced or less frequently used code - like obscure API calls - it's often enough to understand what the code does, how to use it safely, and what results you should expect. Even experienced developers regularly consult documentation or trusted sources for these details.
BJones commented on a UI bug involving the new form zoom feature in Access, where button text wraps oddly at certain zoom levels. This is just one of those quirks you can expect with new features. It's wise to let others uncover the bugs before building production systems around brand new tools, but form zooming is still a welcome addition to Access, and I expect Microsoft will iron out these glitches in time.
Joseph made a valuable point about inactivity timers. If a dialog box is open, it can divert focus and interfere with timer logic. For this reason, I'm a big fan of running timers in the background or in hidden forms rather than relying on pop-ups. Access behaves differently in complex environments, and dialog windows often block background processing, so plan accordingly.
A quick update on a split-test I ran recently: I was experimenting with five different video thumbnail styles across multiple videos. After collecting a few thousand views, the results are basically even. The type of thumbnail seems to have little impact on overall engagement rates. Based on survey feedback, many viewers enjoy the Star Trek themes, but some advised against using unfamiliar branding or unnecessarily provocative images. My takeaway is to match the thumbnail style to the video content itself and not worry too much about chasing trends.
Before wrapping up, a reminder to visit my website for the latest video tutorials, updates, templates, and more. You'll also find my Captain's Log, forum discussions, and my book on Amazon. There's a wealth of resources for Access users of every level.
So today, you've learned the importance of tiny details in SQL statements - missing just one field name can make your query misbehave. We also talked about launching databases in true separate instances, dealing with quirks in new Access features, handling pop-up forms and inactivity timers, and understanding when and where it's okay not to sweat every small detail of API code.
If you enjoy this format or have new questions, let me know in the comments. Send your questions for future Quick Queries videos.
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 Why query criteria depending on the same form breaks searches
How circular references affect Access forms and queries
Separating search criteria forms from data forms
Assigning multiple recordset variables to the same object
Differences between empty recordsets and 'Nothing'
Proper recordset cleanup patterns in VBA
How Access opens databases in shared vs. separate memory spaces
How to force Access to launch separate instances for databases
Risks and recommendations when opening multiple Access files
Debugging SQL WHERE clauses with missing field names
Reserved word issues in SQL field names in Access
Building forms that filter data based on other form values
Effect of launching ACCDB vs. ACCDE on Access process instances
Impact of local or server database location on instance separation
Best architecture for multi-user Access databases
Making desktop shortcuts for separate Access database instances
Typical behavior when double-clicking ACCDB vs. using MSACCESS.exe
Approaching Windows API code without detailed knowledge
Pitfalls of new Access Form Zoom feature and UI glitches
How popup/dialog forms interfere with inactivity timers
Recommendations for reliable inactivity timer design
Using background apps or forms to monitor user activity
Split testing video thumbnails for YouTube performance
How to interpret YouTube thumbnail test results
Considerations when using AI-generated or themed video thumbnailsArticle Today I want to walk you through some common Microsoft Access pitfalls - especially one tiny but important SQL mistake that trips up almost every developer sooner or later. These small errors can stop your queries from working, even if your syntax seems perfectly fine at first glance. Along the way, I will talk about other helpful tips for working with forms, recordsets, multiple database instances, and clean VBA practices.
Let's start with a classic query mistake you might run into. Imagine you are building a search form where users can filter records by date. A user named Chuck noticed that records with null dates seemed to disappear from his results, even though his query criteria looked okay. He was using "is null" criteria on a field while pulling those criteria directly from controls on the same form that was loading. The mystery here is that Access gets stuck because the form is still loading, so your query is trying to read values from the form before it is fully available. This becomes a type of circular reference - just like what can happen in Excel - and the result is you do not get the results you expect.
To fix this, avoid making your query rely on criteria that comes from the same form that is still loading. Instead, put your search fields on a dedicated search form or main menu, then open your result form from there. This keeps the logic separate. After Chuck moved his criteria off the search results form and onto a separate form, everything loaded correctly. It also keeps your database cleaner and more manageable.
It is still possible to use filters from forms, but be careful: if the field you are referencing is on the form about to be loaded, it can lead to unpredictable results. Keep your search and data forms separate when setting up your queries to avoid these headaches.
Moving along, let's talk about recordsets and a performance question. Edwin, a student in Hong Kong, wondered about assigning multiple recordset variables to the same dummy object, then closing only one at the end. If you write code like this:
Set RS2 = RS1
You are not making a copy of the recordset. Both RS1 and RS2 just point to the same area in memory, so closing one closes the underlying recordset for all variables referencing it. This way of handling recordsets is clever, but quickly gets confusing. The best practice is to treat each variable individually for cleanup so you do not accidentally reference an already closed object.
Remember that an empty recordset (with zero rows) is not the same as a Nothing recordset. If your variable is set to Nothing, usually the Set statement failed or never happened. The safest way to close a recordset in VBA remains the tried-and-true method:
If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If
It may look repetitive, but this method is reliable and avoids subtle errors.
Now, let's address questions about running multiple Access databases simultaneously. If you simply double-click ACCDB files to open them, Windows routes them all through the same running MSACCESS.exe process. That means multiple open databases share one Access process, VBA engine, and internal resources. If one crashes or gets stuck, others often freeze too. If you want each database isolated in its own memory space, use a shortcut that explicitly runs MSACCESS.exe and passes your database file as a parameter. Doing this gives you separate Access processes and keeps them independent - so if one crashes, the others usually keep running.
This behavior is true for other Office apps like Word and Excel as well. Just because you can open two copies of the same database does not mean you should. Be especially careful with unsplit databases (where your tables and logic exist in the same file). If you do open the same database in two instances, make sure to only edit in one and keep the other read-only to avoid conflicts.
Now let's return to the original SQL pitfall with a hands-on example. Suppose you have a WHERE condition in your SQL query like this:
WHERE DateTime >= Forms!DatePicker!F_Calendar AND < Forms!DatePicker!F_Calendar + 1
At first glance, this seems fine, but it is not valid. If you substitute the form reference with a variable X for easier reading, you are left with:
WHERE DateTime >= X AND < X + 1
The problem is the second condition is missing the field name before the less-than symbol. SQL needs each condition to explicitly call out the field, so the correct syntax should be:
WHERE DateTime >= X AND DateTime < X + 1
You need to repeat the field name for each condition. Without it, Access does not know what you are comparing and you will not get any results. Many new users, and even some experienced ones, stumble over this.
For further clarity, remember to avoid using reserved words like "DateTime" for your field names. If you must, bracket them like [DateTime], but it is usually best to choose another field name if possible.
Sometimes, if your query syntax is broken in subtle ways like this, it is tempting to work around the issue by building dynamic QueryDefs in VBA and substituting values in runtime. While that can work, often the simpler solution is to fix the SQL syntax directly.
A related question comes up often: does launching an ACCDE file, or putting your database on a server rather than a local disk, change Access's instance-sharing behavior? In short, it does not. What matters is how Access is launched. If you run MSACCESS.exe explicitly for each file, you get separate processes. The file type or file location generally does not affect this.
In production or multi-user environments, the recommended setup is for each user to have a local copy of the front end running in their own Access instance, all connected to a shared backend database on the server. This gives you the most reliable multi-user experience.
Another tip for power users: if you often need to work in multiple databases at once, there is no built-in setting to force every open database into its own Access instance. Set up desktop shortcuts to MSACCESS.exe for each frequently used file instead. This gives you more control and keeps your databases from interfering with each other.
On the topic of VBA APIs and complicated code, some people feel pressure to understand every tiny detail under the hood to be a "real" programmer. That is great as a long-term goal, but you do not need to fully reverse-engineer every Windows API call just to use it effectively. Focus on understanding the tools you use every day - loops, variables, queries, and recordsets. For advanced functions and API code, it is perfectly normal to look up references or copy reliable code patterns as long as you understand their purpose and how to use them safely. Use what you need, and rely on documentation and trusted sources for the rest.
Now let's touch on a new Access feature: form zooming. If you find text wrapping or UI issues when zooming forms (for example button labels not resizing cleanly at certain zoom levels), know that small bugs are normal in early releases. The Access team continues to improve polish and stability, but it is wise to wait a bit before relying on brand-new features in critical systems.
There are also practical points when working with inactivity timers in Access. Using dialog boxes or popup forms can steal focus and interfere with inactivity logic. If your timer form loses focus because a dialog pops up, your timers may pause or your logic may break. A more robust approach is to use background timers or monitor activity internally, and avoid relying exclusively on popups for timing or inactivity warnings - especially in complex environments or when running Access on platforms like Citrix or Remote Desktop. Test real-world scenarios to make sure your logic works as expected for your users.
On a lighter note, when it comes to designing YouTube thumbnails or similar covers for your videos or courses, split-testing images can show you if different styles make a measurable difference. After testing several types of images, I found that, for my audience, the thumbnail style made little statistical difference to view rates. This illustrates a bigger point: sometimes, the content is what matters most, and it is fine to experiment but follow the data.
To wrap up, we have covered how a simple missing field name in an SQL statement can cause your Access query to silently fail, even though everything seems right. This is one of those mistakes that can take hours to spot, so always double-check your WHERE clauses: each condition needs a field name on both sides of the logical operator. Use AI tools or good old-fashioned code reviews to help you find typos and subtle syntax errors, especially when you are stuck.
We also explored how to keep Access databases truly separated in memory for stability, the quirks of new Access features, background timer logic for inactivity, and the importance of focusing your understanding on the parts of code you actually use every day rather than memorizing advanced API calls.
If you have questions or want more help, there are many online forums and communities with experienced developers and tutors who are happy to assist you. Remember, programming is a process - learn from each mistake, keep your setups clean and logical, and do not be afraid to experiment and check the documentation or ask for help when you get stuck. Making small changes, like correcting a single SQL clause, can have a big impact on your Access project's reliability and performance.
Happy coding, and if you are taking a long weekend, I hope you enjoy it! |
