Quick Queries is where I take all the comments, questions, emails, posts, and stuff from out the week and put it together in a Quick Queries video.

So, let's see what's in the mail bag. One of my lifetime members, Kim, posted that she's got an old MDB file. I remember those - Access 2003 and earlier, going all the way back to Access 1.0, had an MDB file, Microsoft database file, a tiny file. But, trying to open it in Access 2021 and getting this error message.

The problem is that you just can't do that anymore. Up until Access 2010, you could open up an MDB file. In 2007, that's when they made that major change. They added the ribbon and made a major overhaul to the file format and created the new ACCDB format. In a couple of versions, 2007, 2010, and I think 2013 too, I'm pretty sure, allowed you to import.

No, 2013 was the first one that you couldn't import. I should read what I wrote earlier today. But they basically removed the abilities to import from MDB formats. In these middle versions, you could import the objects and convert them over to an ACCDB file. But now, if you've got Access 2019, 2021, 2024, or a 365 subscription, forget it.

There are some third party tools and services out there. I have not used any of them myself. One service that has an excellent reputation, run by Wayne Phillips, who is a Microsoft MVP, is everythingaccess.com. He's got a conversion service. There's other tools like the MDB Viewer Plus. I again have not used it.

Now, ChatGPT surprisingly told me that it has access to a virtual machine, which is essentially a version of Windows running this older version of Access 2010. It said that it could try to convert it for her, well, for me. I again, I have not tried this, but it's possible.

Another one of my members, Dave, said that he's got some older versions of Access. I used to keep older versions running too when I did consulting. Customers sent me all kinds of old Access versions. So, I used to keep multiple virtual machines. A virtual machine is like a version of Windows that runs inside your Windows. It's like a little copy of Windows that can run an older version of Windows, Office, and Access. Sometimes you need to use that to convert those files.

I stopped doing that a few years ago when I stopped doing consulting. Now I just teach. So, thank you, Dave, for offering that. That's what I would suggest - try ChatGPT and see if it can pull it off. I don't even think I have any old Access files around here to test that myself. Maybe I'll dig into my archive and see if I can find one of my old MDB files.

Our pair United for 22 says, "Save, Debug, Compile, once in a while." Another t-shirt. Yeah, it is. It's over in the merch store. There it is right there. You get it on a mouse pad, it's on a t-shirt, it's on all kinds of crazy stuff. There it is.

Vera Mann says, "Winning numbers are already available on the internet. It's better to show people how to find the most occurred combinations." First of all, if you know where the winning numbers are on the internet, drop a link. I'd love to know where that is myself.

As far as the most occurred combinations go, I've talked about this in previous videos. Numbers that are "hot" or "cold" don't matter logistically. You have exactly the same odds of getting every number as you do every other number. Hot and cold over a long period of time, it will all work out to even. So that's just a myth.

It's just like flipping a coin. There might be short variations where you might get more heads or more tails. But keep flipping over millions of flips, it will work out to 50-50. Although, I have seen someone, I did read an article one time that said that they measured coins. They measured like five different coins. And a lot of US-minted coins like quarters are slightly heavier, fractions of a gram, on the head side.

But, all things being equal, if your coin is evenly weighted, it's not a cheap coin, for example, then it's going to be 50-50. Same thing with lottery numbers. Unless someone's rigging the system and you have access to those numbers, let me know. I'll split it with you.

Bo Jorge says, "Interesting video. I understood it's preferable the record source of a form be a table, not a query. Is this an exception?"

Well, that depends. If all things are equal, a table is generally better to make as the record source of your form or report. But if you need calculations, those are better off in a query than putting them in the form fields. For example, if you want to do an extended price, which is unit price times quantity, you're better off putting that in a query and making the query the record source of the form than to do that calculation directly in the form.

If you don't need any calculations and you're not bringing in any other fields from other forms, then yes, it's generally preferable to make the form or report based directly from the table.

