Quick Queries #16
By Richard Rost
2 years ago
Shortest Path, Database on CD, M2M Details, More...
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- Shortest path between multiple locations
- Traveling Salesman Problem
- George's question about driving directions
- Access database from CD-R or DVD-R
- Access database read-write permissions
- Google API for QR codes reliability
- Online QR code generation alternatives
- S. Southcomb's inquiry
- VBA editor procedure viewing feature
- Kevin Robertson's tip on VBA editor
- Cascading combo boxes with single options
- Automatically filling in city based on state
- Adam and Kevin's suggestions on combo boxes
- Limiting date selections in combo boxes
- Preventing Access version branding in message boxes
- Aptos font display issues in Access
- Access Forever's font bug fix in version 2309
- Many-to-many relationships in Access
- Handling relationships with a junction table
- Tracking power of attorney in relationships
- Stephen's question about centering table names
- Access runtime availability
- Free version of Access and 30-day trial
- Microsoft Office subscription options
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, traveling salesman, shortest path algorithm, Google QR code generation, Access database CD-R, Access lock file LACCDB, VBA editor tips, procedure view Access, cascading combo boxes, message box title, Access version 2309, self-joined relationship, many-to-many relationship, junction table Access, Access runtime
Intro In this video, we'll talk about a variety of common questions related to Microsoft Access, including finding the shortest path between multiple locations, issues with the Google QR Code API, running Access databases from CD or DVD, handy tips for the VBA editor, automatically selecting combo box values, and tracking relationships with junction tables. We also discuss recent Access bugs like the Aptos font issue, table title alignment between Windows versions, and options for getting Access for free. Quick tips and solutions from the AccessLearningZone.com community are covered throughout in this Quick Queries 16 episode.Transcript It's about that time again, folks. Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. It's time for Quick Queries number 16, where I answer questions about Microsoft Access that may or may not need a whole video of themselves. Alright, let's get to it.
First up, a question from George about my driving directions video. He wants to know if there's a way to get the shortest path between multiple locations. Remember in this video, I show you how to make a little map where you can send it three, four, five different addresses, and it will draw you a map with directions between them. This guy, a little map here. Now, as far as getting the shortest path between them, yeah, there are algorithms out there. It's called the traveling salesman problem, and that's a whole different level of computer science beyond what I'm going to cover in a TechHelp video. I did some work on this years ago with a C program, with longitude and latitude, but it was pretty complex. So, if you guys are interested and want to see more on this topic, let me know. Post a comment down below. And of course, if enough of you are interested, I'll dig deeper into it. And of course, if you guys have some resources for dealing with the traveling salesman problem, let me know and I'll look into those too.
Next up, S. Southcomb mentioned that the Google API that generates QR codes doesn't seem to be working. It works for me a lot of the time, but not all the time. In fact, here's the page that I just brought up a second ago, and I've got this automatically set up on my website, so it shows the URL down here, the picture, and then right down here is my little image for the Google QR Code API. So, that's not working on that. It does work sometimes. I noticed it was working last night when I did this, when I posted this response. I checked it, and it was working. If you've got it so that you know these QR codes are business-critical for you, there are other paid services available. If you do a Google search, you'll find some that can make QR codes that are very reliable. It works sometimes, it doesn't work. Yeah, I can't control that. But if you guys are really interested, if any of you have a source that you use for online QR code generation, post a comment down below. It'd be interesting to see what you have.
I got an email earlier today from one of my students, David. He said, "I wonder if it's possible to run an access database from a CD-R or DVD-R." I don't believe it's possible. I haven't tested this in a while. I got a DVD drive somewhere in my garage. I don't know where it is. I don't feel like going to get it. But I don't believe it's going to work. I remember trying this years ago, and it doesn't work. See, an access database needs read-write access permission to whatever folder it's in. It's got to create that lock file. That ACCDL file. Or ACCDEL, whatever the extensions are. That lock file. If it can't create the lock file, you're not going to be able to run the database. And of course, you know, it goes without saying that users won't be able to make any changes if it's on a read-only drive. And I'm pretty sure that with a CD-R and DVD-R, they don't read-write like our normal file system does. So I don't know. Give it a try. Let me know if it works. I remember trying this years ago when I used to ship my courses on CD. I remember trying, putting an Access database on it, and it wouldn't load. But who knows? They might have changed it in the last 20 years. But I don't think it will work. But this is one of those, try it and see things. Got a whole video on this one. Before asking me, try it. Give it a shot. See if it works. Take a few minutes rather than waiting for a response from me, or post it in my forums. My guys are a lot faster than I am at answering questions.
And yep, still not working. Questions and yet the network, next up, a great tip from one of my moderators, Kevin Robertson. I'd love coming out, his ideas, great tips, and I've never heard of it. They're working in the VBA editor and you want to just show one procedure, you know, one sub, one function. The button down here has a total of buttons all over Access. I've never even looked at that. Right, was this, was kept, I'd never even seen this before. Alright, if you're in the VBA editor, it's right down here, this little guy, right there. Look at that. Why don't you click right here, click, watch, boom, that's all you see now. And this is great if you've got, you know, tons and tons of code in one procedure, one sub that you want to just not be distracted by everything else. But don't forget that you got that turned on, because then you might forget, oh, where's that, where'd that function go? But that's a cool tip. They sneak a lot of things in. Like I started working with Access way back in version two, which was like 1994, I think. Never used Access 1.0, version two is when I started with it. And you know, obviously over the years, they've added stuff, added stuff, added stuff. So people come up to me, like when they first added layout views and forms. I was like, what is this? Right, layout view, never heard of it before. Of course, I still don't like layout view, but that's a different topic altogether. But thanks, Kevin.
Here's another question from the forums on my website. Lots of good stuff on my forums, folks. If you guys haven't visited my website, check it out. From Samantha, she wants to know if you've got like city and state, and there's only one city in that state from the list of valid options. Maybe you're doing cascading combo boxes. If there's only one option for that state, can you have the city automatically filled in? There are a couple of ways you can do it. One of my moderators, Adam, said you can put this in the after-update event. Count the number of items, and if there's only one, just set the value of the city field equal to that. Kevin, yep, another contributor said you can check the list count property, and again, ignore this little pump. Oh, is it a hide menu option? What? Oh, look at that. Hide menu for this site. Let's hide it from my site. There we go. Because I like to double click on stuff and show things in videos. But yeah, there are multiple ways to do it. Check these out. I will put a link to this select if only one discussion thread in the links section down below. And thanks to Adam and Kevin for your suggestions.
Here's another tip from Kevin. He said that he created a set of combo boxes. You got the month and year, and based on the data in the table, it won't let you go back before a particular date. So, for example, if March 2024 was the earliest record, you can't pick something less than that. And here is a sample of what he means. And here's his code. He put all his code online here. I'll zoom in so you guys can see it. There you go. Again, I'll put a link to this one down below. I've actually had something like this on my TechHelp list for a while, so I might do a video on this one, but I wanted to share Kevin's solution first.
Here's a tip from Jeffrey in my forums, following up to my hide Access videos where I teach you how to make it look like you're not using Access. He also mentions whenever you use a message box in code, it's an optional parameter, but make sure you specify a title. Otherwise, it will say Microsoft Access every time you do a message box. So that's a good point. Keep that one in mind.
Next up is a question about a bug that one of my moderators, Adam, brought up in the forum a little while ago. And he's having some problems with the new Aptos font, displaying kinda weird. I guess I didn't zoom in too much, better there for you. But it's supposed to say "Logged in as," and it's just a little bit of a missing stuff. And I did a little Google searchy search, and the guys over at Access Forever noted this a little while back. And it's been fixed in the latest version, 2309. So if you're having a problem with the new Aptos font, well, I'll put a link to this down below, you can check it out, and thanks to the guys over at Access Forever for catching that one.
Next up is a really good question from one of my students, Clay, and he basically said he's got a table that has people in it, right, and he wants to be able to track who a person's siblings are, and that's done with a self-joined relationship, which I've covered before in detail. In fact, here it is. And look, look, look, look, the QR code's working again. It's hit or miss whether it works or not. Right? Now, my self-joined relationship, we talk about genealogy, and you can make a table of people, and the people can be linked to other people, so you can track relationships. Father to daughter, and mother, and siblings. So Clay wants to track people. So we've got siblings, basically a person table, OK? And he wants to say that, OK, I know what the relationship between the people is. I want to also track who has power of attorney for whom, because some of the siblings have power of attorney over other ones. And so I wanted to bring this up because this just shows you how, when you have a many-to-many relationship, you can use the junction table to track information about the relationship itself. So, in this table, you could put in here what their relationship is. You know, parent to child, that kind of thing, but you can also track other information, for example, whether they have power of attorney or not, and that would just be another field in that junction table. The junction ID, which is their primary key, person one, person two, both of their person IDs, all right? And then any other information that you want to track about this relationship will go in the junction table. So, here are some examples. Your junction table will then have records like this: 1, 2, true. That's John and Jane, and true for the power of attorney, and so on. 2, 3, which would be Jane and Sarah and then false for no power of attorney. And you can put any other information in there about that relationship. So, you know, the date that the relationship was made. Let's say that the relationship is married. And you could put, you know, the two spouses in there and then the date of their marriage. And that would go in the junction tables, information about the relationship. Okay, so that's how I would do that. And that's many to many relationships are tricky in and of themselves, but just keep in mind that the junction table can be where you put information about the relationship. And of course, we've got a nice big long video on many to many relationships, and the barcode is not working again, so sorry.
This one comes up in the forums from time to time. This one, Steven asks, how do you center the table name on top of the table? And he shows a picture like this. This has nothing, you have no control over this. This is, I think, more of a Windows thing than an Access thing. For example, I got the latest version of Access running on the latest version of Windows 11. That's what I use for recording my videos right now. I try to stay up to date, and it's all left-aligned. Every application is left-aligned in Microsoft Office. Same thing, you open up Word or Excel, they're all left-aligned. Now, here's a database I opened up on one of my other machines here in the office. It still has Windows 10 on it, and you can see that the title across the top is now centered in the window. Okay, same version of Office. I have the same version of Office on all my machines, but this machine's still running Windows 10. So I'm pretty sure it's more of a Windows thing than an Office thing, and no, you have no control over this application title. So, and your forms and tables and stuff are gonna be the same way. So, sorry. There might be a registry hack or something out there you can do, but I really haven't looked into it, and I don't see how that's a big deal. If you've got a solution, post it in the comments.
This is one of those questions that gets recycled a lot. I get asked all the time how do you get Access for free. There is a free version of Access available; the best you can do is get a thirty-day trial. It's not expensive, though, folks. You can get it with a Microsoft Office subscription. I think I pay like $8 a month. It's not expensive at all. Of course, if you're developing it for your business and you've got multiple people, only one person needs a paid version of Access because you can use the runtime for everybody else who doesn't need to actually develop the database. So for details on that, check this video out.
Alright, well, that's going to do it for another edition of Quick Queries. Hope you learned something today. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main challenge referenced in the video related to finding the shortest path between multiple locations in an Access map?
A. Calculating the total travel distance
B. Dealing with traffic data in real-time
C. Solving the traveling salesman problem
D. Integrating the map into an Access form
Q2. If the Google API for generating QR codes is not working reliably, what does the video suggest as an alternative?
A. Switching to a different Access version
B. Using a local library for QR code generation
C. Employing paid services for reliable QR code generation
D. Waiting for Google to address and resolve the issue
Q3. Is it possible to run an Access database from a CD-R or DVD-R according to the video?
A. Yes, it should work without any issues
B. It is unlikely to work due to read-write access requirements
C. It's guaranteed to work with Access 2019 and later
D. Yes, but only if the database is small enough to fit on the disk
Q4. Which feature did the video highlight for focusing on a single procedure in the VBA editor?
A. Collapse All feature
B. The Step Into Command
C. Procedure View toggle
D. Breakpoint setting
Q5. What is a junction table used for in an Access database?
A. To increase the speed of database queries
B. To store application settings and preferences
C. To show the graphical representation of data
D. To track many-to-many relationships and information about the relationships
Q6. According to the video, what determines the alignment (left or center) of the table name on top of a table in Access?
A. The alignment is determined by the version of Access
B. It's a customizable setting in Access options
C. It's determined by the version of Windows
D. The alignment is based on the screen resolution
Q7. What option does the video suggest for those seeking a free version of Microsoft Access?
A. There is an unrestricted free version of Access available
B. Access can be used for free with a 30-day trial
C. Free Access is bundled with other Microsoft products
D. Access is always free for educational purposes
Answers: 1-C; 2-C; 3-B; 4-C; 5-D; 6-C; 7-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 Access Learning Zone, I tackle a range of questions from viewers about Microsoft Access that do not necessarily require a full video to answer on their own.
The first question is from George, who asked about my earlier driving directions video. He wants to know if it is possible to determine the shortest route between several different locations. In that video, I showed how to create a map that generates directions for multiple addresses. However, if you want to solve for the absolute shortest path through all those stops, you are venturing into what is commonly called the traveling salesman problem. This is a classic, complex issue in computer science and goes well beyond the scope of a regular TechHelp video. I have experimented with this before in other programming languages using latitude and longitude, but it is quite complicated. If enough people express an interest in learning more about it, I can consider creating additional content. If anyone out there has helpful resources or prior experience with the traveling salesman problem, please let me know and I will take a look.
Next, S. Southcomb mentioned trouble with the Google API that generates QR codes, saying it does not always work. My own experience matches this; sometimes it works just fine, while other times, the image does not load. If reliable QR code generation is essential for your work, you may want to consider a paid service. There are numerous online providers that offer more stability. If you know of a particularly good QR code provider, please share your recommendation in the comments.
Another student, David, emailed to ask whether it is possible to run an Access database directly from a CD-R or DVD-R. I am fairly certain that it does not work, at least as of the last time I checked. Access databases need read-write permission for their folder so they can create lock files; CD-R and DVD-R disks are generally read-only, which means the database cannot function correctly. This is something I tested many years ago when I distributed my courses on CDs, and the database would not load. Technology may have changed, but I doubt this limitation has. Still, if you want to test it yourself, go ahead and let me know how it turns out. For answers on questions like this, it is always helpful to try it and see for yourself, or ask in my forums, where help is often available faster than I can reply by email.
Moving on, moderator Kevin Robertson shared a useful tip about the VBA editor. If you want to focus on a single procedure when coding, the VBA editor has a little button that allows you to display just one sub or function at a time, hiding all the others. This is especially helpful when you have a lot of code and want to avoid distractions. However, be careful to turn this setting off when you are done so you do not lose track of your procedures.
Another question, this one from Samantha, concerns cascading combo boxes for selecting city and state. The scenario is if only one city is available for a given state, is there a way to have it auto-fill that city? Yes, there are a few ways to approach this. One option is to use the after-update event and count the number of available options; if there is only one entry, set the city field to that value automatically. Another way is to check the list count property of the combo box, and if the count is one, fill in the value. Both are effective solutions. For more details, I have linked to the relevant discussion thread on my website.
Kevin had another tip, which involves creating combo boxes for month and year selection that only allow dates as early as the minimum date in your data table. For example, if March 2024 is your earliest available record, the combo boxes will not let you pick an earlier date. Kevin posted examples and code online, and I may do a full video on this technique in the future.
Jeffrey contributed a follow-up to my videos about hiding that you are running Access. He pointed out that whenever you use a message box in VBA, if you do not specify a title, it defaults to "Microsoft Access." To prevent this, simply use the optional parameter to enter your own title for the message box.
Adam, another moderator, reported a display bug involving the new Aptos font in Access, where text would sometimes appear incorrectly. This issue was recognized a while back and has since been fixed in an update. If you are experiencing this problem, updating to the latest version should resolve it.
A student named Clay asked about tracking additional information in a self-joined table, specifically regarding who holds power of attorney among siblings listed in a person table. This situation calls for a many-to-many relationship with a junction table. The junction table should store not only the pairs of people (such as the two siblings) but also details about their relationship, like whether one has power of attorney over the other. You can also add fields such as the date this status started or the type of relationship (for instance, spouse, sibling, parent-child). If you want more guidance on setting up many-to-many relationships, I have an in-depth tutorial covering these topics in more detail.
Another common question that pops up in the forums concerns aligning table names over tables in Access. Some users notice the table title centered, while others see it left-aligned. This appears to be more a function of Windows than Access itself. On Windows 10, application titles sometimes appear centered, while on Windows 11 and with the current Office suite, they are typically left-aligned. There is no setting in Access to change this, and as far as I know, no easy way to modify it.
Finally, I frequently get asked how to get Access for free. Microsoft does offer a 30-day free trial. After that, it is not very expensive to subscribe, and a single paid Office license can serve the developer, while other users can use the runtime version free of charge. For more details, refer to my video on this topic.
That wraps up this edition of Quick Queries. I hope you learned something new today. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Explanation of the traveling salesman problem for shortest path between locations
Discussion of Google QR code API reliability and alternatives
Explanation of why Access databases cannot run from a CD-R or DVD-R
Tip for showing only one procedure in the VBA editor
Automatically selecting items in cascading combo boxes when only one option is available
Restricting combo box options based on available data ranges (e.g., earliest record date)
Specifying a custom title for Access message boxes
Aptos font display issue and its resolution in recent Access builds
Using self-joined tables and junction tables to track relationships and additional data (e.g., power of attorney) between people
Explanation of many-to-many relationships and storing relationship metadata in junction tables
Differences in Access window title alignment on Windows 10 vs Windows 11
Overview of free and trial options for Microsoft Access including runtime version for non-developers |
