Quick Queries #41
By Richard Rost
6 months ago
Email Reminders, DAO vs ADO, Insert Rows, PK Gaps
In this Quick Queries video, I answer a variety of viewer questions, including how to email links that open an Access database to a specific record, differences between DAO and ADO recordsets, adding more criteria rows in queries, using the bang vs dot operator in VBA, and how to deal with autonumber jumps. I also discuss saving reports as JPEG images, hiding Access in deployed applications, handling database compacting, and address common issues with prerequisites. Plus, I touch on preventing users from closing your app with Alt+F4 and working with cascading combo boxes.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, hyperlink to specific record, send email with database link, drawing approval system, email reminders in Access, Access server database, automate email notifications VBA, Outlook email automation VBA, CDO email with VBA, recordset loop VBA, compile error user-defined type not defined, DAO vs ADO, Microsoft ActiveX Data Objects reference, SQL Server web portal Access, Insert Rows query design, bang vs dot operator, Me vs bang in VBA, proofread VBA code with ChatGPT, hide Access application indicators, custom Access icon, prevent Alt+F4 close VBA, Access compactor tool, cascading combo boxes country state city, API for address lookup, restore deleted autonumber, create sequential counters, type mismatch error VBA, calculate age in weeks months days, DateDiff function usage, recycle bin button for deleted records, restore deleted Access records, popup form over control, datasheet vs continuous forms, subform as datasheet, Access frontend backend split, compact and repair strategy, exporting reports as JPEG, PDF to image conversion tools, automating repetitive VBA code, prerequisites for VBA learning, TechHelp video follow-along, Access edition purchase options, Access in Office 365 Apps for Business, controlling form close with VBA, message box on form close, membership extended video content, VBA best practices, logical fallacies in consulting, Captain's log blog automation, API lookup for address validation
Transcript
It's Friday. That means it's time for another Quick Queries brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Quick Queries are quick questions that may or may not pertain to queries, but they're going to help you with your Microsoft Access database. So let's see what we've got today.
First up, posted this morning in my forums on my website. Robert would like to know if it's possible to put a hyperlink in an email that would open an Access database to a specific record. He's using Access as a drawing approval system and he wants people to send out emails so that they remember to go to the database to approve the drawings.
As I'll mention in a minute, this brings up the question of is it possible to do it, or is it worthwhile to do it, and do you really want to do it?
So Kevin - I love Kevin - he came up with a whole solution here, where you can create a hyperlink and use it to launch an application via a batch file. Alex has some other ideas here and here's my solution: Reminder - go approve your drawings.
To me, this falls under one of those things where it's like: could you send someone an email with a link that could open up the database to a specific record? Yeah, you could. Or just make it so that you send them an email anyhow, like a monthly reminder or weekly reminder, and then have them open the database.
If the database has user logons and it knows it's them, it can say hey, you've got this many pictures that need to be updated or approved, or whatever. Have the database open and multiple users. All you have to do is open the database using whatever shortcut they have on their desktop, and then the database could say hey, go approve your drawings.
Is it possible? Yes, but it does open up some security risks to have batch files running out of emails. Not a big fan of that.
You could set up SQL Server and give them a web portal. Take your Access data, put it up on SQL Server, and give them a simple web portal that they can just go to and approve the drawings there. That will require a lot more work, but it's certainly a possibility.
I would rather do that than send people emails with hyperlinks that can open up your Access database.
But in a nutshell, simply, I've got several videos to show how to do reminder pop-ups and have reminders pop up on a certain date. You could, with very little modification, change when your event runs and how the event runs.
You can have it queued so that when anyone opens the database after a particular date, it checks these things. Or if you have what I call a server Access database sitting in the corner, just running on a loop (that's what I've got), it can check for these reminders and then fire off those emails.
How do you fire off those emails? Well, there are lots of ways. I've got a regular "send email with Outlook" if you're still using classic Outlook. There's send email with CDO. You're going to need a recordset to loop through it. You'll definitely need some VBA.
I cover some of this stuff in my email seminar, like recordset loops. We build an email server, so there are lots and lots of ways to do it, Robert. It's just a matter of how much effort you want to put in doing it.
If a simple email notification once a week won't do it, you can automate all of this. It's just, again, how much work is necessary.
I've got a whole gigantic list of things that I want to automate both on my website and in my company Access database that I use, but there are only so many hours in the day.
So I'm very much a "squeaky wheel gets the grease" person, so when something starts to pain me, I work on it. There are two times I work on something:
A) If I think it will bring in a lot of sales, like a new seminar or a new video or something;
B) When I get sick of doing something, or if it's just irritating me. That's when I'll spend time on it, usually.
Or if the wife bugs me to do it, of course. That's not work related, but that's something different.
Like just this morning, a little while ago, I fixed a bug on my website. We have this notifications page where you can see all the notifications, like people reply to comments you leave or someone mentions you. I just released the mentioning thing to everybody on the website instead of just the admins and the moderators. Now more people are using it and there was an annoying bug, where if you clicked the notifications it didn't go away; you had to delete it manually. Squeaky wheel gets the grease.
Next up in my Access Developer Forum, which is a little more advanced forum on my website, Monica brings up an issue that she's having: "Compile error: user-defined type not defined." After some digging, she realized that when she declared a recordset source, the type recordset isn't in the IntelliSense drop-down menu.
We did some digging back and forth, and after throwing around the idea, it's possible because she mentioned it's an older database. She had the database she began in the 90s.
There are two different ways to work with recordsets in Access. There's DAO, which is the default - Data Access Objects. And then there's ActiveX Data Objects, which is a different type of technology.
Access started off with DAO, and then sometime around (I don't remember exactly when, I think around 2007 or 2010, don't quote me), in the middle there somewhere they switched it to ADO to try to be more compatible because ADO is what a lot of web services use or if you want to talk to SQL Server.
It's just basically two different ways of talking to databases. Then they realized, let's go back to DAO. So they switched the default back to this. Now, you could always use one or the other if you declared it right. So for example, in your code here, you could have
dim rs as DAO.Recordset
and rs2 as an ADO.
So if you don't have ADO installed, in order to use ADO in the newer versions, you'd have to go up to Tools - References, and see the "Microsoft Access 16.0 Object Library" gives you what you need to work with Access, basically gives you DAO. But you'd have to scroll down and find this guy: "Microsoft ActiveX Data Objects 6.0".
Once you add that as a reference to your database, now
rs2 as ADODB.Recordset
So yeah, it's a pain, basically. I've got separate lessons on this because you almost never need to use it. In fact, the only time we ever use it is in my SQL seminar, when connecting directly to an SQL Server table.
The only time you need to specify DAO.Recordset is if you have the reference for ADO in here. If you don't have that reference (which you don't need unless you specifically want it), then you can just call this Recordset, which is what I do in all my stuff because I only use DAO unless I'm working with SQL Server, and I'm very specific about it.
I know I ask Chat GPT for some code sometimes or different AI, and it'll give me DAO.Recordset. I can always tell when someone posts something in my forums or sends me an email with a question, and it says DAO.whatever. I know they either A) got that code from GPT, or B) they just found it online and some Google thing somewhere because they didn't get it from one of my classes, because you'll never see me declare it that way.
So that's what that was.
So yes, Monica, your database could have old references in it. Get rid of the old ones. Kevin pointed it out right here.
Like I said here, you can make sure that you have a reference at DAO higher in priority than any ADO, because you can move those things up and down in priority.
Got a correction from something that I mentioned in Quick Queries 40. I talked about the SQL lines in Query Design, and I said that the only way to really get more conditions like that was to go into SQL and add more that way. Jerry pointed out that there's an Insert Rows feature, which I completely forgot about.
That's one of those things where I learned Access way back in the early 90s. I don't think Access 2.0 had that, so in order to add more conditions you had to go into SQL.
What we're talking about is down here, these criteria rows - you get nine of them. I always design queries with the ribbon collapsed too, but on the Query Design ribbon, look at that, there's Insert Rows, you can add more. If you do Insert Rows, it adds more, and there's lots of them now.
I totally forgot about that. Even me, if I don't use features, I forget about them. If you close this and come back in and you don't use those rows, they'll collapse back to nine. So use them, but to be honest, you really shouldn't need that many. Very few queries that I've written have needed more than nine rows of OR conditions. Once in a while it happens, but when you get that complicated you might want to start thinking about creating your SQL dynamically so that you don't run into that.
Even I forget stuff. There's so much in here. If you don't use it, you lose it. So again, thanks Jerry for the tip. I haven't checked on YouTube yet, but I'm sure at least five of you have mentioned this. I'm not perfect. I really do appreciate you guys keeping me on my toes. I forget stuff all the time too.
Sometimes you guys remind me of the simplest things that are either new features that the Microsoft team has snuck in that I didn't even know were there because I'm so used to doing something my way, the way I learned in 1997, or like that - the whole Arrange tab. When that came out I was blown away. I didn't know half the stuff that was on that, or it's just something I forgot.
Next up is one from Lars, one of my Platinum members. He said he was going to have Chat GPT proofread his code, and it started making changes that he didn't want. For example, it changed DateStart to Me.DateStart. I have this whole video here on bang versus dot, and when you want to use it, when you don't want to use it, and when you don't need Me. You don't always need Me, you only really need Me if you're referencing properties of the form or report, like Me.txt, Me.whatever, that kind of stuff. You don't need it for all the fields.
He just wants to know if this happens to me with Chat GPT. Yeah, basically.
So I had it look at the transcript. I fed the transcript of this video to GPT and had it summarize it for me. Dot operator, bang operator - the reason why it works sometimes is because Access auto-promotes control names to properties at runtime for convenience.
I believe I mentioned in that video, but it's not always right. It doesn't always work, especially if the field name is a reserved word. I think I remember to put brackets around it. It's just a pain.
That's why I recommend using the bang method - Me!FirstName. You can watch this whole video for more details on it: bang versus dot.
But I said, am I correct? And yes, I am. Of course I'm correct. But sometimes GPT will tell you what you want to hear, so you have to be careful, even if it says I'm correct.It's the one I've verified with the third source, but basically, what you should do is, anything that you have that you want GPT to do, if you're working with it for coding, is specify stuff. Specify what you want it to be, how you want it to act, best practices, that kind of stuff.
For example, one thing that I don't like is that whenever I have GPT write code for me, it always puts each variable on its own line with a Dim statement. So I have to tell it in my instructions, don't do that. Put at least three or four on a line. It just makes the code shorter and easier to read.
I've got issues, too. I use GPT a lot for proofreading. Or I'll do my Captain's log every morning. A Captain's log is basically my blog on the website. It's got all kinds of stuff. Right now, I'm doing a series on logical fallacies, which you'll run into a lot if you're a consultant or a developer. That's not just for everyday life. I try to give an example of each.
I'll use GPT to do my proofreading for me. I'll just get my thoughts out. I'll be like, OK, today's Captain's log is about... and I'll just dictate. In between my voice recognition software and GPT, it'll clean it up for me. It'll fix punctuation, spelling, grammar. Sometimes I'll have it correct the word flow a little bit if what I'm saying doesn't sound right.
But it does certain things that I can't stand. It uses m dashes everywhere. I hate m dashes. I don't like the way they look. I've put it in my system instructions. I've told it a million times: don't use m dashes. Don't use semicolons. In proper writing, they're handy, but no one really writes like that, especially in informal writing in a blog or an email. Who uses semicolons? And I do, once in a while, actually. GPT is kind of making me use semicolons more than I normally would. But things like that I don't like. Curly quotes, those smart quotes you get from Microsoft Word, it throws those in all the time. Or the curly apostrophe. I just want straight, low-ASCII characters and it doesn't listen to me. I have to repeat myself a million times.
So, you have to be specific, you have to give it good instructions, and sometimes you have to repeat yourself. That's just the nature of AI. It's going to get better eventually, but it's still not there yet as far as following instructions.
Going back to something we talked about in Quick Queries 39, Ronald says he would have thought that there's an option to compact and repair on close. Wouldn't that compact every time the user closes the database or not? I'm not a big fan of compact on close. And if you're in a situation where you have a split database, which most multi-user databases are (well, all of them should be and most are), it's not going to compact your backend database, only the frontend. Usually there's no data in the frontend that you have to compact, and if so, it's usually just temporary data. Because if you update the frontends for everybody, that gets replaced.
So your best bet is to, I would suggest once a week or so (which is usually enough for most Access databases unless you have tons of traffic), when everybody's out of the database, just compact it manually. Or, you can run an event at like 3 a.m. Make sure everyone's out of the database, and I've got a compactor tool that I have here you can get, the Access Compactor template. It'll do it for you. You just run it on your main machine and program it to when you want it to go, and it'll just compact all the databases it finds in your backend folder.
I'll put a link to this down below.
Merchandise, merchandise!
Let's head over to the YouTubes now. Let's see, we got Combat Dummy 69 who wants to know if I'm sure. Am I sure of it? No, I'm not, absolutely.
Shadow Dragon is guilty of doing the same thing that I've been doing for years, copy, paste, copy, paste, copy, paste. You write something, even if it's only five or six lines, that's a lot of code if you're copying it in 15 or 20 different places in your database. That's why I like to make little helper functions.
Next up, a comment from Peter. Peter, I see stuff like this all the time, so I'm not picking on you, but a lot of people say stuff like this. You don't understand a particular concept that I'm covering in this video, and you say where I normally explain things very elaborately, I rush through these things. I don't understand at all what the difference is between the data between the parentheses.
If you watch any of my videos, at the beginning, what do I always say? There are some prerequisites. Well, if there are prerequisites, right, then at the beginning of this video, I'm 100 percent sure I say there are four prerequisites, and I always give links to them in the description. There's Intro to VBA, there's the Message Box, If Then, and then Create Function.
Now, if you've watched all these prerequisite videos, in the Create Your Own Function video, which is one of the prerequisites, I take a lot of time at about the four-minute mark or so talking about what these things are. These are parameters. You use them to send information, send data into a sub or a function.
So if you're going to watch my TechHelp videos and I say there's a prerequisite, pause the video. Go watch all of the prerequisites so you understand everything, because I'm not going to take the time in every video to explain everything over again. That would be like going to college and then enrolling in medical level 400 without taking levels 100 through 300. Then you're in a lab and the professor is like, OK do this, and you have no idea what he's talking about because you don't have all the prerequisites. It's the same thing here.
In my full courses, if you sign up for my full courses and take them in order (beginner lessons: beginner one, beginner two, beginner three, beginner four), you take those in order. I don't assume that you don't know anything that I didn't cover in the previous lessons. That's the benefit of taking my full course. It's all arranged in the right order.
But my TechHelp videos are to address specific questions and answer specific problems, and I have to sometimes assume that you have a certain level of understanding. Otherwise, every video would be three hours long if I had to explain everything in every single video. That's why there are prerequisites.
So everybody, next time I say there's a prerequisite, go watch the prerequisites. I'm going to make a video to point people to, that says, did you watch the prerequisite?
Again, Peter, I'm not picking on you. I get this from a lot of people all the time, asking why I rushed through this or saying they don't understand it. Well, it's covered in the prerequisite.
And shout out to Fuego. He took the time to actually explain it, so thank you very much for explaining that. That's the one thing I love about how helpful people usually are here on YouTube and on my forums, especially. I have a great group of moderators. They love answering questions, and I love the fact that you guys help each other out. That's amazing. So thank you, Fuego, for answering Peter's question. Well, it's not really a question; it's a statement. Let's see.
Thanks.
Easy Speaks says, really impressive, quite hard to remember, but thanks for teaching. This is on one of the more elaborate videos. It doesn't really matter which one. If you don't do this stuff all the time, you're not going to remember it. Just bookmark the video or link to my website or whatever, because you're not going to remember it if you don't use it every day. Even some of the stuff I show you guys in these videos, I have to take a few minutes ahead of time to figure it out. You might not remember it all the time, but at least remember that you saw it somewhere because you need to go figure out where.
Got three questions here from Unique Collection Shop.
First, I want to save my form, invoice, or report as a JPEG image on my laptop. I can save it as a PDF but not as a JPEG. I don't know of any natural way in Access to save directly as a JPEG file or any kind of image file. You'd need some kind of third-party converter to handle that.
Now, I haven't looked this up in a while. It's been a couple of years. I don't like not knowing something, so let's go do a little bit of research.
So I asked ChatGPT, and yes, I'm correct. Access cannot natively do that. What it recommends you can do is with a PDF to JPEG external tool. I did something like this similarly years and years ago with a tool. I don't remember which one, though. A client had a similar request, like you need a bitmap to convert into a PCX to be able to send it as a fax. It was weird.
ImageMagick, Ghostscript with a wrapper like IrfanView, or a commercial PDF converter like Adobe Acrobat. You can do the output as a PDF and then the next line of code can just convert it, or you can use Windows API to take a screenshot of the report preview. I wouldn't recommend that. You're not going to get very good accuracy out of that one, very good resolution. Or use a virtual printer that supports image output. PDFCreator, PrimoPDF. Again, I haven't used any of these.
So, is it possible? Yes. Can they do it natively in Access without any kind of external tools? Nope, not that I'm aware of, and not that GPT is aware of either.
Question two, I've developed the software application in Access. I want to hide all the indicators it was created in Access so my clients don't realize it's an Access-based application.
Why? Why would you want to do that? You should let everyone know you're using Access. We want to make Access popular. No, I'm just kidding. I get asked this a lot. I actually have a whole video on this, a two-part series, and I have some other things too, like creating screenshots or splash screens (that's definitely an S word; I couldn't think of it, it was sitting on the tip of my tongue there).
That will hide the little Access logo that pops up. You can set your own icon and the database title. So there's stuff you can do to try to hide Access, but someone who really knows Access will not be fooled. This will fool your average Office user. You can hide most of Access, but not all of it. There are ways that you can figure out that it's an Access application. For example, just look in the Task Manager; you're going to see ms-access.exe running. There's no way around that.
But can you fool the average person?Sure. Go check this video out. Last question you wanted to disable Alt+F4. Alt+F4, for those of you who don't know, closes any Windows application. Now, Alt+F4 itself is handled at the Windows level, so unless you want to use some Windows API hacks, which I usually don't recommend, to intercept the system call on a Windows level, then no, you can't do that straight with Access.
What you can do is intercept them trying to close your main menu form. I do talk about that in this video. You can pop up a confirmation or a message box that says, "Are you sure you want to close this form?" and you can stop them from doing that. In the extended cut, I even show how to prevent it from being closed with the shortcut key.
Right now, you can prevent users from closing the application without specific conditions being met. So there are things you can do; again, is there a bulletproof way to prevent it? No. But there are a lot of tricks you can play to try to keep them from closing it.
I even wrote an application for a customer years ago who didn't want people exiting the database because they used it as a time clock, and so I wrote a hidden program that would run as a service in the background that most users didn't know existed. If the user closed Access for any reason or knew how to go in and end the task, the service would restart it again. It would check to see if the database was running, and if not, it would load it back up again. So there are all kinds of tricks you can play.
Is this stuff built into Access? No, you have to learn how to do it yourself. But three very good questions, and thank you for asking them.
Went to buy Microsoft Access Home or Microsoft's Office Home 2024, but Access is not included. Do I know why? Yeah, because that's not included. The only way you can get Access now is either as part of a 365 subscription - the business one - or buying it on its own.
In fact, Access was never included in the home editions of Microsoft Office. The home or the personal editions always had Word, Excel, Outlook, PowerPoint, but you had to get the business or the professional editions to get Access. I talk about this in my edition of how to buy Access. No, there is no Access 2025. This is the 2025 edition of this video because I have to do it every year because Microsoft keeps changing things.
I wish they'd be more consistent with their marketing and their packaging, but they change it every couple of years and they confuse even professionals like me who work with this stuff every day. So your options now are to either get a Microsoft 365 subscription - I recommend the 365 Apps for Business. It's 8.25 a month and it includes Access and all the other normal stuff: Word, Excel, all that. Or if you want Access and you don't want to pay for a subscription, you have to get Access 2024 by itself.
Here it is right now. That's your only perpetual license option - it no longer comes with Office. So yeah, don't complain to me. That's what Microsoft decided to do.
Next up, Dima says, "Would it still be possible if I want to make the first combo box for street and the second one for country?" All right. This is about my multiple cascading video. In this video, I go country, state, county, city, street in that order because I'm going from bigger to smaller geographically. You pick the country and then inside the country you get multiple states and then inside the state you get multiple counties.
I've seen it online where you start typing in the address, like 231, 456, whatever, and it will start showing you street addresses because it's got a giant database, probably from the post office, of all of the addresses. Then you can pick; you can go that way. That's not really a cascading combo box, that's more of a lookup from a giant address database, and it's doing a lot behind the scenes.
Is that possible to build in Access? Sure. If you've got a post office database with all that data in it, or better yet, you're probably better off using an API, connecting in the background and querying their database unless you want to have all that, you know, gigabytes and gigabytes of data on your local machine. I know it's available.
That's not how I would go about it, but if that's how you want to do it, sure. Again, my job is just to show you the Legos; you put them together or whatever pieces you want. But searching through a database starting with street, I mean, you might have five thousand Main Streets in there, and so to pick street first isn't really going to help you narrow the rest of those down. It might show you some countries that don't have a Main Street.
So I think what you're really looking for is a post office reverse lookup, where you type in the address and it tells you who it is. Is that out there, is it possible? Yeah, sure. I haven't done a video on it. Maybe one of these days if enough of you are interested, post a comment down below and I'll look into it.
Always want to give a shout out to anybody who sends me a Super Thanks on YouTube. Thank you Paul. Super Thanks you can click on and you can send me a tip and I can go buy a coffee. Well, I can't even really buy a coffee with that anymore, but I certainly appreciate every dollar. Thank you very much.
George asks a very common question. His autonumber has jumped from six to ten. How did that happen? George, don't worry about it. Seven, eight, nine, they weren't happy. How's that one go? Why is six afraid of seven? Because seven ate nine.
Those records are not for you. Don't worry about those numbers. In fact, I got a whole video about it. These IDs here are not for you. Should it matter if you miss an ID? They could look like this. Doesn't matter. This is for Access internally to keep track of relationships and make sure your records are unique. That's it.
If you want counters, I got lots of other videos on how to make different sequential counters, record numbering, all that stuff. But autonumbers are not for you. Don't worry about it.
What probably happened, if you didn't delete seven, eight, nine, you might have started entering a record at seven and then hit cancel or escape. As soon as you start creating a record, that autonumber is spent. Create another record after that - that would have been eight. If you delete it or hit escape, that's eight gone. Can you get them back? Yes, but there are tricks you can play, but I'm not going to explain it to you right now. I got videos on that too: how to restore a deleted autonumber. It's on that page I gave you.
But don't worry about it.
This is about my type mismatch video, and for this one I have to put on my stand-up comic voice. Hey, my wife and I got married. We got a type mismatch error. We tried to ignore it twenty years ago. We're still doing fine.
I did get a good chuckle when I read this one.
Wolfwriter's got a comment about my age video where I teach how to calculate someone's age. She said, what if you need to output the age in days, weeks, and months as well? Building a database for a dog shelter. Well, thank you for taking care of the puppies, I appreciate that.
I want to be able to specify four weeks versus ten weeks, or five weeks versus four months for that. There are a lot of different ways you could do it. You could make a query for sure. You could convert it. If you know their birthdate, you can very easily show the number of weeks, right? Because if you subtract today's date from their birthdate, or the other way around - subtract their birthdate (because that's the smaller value) from today's date, that'll give you the number of days they've been alive. Then to get weeks, you just divide by seven.
To get months, that's a little more difficult. If you're only dealing with close enough months, you could just divide by thirty and that'll give you a close enough figure if you just care about whether a dog is eight months or nine months. If it doesn't have to be for legal purposes, you want it to the day, but if you just care about roughly.
Ha ha, see what I did there, "roughly."
I'm just following today. Actually, I can't take credit for the last one - that was the commenter.
If you know, you could divide by seven, divide by thirty for months, or you could use the DateDiff function. DateDiff will give you the exact number of months that they're different, that kind of thing. So either way.
Go to my video on date math that explains more about how date and time values work inside of Microsoft Access. Once you understand this, all the rest of the functions make much more sense.
This one is from Vitum and it's about my recycle bin video. I run into stuff like this occasionally, and I just want to explain why I do what I do.
Now, Vitum said he's never had a problem with what I put in the extended cuts. It's my work. But this is the only one - I'm annoyed at how to get data from the recycle bin. It's like Windows making it easy to put stuff in the recycle bin but not telling or hiding the restore part.
Unfortunately, I can't afford a membership right now. I've learned a lot here, but this one disappointed me. I understand why you're frustrated and disappointed.
For those of you who haven't watched this video: what I teach you how to do is instead of deleting records, we make a recycle bin button. This button you can drop on any form and it will put the data in a recycle bin table for you, so that you have it in case in the future you realize, "Oh crap, I deleted it, I need to get it."
So over the course of three videos, we build this guy here - Recycle - and it goes into this recycle bin table. The data is in here in case you need it. There it is. I'm not hiding it. You can come in here. It's not like with Windows; if Microsoft hid the recycle bin and didn't let you retrieve files out of it, you can't easily recreate an image file or an Excel file.
But this is all just text. You can come in here, copy this to Notepad, and then just copy and paste each of these fields back into your table and there you go - you got your data back.
The point of this was to show you how to save this information for later. Now, yes, in part three, in the extended cut, we build a restore option to make it easier so that you can restore the record with one click.
Yes, this is a lot of extra work, and it's a way for me to add value to my members who are paying for memberships, which allows me to keep the lights on, and it's the reason why I have time to make these free videos in the first place.
It's not like I left you with half a solution. Your data is in the recycle bin. It's safe there. You can go and get it out if you want to and restore that record manually. It's completely different from files in the Windows recycle bin. If Microsoft didn't give you a restore, there's no way that you can easily recreate that image.
I get you can't afford a membership. I know times are tough for a lot of people right now. I'm having a lot of people cancel because of the way the current economy is and it sucks. I know it affects me too. But the extended cuts are extra solutions that I provide for the members to go above and beyond what I cover in the free videos, and the free videos all have value in themselves.I wouldn't make a video - a TechHelp video at all - if it didn't at least answer the question sometimes in the free video.
I show a simple way of doing something and then I show a little more advanced or elaborate way of doing it in the extended cut, or an add-on to what I was talking about, and that's just the nature of my business. That's how I can afford to do this.
Believe me, I wish I made enough money on just running YouTube ads, but no, it's not nearly enough. So I have to sell memberships. I have to sell lessons, and if I didn't do that, then you'd have zero videos from me.
I'm sorry you're disappointed, but the recycle bin videos do give you a solution. Your data is safe - it's in the recycle bin. It just might take you a few more minutes to restore that record than if you had a restore button.
I certainly appreciate you sharing your thoughts with me, and keep watching.
Next up is a question about my pop-up over the control video. This is the one where I show you if you click on this field up here, this box will open up over that one. If you click on something down here, it opens up down here. It opens up over the control that you click on.
Angel is trying to do it with a datasheet view now. I'll be honest, I almost never use datasheet views. I don't like them. I like to design continuous forms if I want to use something like that. Datasheets have some benefits, but they do have their limitations and you just saw one.
Access has no easy way of telling which version of the last name field or the first name field or whatever you are on, because there's a whole bunch of them.
Now, in the extended cut for part two I do show you how to use a subform. A subform is basically a continuous form which looks like a datasheet. Here's the extended cut and you can see back here - let me load it up. All right, here's the extended cut database. Go to orders, and notice if I click up here it opens up there. If I click down here it opens up lower.
All right, so I guess we're going to find out together if this will work if this is a datasheet. Let's try it. Let's go in here and let's change this so its default view is datasheet. All right, save it, close it, open it and click. Okay, and click - and it opened up lower, there it goes.
So yes, the technique that I show in the extended cut will work with a datasheet, but I don't like datasheets. I never have, because you can't really control much about them. The only time I ever use a datasheet is if I'm trying to mimic Excel. Sometimes people want something like that, and in Excel you can freeze columns and do some things you can't do with a continuous form. I just like the way this looks better. That's just me.
So yes, will it work with a datasheet? Yes, the stuff I show in the extended cut will. There you go - another reason to join.
We're going to end tonight with Jim saying this is a good, clear, and to the point video. Surprised it hasn't gotten more likes in three years. Nice job. Yeah, me too. I don't get enough likes. I don't know why people don't like me. No, I get enough likes - I think I'm the same way though when I watch YouTube videos. There are a lot of channels I subscribe to that I don't like every video I watch.
I know their algorithm likes engagement and all that stuff. As long as you subscribe to the channel, I'm happy. If I don't get the likes, that's okay. But yeah, start smashing that like button, people.
Let's see, in three years - I published it three years ago in April of 2022. It's got 134 likes. Uh-huh. That's kind of low. It's got 12,593 views, which is right about what I would normally get for videos at that time, and the watch time is 535 hours, which is a little bit lower than most of my videos get. But I have gotten 14 subscribers from this specific video. So that's not paid subscribers, that's not paid members, that's subscribers. So yeah, it's a good little video. I can't complain.
Well, I didn't realize we're approaching 40 minutes on this video. I try to keep them about 15 or 20 minutes so I know I rambled a lot today, but there you go.
That's going to do it, folks. That is your quick queries video for today. What is today? Friday, the 23rd of May 2025. Hope you learned something.
Live long and prosper, my friends, and enjoy your weekend. Monday is a holiday here in the United States, so it's Memorial Day, so I'll either do a Memorial Day video or not, or there won't be a video Monday.
I don't do that because it's a holiday and I'm taking the day off and all that. I do that mostly because of you guys. I've noticed on holidays, especially our holidays, the views go way, way, way down. So it's like, why should I release a video if no one's going to watch it that day? So I'll save it for the next day, which is Tuesday.
That's it. I'll see you next time. See you next week, guys. Enjoy your weekend. Bye bye.
TOPICS:
Creating hyperlinks in emails to open an Access database
Security considerations of launching Access via email link
Alternatives to email hyperlinks for reminders in Access
Setting up automatic email reminders from Access
Using Outlook and CDO for sending emails from Access
Using VBA and recordsets to automate email notifications
Difference between DAO and ADO recordsets in Access
Resolving "user-defined type not defined" compile error
Setting references for Microsoft DAO and ADO libraries
Managing reference priority between DAO and ADO in Access
Adding extra criteria rows in Access Query Design with Insert Rows
Using the Query Design ribbon to modify SQL criteria rows
Understanding the bang operator vs dot operator in Access VBA
Proper use of Me, dot, and bang referencing in VBA form code
Disabling or customizing close actions in Access forms
Understanding limited abilities to disable Alt+F4 in Access
Compacting and repairing Access databases in split environments
Automating backend database compaction with a scheduled tool
Methods to save Access report or form as an image (JPEG)
Using third-party tools to convert PDF reports to JPEG images
Customizing an Access application to hide Access branding
Setting a custom splash screen and icon in Access
Limitations of hiding Access completely from end users
Understanding why Access is not included in Office Home editions
Overview of available options to buy Microsoft Access
Designing cascading combo boxes for address data in Access
Lookup strategies for street, city, and country entry in forms
Understanding skipped autonumbers and gaps in ID sequences
Explaining how autonumber fields behave and why gaps occur
Calculating differences in days, weeks, and months in Access
Using DateDiff for age calculation in Access
Storing deleted records in a recycle bin table in Access
Restoring data manually from a recycle bin table
Creating pop-up forms over controls in Access
Limitations of pop-up forms with datasheet views
Comparing datasheet and continuous form usability in Access
COMMERCIAL:
In today's video, we're talking about common Microsoft Access questions in another Quick Queries session. We discuss if you can send an email hyperlink to a specific record in your Access database, how to properly use DAO and ADO recordsets and fix "user-defined type not defined" errors, and how to add more criteria rows in query design. We'll also cover hiding Access indicators in your app, saving reports as images, and methods to prevent users from closing your database with Alt+F4. You will learn why auto numbers might skip values, get advice on pop-up controls in datasheet views, and hear answers about software editions, cascading combo boxes, and restoring data from a custom recycle bin. Plus, we address common viewer comments, point out the importance of prerequisites, and share tips for getting the most out of AI tools like ChatGPT with Access. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What was the original question Robert asked about using hyperlinks in emails and Access databases?
A. How to automate batch file creation through Access
B. How to embed Access queries in emails
C. If an email hyperlink can open an Access database to a specific record
D. How to send Access forms as images in emails
Q2. What is the main security concern with using hyperlinks in emails to open Access databases with batch files?
A. The link might not work on some email clients
B. It opens up possibilities for security risks by running files from emails
C. It causes Access to run slower
D. It requires having SQL Server installed
Q3. What is one alternative suggested for managing approvals instead of using email hyperlinks to open the database?
A. Using a macro recorder
B. Sending weekly reminders for users to open the database themselves
C. Printing and mailing lists of drawings
D. Setting up direct text message notifications
Q4. Why might setting up a web portal with SQL Server be a better solution than sending Access hyperlinks via email?
A. It is less expensive to develop
B. It allows batch files to run more quickly
C. It significantly reduces security risks and provides broader access
D. It does not require user logins
Q5. What method(s) are mentioned for sending emails automatically from Access?
A. Only via Outlook integration
B. Only using VBA code with ADO
C. Using Outlook integration, CDO, and VBA with recordset loops
D. Manual copy-pasting data into emails
Q6. What are the two main times Richard says he spends effort improving or automating part of his system?
A. When his team tells him to and when he gets a new customer
B. If it will bring in sales and if something becomes irritating to him
C. Whenever ChatGPT gives him new ideas
D. At the end of every fiscal quarter
Q7. What error did Monica encounter in an old Access database?
A. Invalid database format
B. Compile error: user-defined type not defined
C. No permission to run VBA scripts
D. Broken form references
Q8. What are DAO and ADO, and when would you need to reference each?
A. DAO for graphics, ADO for security; always need both
B. DAO and ADO are both object libraries for interacting with databases; ADO is needed for certain external connections like SQL Server
C. DAO for text import, ADO for exporting to Excel
D. DAO for forms, ADO for reports
Q9. What will happen if you have both DAO and ADO referenced in an Access project and declare a Recordset without specifying the library?
A. Access will prompt the user to pick one
B. It will always use ADO by default
C. There could be ambiguity unless you specify DAO.Recordset or ADODB.Recordset
D. Access will fail to compile any code referencing Recordset
Q10. What tool or menu feature in Access allows you to add more criteria rows in Query Design beyond the default nine?
A. Advanced filter tool
B. The Insert Rows option on the Query Design ribbon
C. SQL View only
D. Create Macros button
Q11. In Access VBA, what is the recommended way to reference controls on a form?
A. Always use dot-notation (Me.DateStart), regardless of context
B. Prefer the bang operator (Me!FirstName) for controls to avoid errors with reserved words
C. Use recordset!controlName only
D. Only refer to controls by their index
Q12. Why is it important to provide clear and specific instructions to ChatGPT (or similar AI) when having it generate or revise code?
A. AI systems understand context without instructions
B. AI will always use Access best practices by default
C. Without instructions, you might get incorrect formatting, naming, or conventions
D. It is not important; AI always produces useful output
Q13. Why is relying on compact-on-close not recommended for multi-user split Access databases?
A. It can corrupt the backend data
B. It only compacts the frontend, not the backend where most data resides
C. It leads to increased memory usage
D. It will prevent users from reopening the database
Q14. What is the suggested approach for compacting the backend database in a multi-user Access environment?
A. Schedule a manual or automated compact when all users are logged out
B. Use backup software to compact
C. Do not compact; just delete and recreate the file
D. Compact whenever any user closes their frontend
Q15. What is a helper function, as discussed in the video?
A. A macro to delete records
B. A specialized subroutine or function that performs a commonly used operation to avoid duplicate code
C. An add-in for sending emails
D. A manual database backup generator
Q16. What are parameters in the context of Access VBA functions and subs?
A. Only variable names
B. Comments used to document functions
C. Data values sent into subs or functions for processing
D. The title of the function
Q17. If a student does not understand material covered in a TechHelp video, what does Richard recommend?
A. Skip to the next video
B. Post a question instead of researching
C. Watch the prerequisite videos linked in the description
D. Only watch the summary at the end
Q18. Can Access natively save a form, invoice, or report as a JPEG image?
A. Yes, in all versions
B. Only in Access 2007
C. No, it requires third-party tools or conversion from PDF
D. Only with SQL Server integration
Q19. What is Richard's advice about hiding the fact that your application was created with Access?
A. There are official tools to rebrand Access applications
B. You can hide indicators and set your own icons and titles to fool most users, but experts can always tell
C. It is impossible to hide any branding from users
D. It requires rewriting your application in SQL Server
Q20. Is it possible to fully disable Alt+F4 in Access using only built-in features?
A. Yes, it is a built-in security option
B. Yes, but only for forms, not the whole application
C. No, Alt+F4 is handled at the Windows level; you need advanced API or tricks
D. Yes, by changing the Access options menu
Q21. Why is Microsoft Access not included in the Home or Personal editions of Office?
A. It is only available for 64-bit computers
B. It is always a separate download for home users
C. Access is only included in Business or Professional editions, or via certain Microsoft 365 subscriptions
D. It is not supported by Microsoft anymore
Q22. In a cascading combo box address selector, why does Richard recommend starting with the largest area (country) and moving to the smallest (street)?
A. He prefers alphabetical order
B. Streets are unique and thus best to start with
C. Starting large to small helps efficiently narrow down the dataset and reduces ambiguity
D. There is no recommended order
Q23. What is Richard's position regarding users being concerned about missing autonumbers in an Access table?
A. Missing numbers indicate data corruption
B. Autonumbers are strictly for the user's tracking and must be sequential
C. Autonumbers are used internally by Access to ensure record uniqueness; missing numbers are not a problem
D. Skip autonumbers altogether
Q24. How can you output a pet's age in different time units (days, weeks, months) in Access?
A. Only by manual calculation
B. By using DateDiff and dividing date differences by specific constants
C. There is no way to do this without SQL Server
D. Access can only calculate years automatically
Q25. What is Richard's approach to offering extended cut videos and extra features to paid members?
A. All free videos are incomplete without membership
B. Free videos always answer the primary question, extended cuts add more advanced features or options
C. Paid members get access to primary solutions only
D. Only paid members can ask questions
Q26. What is the main limitation with datasheet views in Access compared to continuous forms?
A. Datasheet views cannot use macros
B. Datasheet views offer less control over formatting and appearance
C. Continuous forms cannot show tabular data
D. Datasheet views are hidden from users
Q27. What is Richard's main reason for sometimes not releasing a video on a holiday?
A. He is always on vacation
B. Videos cannot be published on holidays
C. Viewer engagement and views are significantly lower on holidays
D. Holidays are reserved for live streams only
Answers:
1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-B; 8-B; 9-C; 10-B; 11-B; 12-C; 13-B; 14-A; 15-B; 16-C; 17-C; 18-C; 19-B; 20-C; 21-C; 22-C; 23-C; 24-B; 25-B; 26-B; 27-C
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'll be answering a wide variety of your Access questions, ranging from technical details in VBA to some practical advice on working with Microsoft Access databases. These questions come directly from my forums, the YouTube comments section, and my Access developer community, and I always try to address them directly based on my own experience.
Our first question today is about adding hyperlinks in emails to open an Access database at a specific record. Robert wants to use Access as a drawing approval system, with email reminders sent to approvers. Technically, yes, you can create such email links which use batch files to launch specific records, and my forum members, like Kevin and Alex, have tossed around some advanced solutions for this. However, I would caution you against running batch files from email links due to security risks. My recommendation is to keep it straightforward: send out reminders (weekly or monthly) via email, then let users open their database shortcut as usual. If you have users log in, the database can notify them of the records needing action. For more robust or web-based access, you could set up SQL Server and a web portal, but that requires a lot more work.
Automating reminders in Access is possible and fairly easy. You can set up pop-up reminders based on certain dates or use a "server" Access database running in the background to check for pending approvals and fire off email notifications. There are several methods for sending emails from Access—I cover techniques for using Outlook, CDO, and looping through recordsets with VBA in my seminars. Ultimately, it comes down to how much effort you want to put in and your specific needs.
Moving on to a VBA question from Monica about a "Compile error: user-defined type not defined" message when declaring a recordset. Access has two main ways to work with recordsets: DAO (Data Access Objects), which is the default and preferred method, and ADO (ActiveX Data Objects), which was introduced for greater compatibility with web services and SQL Server connections. If you want to use ADO in your database, you'll need to set a reference to "Microsoft ActiveX Data Objects 6.0" in Tools - References. Most of the time you can just use DAO or simply "Recordset" for your type declaration. The only time you need to explicitly name DAO or ADO is if there's ambiguity or you've included both references. I stick strictly to DAO unless connecting directly to SQL Server. If you ever see DAO.Recordset in posted code, it probably came from a web search or AI rather than my lessons.
Jerry pointed out a correction from a previous Quick Queries video about adding more criteria rows in Query Design. I had forgotten about the Insert Rows feature on the Query Design ribbon, which allows for more than the default nine criteria rows. Even experienced users like me can forget about these features if we don't use them regularly.
Addressing another question, Lars mentioned how ChatGPT changes his VBA code—insisting on using Me. everywhere, for example. I have a whole video on when to use dot and bang operators (Me. vs Me!), and when you actually do or do not need Me in your code. ChatGPT and other AI will often blindly follow certain conventions, so if you use these tools, always specify your preferences. I also use AI for proofreading my own writing, but sometimes it ignores my dislike for m dashes, semicolons, and curly quotes. You have to be very specific and sometimes repeat yourself to get your desired results.
Ronald had a follow-up on compacting and repairing a database automatically. I do not recommend using compact-on-close, especially with split databases, because it only compacts the frontend and not the backend tables where your main data live. Your best bet is to manually compact the backend once a week or so, or schedule it for a time when no one is using the database. I provide a Compactor template for this purpose.
On to a common Access topic, George asked why his autonumber field skipped from 6 to 10. This is normal—autonumbers are for internal use by Access to maintain unique records and gaps can occur when records are started but then cancelled or deleted. These numbers are not meant to be consecutive for user purposes. If you need consecutive row numbers, there are other ways to implement them.
Unique Collection Shop had a few questions: First, about saving forms or reports as JPEG images from Access. Natively, Access can export to PDF, but not directly to JPEG or image formats without using third-party tools like ImageMagick, Ghostscript, or virtual printers that support image output. There is no direct export feature in Access for image formats.
Second, hiding indicators that an application was made in Access. You can remove or disguise most Access branding by changing icons and titles, or by using custom splash screens. However, a savvy user will always be able to determine it is an Access app (such as by checking Task Manager for ms-access.exe).
Third, disabling Alt+F4 to prevent users from closing the application. Alt+F4 is a Windows-level command, so Access cannot directly override it without complex API interventions, which I do not recommend. Instead, you can intercept the closing of forms in your database and prompt users with confirmation messages.
There was also a question about why Access is no longer included with Microsoft Office Home editions. Unfortunately, Access has not been included in the Home or Personal editions for some time. To get Access now, you need to purchase it as part of Microsoft 365 Apps for Business or as a standalone application.
Dima asked about cascading combos, but in reverse order—starting with street then country. My cascading combo solutions are built top-down, like country to state to city to street, not reverse. To do street-first lookup would require a massive dataset, like a postal service database, or using APIs to query addresses, which is possible but outside of Access's built-in capabilities.
Another listener, Wolfwriter, asked about displaying age in different time units when tracking animal shelter information. If you have a birth date, you can subtract it from today's date to get the age in days, divide by seven for weeks, or approximately by thirty for months. For precise months, use the DateDiff function.
On YouTube, I occasionally receive comments or feedback about features I cover only in extended cut videos for members. For example, in my recycle bin videos, the free version explains how to save deleted records for later retrieval, but the one-click restore is reserved for the extended cut. All the free content provides real value, and extended features are part of how I keep the business running.
Regarding pop-ups over controls, Angel wanted to know if my over-the-control pop-up technique works in datasheet view. While I personally avoid datasheet views for design reasons, the method I show in the extended cut works with datasheets as well, although with certain limitations compared to continuous forms.
I frequently get questions about video pacing or prerequisites. Some viewers feel like I gloss over concepts, but I always include prerequisites for my TechHelp videos. Watching the prior lessons is essential to understanding the topic at hand, much like taking college courses in sequence. My full courses are designed to build knowledge step by step without gaps, whereas TechHelp videos focus on specific answers.
Finally, I sincerely appreciate everyone who likes, comments, or supports me with Super Thanks or through memberships. Your questions, feedback, and encouragement keep these sessions interesting and lively. Even I forget features sometimes, so I'm always learning alongside you.
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
Creating hyperlinks in emails to open an Access database
Security considerations of launching Access via email link
Alternatives to email hyperlinks for reminders in Access
Setting up automatic email reminders from Access
Using Outlook and CDO for sending emails from Access
Using VBA and recordsets to automate email notifications
Difference between DAO and ADO recordsets in Access
Resolving "user-defined type not defined" compile error
Setting references for Microsoft DAO and ADO libraries
Managing reference priority between DAO and ADO in Access
Adding extra criteria rows in Access Query Design with Insert Rows
Using the Query Design ribbon to modify SQL criteria rows
Understanding the bang operator vs dot operator in Access VBA
Proper use of Me, dot, and bang referencing in VBA form code
Disabling or customizing close actions in Access forms
Understanding limited abilities to disable Alt+F4 in Access
Compacting and repairing Access databases in split environments
Automating backend database compaction with a scheduled tool
Methods to save Access report or form as an image (JPEG)
Using third-party tools to convert PDF reports to JPEG images
Customizing an Access application to hide Access branding
Setting a custom splash screen and icon in Access
Limitations of hiding Access completely from end users
Understanding why Access is not included in Office Home editions
Overview of available options to buy Microsoft Access
Designing cascading combo boxes for address data in Access
Lookup strategies for street, city, and country entry in forms
Understanding skipped autonumbers and gaps in ID sequences
Explaining how autonumber fields behave and why gaps occur
Calculating differences in days, weeks, and months in Access
Using DateDiff for age calculation in Access
Storing deleted records in a recycle bin table in Access
Restoring data manually from a recycle bin table
Creating pop-up forms over controls in Access
Limitations of pop-up forms with datasheet views
Comparing datasheet and continuous form usability in Access
|