I skipped one, I put one extra video in there that I wanted to do. I skipped one day with the fitness database and I got like 10 different complaints. It is common. I just released part 15 today, or yesterday, I should say. Part 16 will be on Monday, so relax. I might sneak in another different video here or there, it doesn't mean I'm abandoning fitness. I'll let you know when the fitness database is done. We're far from finished with it.

Angelus says, "I'm having trouble with this code. Whenever there's a space involved in the address or the name itself, the program won't send the entire string. For example, one two three space example space street will only send one two three example."

That's kind of weird. For those of you who aren't familiar with this video, I show you how to take Access and use it to fill in a PDF form. It's a PDF file that has form fields on it. You can use Access to open the PDF file in your browser or whatever your default PDF reader is, and then using SendKeys will send the keys to the PDF file to fill in the form.

I've never had a problem with spaces not working. Are you sure they're space characters? Are you sure it's not some other weird character that's in there? Try replacing the spaces with something else like a hyphen. See if that works.

This is one of those examples though that I'd really have to see to tell you for sure. Maybe try putting a little pause in there between the characters. See if that fixes it.

This is one of those things where I'd have to see the database to play with it. If you were a member on the website, I'd say to post some screenshots. That's a tough one. I can't think of anything off the top of my head. If anyone else has any ideas, post a comment below.

Noelle says, "Is there a video for fixing errors in an already built database? Trying to delete a button that runs a report from a switchboard form. When I delete it, I get an error and I have to cancel the save to be able to use the database and modify the existing template. And I love what I have so far. Too far into it to start from scratch."

In order for me to make a video to fix a problem, I have to know what the problem is. I don't have a copy of your database. I don't know what you're working with. I can't see anything from here. So it's really impossible for me to help you.

If you're using a switchboard form, then you probably have to go in and figure out what macro or VBA code that button is running. I could teach you how to do this from a new button on a new form, but I can't fix that database without seeing it, and that's not really something I do. Without a lot more information, there's not really a lot that I can do for you.

As far as fixing errors goes, I've got lots of videos on error handling, troubleshooting, and all that kind of stuff. Check my website for more details, and hopefully something on there can help you. But this is one of those cases where again, I've got to see the database or at least more information to be able to help you.

Here's one from a member, Yorgan. He says, "If I have a database front end and need to print to a customer's printer, but it's not the same on every computer, is there a way to manage that without having to change it on each computer? It cannot be the default printer."

This gets pretty tricky because normally Access will print to the default printer unless you specify a specific printer. It's got to be the same printer with the same name on the same port. So if it's like LPT1 and it's named Lexmark or whatever, then that's what that report is designed for.

Yorgan also posted this on the website, he's a member, and we've got some extra stuff in here for you. Donald, thank you, came up with some code from Copilot to print to a specific printer that you can change on the fly. You can loop through Application.Printers to list the available printers for the user. That's kind of tricky. I've done that before. This way you can give the user a form that shows the printers on their system, and then you can have that report go to that printer. It's kind of tricky though.

If you still have any problems after this, then post a follow up comment and let me know. Maybe it's something that I can put a video together for.

Michael Carter says, "I'm getting a runtime error at $375 syntax error missing operator in query expression, and food description like," and then quote. It looks like to me, you've got spaces in there. You got a double quote, space, asterisk, space. That stuff matters. You can't have those spaces in there. Programming is very specific and no spaces. So get rid of the spaces. Let me know if it works.

Out by 50 says, "The origin story of attachments and multivalued fields is rooted in Access to SharePoint integration because SharePoint allows similar mechanisms as much as making something bad, simple for noobs to do."

I agree. When SharePoint came on the scene, it allowed things like this, like attachments right in the database, multivalued fields - that's basically a combo box saved in the field. I don't like either one of those things and I hate the fact that they added it to Access. I get where they were coming from. They wanted newbies to be able to easily store a Word document in a customer's record or be able to easily pick from a list of shipping options.

