Quick Queries #72
By Richard Rost
22 days ago
How to Find Out Who's Locking Your Access Database
In this video, we'll talk about a wide range of Microsoft Access tips, tricks, and shortcuts shared by users from my website forums, YouTube comments, and other channels. I'll go over conversation starters such as favorite time-saving techniques in Access, including control wizards, auto backup, using SQL as a data source, combo box tricks, working with special characters, table and relationship importing, helpful keyboard shortcuts, database design advice, and more. We'll also answer popular community questions and share comments, suggestions, and database design examples from fellow Access users. Quick Queries 72.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Query 72, debug compile, option explicit, compact and repair, input box equations, fitness database, global function strip name combo, VBA templates, shift control button commands, date field plus minus t buttons, new record property combo box SQL, auto backup code, SQL as data source, recently viewed customer list, insert into new table from existing table, arrange tab tabular continuous form, control apostrophe, control semicolon for date, quick access toolbar, compacting database reset autonumbers, customer code, decompile corrupted module, F2 control shift F2 procedures, dashboard shortcut, right click filter equals, Excel output reporting, image gallery purge, character map tool, symbol picker, ACCDE import tables relationships, ExportXML ImportXML, control wizards, reset database for new year, ticket ID year sequence reset, SQL Server for Access Developers, Access with OpenAI
Intro
In this video, we'll talk about a wide range of Microsoft Access tips, tricks, and shortcuts shared by users from my website forums, YouTube comments, and other channels. I'll go over conversation starters such as favorite time-saving techniques in Access, including control wizards, auto backup, using SQL as a data source, combo box tricks, working with special characters, table and relationship importing, helpful keyboard shortcuts, database design advice, and more. We'll also answer popular community questions and share comments, suggestions, and database design examples from fellow Access users. Quick Queries 72.
Transcript
Today we're going to talk about that, plus lots of answers to questions from my website forums, YouTube comments, email, and wherever else I happen to get them. It's Quick Query 72, folks. Here we go.
Starting off today, every now and then I post one of these things. I like to call them conversation starters. I like to just put something out there and get your feedback and see what everybody has to think about a particular topic. The last time I did it was back in November. And the question was: What is your favorite trick or shortcut in Access that saves you time? We got lots of replies to this one. Let's go over them.
Starting off on my website and the forums there, Adam says debug compile, option explicit, and compact and repair definitely save me more time in Access than anything else. Yeah, debug compile obviously, but option explicit is a big one. When I first started working with Access, I didn't know about option explicit. So you could have a variable spelled wrong, even though you are camel casing them yourself as you type them in. If you don't have option explicit, it's not going to catch it as a wrong variable name. So definitely.
Sandra says the calendar opening over the date field, yep, and being able to enter equations in my fields, either by input box with a new method. Yep, that was covered. I think we did that in the fitness database series. So you're missing out if you're not watching that. Yeah, that was fitness 49. See, I cover lots more cool tricks and stuff in these fitness database videos than just fitness stuff.
This one's how to enter Excel-style equations in any text box. You can type in equals 60 times five, or I have a version where it will pop up an input box. I actually like the input box one better, because then when you hit enter or when you hit equals, it pops up the box and you can modify stuff in there before it changes it in the form. So there are all kinds of great ways to do that. Watch the fitness database videos.
Matt's got some cool stuff here. He's got a global function that just basically strips the name combo off of boxes so you can get state combo, country combo. That's pretty cool.
Raymond uses the single and continuous forms in my blank form template. He's got his own VBA that he put in there. Using templates is definitely a time saver. If I had to restart a database from scratch every time I did a video, the videos would be five times longer.
Oh, yeah, the shift or control when clicking a button to do different commands. I covered that in a recent video. My status function. Yep, that's a great one.
Joe mentioned my date fields. I like to make the little plus and minus buttons and then a T so you can add a day, subtract a day, or switch to today for any date field. That's a good one.
This is a cool trick that Joe mentions: using the new record property to change the SQL resource of combo boxes. For example, only active employees should show up for new records because you have to pick someone who is currently an employee. But for older ones, you have to show all of them because you have to show the previous ones too. That's a great idea. I might steal that for a video.
Sammy, having watched every course, every template, every TechHelp video, and every Quick Query you have made: If I start listing all the things I use that you've taught, you will ban me from the site. No, I would never ban my diamond sponsor from the site, of course not. Since he is not only my diamond sponsor, but also my premier sponsor, I will give him a quick plug. If you need any Access development work, here he is. Check him out. Does great work. Got a whole team of people that do awesome stuff. All right, plug over.
To pin down one item, it would have to be the backup code before closing the database. That's helpful. Sammy says he backs up the data when the first user accesses the system and is running. I personally like a timed backup. I have mine set to like 3 a.m.; it backs up all the data.
Carey says using actual SQL as the source of data instead of a query. Yes, saved queries are helpful. They're essential for beginners because it's a lot easier to teach the query design grid than it is to teach SQL. But once you learn proper SQL, it's a whole lot easier to use SQL in a lot of places than to constantly build queries. And you get five million queries in your navigation pane.
Sandra changed her mind. Okay, that's fine.
Monica's got the auto backup feature in all her databases. It hasn't saved her yet, but give it time, Monica. That's one of those things that is like insurance. You pay for it every month, hoping you'll never need it, but it's there in case you do.
My helper tables, those are great.
Blank database with your preferred settings. Yep.
No, Joe's trick wasn't in a video, but I'm going to steal it. Don't worry, I'm going to make a video out of it eventually. Oh, there you go. No video needed there. Joe posted the code.
But I see what you're saying, basically. Think of it like if you have sales reps and someone leaves the company, so you just mark them not active. Now when you open up a new record, you just change the employee row source. That's a great tip.
The recently viewed customer list, this one I honestly use every day when I'm doing my customer service, poking through the database. I'll think of something that I should have said to someone that I closed an email for two minutes ago. I just open up my recent customer list and I can see the last 20 people that I had the record open and I can just go right to it.
Any of these, by the way, I'm not going to put links to all this stuff down below. Go to my website and do a search. I've really improved my search engine on my site, so just pick any keyword that you're looking for and you should find it.
Kevin, yeah, great trick. Insert into a new table from an existing table. That will create a new table. If you say where false, then it doesn't create any records, it just copies the structure of the table. That's an excellent tip.
Arlen says he saw it on another site. When he's building a continuous form, he pulls all the fields and then he goes to the arrange tab and selects tabular. Then it automatically takes all the labels and puts them in the header and the controls of the detail section, and you can rearrange them. Then you click remove layout.
I didn't actually know about this because I've been using Access since way before that layout view was ever added. So I'm just old school. I'm used to dragging them by hand. I will probably always do that.
I remember this trick. I actually made a video about this years ago after someone showed it to me and I thought, this is pretty cool. I'm not going to do it because I still do it the old fashioned way. I think it's easier just to arrange this stuff by hand myself than to do that. If you want speed, that's not a bad tip. Here's the video I did on it four years ago after someone taught it to me, one of my students. I love when you guys teach me stuff because they're always adding new features here and there. I don't always stay on top of every new little thing that's been added. But yeah, there it is. I'll put the link to this one down below.
Then Joe mentioned again the enter calculations in the field. That's very helpful for me. I use that in a lot of my databases too, like my account balances database where I have to put in your pending amount, your total amount.
All right, let's head over to YouTube and see what people said on this one.
Control apostrophe to copy a field value from previous record. That's a huge time saver. I'm putting together a cheat sheet that's got all the popular shortcut keys. I used to cover a shortcut key every couple of weeks, but those are too hard to keep track of. I'm going to put them all again on a mouse pad or something. That's a big one that I use a lot. I also use control semicolon for the date, control shift semicolon or control colon for the time. Those are two of my favorites.
Using the quick access toolbar. Absolutely. You need to put links up there for design view, form view, visual basic code view, all that stuff. I've got all those on mine too.
Compacting a database to reset the auto numbers to one after deleting test data in tables. I used to do this a lot because people would always complain, like, why isn't my customer number starting at one or my order numbers, whatever. Then I had to go into the whole explanation of auto numbers aren't for you, you shouldn't worry about what they are. There's a whole video on it. But yeah, everybody gets a brand new database and they want those numbers to be set at one. You can tell people that those numbers don't matter, but they don't care.
I got to the point where I teach them, obviously, because I want beginners to get used to them to understand what they are. But when I used to make finished production databases, I never showed the auto numbers in my final products, so clients never could complain about it. If they needed some kind of a number, like an order number or a customer number, I would make them a customer code. I have a whole separate video on that too, if they needed a number to work with.
Oldie but a goodie: shred data-generated customer code. It's unique for each customer. You can tell it's an older video because there's no picture in it. These are some of the older ones. Hold on, this one's five years old already. Wow. That's about when I started doing TechHelp. It was about five years ago.
Let's see what else we got in here. Compile in the command line to recover a dead corrupted module or file. That's good. If you've got weird errors or problems with your database and you can't figure it out, use decompile. Again, there's a video on this on my website as well. Sometimes the code gets corrupted and decompiling it and then recompiling it again using debug compile will fix weird problems.
Select F2 and control shift F2 to access procedures from where they are called. This is basically the same as right clicking and then going to definition on something. This just goes back to the last position you were at. You can use these keys too if you prefer. I always forget keyboard shortcuts unless I use them every day, but let's say here I've got a function called get setting that I wrote. You can right click on it and go to definition and that'll bring you to that setting. Control shift F2 I do use every day, so I remember that one. If you went on this and just did shift F2, it's the same thing as going to definition. That's pretty cool, and that's how you have to remember it.
I always just right click on it and go to definition. But that's a great one.
What do we got? Control plus control period to switch between normal mode and design mode. That's one that I don't use myself, but that's pretty handy.
Editing a class module in notepad and changing the attributes. Okay, that's a little more advanced.
Just like this one here: creating extra records for your table. There are lots of different ways to do this kind of stuff. That's a pretty advanced suggestion, but thank you for that one.
Over on Facebook, Robert says dashboard shortcut and quick access toolbar. Making a shortcut to your main menu, for example, I do that one in my blank template video. Included another shortcut for the color chart as well as the obvious ones: the format, top, bottom, left, right. That's good. I probably stole them from you, so I've got nothing; at least you're honest.
Had some good ones over on Reddit. I posted it in the MS Access group or whatever they call them--subreddits, I guess.
This is one of my favorite ones: the new prisoner. Putting lots of comments on my VBA so that two years later, I don't have to scratch my head thinking what on earth does this function do. I talk about this a lot in my videos. Yes, you are not commenting for other people; you are commenting for yourself in the future. Definitely.
Run and Through Life says right clicking the LDB file and opening in an editor. He says Notepad++, but you can use any text editor to see which computer has the database locked during their lunch break. This is a good tip. The lock file, the LACCDB file (LDB is the old standard, but the new ones are LACCDB). Here you can see a database I've got open right now. This guy right here, the lock file. If you right click and open that in notepad, let's say, open with notepad. There it is in notepad. This will open up and show the people that have it open. The computers that have it open will be right here; it will be a list of them. It's just on my computer right now, so my PC is named Piccard, of course.
That's a great trick if you have a database, especially if it's something you have to back up or copy or update and it's saying that someone else has it locked. Well, who's got it locked? Look in the lock file. That's a great tip, thank you.
Here's one from Access Helper. Only recently I realized if you right click on a field and use the filter equals option, you don't need to put a specific value and you can use any operator like greater than 1000 or less than 20. For text fields, you can use wildcards. This works like older versions of Access when all you had was one general filter field. In other words, you don't really need all the other filter options like begins with and so on. That's a great tip.
Using Excel as the output and Excel file links to queries and all subsequent reporting and dashboards are in Excel built off the linked data. This helps you use Excel's rich functionality while still being able to keep data current in real time.
I've always said that Access is where you want to store your data and manipulate your data and work with the data, data entry, all that stuff. But when you want to put something together to present it, especially like a report or something, yes, Access can make some simple rudimentary reports, but really bring it over to Excel and you can make really cool charts and all kinds of stuff there. So Access and Excel working together is one of the strong points of using Microsoft Office.
I already mentioned these ones, but I just had to mention this post because it's Cyborg Penguin. I love penguins, so you get a mention.
Thanks to everyone who posted one of their favorite tricks. I really enjoy these conversation starters and reading all your replies and going over them. We're going to do more in the future, so stay tuned.
Next up, let's head over to the forums on my website.
Dan is posting that he's got a file error that won't go away. He says he opens up the file and he gets a can't open the file error. One's looking for a JPEG and the other for a PNG. These are probably images. He watched my video on purging the image gallery, which, if you've got images that you've used in design in your forms and stuff, which is the only way that I recommend you use images in your database, by the way, is in design, then they'll go into a thing called the image gallery and that way you can use the same images on multiple forms, like a company logo or something like that. We all know we don't store images in the database like employee photos and product pictures and stuff like that.
Sandra and Kevin gave him some things to suggest. Then I gave him a list of other stuff in here too. Check your format properties for pictures. Sometimes they can get stuck in the properties there, including in your header and detail sections, your footer sections, navigation forms. A missing background image can cause this error. Look for any hidden image controls on forms. They can be tiny. They can be hidden behind other controls, left over from older form designs. Check for evil things. I have a whole page full of evil things like OLE objects or attachment fields. Get them out of your database completely. Do a global search in your VBA editor for JPEG, PNG, BMP, the load picture command, all that stuff. See if it happens if you shift bypass when opening the database. Then try opening your forms one at a time until the error appears. Then you'll find out what form is causing it. Then you just have to figure out what object is triggering it. Of course, make sure you compact and repair. Make sure you run down the troubleshooter. There are all kinds of things it could be. It's probably Access failing to find a missing external file, so you just have to figure out what object is calling it. And if worse comes to worst, what I usually start doing is create a blank new database and then just one at a time import the objects. You could try doing blocks of them if you want to. Bring over all the tables, start the database, see if you get the problem. Bring over all the queries, restart the database, see if you have the problem. Then the forms, if you have 30 forms, maybe bring over five of them and then restart the database. See if you open those forms up. Eventually you'll figure out which object has the bad guy in it. Let me know if you get it fixed. I know you posted a follow up that you still haven't found it yet, but we're still waiting to hear back from you if you got it fixed or not.
This isn't a question, but one of my students, Lisa, posted some really cool shots of some forms that she's built. I just wanted to share them with everyone. They look really cute and they're just nice. This is what really classy database design can do. Aren't these great? I think that's the last one. Yeah, those are really super cute. I love them. Thanks for sharing. I love when you guys share your design work with me. That's awesome.
User Kim says if someone could give her help figuring out how to get some characters with the weird little symbols in them. There are lots of different ways you can do this. This isn't an Access feature. It's more of a Windows thing.
Dan posted a link to the askycode.com.ar. Alex has some different links in here as well. The ASCII table. There's another one that Stephen shared. I posted in here that Access will store and display accented and international characters, as long as Windows can type or paste them. So there's the character map tool: press the WinKey plus R to run something and then type in charmap. That'll bring up this guy. You can see there are all kinds of different things in here. There are symbols, there are characters with the whozits over them, and all kinds of different font sets you can pick from.
There's also the newer Windows symbol picker, which is Windows key plus period. This brings up all kinds of emojis and symbols and things. There are lots of different ways to get these symbols into your website or your application or Access or whatever. You can also use the alt keys if you know the ASCII key combination for it.
Brian shared this one here, the unicodesymbol.com. So lots of different ways to get these symbols.
Next up, Tom is asking if he can import the relationships when he imports his tables. He's got an ACCDE file that periodically needs updating. Currently, I import the form and the table from the ACCDE into the ACCDE. When I import these, the import adds all one to the end of the table name to differentiate it from the original. Then I delete the original and rename the import. When I import the tables, the relationships do not come with the import. Is there a way to get these relationships so I don't have to keep recreating them?
The short answer is no, not automatically and not easily. When you import tables using the VBA methods -- if you're using Transfer Database or Transfer Text or a DAO TableDefs copying or importing tables one at a time with that method -- the relationships do not come along automatically. Relationships live at the database level. They're metadata in the database structure, not inside the table itself. So Access treats them separately and leaves them behind. That's why when you import tables one at a time, you always end up having to recreate the relationships manually.
You could import all the related tables in one operation using the external data Access feature. External data - new data source from file or from database. Pick your database file. Then you get this dialog. Pick your table and then under options, you can import these things. But this is a manual process. This is not exposed through VBA. This logic lives inside the Access import wizard and you can't really get to it through something like Transfer Database.
Another approach is to use a staging database that already has the tables and the relationships. You can then update that database and then replace or link the old one. I would personally not bother with just replacing the tables themselves. Just import the data into the existing tables. You can use append queries; you can replace the auto numbers too.
And I also mentioned: why do you need the global system relationships in the first place? I rarely use global system relationships in the database at the database level. I never use cascade updates because I always use auto numbers. A cascade update is like if you have something linked by, let's say, a social security number, and then if you change it in the parent, it changes all the children. Well, I never have to deal with that because I always use auto numbers and those numbers aren't going to change. Cascade deletes are the other reason why you want to enforce referential integrity. Those can be dangerous too. So I maybe use them for log entries or something that's not important data. I like to handle all of that logic myself explicitly in my code so I know exactly what's happening and when.
There is one other thing that I thought of after I posted this: you could use XML. There's Application.ExportXML and ImportXML, and XML relationships can be included because they're part of the schema. But almost nobody ever uses this for routine table refreshes. It's clunky, it's fragile, and it doesn't scale well for large tables. You could do that. If anybody really wants to see how to do that, let me know. XML files are good for, let's say, you want to export a customer and all of his related contacts and all of his orders and all of those orders' order details. XML is a good structure for that (like JSON is) because it'll store a record and all of its related information. So that's one way you could do it. This might make a good video, so if anybody wants to see it, let me know.
But I hope that helps to answer your question, Tom.
All righty. Let's head over to the YouTube comments.
We got a comment from a beginner on my Access Beginner Level 2. He says, I appreciate your help. After adding the combo box to my form, the combo box wizard menu does not pop up on my end. It just has the combo number and unbound in the description box.
This is actually a very common problem that beginners run into. So common, in fact, that I got a whole video about it. Basically, your control wizards somehow got turned off. That happens by accident. It can happen very easily. So here's your form in design view. Up here is your control box. Drop this down. There's this little guy here who says "Use Control Wizards." If that somehow accidentally gets turned off (I didn't even realize I just turned it off), if I go to grab a combo box now and drop it on my form, that's all you're going to see. Nothing happens. All you need to do is come back in here, drop that down, turn on the control wizards, and now when you grab a combo box and drop it, the wizard starts up. Yay.
Now, there may be other reasons why this wizard doesn't start. But at your beginner level, if this doesn't fix the problem, you're going to want to uninstall Office and reinstall it again, because those control wizards can actually get corrupted. So if something happened on your computer and you do what I just showed you and it still doesn't work, you're going to want to uninstall Office. Don't just reinstall Access; uninstall all of Microsoft Office and then reinstall it again. That's how you fix it.
Good advice from Borin Betten -- I don't know how to pronounce that -- but he says, this is exactly why I watch every single video, no matter what the topic. Yes, you definitely should watch every video that I release, no matter what the topic is.
This is about my resetting your database for the new year. I get the same questions every year. I reposted this a couple of times. I think I recorded it in 2023 or whatever. But a lot of people post comments or questions or send me emails like, my order table is for 2025, how do I rename it now for 2026 and have it work with all my forms? No, you don't do that. Or if you have fields in your tables that are like 2024 sales, 2025 sales, don't do that. Just have a "sales" field, then you put a year field in the table, or you use a date, that kind of thing. You don't customize table names or field names for the year.
I see people do this all the time in Excel, and honestly, in Excel, if you're just doing summary information, that's not a terrible thing to do. I teach that in my beginner classes. You can have your 2025 sheet tab and your 2026 sheet tab. It's okay for small bits of data for summarizing information. But when you start talking databases, that's bad practice. You don't want to do that. So yeah, watch this video and watch all of my videos, just like he said. The guy with the pitchfork is right.
Mitchell, thank you very much. I always like to give a shout out to people who send me a super thanks. I really appreciate that. It definitely keeps me going, keeps me inspired to make new videos, and I appreciate that you appreciate my work. Thanks again, and happy holidays.
Carlos writes: I got an issue with scrolling in Access. I have a brand new HP laptop. I've gone to its settings, thinking it was that, but everywhere else is fine, just inside Access. I don't have a separate mouse.
That's a weird one. I have never run into a situation where you can scroll up and down in other applications like Word or Excel, but you can't scroll inside Access. I'm assuming you've got a table, you're in table view or a continuous form, and it won't scroll up and down. That's new to me. If you can scroll up and down in your other apps, you should be able to scroll in Access. So my advice, reboot the computer. Don't just shut it, don't just close your lid. Do an actual shutdown and restart it and see if that goes away. But I need a lot more information to be able to help you. No idea what you've got going on there. Also, make sure it is scrollable because sometimes people open up a form and it's just one record in a single form, and they don't realize that's it. That's all you see. You can't easily scroll that, so you have to use the navigation buttons at the bottom of the form.
Cruisin Fiber Geek says he's got a ticketing system where the ticket ID carries the year plus a sequence starting at one. That's not a bad system. Each year, I need to reset the sequence to start the new year at one for the new sequence. I've been doing this manually, creating the first ticket ID, and then Access will then follow the new sequence every time someone creates a new record. You could do that, but what if you're out that week? Or what if the new guy who comes in after you doesn't know to do this? I would suggest having something in when a new record is created. Have it look at the system date, and if the month is less than the last ticket entered, then reset it instead of relying on you manually doing that. You can look up the last date of the last ticket that was entered in the system, and that should tell you what its month was. If it was December, just look at today's month. If, assuming your system clock is right, which nowadays most machines are, take a look at the month of now versus the month of that previous ticket, and if it's less than the previous one, that means the year has flipped. Increase the year. It's little things like that, because what if you get hit by a bus? What's going to happen then?
Next up, Steve wrote in to say that he didn't know anything about Access until he started watching my videos and he used it to build a job tracking database for his workplace. The engineers and staff loved it and used it, but the head office and the IT later rejected it despite how well it worked. First of all, I appreciate the kind words, thank you. It's great to hear you were able to build something genuinely useful with Access. And you're definitely not alone. There's a long-standing prejudice in the IT world that Access is a toy database. That's what everybody says, even though in the real world it often solves problems faster and much less expensively than other solutions. If the engineers and the staff using it loved it, that says more than what any head office opinion is, because the head office people are going to listen to the IT manager, and IT guys are all like, no, it's got to be cloud-based, and all this. I've done several videos on how lots of people in the IT world hate Access, but that's their problem. Glad the videos helped and best of luck with whatever you build next, and Merry Christmas to you as well.
Jose says, how's it coming on the course on SQL Server on the local computer? I'm having quite a long holiday season this year and would be happy to spend some time learning new skills.
Well, I am working on a new course. It's called SQL Server for Microsoft Access Developers. It's basically taking your Access database and migrating the backend to SQL Server. It's going to teach mostly how to use it with an on-premises SQL Server for security, stability, and speed. But I never give estimates on stuff until it's ready to release. I used to do that. I used to give a calendar of like, I'm going to have this released in January, this in February. I stopped doing that because I am terrible at schedules. Things happen, stuff comes up, and I would rather just keep you waiting than tell you, okay, I'm going to have it January 5th and then it doesn't show up. So when you see me post a new release on it, it is, aside from my daily stuff that I'm trying to get done (my TechHelp videos, extended cuts, Access developer lessons), very high on my to-do list. So it's coming. Don't go anywhere. I might not have it ready for your holiday season binge-watching since yesterday was Christmas already, but I'm working on it. It'll be done when it's done.
And finally tonight, Jeff says, I'd like to see how AI works with Access. I'm a developer since the 90s, but I don't know how to get started with AI.
Well, start with this. This is my first TechHelp video for using Access with OpenAI. It will teach you how to talk to the chatbot, get information, and I've got lots of videos after this one. You'll find links on this page that teach you how to do all kinds of stuff, including a template that will show you how to have Access send data from the database to the AI and get it back and work with it and do all kinds of stuff. This is the one to start with. Go watch this one. There are links to more stuff on here that use AI, so check it out.
All right, so don't forget to mark your calendars: Access Day 2026, in Redmond, Washington, March 27th. Check it out. Save the date.
Don't forget to stop by my website and check out the What's New page. I'm always adding new videos, updates, templates, random bits of Access goodness here and there. It's always worth a quick look to see what you might have missed.
While you're there, make sure you subscribe to my mailing list. You'll get an email every time I post something new.
And don't forget, right now--see, today is December 26th--the holiday special is still running. I do one sale every year, folks, and this is it. If you are not a member already, you can get a discounted membership, get an annual membership, get a 13th month free. I'm also doing 50 percent off all of my courses, templates, seminars, all that stuff. This is the only time of the year that I do it. So if you don't get it now, you have to wait until next year.
Be sure to check out my Captain's Log where I post my thoughts about whatever happens to be running through my brain that day. This year I dropped my holiday classic, 'Twas the Night Before Christmas on the Bridge. Check it out.
Make sure you head over to the merch store, get all your Access Learning Zone merchandise: postcards, teddy bears. I sound like the guy at a ballgame: get your bear here, cold bear, popcorn.
I'm going to drop a link to my book. Access Beginner makes a good Christmas present, even though you missed Christmas this year. You can send it to anybody.
So that's going to do it, folks. That is your TechHelp Quick Queries video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. What is the main purpose of using "Option Explicit" in Access VBA code?
A. To automatically format your code
B. To catch misspelled variable names during compilation
C. To speed up code execution
D. To import external data
Q2. What does "Compact and Repair" do in an Access database?
A. Deletes all data in tables
B. Optimizes file size and repairs potential corruption
C. Duplicates database objects
D. Converts data to Excel format
Q3. What is the benefit of using a global function to "strip the name combo" off combo box controls?
A. It hides all combo boxes from forms
B. It allows for standardized naming and easier code reference
C. It deletes all unused controls
D. It imports data automatically
Q4. Why is using templates in Access recommended by the video?
A. They reduce video length and save development time
B. They increase the database file size
C. They make forms slower to load
D. They prevent future edits to the database
Q5. Which shortcut allows you to copy a field value from the previous record in Access?
A. Control semicolon
B. Control apostrophe
C. Shift F2
D. Alt F4
Q6. Why is it advised to avoid customizing table or field names for particular years in Access?
A. It makes the data look unprofessional
B. It complicates data management and requires frequent renaming
C. It reduces database size
D. It enhances security measures
Q7. What should you check if the combo box wizard does not start when adding a combo box in Access?
A. Internet connection is stable
B. "Use Control Wizards" option is enabled in the toolbar
C. The database is open in exclusive mode
D. You have macros enabled
Q8. How can you view which computers currently have an Access database open?
A. Check the error logs
B. Open the LACCDB (lock) file with a text editor
C. Review the navigation pane
D. Run a query in SQL view
Q9. For advanced importing of relationships when migrating Access tables, what less-common feature can be used?
A. Import wizard only
B. Application.ExportXML and ImportXML with schema
C. Copy-paste in table design view
D. Switchboard manager
Q10. According to the video, what is the main argument against using global system relationships and cascade updates/deletes in Access databases?
A. They are too hard to understand for beginners
B. They should be controlled explicitly in code for accuracy and safety
C. They are only available in SQL Server
D. They increase query performance
Q11. What is one recommended use case for combining Access and Excel?
A. Data entry in Excel, reporting in Access
B. Data storage in Excel, manipulation in Access
C. Data entry in Access, advanced reporting and dashboards in Excel
D. Only use one, not both
Q12. What is a common troubleshooting approach for persistent "can't open the file" errors related to images in Access?
A. Reinstall Windows
B. Search for hidden or leftover image controls and check image properties
C. Compact and repair only
D. Upgrade to the latest Access version
Q13. What should you do if keyboard shortcuts are hard to remember according to the video?
A. Look up individual keys before each use
B. Create or find a printable cheat sheet of shortcuts
C. Only use the mouse for navigation
D. Change the shortcuts
Q14. Which feature allows users to enter Excel-style equations into text boxes in Access forms?
A. Built-in Access calculator tool
B. Custom VBA function or input box for calculations in fields
C. Query design grid
D. Property sheet setting
Q15. When importing tables using VBA (TransferDatabase or DAO TableDefs), what does not get imported automatically?
A. Data
B. Relationships
C. Table structure
D. Field types
Q16. Why does the video encourage adding comments to your VBA code?
A. To improve code execution speed
B. To help your future self understand what the function does
C. For publishing your code
D. For reducing database size
Q17. What is a quick way to add special characters or symbols into Access or other Windows applications?
A. Using the Windows Character Map or Windows key plus period
B. Enabling macros
C. Changing the database regional settings
D. Downloading additional font packs
Q18. Which shortcut key in Access enters the current date in a field?
A. Control apostrophe
B. Shift F2
C. Control semicolon
D. Control period
Q19. What is the recommended routine for backup, as discussed in the video?
A. Back up manually at the end of each workday
B. Use automatic timed backup, such as every night at 3 a.m.
C. Only back up after database corruption
D. Rely solely on Windows restore points
Q20. What is the general advice given in the video regarding handling primary keys and auto numbers for customer/order codes?
A. Always reset auto numbers manually to start at 1 for each database
B. Don't show auto numbers to end users, create a separate customer/order code if needed
C. Use same auto number as visible customer code
D. Frequently delete and re-add auto numbers
Answers: 1-B; 2-B; 3-B; 4-A; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-B; 14-B; 15-B; 16-B; 17-A; 18-C; 19-B; 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, I'm going to cover a variety of topics raised by questions from my website forums, YouTube comments, emails, and other places where students reach out to me. This is Quick Query 72, so let's jump in.
Occasionally, I like to post what I call conversation starters to prompt feedback on various topics. The most recent one asked for favorite Access tricks or shortcuts that save time. Lots of great responses came in, so let's look at a few highlights.
Adam on the forums emphasized the importance of using debug compile, option explicit, and compact and repair. Option explicit is especially valuable because without it, misspelled variable names can go undetected, which can be a hassle to troubleshoot later.
Sandra appreciates having a calendar pop up over date fields and the ability to enter equations directly into form fields. I covered methods for equation input, including one in the fitness database series, where users can either type calculations directly or use an input box for additional flexibility. If you haven't watched those fitness videos, you're missing a variety of tricks beyond just fitness-related topics.
Matt shared a helpful global function for simplifying combo box names, making it easier to handle multiple controls like state and country selections. Raymond uses blank form templates with his own VBA added, pointing out that templates can save significant development time—otherwise, every new project would take much longer.
Using modifier keys like Shift or Control when clicking a button to produce different commands is something I covered in a video focusing on status functions. Joe mentioned a technique with date fields: he adds plus and minus buttons and a 'T' to navigate days or jump to today's date quickly. Another valuable idea he contributed concerns using the 'new record' property to adjust combo box SQL sources. For instance, showing only active employees for new records, but including inactive ones for historical records. I think this one deserves a video in the future.
Sammy, who has seen just about everything I've produced, highlighted his use of backup routines before closing databases and prefers running backups at specific times, such as 3 a.m., to safeguard data reliably, which is a strategy I agree with.
Carey prefers to use actual SQL rather than saved queries as data sources. While saved queries are great for beginners, learning SQL opens further options and keeps the navigation pane less cluttered. Monica and others mentioned using automatic backup features, which act as a kind of insurance. It's something you hope to never need, but you'll be grateful for it should something go wrong.
Kevin brought up a classic Access trick: using an append query with a 'where false' clause to duplicate table structures without copying the data, which is handy when you need to start a fresh table with the same design.
A tip from Arlen described quickly arranging controls in forms using the 'tabular' layout, an option I did not have when I first learned Access, so I tend to arrange controls manually out of habit. Still, this can speed things up for many users.
Joe also mentioned entering calculations directly in fields, a technique I use often myself, especially in databases like account balances.
Moving over to YouTube responses, someone pointed out the great time-saver of using control-apostrophe to carry over a value from the previous record, as well as shortcuts like control-semicolon for today's date and control-shift-semicolon or control-colon for the current time. These are the kinds of tricks that can really add up in terms of efficiency.
Several mentioned customizing the quick access toolbar for easier navigation between views and code. Compacting databases to reset auto numbers after clearing test data came up too, though I always remind students that auto numbers are not designed to be meaningful to users. For user-facing numbering, it's better to generate your own codes, as I explain in separate videos.
Using compile and decompile commands as a way to resolve strange database errors is a good troubleshooting method. Some users also suggested keyboard shortcuts like F2 and control-shift-F2 to jump to function definitions in VBA, or using right-click go-to-definition, both of which come in handy when navigating larger code projects.
There were some advanced suggestions too, such as editing class modules in Notepad or creating extra records through various means.
On Facebook, Robert and others recommended using dashboard shortcuts and expanding the quick access toolbar to include common commands.
One of my favorite suggestions from Reddit was the practice of thoroughly commenting VBA code. This helps immensely when revisiting code months or years later. Remember, you're writing these comments more for your future self than anyone else.
Other helpful administrative tips included opening the LACCDB lock file in a text editor to identify who currently has the database open, which can quickly resolve issues when you need to back up or update files still in use.
Access Helper noted that right-click filtering on fields can use operators other than equals, such as greater than or less than, and supports wildcards, making it a powerful yet often overlooked feature.
Linking Access data to Excel for downstream reporting is another favorite trick, allowing users to benefit from Excel's advanced visualization features while still keeping data live and updated from Access.
Thanks to everyone who participated in the conversation! I always enjoy these threads and plan to do more, so keep your eyes peeled for future topics.
Now let's move to some forum questions. Dan reported an error about missing image files when opening a database, likely caused by background images or header/footer graphics that Access expects to find but can't. The best troubleshooting path involves checking format properties on all forms, looking for hidden or misplaced objects, and doing a global search in code for references to things like JPEG or PNG files. Sometimes these errors can persist if Access is still attempting to call for a missing asset. If all else fails, importing objects one at a time into a blank database can help isolate the source of the issue.
One of my students, Lisa, shared screenshots of her form designs that I wanted to highlight. They're visually impressive and a great example of what's possible with solid database design.
Kim asked about entering special characters with accents or symbols. This is not unique to Access, but involves general Windows features. The character map tool (run 'charmap'), the newer Windows symbol picker (Windows key plus period), or using ASCII/Unicode shortcuts all make it possible to input a wide range of international or symbolic characters. There are also several online resources for quickly finding and copying these symbols.
Tom wanted to know if relationships can be imported when transferring tables. Unfortunately, relationships are stored at the database level and don't come along when tables are imported individually via VBA or through common import methods. You can manually import a whole group of related tables using Access' import wizard, and some options exist for exporting and importing relationships via XML, but this is not commonly done for routine updates. For most scenarios, it's usually best to manage data imports via append queries and rely on application-level logic instead of enforcing referential integrity through global relationships, especially for production environments.
Let's move to a question from YouTube: a beginner installed a combo box on their form and noticed the combo box wizard did not appear. This typically happens if control wizards are turned off, which is easy to do by accident. Re-enabling wizards in the form design tool should resolve the issue, but if not, the next step is to uninstall and reinstall the whole Office suite, as these features can sometimes become corrupted.
I also want to emphasize the annual reminders I give about handling tables or fields named by year. It's bad practice to create new tables or fields for each year in your database; instead, organize your data with a year or date field. This advice is particularly important in Access, even though some people get away with it in smaller Excel projects.
Several viewers shared positive feedback, and I want to thank those who give Super Thanks on YouTube—your support helps drive the creation of future content.
Carlos wrote in about a problem with scrolling in Access that didn't occur in other applications. My advice in these cases is always to try a full system reboot first, since temporary glitches happen, and double-check how your forms are laid out and whether scrolling is possible in the current view.
Another user described a ticketing system in which the ticket ID includes the year and a sequence, and each year the sequence needs to start at one. While it works to rely on a manual reset, I suggest automating the reset process in your code based on the system date, to avoid manual errors or omissions.
Steve shared his story of how my tutorials enabled him to build a job tracking database that was well-received by staff, though ultimately rejected by head office and IT. This is a common situation, with a lot of skepticism about Access in the broader IT community, despite its effectiveness for many real-world business needs. To Steve and others in the same boat, keep building things that genuinely help your users—sometimes that's more valuable than official approval.
Jose inquired about my course on using SQL Server as a backend for Access. I'm working on a comprehensive course for Access developers looking to move to SQL Server for improved security and performance. I avoid giving specific release dates, but it remains a top priority after my daily TechHelp routine and other projects.
Jeff wanted to know how to get started integrating AI with Access. My first TechHelp video on using OpenAI APIs with Access will help you get going. After that, there are more resources and templates for moving data between Access and AI.
A quick reminder: Access Day 2026 is on March 27th in Redmond, Washington. Mark your calendars. On my website's What's New page, you'll find updates, new videos, templates, and lots of resources, so check it often. While you're there, subscribe to the mailing list to stay up to date.
The holiday special is still active if you're reading this in December—this is the only annual sale, with discounted memberships, a free thirteenth month, and half off on all courses, templates, and seminars. Merchandise is available in the store, and you can find my Access Beginner book as well.
That wraps up today's TechHelp Quick Queries video. I hope you found these tips useful. 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
Favorite Access tricks and shortcuts submitted by users
Entering Excel-style equations in Access text boxes
Using input boxes to modify equations before entry
Creating global functions for standardizing control names
Using status functions and multi-command buttons
Custom plus, minus, and today buttons for date fields
Changing combo box row sources based on record status
Implementing automated database backups
Sourcing data with direct SQL statements vs saved queries
Using a blank database template with personal settings
Creating and using a recently viewed customer list
Copying table structure using SELECT INTO WHERE FALSE
Arranging continuous forms with the Arrange tab and layouts
Using keyboard shortcuts like Control Apostrophe and semicolon
Utilizing the Quick Access Toolbar for faster navigation
Resetting autonumbers by compacting the database
Creating customer codes with data shreds
Decompiling and recompiling Access databases to fix corruption
Navigating procedures with F2 and Control Shift F2
Switching between form and design modes with keyboard shortcuts
Reading the Access lock file to find who has the database open
Using advanced right-click filter options in datasheets
Linking Access queries to Excel for reporting and dashboards
Troubleshooting missing image file errors in forms
Locating hidden or leftover image controls in forms
Using Character Map and Windows symbol picker for special characters
Importing tables and the limitations of relationship imports
Using Application.ExportXML and ImportXML for schema and relationships
Ensuring Control Wizards are enabled for combo box creation
Best practices for structuring tables and fields for multiple years
Resetting ticket IDs based on year changes
Troubleshooting scrolling issues in Access with touchpads
Overview of SQL Server for Access backends course
Introductory guidance for using AI with Microsoft Access
Article
If you are an Access user or developer looking for ways to improve your workflow and avoid common pitfalls, you will find a treasure trove of time-saving techniques, troubleshooting tips, and practical advice gathered here from real-world experience, community forums, and user comments. In this tutorial, I will share a variety of useful Access tricks, keyboard shortcuts, solutions to common issues, and advanced strategies that have proven invaluable for many users. Whether you are just getting started or are looking to level up, there is something here for you.
One of the most highly recommended practices in Access VBA development is to use "Option Explicit" at the top of every module. This requires all variables to be declared, which prevents the accidental use of misspelled variable names and saves countless hours spent debugging. Additionally, the "Debug > Compile" command is your friend for catching errors early. Do not forget the importance of using Compact and Repair regularly to help maintain your database and recover disk space.
For data entry and form optimization, there are several tricks that can save time. You can enable a calendar to appear right over a date field for easy date selection, and you can even allow users to enter Excel-style equations directly into fields. For example, setting up logic so a user can type "=60*5" into a textbox and have it compute the result is surprisingly useful. This can be implemented in VBA using the Eval function, or for more control, with an input box that pops up when "=" is detected, letting the user confirm or edit the equation before finalizing the value.
Another great tip is to strip generic suffixes like "Combo" from control names in your code, making references like "StateCombo" easier to handle consistently across your forms. Templates also come in handy – maintaining a blank database with your preferred objects and settings can dramatically cut setup time for new projects.
You can enhance your forms with extra conveniences like small plus and minus buttons next to date fields to quickly add or subtract days, and a "T" button to jump to today's date. For combo boxes, you can use the "NewRecord" property to dynamically change the RowSource property. For example, when entering a new record, you might want the combo box to show only active employees, but when viewing an existing record, display all employees, past and present. This is especially handy for fields like sales rep or assigned employee.
Keeping recent records quickly accessible is another productivity booster. Many developers create a "Recently Viewed Customers" list that tracks the last 20 records you had open, making it easy to return to a previous customer without searching again.
A classic Access shortcut is pressing Control + Apostrophe (') to copy the value from the same field in the previous record. This is a big time saver for entering repetitive data. There are also shortcuts like Control + Semicolon to enter today's date instantly, or Control + Shift + Semicolon to insert the current time. Take advantage of the Quick Access Toolbar by pinning your favorite commands, such as switching to design view, form view, or visual basic code.
If you have deleted all the records from a table during testing and want your AutoNumber fields to reset to one, you can perform a Compact and Repair. However, it is worth remembering that AutoNumbers are not meant for meaningful values—they simply guarantee unique keys. For user-facing IDs, consider generating your own codes.
Should you run into a corrupted module or weird, unexplained behavior in your database, try running Access's decompile command. You can do this by launching Access from the command line with the "/decompile" argument and then recompiling your code with Debug > Compile in the VBA editor. This can fix issues that standard repair operations do not.
Among the less obvious but incredibly useful VBA editor shortcuts are Shift + F2 (or right-click and choose Go To Definition) to jump to the definition of a function or procedure, and Control + Shift + F2 to return to your previous location. Keyboard shortcuts like these can greatly speed up navigation in your code.
If you use continuous forms in Access and want to rapidly arrange controls and labels, take advantage of the Arrange tab's Tabular layout. This feature will move labels to headers and controls to the detail section, allowing for quick rearrangement. Some seasoned users still prefer manual layout, but the automatic tools are available if you prefer speed.
For troubleshooting mysterious image errors, such as "Cannot open file" for missing PNG or JPEGs, check every form for leftover image controls, hidden or minuscule ones, or images set as backgrounds in form sections like the header or footer. Examine properties for references to missing files and search your entire project (including VBA modules) for terms like "JPEG", "PNG", "BMP", or "LoadPicture". Sometimes OLE objects or attachment fields can be culprits, so remove any that are not essential. If you still cannot find the source, try importing objects into a new blank database one at a time or in batches to isolate the problem object.
For entering special symbols or accented characters, you have several options in Windows. Use the Character Map utility by pressing Windows Key + R, typing "charmap", and browsing for the symbol you need. For a more modern tool, press Windows Key + Period to bring up the emoji and symbol picker. Alternatively, you can use ALT codes if you know the key combination for your desired character. Access will store and display these as long as you can type or paste them into the field.
When importing tables from one database to another, especially if replacing or upgrading tables, note that Access does not automatically import relationships with tables if you use standard VBA commands like TransferDatabase or TableDefs. Relationships are stored at the database metadata level and are not considered part of the table object itself. If you use the manual External Data import wizard in Access, relationships can come along, but this process is not available through code automation. If you are frequently replacing tables, try updating the data inside your existing tables with append or update queries, and manage relationships directly in the database. You could also explore exporting and importing tables with relationships via XML, as relationships are included in XML schemas, but this solution is rarely used because it can be clunky and difficult to automate for large-scale migrations.
Another common beginner issue is that, when inserting a combo box on a form, the combo box wizard fails to appear; only the control itself is inserted. This usually happens because control wizards are turned off. In design view, check the toolbox ribbon for the "Use Control Wizards" toggle button next to the control buttons. Make sure it is on before inserting the combo box. If you continue having trouble, your Office installation may be corrupted, and a complete uninstall and reinstall of Microsoft Office (not just Access) may be needed to resolve it.
Regarding table design, do not fall into the trap of creating a new table or field for every year or similar recurring period. Instead, store all records in a single table and differentiate them by a Year or Date field. This makes aggregation, filtering, and reporting possible without resorting to fragile structures, and prevents headaches when reports need to span multiple years.
If you need to reset a sequential ticket number, such as a yearly job or order number, set this up programmatically. For instance, use VBA to automatically detect the current year, find the last number used for that year, and increment it only when the year changes. This design allows the process to be automatic and reliable, rather than something you have to remember to do manually.
For security, reliability, and scalability, many Access developers eventually migrate their backend data to SQL Server. While Access is powerful for rapid development and prototyping, when you need higher performance or centralized security, SQL Server offers a logical next step. There are many approaches to synchronizing data and relationships between Access and SQL Server, often using ODBC links or exported/imported schema. If you are interested in learning more, look for specialized courses on SQL Server for Access users.
If you are interested in integrating AI with Access, start by connecting Access to OpenAI's APIs. By setting up a routine that sends your Access data through HTTP requests to OpenAI and then processes the responses, you can automate tasks like generating summaries or extracting information. There are Access templates and videos available demonstrating how to pass data to AI models and insert results back in to your application.
When working in a networked environment, figuring out who currently has your database open can sometimes be a challenge. Access creates a lock file (with the extension .laccdb) whenever the database is opened. By opening this file in Notepad or another text editor, you can read the names of PCs currently accessing the database, which can help with troubleshooting when an update, backup, or file move is blocked by a lock.
Finally, some practical wisdom: always heavily comment your VBA code. While it may seem like you are writing notes for other people, really, you are writing them for yourself six months or two years down the line, when you have forgotten what a particular function or block of code does.
Whether you are just getting started or have years of Access development under your belt, the key themes are the same: take advantage of built-in tools and shortcuts, automate repetitive tasks with VBA, design your tables and forms cleverly, and keep learning from others in the community. Many tips and solutions will stand the test of time and can save you hours of frustration down the road. Remember to keep backups, document your work, and share any discoveries that might help others. If you keep these principles in mind, your Access experience will be much smoother, more productive, and even enjoyable.
|
