Quick Queries #51
By Richard Rost
3 months ago
Cannot Open Database Created with Previous Version In this Microsoft Access tutorial, I answer viewer questions on topics like importing old MDB files into modern Access versions, using tables vs. queries as form record sources, printing to different printers in split databases, form field naming issues, database locking errors, and best practices for working with cascading combo boxes and attachments. I also talk about legacy scripting (VBScript, classic ASP), preference for mapped drives vs. UNC paths, and the importance of proper quote usage in Access and programming. This is part 51. PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, Quick Queries, MDB file, ACCDB format, error opening old database, database conversion, everythingaccess.com, MDB Viewer Plus, virtual machine, VBA code, PDF form fill, SendKeys, switchboard form error, printer selection code, Application.Printers, attachments, multivalued fields, SharePoint integration, lookup wizard, UNC path, mapped drive, splitting database, FirstName text box, ACCDE file, runtime error 3734, lock file troubleshooting, macro recorder, classic ASP, cascading combo boxes, indexed seek, DLookup alternative, SQL in record source, front end back end, antivirus database issue
Transcript
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.
Quiz
Q1. What is the primary difference between MDB and ACCDB file formats in Microsoft Access?
A. MDB files are used only for reports, while ACCDB files are used for forms.
B. ACCDB introduced new features and replaced the older MDB format starting with Access 2007.
C. MDB files support VBA, whereas ACCDB files do not.
D. ACCDB files are compatible with all versions of Access, including Access 2003.
Q2. Which version of Access was the last to allow importing MDB objects directly into ACCDB?
A. Access 2013
B. Access 2010
C. Access 2002
D. Access 2021
Q3. If you need to run older versions of Access to convert MDB files, what is a recommended method covered in the video?
A. Reinstalling Windows every time you need to switch versions.
B. Using a virtual machine with older versions of Access installed.
C. Running Access in Safe Mode.
D. Using Access in compatibility mode only.
Q4. What is the main advantage of using a query as a form's record source, as opposed to using a table directly?
A. Queries can provide calculated fields needed for the form.
B. Tables guarantee better performance.
C. Queries are always faster than tables.
D. Tables allow for more advanced filtering.
Q5. When considering hot or cold numbers in lotteries, what does the video suggest?
A. Hot numbers dramatically increase your odds of winning.
B. Cold numbers should always be avoided.
C. All numbers have equal odds, so hot or cold status is a myth.
D. You should only play the numbers that have not been drawn recently.
Q6. Why is using attachments and multi-valued fields in Access typically discouraged for intermediate or advanced developers?
A. They take up too little disk space.
B. They prevent use of queries in the database.
C. They create design problems and complicate future development.
D. They are faster than traditional relational designs.
Q7. What is the recommended way to address error handling or troubleshooting issues in an Access database according to the video?
A. Always restart your computer.
B. Replace the database with a blank one.
C. Refer to available videos and resources, and gather more specific details about the problem.
D. Ignore small errors unless users complain.
Q8. When printing to a specific printer in Access and the default printer cannot be used, what is a possible solution discussed in the video?
A. Only print from design view.
B. Use Application.Printers to list available printers and allow the user to choose.
C. Print only to the last used printer.
D. Edit the system registry to change the printer.
Q9. If you see a runtime error involving invalid operator or spaces in query expressions, what is the likely cause according to the video?
A. Too many queries in the database.
B. Inappropriate use of spaces or operators in the expression.
C. The database file is too large.
D. The Access version is outdated.
Q10. Why does using ACCDE for front-end Access databases help avoid multi-user issues?
A. ACCDE files are smaller.
B. ACCDE prevents users from opening forms.
C. ACCDE files do not allow users to access design mode, avoiding accidental lock issues.
D. ACCDE files are only for back-end databases.
Q11. What is one main consequence of naming a control (like a text box) differently from its bound field in an Access form?
A. The form will always crash.
B. The control will sync perfectly with the field.
C. Updates to the field may not immediately display in the control unless the record is refreshed.
D. Calculated fields stop working.
Q12. What does the "Me." keyword represent in Access VBA code?
A. The current database.
B. The current module.
C. The current form or report instance.
D. The current table.
Q13. In database design, what is the best practice for storing lists that might be reused in multiple forms?
A. Use value lists everywhere.
B. Place the list in a linked table.
C. Create a separate table and use it in lookups for proper relational design.
D. Store the list in form properties.
Q14. What function or method is fastest for searching indexed fields in local Access tables, but has limitations in broader scenarios?
A. DLookup
B. Seek with table-type recordsets
C. FindFirst
D. SQL Server queries
Q15. According to the video, what is a mapped network drive letter in a split database setup?
A. A printer setup process.
B. The server folder mapped to a drive letter, like Z, on each user's computer.
C. A new file format for Access.
D. A special backup location for reports only.
Q16. Why is the macro recorder recommended as a learning tool in Word and Excel VBA?
A. It writes the most efficient VBA code.
B. It helps generate syntax and understand VBA operations by recording actions.
C. It is available only in Access.
D. It automatically fixes all errors in VBA.
Q17. What is a main reason the presenter avoids offering one-on-one technical help for individual Access databases?
A. He is not familiar with Access databases.
B. It is impossible to provide solutions without seeing the actual database and details.
C. He prefers sending email rather than making videos.
D. He recommends only paid support.
Q18. In terms of Access field and control naming, what reduces confusion and makes maintenance easier?
A. Using entirely random names for each control.
B. Naming the text box the same as the bound table field.
C. Using numbers in place of names.
D. Using reserved words for fields and controls.
Q19. What was mentioned as a possible reason why Access is moving away from VBScript?
A. Lack of popularity in the market.
B. Security concerns leading to deprecation.
C. It cannot be used with forms.
D. It is not compatible with macros.
Q20. According to the discussion on quotes and apostrophes, why does the presenter stick to "single quote" and "double quote" terminology?
A. It is traditional grammar.
B. These terms are less ambiguous when discussing programming and keyboard keys.
C. They sound more professional.
D. He dislikes programming.
Answers: 1-B; 2-B; 3-B; 4-A; 5-C; 6-C; 7-C; 8-B; 9-B; 10-C; 11-C; 12-C; 13-C; 14-B; 15-B; 16-B; 17-B; 18-B; 19-B; 20-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
In today's Quick Queries video from the Access Learning Zone, I am taking a selection of recent comments, questions, emails, and posts from throughout the week and providing answers and discussions for each. This format is an opportunity to address a variety of topics in one place, based on what is currently on the minds of our viewers.
First up, a question from Kim about working with an old Access MDB file. MDB files were the standard all the way back to Access 1.0 through 2003. Trying to open one of these with Access 2021 is no longer supported, as Microsoft dropped this functionality after Access 2010. For a period, in versions 2007 and 2010, you could import objects from an old MDB into an ACCDB format, but that is no longer available in any current version like Access 2019 or newer. Some third-party services, like the one provided by Wayne Phillips at everythingaccess.com, have earned an excellent reputation for converting legacy MDB files. Another tool, MDB Viewer Plus, is out there, although I have not used it personally. Some platforms might offer virtual machines with older Access versions for converting files, but again, I have not tested those approaches. If you keep older versions of Access in virtual machines, that is another viable option for those needing to convert old databases.
Switching gears, I got a fun reminder from United for 22 about the "Save, Debug, Compile, Once in a While" merch, which is indeed available as a t-shirt, mouse pad, and more.
Vera Mann wrote in about lottery numbers, suggesting it is better to show people how to find the most common combinations. I have covered this before: statistically, every number has the same odds, and the hot/cold number theory is just a myth. Over many draws or coin flips, results even out—unless, of course, there is some external tampering, in which case, let me know and we can split the winnings.
Bo Jorge had a question about whether it is always preferable to use a table as the record source of a form rather than a query. Generally, using a table is simpler and sometimes more efficient. However, if you need calculations—like an extended price—you are better off performing those calculations in a query and setting that as the record source.
I also briefly mentioned the ongoing Fitness Database video series. I assure you it is not abandoned if you do not see a new video every day. The next part is coming soon, and when the project is truly finished, I will let everyone know.
Angelus raised a question about sending data from Access to a PDF form, specifically with issues regarding spaces not coming through. In my experiences with automating PDF form filling using Access and SendKeys, I have not run into that issue. I suggest checking if the spaces are actually regular spaces and perhaps experimenting with different characters or introducing a pause between keystrokes to see if it helps. These scenarios often require looking at the specific database to truly diagnose.
Noelle asked about fixing errors in an existing database, saying she gets an error when trying to delete a button from a switchboard form. Without seeing the actual database, it is hard for me to diagnose specific problems, but often the issue lies with underlying macros or VBA code associated with the button in question. I have many videos on troubleshooting and error handling—check my website for details, and hopefully one of those will point you in the right direction.
Yorgan brought up a common challenge: printing to different printers from an Access front end, where users' printers vary and the default printer is not always an option. Access typically sends its output to the default printer unless otherwise specified, but you can programmatically display the available printers so the user can select the appropriate one each time. This involves listing printers and changing the report's destination printer on the fly.
Michael Carter wrote in about a syntax error in a query. Often, such errors are caused by misplaced spaces or quotes. In programming, syntax is extremely particular, so double-check for extra spaces or misused quotation marks.
Out by 50 commented on the origin of attachments and multi-valued fields in Access. These were added to accommodate integration with SharePoint, which already supports those features. While convenient for beginners, storing things like Word documents directly in database fields or using multi-valued fields runs contrary to good database design, and it tends to create problems as your application grows. As always, I recommend sticking to relational table structures and only using value lists in contexts where they are guaranteed to remain static and singular.
I also had a comment about VBScript and the upcoming changes there. VBScript is being deprecated in favor of more secure alternatives, but many related technologies remain for now, including the Windows Script Host and JScript. For web development, JavaScript can accomplish much of what VBScript could, just with a different syntax.
Last week, I mentioned that you need to use Me. to refer to form properties in Access VBA. RTAT1 clarified that it is not strictly necessary—properties can be set directly, and Access will understand. In my teaching, however, I encourage the use of Me. for clarity and to help students differentiate which object they are working with.
Mac Frank argued for using UNC paths rather than mapped drive letters for Access back ends. In my experience, either works, and I personally prefer drive letters. Access converts mapped drives to UNC paths behind the scenes anyway, so functionally, it does not matter much.
George Eos brought up an important nuance when renaming text boxes on a form that are bound to fields. If you rename a text box but keep it bound to an underlying field, changes can still update the field but not reflect on the form's control immediately. To update the display, you may need to refresh the form or reference the correct control name.
Bytebash50 mentioned inheriting legacy ASP sites. Classic ASP still works well for many sites—including my own—proving that there is often no need to switch just for the sake of using the latest technology.
Shadow Dragon talked about using the macro recorder and converting macros to VBA as a method for learning Excel VBA, which I agree is a very practical approach. Even experienced developers take advantage of the macro recorder in Word, Excel, and PowerPoint, since remembering all the different syntaxes can be a challenge. Access, unfortunately, never had a macro recorder.
For more specialized Access support, I recommend checking out the list of consultants on my website, as I myself now focus on teaching rather than hands-on troubleshooting.
B. Miller chimed in on Times New Roman, reflecting fondly on its use in earlier word processors. Fonts like Arial and Times New Roman have stood the test of time, and while preferences change, every font has its place.
Michael commented on cascading combo boxes and preferred using more generic terms like Region or Municipality. As I showed in my video, you can tailor your dropdowns and labels to your audience, whether local or international. The core principles remain the same—customize as needed for your specific requirements.
Xavier brought up the fact that using a table-type recordset and Seek is even faster than the optimized DLookup solution I presented. He is absolutely right: Seek is fast, but limited to local, indexed Access tables—not available for queries or SQL Server back ends. My approach was to trade peak speed for greater compatibility.
Someone else asked about doing away with saved queries and using their SQL directly in form properties. You absolutely can do this, and while saved queries are ideal for beginners and certain scenarios, complex projects sometimes benefit from embedding SQL directly in forms to avoid clutter.
Steve presented a classic multi-user challenge: runtime error 3734 when users have uncompiled front ends (ACCDB rather than ACCDE) sharing a split database. The issue is likely because uncompiled front ends allow accidental design changes and can create locks. The solution is to distribute compiled front ends (ACCDE) to all users and avoid file sharing tools that might interfere with Access lock files. There are many troubleshooting steps for resolving these sorts of problems—check out the resources on my website for a comprehensive guide.
Finally, Jack wrote about the terminology for single and double quotes, apostrophes, and tick marks. In programming, I stick with "single quote" and "double quote" for clarity, given their roles in Access, even though older naming conventions vary. There is always room for a little classic grammar trivia alongside the technical.
That wraps up this edition of Quick Queries. For more in-depth, step-by-step tutorials on everything discussed here, visit my website at the link below.
Live long and prosper, my friends.
Topic List
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
|