But by allowing users to do that, they are not teaching proper database design, and it makes things even more difficult down the line when you do decide to become a more serious database developer. Now you've got to fix all these bad fields you've got in your database.

So, I try not to teach them from the beginning, or at least when I do cover them, I explain to people thoroughly, you shouldn't use these. But if you want to, you can, but know you're going to have problems with them later on. Some people have no desire to be proper professional database developers, and they want these shortcuts. As long as you realize that that's your limitation up front, then great.

But yes, exactly. Thanks for sharing. You're absolutely right. Very similar comments to the lookup wizard as well. I prefer to teach people to properly have relational tables so your combo boxes can work properly, and all that stuff too.Value lists have their place as far as lists that you are sure will never change or will only ever exist in one spot. If you have a list of names and that list is never going to change and it is only here, fine, use a value list combo box. But as soon as you discover you want to put that in a second place, it needs to go in a table.

Another excellent comment. I had a video where I talk about the end of VBScript, and VBScript allows you to write VB-like scripts or batch files, if you want to call them that, which you can run from the command line. I have a bunch of them that I use on my system. They are basically getting rid of it, but making it an optional add-on for a while. Essentially, there are some security issues with it, so they are deprecating it. That is what that video was about.

He is saying that the Windows Script Host is separate and distinct from VBScript. That is true, just like the Windows Scripting Runtime, with those two libraries for FileSystemObject and Scripting.Dictionary. There is also JScript, which is kind of like JavaScript, and those are not necessarily going away just because VBScript is. JavaScript can pretty much do everything that VBScript can; it just has a different syntax. I use JavaScript as well as ASP, which is basically VBScript, on my website.

I used to be a C programmer back in the day, so I get the appeal of JavaScript. It is just difficult jumping back and forth between them. You have Script, VBScript, classic ASP, VBA, VB.NET. They are all similar, but they have subtle differences that trip you up from time to time. So thank you for that great clarification. I appreciate that. It would be nice if all of the developers in the world got together and just made one language. I know it will never happen, but like one spoken language for everybody.

RTAT1 is saying in last week's Quick Queries I said that Me. is necessary for form properties. This is absolutely not true. RTAT is correct. You do not absolutely need to use the Me. to access the form properties. You can say just Caption = "Hello There" and the form's caption will change. I should have probably specified that. That is how I teach it because I want students to associate the properties of the form. Me means the form itself.

So do you need to use it? No, you do not need to. I should have meant that you need to allegorically in my class. In my class we use Me for properties. That is kind of what I meant. I have always taught it that way. It helps to keep things separate in the students' minds. But do you need to use it? No. I just think it is good form, but that is my preference. Thank you very much for the clarification.

Mac Frank says I think you should have gotten into UNC file name structures reporting to the backend on a local network. Otherwise, why would you split the database to start with? Every network that I have built since the 80s, I have always used a mapped network drive letter like Z or something like that to represent the server or the file share. That is just the way I have always done it.

UNC paths are fine. I like drive letters. That is just my preference. For everybody else who does not know the difference, a UNC path looks like this, where it is just the name of the machine, like SERVER, and then the folder name where your share is. I prefer to map that to a drive letter on each machine on the network. Both work with Access.

Access will actually usually translate mapped drive letters over to UNC file name types, so either one works. 601.5 does not have another. It has nothing to do with splitting the database which type you use. Splitting the database is its own reward.

George Eos says at 6:02, you say it updated in the background, meaning it updated the first name field, but not the text box, since the text box is now named FirstNameBox.

In the last Quick Queries, I said if you take a text box that is called FirstName and it is bound to a field called FirstName, and let's say we rename that text box to FirstNameBox, just like right here, what is going to happen is if you change FirstName, it will change the value in the field but not update the box on the form. You will see the record go dirty, but the box does not change unless you leave the record and come back to it and then it updates, or you issue a Me.Refresh or something.

