Quick Queries #86
By Richard Rost
12 days ago
Fixing Missing References When Built In Functions Fail In today's Quick Queries video, we discuss several common Microsoft Access issues and questions, including why Access might stop recognizing built-in functions like Date or Format, how to fix ACCDE files that won't compile, resolving ambiguous outer join errors, and the importance of checking references when encountering unexpected errors. We also review Access updates, compare storing sensitive data in Access versus SQL Server, discuss database design for inventory systems, cover version control options, and address subform filtering strategies. Along the way, I share viewer questions and comments, and talk about recent Access events such as Access Day and Access DevCon. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, AccessLearningZone.com, date function not working, format function not working, broken VBA reference, ACCDE file won't build, ambiguous outer join error, storing sensitive data Access vs SQL Server, version control GitHub, barcode scanner integration, subform filtering master child links, inventory database design, Access Day review
Intro In today's Quick Queries video, we discuss several common Microsoft Access issues and questions, including why Access might stop recognizing built-in functions like Date or Format, how to fix ACCDE files that won't compile, resolving ambiguous outer join errors, and the importance of checking references when encountering unexpected errors. We also review Access updates, compare storing sensitive data in Access versus SQL Server, discuss database design for inventory systems, cover version control options, and address subform filtering strategies. Along the way, I share viewer questions and comments, and talk about recent Access events such as Access Day and Access DevCon.Transcript Ever have Microsoft Access suddenly act like it doesn't recognize simple functions like date or format? Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today we're going to talk about why Access suddenly forgets built-in functions like date and format. We'll look at what causes it and how to fix it without tearing your hair out. We're also going to cover things like storing sensitive data in Access versus SQL Server, why your ACCDE file won't build, how to fix ambiguous outer join errors, designing inventory systems, and whether you should filter subforms or rewrite them in code. Plus, we've got answers to your comments on my forums, YouTube, and lots more. So let's jump in.
All right, quick news item to kick things off today before we get to the Q&A. Microsoft just dropped the latest Access update in their blog, and honestly, it's mostly what you'd expect. Lots of bug fixes, not a lot of flashy new features, but that's actually a good thing. A couple of these are worth calling out. Let's see.
The Edge Browser Control Fix is a big one. If you ever tried to navigate with VBA inside a tab control and nothing seemed to happen until you clicked around, that's now fixed. That was annoying.
There's also a sneaky developer one in here. Access was actually executing queries just from reading field properties like precision and scale. That's a big deal if you're looping through fields in code. That could slow things down or cause side effects, so that's been fixed too.
These are mostly quality of life updates. Unicode characters display correctly, titles not getting chopped off in print preview, the filter button working properly when no records show, little stuff like that.
But here's the bigger takeaway. This is exactly what I was talking about in my review of Access Day yesterday. Access is not dead. It's being actively maintained. The team is mostly focused on stability, security, compliance, and compatibility issues. They don't necessarily have a lot of time for adding flashy new features. They're a small team, but they're doing a great job and they're working on it. So to all of you naysayers who say that Access is dead, no, it's not. It's very much alive and well.
So if you're having weird issues, definitely check out the update notes. I'll put a link to this page down below. You might not be crazy. The problem that you're having might have already been fixed.
So there you go.
All right, let's get to the questions.
All right, looking at the forums on my website, Alex, one of my golden members, kicked off a discussion about storing sensitive data like credit cards or medical info in Access versus SQL Server. Thomas pushed back by saying that both can be hacked anyway. The thread turned into a good debate about whether SQL Server is really more secure, but it ended up coming down to how the data is stored and controlled. It was a pretty good thread back and forth. I'll put a link to this one down below if you want to read the whole thing.
This is a great discussion, and Alex and Thomas both made valid points here. Thomas is absolutely right that nothing is hack-proof. If someone can see the data, they can copy it, take a picture, write it down, whatever. But that's not an Access problem, that's just a reality problem.
Where Alex is right, and this is the key takeaway, is how data is stored and controlled. Access is file-based. That means your entire database is just sitting there on the network as a file. If someone gets access to that file, which they need access to in order to be able to use it, they can copy it, delete it, take it home, work on cracking it offline. At that point, you've lost all control.
SQL Server is different. The data stays on the server. Users don't get a copy of the database. They just get access to whatever you give them access to. You can control permissions, restrict tables, even limit specific fields. I'll be covering all this in my SQL Server course, and that's a whole different level of security.
So it's not about perfect security, because perfect security doesn't exist. It's about reducing risk. For small stuff, Access is just fine. For anything sensitive or regulated, you really should be on SQL Server or something similar, a secured database server platform. Great discussion, guys.
Next up, Andrew, one of my platinum members, was trying to build a printed inventory sheet for each cube or office showing all of the equipment assigned there, like the computer, the hard drive, the monitors, and so on. The thread really became about the best way to structure that database so you're not designing the printout before you design the data.
Again, lots of different people chimed in on this one, and I'll put a link down below if you want to read the whole thing. This is a classic design trap, and Andrew, Alex, Thomas, Juan, and Donald all nudged it in the right direction.
The big takeaway is this: don't design the table around how you want the paper to look. Design the data first. What you really want is a location record, and then asset records for each piece of equipment - fields like asset type, manufacturer, model, serial number, and whatever location the user assigned it to.
Then the interface becomes easy. You use a main form for the office or cube or whatever, and a subform for all the related assets. When it's time to print, that's a report job, not a table design job.
Back in my consulting days, I had so many companies that I worked for that had paper forms they designed years ago and they wanted to keep working with because that's what everybody knew. They didn't want to change the design of the paper form. I get it. Sometimes it was a required paper form, like I did one database for a medical billing company, and they had forms that they had to use that were assigned by the insurance company, so they couldn't deviate from that printed form. The database had to spit that out exactly.
But that paper form shouldn't dictate how you build the database. Later on, you might have to play some games to get the data to print in that way, but that's a printing problem, not a database design problem.
Thomas was also right to think bigger than just PCs, because once you build this right, you can track anything. Juan and Donald were right about barcode scanners too. Most of them are basically just keyboard input and they're a lot easier to use with Access than most people think.
As far as inventory goes, I have lots of different videos on scanning barcodes and keeping track of inventory. I cover inventory in Developer 23, more in Developer 27. I've got an asset manager seminar that keeps track of just this exact thing, like what equipment is in one location. Check it out. I'll put links to all this down below.
Hey, if you're enjoying this, hit that like button, hit that subscribe - helps me out. Thank you. Appreciate it.
Next up, DeSilly noted that changing a field caption in a table doesn't update the forms that were already built, even though newer versions of Access seem to carry captions through queries a bit differently, and he was wondering why that behavior feels inconsistent.
Yeah, this is one of those classic Access "depends on when you built it" situations. The key thing to understand is that forms and reports are not dynamically tied back to your table captions. When you drop a field onto a form, Access copies the caption into the label at that moment. After that, it's just text sitting on the form. If you go back later and change the caption in the table, your existing forms don't just magically update. You have to fix those labels manually.
Queries are a little looser. Newer versions of Access do a better job at carrying captions through when you add fields fresh, but as soon as you alias something or build on top of it, that connection can break too.
As always, be careful with that auto-complete feature that we talked about in a previous video. If you change the name of a table field, it propagates through to your form fields, but the names of those fields don't change, just the control sources change, so it can be very, very confusing. I turn that off most of the time in production databases myself. I leave it active in the videos that I show you because I don't want anybody to get confused, but I usually turn that off.
So the real lesson here, and DeSilly said it perfectly, is to get your field names and captions right early, and don't rely on Access to keep everything in sync later, because it won't. Honestly, in a real production database, I almost never bother with descriptions or captions or things like that at the table level, because my end users don't get access to those tables, and usually (not always, but usually) there is only one place for my users to interact with that data. So I put the captions there.
Like customers, for example. If you want to change customer information, you have to do it on the customer form. I lock down the customer list form. You can't change customer data on the order form or the contact form or any of that stuff. That one form where you can modify the customer - that's where I put the captions and the descriptions and the labels and all that stuff that describes that field.
The only thing I ever rely on the description field at the table level for is explaining a field that might be a little hard to understand, like "IsActive" for a customer. I talk about this in my Access Beginner Level 1 class. What does the IsActive field mean? Does it mean that the customer likes to exercise regularly? No, it means he's an active customer, he's still buying stuff, he's on our mailing list, that kind of stuff. So I'd put that in the description field.
But yes, sometimes that will propagate through to your fields too, so be careful what you put in there. Don't ever put something in the database that you don't want your end users to see because you never know where it might pop up.
Side story. I remember way back when the web was still a baby and I was learning how to build web pages. I didn't realize that comments were visible in your HTML source. We're going back to the 90s here. Because in programming, if you're programming in BASIC or in C or whatever, the end user can't see the comments that the programmer types in the program when they're running the program; it doesn't show up anywhere. So I assumed the comments were hidden. So I put a bunch of comments in my website in the HTML tags, thinking they were private, and they weren't. So yeah, be careful out there, my friends.
Oh, and as a side note to my side note, for those of you who do program in HTML, you know that comments like this - these are the standard HTML comments. This will show in the source code if someone looks at it (click View Source). If you use ASP, like I do, then you can put your comments inside of this and these are handled on the server and are compiled before the user gets the data. So these kinds of comments do not show in the source code. Just a little tidbit for you guys.
I do have an old ASP course on my website, and I'm planning on updating it because I still love ASP. I know there is lots of newer stuff out there, but ASP, it's old, but it's bulletproof and it still works great. My website still runs on it. So yeah, more news on that later.
Let's get back to the questions.
Next up, Lee, one of my students, is getting that ambiguous outer joins error. This guy, I'm sure you've seen it before. In fact, I've got a whole video coming out on this one pretty soon. I've had this on the list for a TechHelp video for a while. Let me show you what he's got going on here. He's trying to use one caliber table with both his firearm and magazine tables, and Access throws up the lovely ambiguous outer joins error. The real question is whether he needed duplicate caliber tables in the database or just a different way to build the query.
The way he explained it was: a magazine can have a caliber, and the firearm itself can have a caliber as well. This is a great beginner/intermediate query design question. Donald nailed the fix right away. No, you don't have to have two caliber tables in your database. One table is fine. But if you want to use the same lookup table in a query for two different relationships, then you actually bring the query in twice. Access will make a separate copy of the caliber table. It'll be like CaliberT_1, and that lets each join resolve independently. You'd have a join from here to here, and then a separate join from here to here, from here to the new table. So each of these only links once. You don't link this twice back to two other tables. If you do this, Access gets confused because you're asking one copy of the table to do two different jobs at the same time, and that's what an ambiguous outer join means.
Brian came up with a smart diagnostic question: what happens if you remove one join? That's often how you isolate where the problem is. Also, if the magazine already points to the firearm, and the firearm has a caliber, then storing caliber again in the magazine gives you extra flexibility, but it also gives you redundancy. That's fine if you want magazines to exist independently. But the way I understand it, if the firearm has a particular caliber, then the magazine has to have that caliber too, unless I'm wrong.
I have a few guns myself, but I'm not a gun expert. I live in Florida. They basically give you a gun when you move here. No, seriously. I used to spend a lot of time at the range when I first moved here. I haven't been there in a while, but my understanding is you can only use a magazine that's got the same caliber as the firearm.
Now, see, I had to ask ChatGPT this, and it turns out that you can. I understand now why Lee set it up this way. Some firearms can accept multiple calibers with conversion kits: same gun, different barrel, different caliber, and even the same magazine or slightly different. So, I love learning new things. This is why I love my job. Back when I was a consultant, I was constantly learning things about different businesses and how they run. That's why being a database consultant is never dull. It's a fun job because you go into businesses and you learn all kinds of different cool things. I've learned stuff about chemistry. I've learned stuff about accounting and every different industry. It's really fun.
Even GPT says from a design perspective, what Lee did was actually reasonable - you're trading a little redundancy for flexibility, and that makes sense. Again, I've got a different video coming out on this one, so look for it. It'll be a TechHelp video soon. I never give dates, so soon.
All right, next up, David, one of my gold members, thought Windows 11 or the latest version of Access broke ACCDE creation because his database wouldn't compile into an ACCDE. Then Brad jumped in and said that he was seeing the same error, and it turned out the real issue was something very simple.
All right, so here's the problem in a nutshell. If Access says it can't create an ACCDE file, go compile your database. Compile the code: open the VBA editor, Debug - Compile. Debug - Compile once in a while. It's not just a catchy phrase. I say that a lot and I drill it in your head because I want you to do it all the time. When you make your changes to your code, Debug - Compile. Nine times out of ten, if it can't create an ACCDE file, that's the problem.
David thought something was broken with Windows 11 or the new version of Access and Brad saw the same error, so it looked like a bigger issue, but Tony actually nailed it. He got to it before I did, so good job, Tony. If there's a compile error sitting in the VBA, it's not going to compile. Access will not build your ACCDE file if the code doesn't compile cleanly, and a tiny error in there somewhere in any module will stop the whole process.
You'd think that's one thing the Access team might put on the list. Sammy added to the list. If an error comes up, it should say, hey, try compiling your database first. Or maybe if you try doing an ACCDE, it should run through a compile first. Or just Debug - Compile once in a while.
So moral of the story: before you go blaming Windows or Access or Microsoft or me or aliens or Klingons or whatever, open the VBA editor, Debug - Compile once in a while. If it finds anything, fix it, and then Debug - Compile again and then try to make your ACCDE file.
Just to be clear, he said something about it overwriting the ACCDEB version. It shouldn't do that either. It should prompt you for a new file name. So if you see something weird like that, double-check what you clicked on and make sure you have good backups. Backups are very important.
Oh, let me - where's that slide? Hold on, I'll go get it. Gotta pull this guy out. It's an oldie but a goodie: Back up your data every day, every night, play some Sundays and all that stuff.
All right, so bottom line: nothing's broken. Your code just has to be clean before Access will lock it down into an ACCDE. They don't want you sending bad databases to other people. You can't send out stuff with bugs. So if it doesn't compile, you can't make an ACCDE file.
Moving on.
All right, next up, James, gold member, started getting some really weird errors after reinstalling Office. Things like can't find project or library. It's highlighting date, compile error. That's a weird one. And, undefined function, format. Well, format, okay.
It turned out this wasn't a code problem at all. This is one of those things that looks scary, but once you know it, it's easy to fix. Donald nailed this one. Donald answers a lot of questions. I love Donald.
Basically, if you ever see errors like can't find project or library, or suddenly the built-in functions like format or date stop working, that's almost always a broken reference in your VBA project - not your code and not your copy of Office.
This can happen after reinstalling Office, upgrading, or moving to a new machine, exactly like James did. Something in the references list gets unchecked or marked as missing.
So the fix is: go to your VBA editor (and yes, I'm in dark mode). Go to Tools - References, and in this window, you want to look for anything that says MISSING. It will literally say the word MISSING right here, all caps. Fix it: uncheck it or recheck the correct libraries, like Donald mentioned. Sometimes you even have to just uncheck them, and then recheck them and then restart Access.
You're basically going to have Microsoft Visual Basic for Applications, Microsoft Access 16 Object Library (or whatever version you are on), OLE Automation, and Microsoft Office Access Database Engine Object Library. Those four should be checked on - those are by default. I try to avoid using any different ones in here. Sometimes I'll use a different one once in a while. I have some different projects where I show it. But these things are notoriously troublesome, especially when you're copying a database from machine to machine because these references have to be exactly the same. If it's not installed in this location (if someone decided to be fancy and installed Windows somewhere different), then it's not going to work.
That's usually what the problem is if you get those kinds of weird error messages like that. Kevin was thinking along the right lines with reinstalling Office, but usually you don't need to go that far. Check the references first. I should probably add this item to the troubleshooter above reinstalling Office, and I will.
I've had this actually on my list for a TechHelp video for quite a while now, but this is one of those things that comes up extremely rarely. But when it does come up, it's always the same problem.
Whenever you see that undefined function and it's a function you know is part of Access, like date or format, that's the problem: you've got a missing reference.
I just did a Google search on it and one of my videos doesn't come up for that. Remember, everybody, if you search for a problem related to Microsoft Access and one of my videos doesn't show up, I want to know about it and I'll make a video to plug that hole. That's my goal. I want to own every keyword related to Microsoft Access. I can't say Office because the word Excel and there's millions of videos for that. But Access, that's mine. I got it.
Movie quote: It's my island. It's mine. What movie is that from? Anybody?
All right, let's head over to the YouTubes.
It's a series of tubes. The senator said that a year ago? A couple years back when they didn't understand how the Internet works. It's just a series of tubes.
Access Days. I did my video review yesterday. Lots of views on that one. I know you guys are really interested in what happened.
This guy, Boer and Baton, said he would love to join remotely. I totally get that. I flew all the way from Florida to Seattle for this, and I know that's not realistic for a lot of people, but I thought it was worth it. I had a lot of fun, even though I was fighting a cold the whole week. Part of the reason Armand Stein, the guy who runs it, keeps it in person is because it's not just about the sessions. It's not just about the technical stuff. It's about the conversations in between, the hallway chats, the breaks, meeting other developers, that kind of stuff. They have a dinner afterwards. Sadly, I didn't go to because I wasn't feeling well.
Honestly, some of the best value that I get out of this is just talking to people face to face, and that makes it worthwhile for me.
That being said, if you can't travel, don't worry. There are still a lot of great virtual conferences out there. Access DevCon is coming up pretty soon. I'll talk about this more at the end of the video. There's lots of good stuff you can still attend online.
Next up, Xavier wants to know if they talked about version control or GitHub at all. The Access team didn't mention anything about version control or Git this year. I think it came up briefly last year, but it wasn't part of the discussion this year.
That said, version control with Access is possible, it's just not built into the product. One of my moderators, Alex, close friend of mine, actually put together a really nice walkthrough showing how to do this properly using Git.
He's actually put a couple of videos on my website. There's more on my website than just Microsoft Access stuff, folks. He's got two videos. In his first video, he showed you how to take an Access database and break it apart into text files using a free add-in. It exports all of your objects, your tables, queries, forms, and reports, everything, into readable files. That's the key step because Git can't track changes inside an ACCDB file. It's a binary file, but it can track text files.
Then in the second video, he takes those exported files and puts them into a GitHub repository. He walks through creating the repo, committing the changes in a clean way, and even versioning your database with releases. He also shows how you can make changes to your database, re-export everything, and actually see what changed between versions, which is the whole point. The really cool part is you can even rebuild your entire database from those exported files if something goes wrong. So it doubles as a backup strategy too.
Now, it's not as seamless as version control in something like C-sharp or SQL Server, but it absolutely works. Alex does a great job explaining it step by step. I'll put a link to this page down below. It's definitely worth checking out if you're doing any serious Access development.
Alex has been trying to get me to use version control for a while, but I've always been a solo developer, and I know this works great for people working in teams. Me personally, I just back up whatever I'm working on. If I'm going to make a modification to a module, I'll back up the module. If I'm going to make a modification to a form, I'll just back up that form, and I'll back up the whole database nightly, of course. If I goof something up, if it breaks right away, I know where the backup is. If it breaks three months from now, I keep rolling backups so I can always go back three months and figure it out.
No, this is definitely great for any professional development stuff. If this was available 20 years ago when I started doing consulting, I probably would have used it. But now I just teach, so there you go. Alex is going to teach you how to do it.
Next up, I'm so glad that most of you have a fantastic sense of humor and you enjoyed my Clippy video on April Fool's Day. I got a ton of comments from people laughing, saying I got them or figuring it out halfway through. A couple of my favorites were people waiting for Clippy to pop up and saying "I'm back." A few of you said I had you for a few seconds before you remembered what day it was. Exactly the reaction I'm going for.
Now, out of probably a hundred comments, I did get a couple of complaints. One person said that it was childish and another said that it wasn't fun to get their hopes up like that. I get it. Not everybody celebrates April Fool's Day the same way that we do here in the States. I guess one person was from the UK and they don't do that there. But this is something I've been doing for years, and the vast majority of people enjoy it, so I'm going to keep doing it. When I release a video, check the calendar before you get too excited so you know what day it is.
Some of my favorite comments: Matt finds himself oddly disappointed. Len says I got him good and proper, and Len's from the UK. Not everybody there has a problem with it. He's got a sense of humor. I went to the UK last year and everybody that I met there was funny.
One person unsubscribed. One unsubscriber, and this was as of yesterday. Let me see if it's any more today. Oh, nope. I just checked. As of today, it's back to zero, which means that either the person that unsubscribed, resubscribed, or one other person subscribed because of the Clippy video. So it's net zero now. I really want to know which one it is, but YouTube doesn't show you that. I hope it's that one person actually subscribed because they liked the Clippy video.
Oh, and here's the comment. This one person whose name I blanked out said it's silly time in the US - very childish. Oh, well, if you don't like it, scroll on.
Richard said I'm cruel, very cruel. Sandra, one of my moderators, was wondering yesterday what I'd do and still fell for it. That's awesome. Jeffrey wants his three-plus minutes back, called me an evil, evil person. I know you guys are just joking around though. When you guys call me an evil person, my students - if you're with me for two years and you're a gold member - you know my sense of humor by now. He's a Trekkie, that means he's cool just in general.
Dave Clark, who I actually met at Access Day, was mentioning no AI for Access at Access Day - great April Fool's joke. Like I said, there could be stuff at the MVP summit that I'm not allowed to share because the Access team gave a presentation at Access Day, but that's open to the public. Literally, the day before they gave pretty much the same presentation at the MVP summit, which was all under NDA. So we can't talk about everything that we heard. They could have talked about Clippy at that, but they didn't. It's just a joke. Kevin made me a picture of Clippy. He's joined Starfleet. That's awesome.
Maybe someone from Microsoft will watch it. Probably not. They don't watch my videos. Monica forgot what day it was. Don't worry. I'll get you next year too. I have something planned already. It's pretty cool.
Getting back to the Access Day thing, Lucas wants to know when they're finally rolling out the large format monitors feature. He's desperate for that. I'm kind of desperate too. I really, really, really want that because I have a big monitor on my desk, like I said in yesterday's video.
Large monitor support is still in development and it has not yet made it to beta. Form zooming is in beta right now. If you switch to the beta channel, you can get your hands on form zooming. I've got a video coming out soon showing you how to enable beta mode so you can get the beta version. Hint: don't do it on your only or your development machine. Do it on a sample test machine because it's a beta version. If you want to try it early, you can do that. I'll be showing that in the video soon.
But large monitor support is not yet in beta. They're still working on it. As I mentioned in the Access Day review, the Access Team doesn't give release dates or make promises until things are finished, which honestly, I get it. It's the same way that I work with my stuff. Things take time and it's better to get it right than to rush it out. So I don't give release dates for stuff either. You just have to be patient. You have to wait for it.
Next up, ZXYQ1 asked whether it's better to filter a subform using master/child links or by dynamically changing the subform's query in code. Short answer is I would stick with master and child links as long as it works. That's what Access is optimized for. Rewriting the record source in code can work, but it's usually more complicated and harder to maintain, and you're not going to see any meaningful performance gain in most cases.
Access is already doing a good job pulling just the related records that you need based on the parent record. So unless you've got a really unusual scenario, keep it simple. Let Access handle it. The only time I dynamically change the record source of the subform is if I want to change the whole source object, like put a different form in there. I show how to do that in this video where we just swap between contacts and demographics. We swap the subform object, but that's not the same as changing the data behind that as far as the record source of the subform itself. I'm swapping out the entire subform.
All right, finally today we have Peter, who's commenting on my update field video. He says, how is writing code that changes a field value more efficient than just opening that form and typing it in?
That's a good question. You're not wrong. If all you want to do is change one field, then yes, open the form and type it in. That's perfectly fine if you want to open up Jim Kirk's record and change his rank from captain to admiral - great, do it.
What I'm showing in this video is for automation. It's for when something needs to happen behind the scenes without you touching anything. For example, you print an order and it automatically updates a field, logs something, changes a status somewhere. It's not about being faster for one manual edit, it's about saving you from having to write the same SQL or the same recordset over and over again when your database is doing work on its own behind the scenes.
Think of it as a convenience tool for your code, not a replacement for simple data entry. This can be used for all kinds of things. I use it all the time in my database. Some examples:
- An order is marked as shipped - you can automatically set the customer's last order date.
- Someone submits a form - automatically update their status field to completed or pending review.
- A payment comes in - automatically mark the invoice as paid without opening the invoice form up.
- User logs in - update their last login date in the background.
- You run a nightly process - flag old records as archived.
All kinds of stuff you can do with this. And this way you don't have to sit there and write SQL or build a custom recordset every time. You just call your update field function, give it the WHERE condition and the value you want to set, and you're done. It's just a way to make it more efficient, that's all.
All right. So before we're done, make sure you are subscribed to my mailing list. Make sure you hit that like and subscribe.
Earlier in the video, I mentioned Access DevCon. It says DevCon Vienna, but it's held virtually, so that means online. It's in Microsoft Teams, April 16th and 17th. If you're serious about Microsoft Access development, check it out. Two full days of sessions from top experts, MVPs, updates straight from the Access team. They're probably going to talk about a lot the same stuff they talked about in Access Day.
I'm going to be doing a separate video with more about what's going to be covered. There's the link. Check it out. All the presenters are listed there. I've been signing up for this for the past few years and I'll be honest, I can't take two whole days and just sit there and watch videos. One of the nice things is all the recordings are saved. What I'll do is I'll download the recordings once they're done and then you can watch them at your own pace, and that's what I do. Or you can attend live if there's something you really want to watch and you're really interested in it. You can attend that session live in Teams and you can ask questions, that kind of stuff. Check it out. There's a link. I'll put a link down below.
Before you go, make sure you stop by my website. Check out the "What's New." Not a lot of new stuff happening in the Captain's Log this past week because I was gone. I was out of town for Access Day. Got a discussion on Trek and Artemis too, which is really cool. And this is what I sent my wife for April Fool's Day. Although she probably knew it was an April Fool right off the bat. I said, "Hey, I bought a penguin." Yes, there are penguins in tropical settings. These are African penguins. It's my favorite kind of penguin. They're obviously in Africa, South Africa. There are also Galapagos penguins. They're the only penguins that are found in the northern hemisphere. I could talk about penguins all day long.
Make sure you stop by my merch store. Get yourself a yellow Access shirt and wear it next time you come to Access Day like Dave. That's awesome. Or if not, just get a mouse pad or a coffee mug or whatever. Grab a copy of my book on Amazon. You can get it on Kindle too. If you're a Kindle subscriber, you can get it for free. So check that out. Working on the SQL Server one right now.
So the big takeaways today:
If Access suddenly starts throwing weird errors like date or format not working, check your references first.
If your ACCDE won't build, compile your code.
When it comes to things like subforms or queries, keep it simple and let Access do the heavy lifting unless you really need something custom.
Now post a comment down below. Let me know what you thought of today's video and if you've run into any of these issues and how you're using this stuff in your database. And of course, send me your questions for next week's quick queries.
That's going to do it. That's your quick queries video for today brought to you by AccessLearningZone.com. I hope you learned something.
Live long and prosper, my friends. I'll see you next time. Enjoy your weekend.Quiz Q1. What is a common reason Access suddenly stops recognizing built-in functions like Date or Format?
A. A broken reference in the VBA project
B. Outdated Windows version
C. Lack of available RAM
D. Too many users editing the file
Q2. What is the main security concern with storing sensitive data in Microsoft Access?
A. Users can edit the tables directly
B. The database is prone to viruses
C. The file can be easily copied or taken offline
D. Access cannot encrypt data
Q3. Why is SQL Server generally considered more secure than Access for sensitive data?
A. Users never get a copy of the entire database file
B. SQL Server automatically encrypts all data
C. It runs only on Linux servers
D. It cannot be accessed via network
Q4. When designing an inventory database, what should drive your table design?
A. The layout of the printed report
B. The requirements of the barcode scanner
C. The structure of the data and relationships
D. The color scheme of the office
Q5. How are field captions on forms and reports tied to table field captions in Access?
A. They update automatically whenever the table caption changes
B. They are statically set when the field is first dropped onto the form
C. They always inherit changes from queries
D. They synchronize only after a compact and repair
Q6. What does the ambiguous outer join error in Access queries usually indicate?
A. The same lookup table is joined more than once without using aliases/copies
B. There are too many tables in the query
C. Fields are missing in the output
D. A field has been renamed in the source table
Q7. What is usually the root cause if Access will not compile an ACCDE file?
A. A missing VBA reference or code compile error
B. Insufficient disk space
C. File opened in read-only mode
D. Incorrect file extension
Q8. What is a recommended first troubleshooting step if you suddenly get errors like "Can't find project or library" or "Undefined function" in Access?
A. Check Tools - References for any marked as MISSING in the VBA editor
B. Reinstall Microsoft Access immediately
C. Upgrade Windows to the latest version
D. Compact and repair the database
Q9. What must you do if your form or report captions do not update after you change the underlying table field caption?
A. Manually update them on each form or report
B. Recompile your database
C. Import the table again
D. Run a database macro to sync
Q10. What is the main reason to use version control with Access databases?
A. To track changes and recover previous versions by exporting objects as text files
B. To decrease database size
C. To increase user security
D. To enable multi-user editing in real time
Q11. What is the recommended way to filter a subform in Access unless you have a very unusual requirement?
A. Use master/child links between forms
B. Rewrite the subform's record source in code each time
C. Apply a global filter to all forms
D. Use a macro to hide unaffected records
Q12. When is it more efficient to update a field's value using code rather than manual data entry in a form?
A. When the update can be automated as part of a background process or event
B. When you only need to make a single manual change
C. When forms are unavailable
D. Only when working in SQL Server
Q13. What is the Access team primarily focused on according to the video?
A. Stability, security, compliance, compatibility, and bug fixes
B. Creating flashy new features
C. Marketing and promotion
D. Eliminating all file-based databases
Q14. What should you do before blaming Windows, Access, or Microsoft if you cannot create an ACCDE file?
A. Open the VBA editor, Debug - Compile, and fix any compile errors
B. Uninstall and reinstall Access
C. Delete the database and start over
D. Change the database to read-write mode
Q15. What is a benefit of using barcode scanners in inventory systems in Access?
A. Most act as keyboard input and are very easy to integrate
B. They require custom hardware drivers
C. They only work with SQL Server
D. They can only scan QR codes
Q16. If a field name or caption is changed in a table, what behavior can be expected in related forms and reports?
A. Existing labels will not update automatically; manual adjustment is needed
B. Forms and reports will sync immediately
C. The whole database must be recreated
D. No changes are allowed after creation
Answers: 1-A; 2-C; 3-A; 4-C; 5-B; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from AccessLearningZone.com, I want to address some common questions and issues that have come up recently in the world of Microsoft Access. I'm Richard Rost, your instructor, and today we'll dig into a variety of topics, all focused on helping you avoid frustration with Access, improve your development process, and stay updated on what's happening in the Access community.
A frequent concern I hear from Access users is when the program suddenly stops recognizing simple built-in functions like Date or Format. Many times, this happens unexpectedly - everything was working fine, and then out of nowhere, you get errors saying built-in functions aren't defined. Before you panic, know that this is usually an easy fix related to broken references in your VBA project. After an Office reinstall or upgrade, some references may get unchecked or go missing, causing these errors. Head to the VBA editor, open the references list, and look for anything marked as MISSING. If you find one, uncheck it, or recheck the correct library, and restart Access. That usually solves the problem. This issue isn't caused by your code or a problem with Office itself; it's all about those references.
Now, before diving further into questions, there's some news worth sharing. Microsoft recently put out a new Access update. While it's mostly bug fixes with no major new features, that's actually a positive thing for stability and reliability. Notable fixes include an important update to the Edge Browser Control so navigation with VBA inside a tab control now responds correctly, and a subtle but significant change where Access no longer unnecessarily executes queries when reading certain field properties in code. These quality-of-life improvements also cover Unicode display, print preview issues, and more. The main takeaway here is that Access continues to be maintained and developed. The development team is small and most of their focus is on security, compatibility, and stability. So, for anyone worried that Access is dead - rest assured, it's alive and being looked after.
Let's move on to the user questions and forum discussions from this week. One of the most interesting threads came from a debate about storing sensitive information - like credit card data or medical records - directly in Access versus using SQL Server. The lesson here is not about finding a perfectly secure database (which doesn't exist) but about reducing risk. Access is file-based, so if someone gets hold of the file, they have everything. In contrast, SQL Server keeps the data on the server and gives you fine-grained control through permissions. You control exactly what users can see or modify, which is critical for sensitive or regulated data. For small, simple tasks, Access is fine, but for anything where security is a major concern, use SQL Server or an equivalent.
Another forum question centered on designing an inventory system and how to structure it for optimum reporting and flexibility. This is a common scenario: someone wants a paper printout for each office or cube of all assigned equipment, and instinctively starts designing the database based on the printout. The right way, however, is to design your tables for the data, not for the printed output. Create a table for locations and separate tables for each asset, linking assets to their locations. Once the data is organized properly, generating forms and reports becomes straightforward. Don't let a paper form dictate your table design - work from the data outward, and adapt the output when you need it.
Captions and descriptions in tables brought up some confusion as well. In Access, forms and reports do not update their labels automatically if you change a field caption at the table level. When you first add a field to a form, Access copies over the caption, but after that, it's just static text. Changes to captions or descriptions in the table don't push through to forms already built - so keep field names and captions consistent early on. Personally, I mostly use descriptions for fields that need extra clarity, like "IsActive" flags, and I make sure not to include anything in descriptions that I wouldn't want end users to see, because those can pop up unexpectedly. This is especially important considering, for example, the way HTML comments can accidentally leak developer notes.
Ambiguous outer joins are another classic headache, and one user faced this when joining a Caliber table to both a Firearm and a Magazine table in a query. The fix is to bring the lookup table into the query twice, allowing each join to be unique and independent, which eliminates Access's ambiguity. If you join one table to multiple other tables in a single query without separate instances, Access can't resolve which relationship to prioritize.
Building ACCDE files in Access and finding that they won't compile is a surprisingly common support topic. The solution is almost always to compile your code in the VBA editor (choose Debug - Compile). Most of the time, a compile error in any module stops the ACCDE from being created. Clean up every error, recompile, and try again. Don't assume it's a problem with Windows or a new version of Access - 99% of the time it's about compilation.
We also discussed user comments about version control in Access. While it's not built in, it is possible, especially with tools that export objects as text files you can then track in Git. This allows you to manage change history and even rebuild databases if needed. For most of my career I've worked solo, so manual backups have sufficed, but if you're in a team or handling complex projects, version control is worth learning.
Some lighter comments followed April Fool's Day, like reactions to my Clippy video. Most users took it in stride, though a few weren't as amused. I plan to keep up those pranks - just remember to check that calendar when my videos seem a little strange.
On to technical questions: Should you filter subforms via master/child links or dynamically change their record source in code? As a rule, stick to master/child links - they're easier to set up, maintain, and are what Access is optimized for. Only go the dynamic route if you have highly specialized needs that Access's built-in features can't handle.
Finally, I addressed a question about the usefulness of code for updating fields versus just typing the values in yourself. For single changes, manual edits are fine, but in cases where automation is required (like updating status after submitting a form, or marking orders as shipped), having an update field function can save you repeated effort and makes your codebase neater and easier to maintain.
As a closing note, remember to make regular backups of your databases, check your references when encountering odd errors, and let Access handle tasks for you when it can, instead of bending over backwards to code what is already handled effectively.
If you're interested in following developer conferences virtually, Access DevCon is coming up soon with recordings available for later viewing - perfect if you can't attend live. Also, check out the latest content and resources on my website, including videos, seminars, merchandise, and my book, which is now available on Kindle.
You can watch a complete video tutorial walking through step-by-step solutions for everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Causes of missing built-in functions like Date or Format
Fixing missing built-in function errors in Access
Difference between storing sensitive data in Access vs SQL Server
Security risks of file-based vs server-based databases
Best practices for designing inventory databases in Access
Using main and subforms for equipment tracking
Handling field captions and descriptions in Access tables and forms
Impact of changing field captions on existing forms
Dealing with ambiguous outer joins errors in Access queries
How to handle multiple relationships to the same lookup table
Diagnosing and fixing ACCDE build failures in Access
Importance of compiling VBA code before creating ACCDE
Resolving missing references in Access VBA projects
How to check and fix broken references in the VBA editor
Using version control and Git with Microsoft Access databases
Exporting Access objects to text for version control
Filtering subforms with master/child links vs changing record source in code
Best practices for subform performance and maintenance
Automating updates to field values using VBA functionsArticle Sometimes when you are working in Microsoft Access, it suddenly acts as though it no longer recognizes basic built-in functions like Date() or Format(). This can cause strange compile errors or prevent your code from running. Before you panic or start thinking something is wrong with your installation or with Windows itself, there are some logical reasons behind this behavior, and understanding them will help you fix these issues easily.
The most common cause of built-in functions like Date or Format suddenly not working is a missing or broken reference in your VBA project. This issue often pops up after you upgrade Office, reinstall Access, or move an Access database to another machine. When the references aren't set up properly, Access can't find the functions, even though they are supposed to be part of the core language.
To fix this problem, open the VBA editor in Access by pressing Alt + F11. Then go to the Tools menu and select References. In the References dialog box, look for any that say "MISSING" in all capital letters. These missing references are what's causing your trouble. To resolve it, uncheck the missing reference, or locate and re-check the correct library if you know what it's for. Your project should always have the defaults checked, such as Microsoft Visual Basic for Applications, Microsoft Access xx.x Object Library (where xx is your version), OLE Automation, and Microsoft Office Access Database Engine Object Library. Avoid adding unnecessary references if you can, because the more you have, the more headaches you might run into when moving your database around or sharing it with others.
If you see a missing reference and remove or correct it, restart Access and your functions like Date and Format should start working again. This step is a lot faster and more reliable than reinstalling Office or Windows, and in most cases, it's all you need to do.
Another issue you might run into is not being able to build an ACCDE file (the compiled version of your Access database). If Access refuses to create an ACCDE file, nearly every time this is due to code that does not compile. Even a tiny error somewhere in any module will stop the whole process. To check for this, go into the VBA editor, press Debug and then pick Compile. If Access finds any errors, fix them, then repeat the process. Only once your code compiles successfully will Access allow you to create your ACCDE file. So always remember: Debug - Compile once in a while. It is a good habit and will save you plenty of grief when trying to distribute your database or lock down your code.
Database design is another important topic. When creating systems for things like inventory, it's tempting to try to make your table structure match the layout of whatever printout or form you want. But you should always design your tables according to the data - not the way you want the report to look. For example, for a printed inventory sheet per office or storage cube, create a table for locations, then a separate table for assets, including fields for asset type, manufacturer, model, serial number, and a link to the location. When it's time to print, you can build a report to group all the assets for each location. Resisting the urge to let your printouts dictate your table design will make your database more flexible and easier to work with later.
If you find that changing a field's caption in a table does not update the forms or reports that use that field, don't be surprised. When you add a field to a form or report, Access copies the caption into the label at that moment. Changing the caption in the table later does not update existing forms and reports - you'll need to update each caption manually. Queries do handle captions a bit better in recent versions, but once you use aliases or make other customizations, captions may not carry through.
Security is always a concern, especially with sensitive data like credit cards or medical information. Access databases are file-based, meaning anyone with access to the file can copy it or even take it home and attempt to crack it offline. This is different from SQL Server, which is server-based and allows you to grant and restrict user access at the database, table, or even field level, with data never directly accessible as a file. While no system is perfectly secure, if you need to handle sensitive or regulated data, you should use SQL Server or a similar database server, not Access.
Query errors like "ambiguous outer join" can confuse many users. This error often occurs when you try to use the same lookup table in two different joins in the same query - for example, when linking both a firearm and a magazine to a caliber table. The fix is to add the lookup table to the query twice so that each join is independent. Access will assign different aliases to each instance, like CaliberT_1 and CaliberT_2. This lets each join work separately and gets rid of the ambiguity.
Version control is important if you are developing serious database projects, especially in a team. Even though Access does not natively support version control like Git, it's possible by exporting all your database objects into text files using a free add-in. Once your objects are in text format, you can store them in GitHub or another version control system. This allows you to track changes, roll back if necessary, and even rebuild the entire database if something goes wrong.
When it comes to filtering subforms, the best practice is to use the master/child link properties in Access. This is what Access is optimized for - it automatically handles syncing your data based on the current record in the main form. Only switch to dynamically changing the subform's record source in code if you have a very specific need, such as swapping the entire form out for a different one based on user action. Otherwise, stick with the built-in linking system.
For automating updates in your database, such as updating a field value based on some action (for instance, marking an order as shipped automatically updates a customer's last order date), writing some VBA code is the way to go. Manual editing is fine for occasional, single-record changes, but automation shines when you want background processes to happen without user intervention. Using a function that updates a field via SQL based on a specified WHERE condition can make your code cleaner and save time, especially if you need to update records frequently based on automated logic.
Finally, remember to back up your data regularly and stay updated on Access improvements by reading the official blogs or release notes. Even though flashy new features may not appear often, the Access team continues to focus on stability, security, and compatibility. Major new additions come slowly, but ongoing bug fixes and improvements mean Access remains a viable and actively maintained tool for building robust database applications.
If you run into weird errors, always check your code for compilation issues and look for broken references before making major changes or reinstalling software. Often the solution is simpler than it seems once you know where to look. And be sure to post comments or questions in Access communities if you get stuck - you're likely experiencing something that others have tackled before. |