Now, Me.FirstName and Me!FirstName are essentially the same thing because what Access does is provide the appropriate way to reference fields. But a form will kind of fake-promote that field to a property of the form for your convenience. It does not always work, especially if you have a field that is named something that is a reserved word, like a form property, such as Caption. If you have a field named Caption or Picture, it might not work. So you do not always rely on that. You can always rely on the bang to work.

One more time, let's say you have a form. Here is FirstName. We are going to rename the box to FirstNameBox, but it is bound to a field called FirstName. Save that. Now, let's take a button, right click Build Event. In this button, I am going to say FirstName = "XXX".

If I hit the button, notice the record goes dirty. The first name in the table has changed, but the box has not updated. If I leave the record and come back to it, you can see that it is updated. If I say FirstNameBox = whatever, and hit the button, it changes it immediately.

This is why I generally like to have the box named the same thing as the table field, so they are identical. It does not always happen, but it helps. If you say Me!FirstName = that, and then hit the button, it is essentially the same thing as saying FirstName = whatever. It is no different from this because Access has promoted FirstName to a property of the form. That is why it looks like all the other properties. That is a convenience thing.

Hit the button. It still does the same thing. It is still not updating the text box. For that, you need to go to FirstNameBox.

As I mentioned earlier, I prefer to use Me. for form properties and methods. That is just my preference and how I have always done it. It has always worked well for me. If I am referring to a field, I will use that, and you do not even need that if it is on the form you are on; you just need that. It is a little confusing, which is why I have my method that I teach. I try to make it less confusing because there is a lot of weird stuff going on in the background.

Moving on, bytebash50 says thank you so much for this. We had a team member retire and it has been a little challenging keeping up some of his old ASP legacy sites. These tutorials are a major help. ASP is still alive and well. I still run my website on classic ASP. I started building it in 2002, when ASP was still popular, and it is still running great. No complaints.

Are there newer languages and stuff out there? Yeah, but if it is not broke, do not fix it. I have a feeling it is going to be like COBOL programmers for the longest time. Our software was written in 1968 in COBOL and we are still running our business on it in 1994. That is how old I am.

Shadow Dragon says back in the pre-Internet era, I would use the macro recorder and the converter from macro to VBA to get a first cut of Excel VBA code. I sometimes still use this technique when I cannot get some Excel VBA code to work. Maybe not the most efficient way to code, but it works for me.

I do it too. I will be honest with you. I know Access VBA like the back of my hand, but there is a lot of VBA with Word and Excel that even I sometimes have to look up. The macro recorder is a great way to learn. I use the macro recorder sometimes in Word, PowerPoint, and Excel. Of course, Access, the literal redheaded stepchild of the Office family, does not have a macro recorder. But do not be embarrassed. I use it all the time. It is hard to remember all the different commands for all these different applications.

So it is either the macro recorder or ChatGPT for syntax. I spend about 95 percent of my time in Access, so I just remember what you do. I do not write a lot of Excel VBA code and Word even less. Just use the macro recorder, see what it is doing, and then you can adjust it to your own needs.

I get lots of people who post comments on my YouTube channel and other social media that they need help. I do not offer help unless it is something I can make a video on. But I can direct you to my Access Developer Network, where I have a list of other consultants that do offer help.

I am going to do my sponsors here: Shamaan Consultancy, AccessExperts.com. There are other consultants listed on this page. If you need help with your Access database, these are the people to talk to. Not me, I just make videos. You have to have a question that I can help a bunch of people with. I do not work on just people's databases anymore. I used to do that, and I pulled out enough of my hair that I do not do that anymore. But these guys do. You can see Sammy is pulling out his hair too working on people's databases, and so is Juan. I guess helping people with databases is tough. I still have some hair left. This picture is a few years old, but it is not that far off.

B. Miller, going back to my Word default template video, says he or she went back to Calibri and then said, "Lord, how I have always hated Times New Roman." Do not hate on Times. Times New Roman was the jam back in the day. I loved Times New Roman in my WordPerfect 6. I loved that when Word had Times New Roman and they introduced Arial, you could use Arial for headings without the serifs and then Times New Roman for the text. Those were the days. So do not hate on Times New Roman. LOL.

Michael is talking about my multiple cascading combo boxes video. He says, "To be more inclusive, I use Region instead of State/Province/Territory and Municipality instead of City/Town." You can make this as regional or as generic as you want. I was just using an example of what I know from here in the States. We have state, county, city, and so on. That is what I use. You can make it however you want to, and you can get really complicated with this stuff. If you want to do something for both the US and Canada, you can have it so that if they pick US for the country, call it state, if they pick Canada, call it province, and so on. I have built some really complicated databases like this and it is all a matter of how much work you want to put into it. My goal for this was just to teach you how the cascading combo boxes work. But again, just more Legos to play with. Thanks for the comment.

Next up, Xavier says using DB OpenTable recordsets indexed and searching the value using Seek is even faster than my faster DLookup function. Yes, you are correct under ideal circumstances.If you are working with local Access tables and you are looking up a field that is indexed, seek can be lightning fast, but it does have limitations. It only works with table-type recordsets, not SQL or queries. It only works with indexed fields, and it will not work with SQL Server tables.

So yes, if you are in the sweet spot, using just a local Access table with an indexed field, seek is great. However, I prefer doing it the way I did it because it will still work with queries, and it will still work with a linked table.

Are you correct? Yes, you are absolutely correct in that specific set of instances. I chose something that is a little more generic and still faster than the built-in DLookUp. That is what I was going for. If you are running something where every millisecond counts, by all means, your method is definitely the fastest.

Excellent. You know your Access recordsets, sir. I give you that.

Can you delete all the queries if you copy the SQL and use the SQL in the record source of the form object? Yes, you could. I do my best to avoid using saved queries. They are fantastic tools for beginners, and I will continue teaching them, especially in my beginner and expert lessons, because they have their place.

However, I do not like it when my database gets to the point where I have hundreds of extra queries floating around. If I can take that query and put it in a form directly in the record source or wherever, I will sometimes do that, but not always. This is one of those things that is up to you. There is no right or wrong answer, so do whatever you like.

Steve posted this: Can someone help with this recurring issue? I have a split database. All users run the front end locally. That is good. There are approximately 12 users. The back-end data is on a mapped drive on a server. For some reason, my users are getting a runtime error 3734: The database has been placed in a state by user...

Many times, I thought I set this up properly to avoid this. My users are all running uncompiled versions of the front end, but none have any knowledge to access the design modes. By uncompiled, I assume you mean they have ACCDB files. That is bad, because even though they do not know how, they can still accidentally get into design mode, and it is possible to create problems and issues with the database.

You are really better off giving them ACCDE files so they cannot get into design. Even getting into design mode can still lock objects in the back-end tables from improperly changed front-end objects. It sounds weird, but it is also possible that someone has directly opened the back-end database in exclusive mode and the lock file got stuck.

If the user is running an uncompiled front end and Access is auto-saving some design change in the background, that could be an issue. Try deleting everyone's LACCDB files - the lock files. Every database file that has tables has one, and even your front-end files might have them too.

Bottom line, I think you will save yourself some headache by giving everybody ACCDE files instead. That is just my experience. Of course, as always, run down the troubleshooter. Do things like check if you have any kind of file sharing such as Google Drive, Dropbox, or OneDrive running either on the front end or the back end; get rid of that. That will cause problems.

Your antivirus could be causing problems if it is trying to scan the Access database file, so make sure you exclude any of those folders. There are a lot of different elements to check. Check out my troubleshooter page. I have a million things you can look into. There is a video here too. Go through all these troubleshooting steps and check for all these issues. I tried to organize these in the order in which they generally cause problems.

Finally, tonight we have Jack. I love that name. Without trying to outshine the master - nonsense. I like when people call me out on stuff, correct me on things, or tell me things I did not know. I love learning things from you guys. I do not always act like I know everything, and trust me, I do not.

Jack says what you are referring to as a single quote was initially called an apostrophe or tick mark back in the day when I first learned to type on a manual typewriter in high school circa 1980s. Oh, you are the same age as me. I graduated in 1990, folks. The double quote was simply referred to as a quote. You had opening quote and close quote.

An apostrophe or a tick mark was used in possessive words such as Rick's YouTube channel or the dog's bowl or Rick's dog's bowl. That is absolutely true. If you learned to type on a manual machine back in the 70s or 80s, you would hear tick mark for apostrophe and just quote for the double mark.

When I am teaching programming or databases or whatever, I try to stick with "single quote" and "double quote" to avoid any confusion with apostrophes, because on the keyboard, it is on the same key. You just hold the shift key to get the double quotes, and they have specific meanings when it comes to Microsoft Access.

That is why I stick with "single quote" and "double quote." When you are dealing with grammar, there is a whole bunch of different rules. There are smart quotes, which I hate: the left-leaning open quotes and the right-leaning close quotes, and even apostrophes lean a certain way. There are all kinds of advanced characters for them, and there are differences in whether you are dealing with typing, grammar rules, or programming.

So you will always hear me refer to a single quote or a double quote. Tick mark is something I have not heard since the 80s. The oscilloscope seems to refer to them as tick marks.

All right, that is going to do it for Quick Queries 51. Make sure you check out my Captain's Log where I publish something every day. I try to write something daily. It is not always good stuff. Sometimes it is, sometimes it is not.

Check out the merch store and get your Debug Compile Once in a While mousepad. Check out my website if you want to learn lots more about Microsoft Access, Excel, Word, all the other things I teach, and ASP.

That is going to do it for today, folks. I hope you learned something. Live long and prosper, my friends. I will see you next time.

TOPICS:
Opening old MDB files in newer versions of Access  
Options for converting MDB files to ACCDB  
Limitations of importing from MDB files in Access 2013 and later  
Using third-party services to convert older database files  
Running older Access versions in virtual machines for file conversion  
Table versus query as form or report record sources  
Placing calculations in a query versus a form  
Using Access and SendKeys to fill in PDF forms  
Troubleshooting issues with spaces in SendKeys when filling PDFs  
How to troubleshoot and delete buttons from a switchboard form  
Finding and modifying macros or VBA for switchboard buttons  
Managing printer selection for Access reports across multiple computers  
Using Application.Printers to list available printers in Access  
Solving syntax errors from extra spaces in Access query expressions  
Origins of attachments and multivalued fields in Access  
Problems caused by using attachments and multivalued fields  
Best practices for relational tables and combo box value sources  
Differences between mapped network drives and UNC paths in Access  
How Access translates mapped drive letters to UNC paths  
What happens when you rename text boxes bound to fields in Access  
Difference between Me.FirstName and Me!FirstName in Access forms  
How to force form controls to reflect field changes  
Use of the macro recorder in Word, Excel, and PowerPoint for VBA  
Using Seek with table-type recordsets for efficient lookups  
Limitations of the Seek method versus DLookup alternatives  
Pros and cons of using saved queries versus SQL in form record sources  
Benefits of distributing ACCDE versus ACCDB front ends in split databases  
Troubleshooting error 3734 database lock issues in split Access databases  
Risks of uncompiled front ends and accidental design changes  
Impact of lock files on Access multiuser environments  
Role of antivirus and file sharing services in Access database problems  
Naming conventions for single quote, apostrophe, and double quote  
Keyboard character differences in programming and grammar contexts

COMMERCIAL:
In today's video, we're discussing a wide range of viewer questions and comments, including how to deal with old MDB database files in modern versions of Access, whether you should set your form's record source to a table or a query, the myth of "hot" and "cold" lottery numbers, common printer issues in split databases, why you might run into syntax errors when querying, and handling tricky fields like attachments and multivalued values. We'll also talk about the importance of naming conventions, using the macro recorder in Word and Excel, classic ASP, and the real story behind single and double quotes in Access programming. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.