Quick Queries #52
By Richard Rost
3 months ago
GUIDs Across Databases, Calculated Fields in Tables
In this Microsoft Access tutorial I will show you how to handle recent viewer questions on topics like using the new Monaco SQL Editor, best practices for backing up your Access database, creating custom codes in your tables, working with GUIDs for syncing multiple databases, cascading combo boxes, and common troubleshooting with split databases. We'll also touch on choosing the right controls, safely automating tasks, and tips for keeping your Access projects organized and secure.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, Monaco SQL Editor, query design error, classic SQL Editor rollback, breaking up complex queries, query based on query, database backup Google Drive, Google Drive version history, calculated fields queries, custom product codes, GUIDs replication IDs, synchronizing databases, Edge browser control, web automation VBA, ActiveX tree view, cascading combo boxes, database splitting, accounts payable, general ledger, captain's log, Active Directory security, fitness database series
Transcript
What's a Quick Queries video? Well, I sort of take all the questions, comments, and posts and stuff sent in over the last couple of weeks that do not necessarily need a video by themselves. I put them all together, and that's called Quick Queries. Let's see what we have today.
First up, today, a couple of comments from the forums on my website. Peter says, all of a sudden I am getting an error message when I try to run a query in design mode: executing selected SQL text is only supported in the Monaco SQL Editor.
Now, this happens when you try to highlight and run only a part of an SQL statement in SQL view. Access does not support that. You have to run the entire query. Save it, close it, open it.
Monaco is a new SQL Editor that does allow running a selection of the text, just like SQL Server does, but it is still buggy. Personally, I am not even using it yet.
So if you want to roll back to the older version that has the classic old Editor, you will find instructions in this video. In the meantime, if you are working with longer complex queries, try breaking them up into smaller saved queries, then build on them incrementally, like I show in my videos.
You can make queries that are based on other queries. You do not have to try to cram it all into one big query. So the Monaco Editor is coming along. It is looking nice from what I have seen online, but I am still not using it in my development databases or in my classes yet because there are still some bugs they have to work out. Just like all software, that is just part of the nature of the game.
Next up, John is using my Access updater and backup templates successfully. He wants to know how he can back up his admin front end, which is located on his local PC, without having to push it out to the other front ends so that it gets picked up in the backup form.
Now, personally, as I replied here, I use Google Drive for that. You can set up your database folder to be backed up by Google Drive's backup feature, and that way, it will just copy it up to the web. It also retains version history, so if you realize that you messed something up two days ago, you can still go back a couple of versions and grab that copy of your database.
Now, I always stress that you should not run a shared Microsoft Access database out of a shared drive folder like OneDrive, Dropbox, Google Drive, or any of those. Do not share your database there and expect people to run that database. No.
But as far as your local dev copy, the copy that you build on before you push it out to your network, you can have that folder backed up by Google Drive. If you want to learn more about setting up Google Drive, I cover it in this new PC setup video. It is in one of these.
In here, I cover a whole bunch of stuff. This is four years old, I am setting up a new Windows 10 PC, but it is all the same stuff for Windows 11. Right here is Google Drive, Google backup and sync, Chrome Remote Desktop, I still use all of this stuff today.
Next up, Sehan is building a database and has a products table. He wants each product to have a unique code made by concatenating three text fields, like category, subcategory, sequence, like he has listed out there.
Now, as I said here, I strongly recommend against calculated fields in tables. I do not like them. I suggest you use a calculated field in the query instead. Do not build relationships on that calculated field.
Instead, use autonumbers for your keys and then build your product code via the query. Or you can make your own product code and save it in the table, but do not use that as your primary key. You can have that as a separate identifier.
In fact, in my customer codes video right here, I explain how to do that. If you want to make your own custom code because you do not want your customer seeing that they are customer number four, for example, this video shows you how to make your own custom customer codes.
Because then you run into the German tank problem, where the customer can see he is customer ID 15. There are lots of different workarounds, and these videos will explain a lot of that for you. But do not use calculated table fields. They are just bad news. Trust me.
Next up, by email, Rick, cool name, asked me, I am merging several old databases. If I assign the GUID to each person, should I copy that into the related tables to track the same person across systems?
All right, for those of you who do not know what a GUID is, it is a globally unique identifier, and it is a big, long number text string that looks like that. It is basically used so that if you were to assign any two items a unique identifier, the chances of you actually duplicating it in realistic use are one in billions. I covered the actual math in this video.
Microsoft Access does work with them. They are called replication IDs. You can set them in the table properties, and again, this video goes over the differences.
But basically, if you are working with just one Access database, I would say no, use an autonumber. It is simpler, faster, and smaller. But if you are working with separate individual databases, that is where using a replication ID, a GUID, actually makes sense. It gives you a globally unique identifier you can use across all those different databases.
So you generate it once for each person, then you can use that ID in each system. That way, if you ever want to consolidate or sync those databases later, or bring them all together into one database, you know that you are dealing with the same customer because they have the same GUID.
So yes, that would be one of the few scenarios where I would recommend using GUIDs. Just be careful with how you copy and generate them. Do not let your multiple databases generate their own versions of the GUID.
So make sure if you have got John Smith at 123 Main Street, and it is the same John Smith at 123 Main Street in the other database, you want to have the same GUID. You still have to copy and paste it between them, basically.
Usually you use GUIDs for synchronizing remote databases. Let's say you have got two people with their laptops. They travel a lot, and they do not always have internet access. So they have got a list of customers. Let's say they are entering contacts for those customers, or even orders.
Agent A adds in a bunch of contacts and Agent B adds in a bunch of contacts, or orders. That way, when they come back to the office and they synchronize their databases, they can import all their new stuff, and their autonumbers will not conflict with each other.
If your contact table is on autonumber 100, and they both separately add a new record in their disconnected databases, they will both get 101. See, so that prevents that situation.
So that is usually the only time I really recommend those, when you are dealing with offline databases that have to be synchronized. But yes, they are pretty cool. Watch that video for more details.
This one goes back a little bit to my video I did about Quora and how they just completely destroyed my little Access community that I started there. It only had a handful of users, but still, they just got rid of it for no reason.
Luca posted a comment that said, early on, I came across a helpful article that advised against investing yourself heavily in platforms. They called working on platforms digital sharecropping. You are tilling someone else's soil, I get it.
I had the same exact problem with Facebook. I spent years building up a little, it happened to my Excel page. I had a Word page, an Excel page, an Access page, and an Access group. Out of nowhere, randomly one day, Facebook was just like, oh, your Excel page, it is gone. Bye.
There is no one to contact. There is no one you can get a hold of there. So I do not really try to invest too much in any one social platform. The exception, of course, being YouTube.
I love YouTube. YouTube has always been very good to me. I have almost a quarter million subscribers now and all of you guys, that is probably where you find me. YouTube actually gets back to me if I email with problems and questions. There is actually a live person there.
So I love YouTube and I will always continue to invest in YouTube. But other social media platforms, no. The only other place I really post stuff regularly that is not automated is my own website. I have some automations where, when I post things to my website, they get copied over to Facebook and Twitter. I refuse to call it X, and those kinds of things.
But pretty much, if I cannot automate it, I do not even bother anymore with other social media platforms. I tried putting video on TikTok, but that is short format. It just does not work. People are just scroll heavy. It is hard to do quality content like I do in short little bursts. The 10 minute TechHelp videos are about as short as I can go and still get a point across.
Anyways, I agree 100 percent. Thank you, Luca, for the comment.
Next up, Black Russian says, after watching your videos, I am confidently saying I am no longer scared of MS Access. Awesome. Do not be scared. It is not that hard. It is just different.
It is not like Excel or Word where you can sit down and just start doing it and it makes sense, is intuitive. Access is not naturally intuitive because you have to put work into designing stuff and setting stuff up first.
Then you can make it intuitive once you understand what you are doing. But it is just a different, what is the word they use in business, it is a paradigm shift.
But yes, the more you learn, the more comfortable you will get with it. Trust me, it is not hard. It is not rocket surgery.
OutBy50 is talking about my Edge browser control videos. You are saying the other issue with emphasis is that any programming that depends on somebody else's page staying the same so you can programmatically scrape it or interact with it, fill in forms, is very fragile.
Yes, I agree, 100 percent. The page owner may just change it because they want to be different, and some sites actively despise people scraping and will muck with their pages just to break you. Yes, 100 percent.
Your bank only shows your account balance because they need your reason to attract you to the pages, see their ads and offers. Oh, I do not know about that. I do not see a lot of ads on my bank or credit card sites, maybe sometimes. But, yes, everything you said is definitely true.
I have automated scripts that I use, whether it is using the Edge browser control, or in a different video I show how you can launch your web browser, go to a page, move around and fill in forms, and use SendKeys. I know I hate using SendKeys, but it is the only way you can really interact with something outside of Access sometimes, if it is not like Word or Excel that you can automate with VBA.
So I have scripts that I use, like when I log into my bank website. Once I am logged in, because a lot of them prevent you from automating your login, then I can click a button and it will read the page text and figure out my balances and all that stuff. But you are right, they make changes once in a while and they screw with it.Even YouTube, believe it or not, I've got a script that does the same thing. I prepare my video and then I click one button, and it fills in all the information on the YouTube upload page. So I don't have to manually type all the keywords, the description, the title, and all that stuff.
Once in a while, they'll just make a tiny change, like they'll add a little help button or change the tab order slightly, and it messes up my script and I have to revise it. I'd probably say maybe once every three or four months. But it still saves me time versus having to type all that in manually.
So you just have to watch it and make sure it's doing the right thing. As soon as you realize it's off, I have a big abort button there I can hit. When I notice the script doesn't follow the same instructions that it needs to follow, I hit the abort button fast and you can just go back and fix it.
So yeah, you have to be careful doing any of this kind of stuff. Whether you're reading or writing data, you have to watch because they change their interface and you're just done. Not everybody has a nice, easy API that you can work with, so you have to resort to these tactics sometimes.
Once in a while, I get a comment from someone that wants to join but they don't see that blue join button, and that's because YouTube doesn't offer membership in every country around the world. If you're in a country that doesn't have the YouTube join button right under my videos, then you might have to sign up directly on my website. There's the link right there, and I don't have as many restrictions on my website.
For Wago, this class was fun, especially with the record set clown. Clown, there's a record set clown too. Does he do parties? I love him.
Oh, you mean the record set clone? The clones. Oh, okay, I got it now. Ha, ha.
For the rest of you, if you're not watching my fitness database series because you don't care about fitness, you're missing out on a lot of cool stuff. Fitness is just a backdrop. I'm putting all kinds of stuff in this database. Really cool techniques and stuff.
In fact, I'm getting ready to make a video just to tell people if you're not watching this fitness series, you're really missing out. So go check it out. What are you waiting for? But yeah, the record set clown, I love that. Ha.
Another comment on that from Jimbo, loving the series, keep her common. I'll keep her common.
Really sad that tree view control is not a native control. Yeah, it's an ActiveX control. You have to use it. I don't like using outside stuff, mostly because when you deploy it to other people's machines, they have problems sometimes. If their path and structure and versions are not all exactly the same, it'll break it. I try to keep everything right inside of Access.
If it's just for you, something that you're only using on your PC, then don't worry about it. You can use ActiveX controls just fine. It's only a problem if you have to distribute it to other people.
For those of you wondering, I do cover the tree view in Microsoft Access Developer Level 20. If you guys really, really, really want to see a quick video on using tree view, I might be convinced, as the word I'm looking for here, to do a quick TechHelp video on it. Post a comment down below and let me know that you want to see the tree view control.
Oh, and if you don't know what the tree view control is, it's just a thing. You're going to have people with people on the room like managers and subordinates and all that kind of cool stuff. It's a tree.
I just wanted to give a quick shout out to Steven. Hope your recovery from surgery goes well, and I'm glad that I can entertain you with my videos while you're in the hospital. Get better soon.
As Mark B says, can I add a sub list when a particular text value is chosen from a drop down, a combo box? In fact, I did the video, titled it drop down, because everyone calls it drop down. How do I make a drop down in Microsoft Access? It's called a combo box in Microsoft Access, but that's why I made it, I call it drop down. But how do I make a sub list so the user can narrow down their selection even further?
Well, just like drop down is called combo box, all you need to know is the right terminology. This is called a cascading combo box, and I've got several videos on cascading combo boxes. That's where, for example, you pick a state, and then the city combo box will only show cities that are in that state. You can, as you say, narrow down the selection even further. Well, there you go. That's what it's called.
I would say so many times when people ask questions, I would bet that if you just knew the right terminology, you could probably find it with a YouTube or a Google search if you knew it was called cascading combo boxes. That's why education is so important, so you learn the terminology too.
It's like in Access. People are saying, well, I need to create a form that people can fill out. They want to print it out. If you know that the proper terminology is, you need to create a report that you can print out that people can then fill out in your office. See, it's just learning the terminology.
A lot of people, they see that in Access Beginner 1, I go over some basic terminology, or even in my Intro to PCs class, I go over basic terminology. This is a mouse, this is a keyboard. You'd be surprised how many people just don't know basic definitions. That's why you have to start with that stuff.
Next up, Project One says, I split my database, but Access is still creating multiple files for both the backend and the frontend for multiple users. That means all the changes made by individual users are stored in separate copies of the database file. I'm assuming you mean data. Can you advise how to stop Access from creating separate copies of the database file for every individual user?
It shouldn't. If you've split it correctly, you're going to have one frontend database file that's got your forms and reports and your code in it. For each user, they'll get their own unique frontend file that goes on their individual C drives, their local hard drives. They will all be linked to one backend file. Everyone works off the same backend file that's got your tables in it. That way, you're all sharing the data, but you will have your own individual frontend.
I would suggest starting over with a single copy of your database and resplitting it. Then you just copy that frontend file. This is the one with the tables in it, sitting on your server, and these are the individual user workstations with the frontend files. They should all ideally get ACCDE files, the ones that you can't design. If you'd like to learn more about it, I've got a whole seminar just on splitting your database. This covers all the other questions people sometimes ask, lots of different topics covered in this one. I go over it in a lot more detail.
Next up, we're going to do some chips if it's, yeah. I don't know how to pronounce it, I'm not even going to try. Ha ha ha.
Talking about my accounts payable TechHelp database, he says is it a good idea to consolidate payments like this on the transaction table or to have multiple payment tables when making general ledger to maintain a cash flow balance? That's completely up to you and how complicated you want to get with it.
Obviously, in this TechHelp database, I didn't go into making a full general ledger. This video is literally just about payables. It's putting in your bills, knowing when they're due, knowing when you've paid them, what your terms are. You could very easily mix this in together with another database or build onto this one and do accounts receivable too, and then do a general ledger, and then every plus as a minus and all that good, handy accounting stuff. That's up to you. Payables was just the focus of this video, so that's all I built into this database.
I do also have a full Access payables seminar that goes into a lot more detail. I still don't do a full general ledger in this one, though, as again, it's focused on just payables. I cover accounts receivable in my full Access developer course because it's more aligned with running your business as far as issuing invoices and making sure your customers pay you, the other side of the coin, and then payables is covered in this video. If you want to tie them together into a general ledger, absolutely. Once you have both things built, it becomes really easy too.
Again, if you guys want to see me put together a video on running a general ledger, let me know. Post a comment down below.
All right, so that's going to about do it for this week. I want to remind everybody to check out my captain's log. That's where I post my daily musings, whatever I happen to be thinking about that day. I like to write something every morning. It gets my brain started, even if it's not necessarily a computer topic. I just like to get the juices flowing.
Over the past week, I talk about how GBT just released a version you can run offline. You can actually set up a computer in your office that's got your own GBT on it. Check that one out.
There's an article on investing in your crew. In other words, employee training is very important. Make sure that you're appreciating your employees that want to learn more so they can do their jobs more efficiently.
The leading inconvenient data, no, not this data, this data. I've covered this in videos before too. Don't delete old data, just archive it.
I've done a bunch of articles on logical fallacies. For example, discarding an idea as a good idea just because it comes from a questionable source.
Quick mention of some Active Directory security. Access MVP Adrienne Bell put together a video with accessusergroups.org. If you're a large organization that uses Active Directory, you can access that security from inside your Access database.
Don't give up on understanding. Talking about people who believe there are literal gremlins in their computer or in their Access database, it just decided not to work today.
Five stars for whoever pays the most. This one's a scathing review of the ELP because they recently took down one of my reviews that I put up about a company that just blatantly ignores no soliciting signs when they go door to door trying to sell their stuff. So I had some things to say.
One of the shows I love, and it only got one season, at 10 years old already, is The Grinder with Rob Lowe. He's got some crazy things that he does, but he's got a saying. People will say, we can't possibly win this case, and he'll be like, what if we could? It's for comedy but it makes you think, what if you could? So that's an article that talks about that.
Finally, if you're dealing with rude people, just calmly ask them, why would you say that? Why would you say that? It makes them reevaluate what they just said and if they're being rude, they have to justify their rude behavior. Check that out too.
That's all covered in my captain's log, links right there.Make sure you stop by the homepage to see what's new. Mostly just fitness videos this past week, but again, the fitness videos have lots of regular Access content to them. It is not just all fitness stuff.
And of course, make sure you stop by and check out the merch store. Get your hats, get your sweatshirts, get your whatever else I got on there. I do not even know, maybe teddy bears. Now, when they have penguins on there that I can put my logo on, then I'll start pushing the penguins. I love penguins.
But that's going to do it for today, folks. That's your Quick Queries number 52. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It is a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that Show More link right there. YouTube is pretty good about hiding that, but it is there, just look for it.
Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It is over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on, and did I mention it is completely free?
And if you like Level 1, Level 2 is just one dollar. That's it. And it's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses, all of them from every subject. And you get one free advanced or developer class every month after finishing the expert series.
And you can become a Diamond Sponsor and have your name listed on the sponsor page on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by accesslearningzone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Monaco SQL Editor vs classic SQL Editor in Access
Breaking up complex Access queries into smaller queries
Backing up Access databases using Google Drive
Using Google Drive's version history for database backups
Concatenating text fields to generate product codes
Avoiding calculated fields in Access tables
Using autonumbers as primary keys in Access
Using GUIDs for merging data from separate databases
Replication IDs in Microsoft Access
Synchronizing disconnected Access databases with GUIDs
ActiveX tree view control in Microsoft Access
Distributing Access databases with ActiveX controls
Cascading combo boxes (dependent drop downs) in Access
Proper use of Access terminology (combobox, report, etc)
Frontend and backend structure after splitting Access databases
Troubleshooting multiple frontend/backend files after splitting
Consolidating payments in transaction table for payables
Limitations of the accounts payable sample database
Automating web page interaction using Edge browser control
Fragility of browser automation due to page updates
Scripting website interactions with SendKeys
COMMERCIAL:
In today's video, we're discussing a bunch of great viewer questions and comments from the past couple of weeks in Quick Queries 52. We'll cover issues with the new Monaco SQL Editor in Access, strategies for backing up your database with tools like Google Drive, the right way to generate unique product codes and why calculated table fields can be trouble, using GUIDs for syncing data between separate databases, and the risks of depending on outside platforms like Facebook for your tech communities. We'll talk about making your Access experience better with handy tips on splitting your database, tree view controls, cascading combo boxes, and lots more, with a little humor sprinkled in. You will also get a peek at the latest articles and updates in my Captain's Log and learn about channel memberships, resources, and where to get help with your Access projects. 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 main purpose of a Quick Queries video?
A. To answer a collection of questions and comments that do not need their own video
B. To review Microsoft Access updates
C. To demonstrate full-length training tutorials
D. To showcase new Access features in detail
Q2. What is a limitation of running partial SQL statements in Microsoft Access Design mode?
A. Partial SQL statements can only be executed in the Monaco SQL Editor
B. Access only allows one query to run at a time
C. Design mode does not allow saving any queries
D. You must run queries from the command prompt
Q3. What is recommended when working with complex queries in Access?
A. Break them into smaller, saved queries and build incrementally
B. Combine all steps into one large, complex query
C. Use the Monaco SQL Editor regardless of its stability
D. Always use macros for query execution
Q4. Why should you avoid running a shared Access database out of a cloud-shared folder like Google Drive or Dropbox?
A. It can cause database corruption and multi-user issues
B. Cloud folders do not allow file versioning
C. Access databases cannot be opened from cloud folders
D. It increases your internet bandwidth usage significantly
Q5. What benefit does Google Drive backup provide for your local Access development database?
A. File version history so you can restore earlier versions if needed
B. Free unlimited storage for Access databases
C. Allows real-time collaboration for multiple users
D. Automatically repairs corrupted databases
Q6. When should you use calculated fields in Access tables, according to the video?
A. You should avoid using calculated fields in tables and use queries instead
B. You should always use calculated fields for IDs
C. Only use calculated fields for primary keys
D. Calculated fields are required for building relationships
Q7. What is the recommended method for assigning a unique identifier to each record when merging databases that need to stay in sync?
A. Use a GUID (globally unique identifier) as a replication ID
B. Always use an autonumber across all databases
C. Assign sequential numbers manually
D. Use the customer's email as the unique key
Q8. What scenario makes GUIDs especially useful in Access databases?
A. Synchronizing data across multiple, disconnected or remote databases
B. Storing images directly in a table
C. Enforcing referential integrity between tables
D. Preventing null values in text fields
Q9. Why is building a community or resources only on third-party platforms risky, according to the video?
A. The platform can remove your community or content without notice
B. It guarantees higher traffic than your own website
C. There are always better moderation tools
D. It provides more flexibility with your content
Q10. Why is the Edge browser control or web automation considered fragile?
A. Changes to the target web pages can break your automation scripts
B. The Edge browser is not supported by Windows
C. Access does not allow any form of automation
D. Web automation always bypasses security restrictions reliably
Q11. What is a cascading combo box in Microsoft Access?
A. A combo box whose available choices depend on the selection in another combo box
B. A drop-down that opens automatically when a form loads
C. A combo box with multi-select enabled
D. A combo box that displays images alongside text
Q12. When splitting an Access database, what is the correct file arrangement for multi-user environments?
A. Each user gets their own frontend file linked to a single shared backend
B. Every user should have copies of both backend and frontend on their PC
C. All users share a single database file over the network
D. The backend must reside in each user's home folder
Q13. Why can using ActiveX or non-native controls cause problems when distributing Access databases?
A. They may fail if recipient machines have different configurations or versions
B. They double the database file size
C. They automatically update themselves to the latest versions
D. They are only compatible with cloud databases
Q14. What should you learn to find more resources and support using Access or other software effectively?
A. The correct terminology for features and concepts
B. The history of the application
C. The operating system source code
D. File system management
Q15. What is the captain's log mentioned in the video?
A. A regularly updated section on the presenter's website with daily thoughts and articles
B. A SQL logging tool
C. An Access error reporting feature
D. A cloud-based note-taking app
Q16. Why is it important not to delete old data but to archive it instead?
A. Archived data can still be retrieved if needed in the future
B. Deleting data will improve performance indefinitely
C. Old data must always be visible to all users
D. Archiving data doubles storage space
Q17. What is a commonly recommended way to help users narrow down selections in forms, such as choosing a specific city after selecting a state?
A. Use cascading combo boxes
B. Use option groups
C. Use pop-up message boxes
D. Use a filter on the report only
Q18. What is a main advantage of having membership levels on the Access Learning Zone YouTube channel?
A. Members get access to extended videos, sample databases, code vaults, and free classes based on their membership tier
B. Membership gives editing rights to all videos
C. Members can directly upload their own content
D. Only Diamond sponsors can comment on videos
Q19. Why is knowledge of basic terminology taught early in Access Beginner courses?
A. Understanding terminology enables better searching, learning, and communication about features
B. It is required by Microsoft certification
C. It shortens installation times
D. You need to know it to use VBA
Q20. What is the primary difference between the focus of the presenter's payables seminar and his developer course?
A. The payables seminar focuses on accounts payable; the developer course covers accounts receivable and a wider range of business processes
B. The payables seminar covers marketing features
C. The developer course is only for fitness databases
D. The developer course only covers SQL programming
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
In today's Quick Queries video from Access Learning Zone, I am going to tackle a variety of questions and comments that have come in over the past couple of weeks. These topics are generally not quite big enough for dedicated videos, but they are worth exploring. Let's go through them one by one.
Let's start with an error message some of you have seen in Access while running queries in design mode. The error says that executing selected SQL text is only supported in the Monaco SQL Editor. This occurs when trying to highlight and run just a portion of an SQL statement in SQL view, which Access does not support – you can only run the entire query. If you want to go back to the classic SQL editor, I do have a video with instructions on how to revert. For those of you working with more complex SQL, consider breaking your big queries into smaller, saved ones that build upon each other, just as I show in my tutorials. Monaco is a newer SQL editor that aims to let you run code selections like SQL Server does, but it's still a bit buggy, so I am not using it for my development databases or in my classes yet.
Next, John has been using my Access updater and backup templates successfully and asked how to back up his development copy of the front end database, which is stored locally, without pushing it to all users. I suggested using Google Drive for automatic, versioned backups. You can set up your local folder to be backed up by Google Drive, giving you easy access to previous versions if you ever need to revert. I must stress, though, that you should not run your shared Access database out of a cloud-synced folder, such as Google Drive or Dropbox, because it will almost always result in problems. This method is only for your local development copy. I cover details for setting up Google Drive in one of my PC setup videos, which is still relevant whether you are using Windows 10 or Windows 11.
Someone else, Sehan, is working on a database where each product needs a unique code formed by combining three different text fields. I strongly advise against using calculated fields inside tables for this purpose. Instead, generate your codes using a query, not inside the table structure itself. Autonumbers should serve as your primary keys, while your custom product code can be created in a query or stored separately. Do not build relationships on calculated table fields. For those wanting a detailed guide on making user-facing codes like customer identifiers, I have a video about generating your own codes for things like customer IDs, solving issues like the so-called "German tank problem" where sequential numbers reveal internal details.
Rick, who has been merging old databases, asked if he should assign and carry GUIDs (globally unique identifiers) across tables to help match people between systems. For a single database, using an Autonumber as your key is easier and takes up less space. But if you are synchronizing between separate databases, using GUIDs makes sense since it nearly guarantees uniqueness across systems and simplifies future data merging. It is important that you manage and copy these IDs correctly between databases. This approach is most useful in scenarios where multiple users are offline with their own database copies, then need to sync them later, preventing data conflicts.
There was also a comment that talked about the dangers of building a following or investing too much in platforms like Quora or Facebook, since they can remove your group or page at any moment without warning. I have experienced this myself. This is called digital sharecropping, meaning you do not truly own your space on these platforms. The only real exceptions for me are YouTube and my own website. YouTube, so far, has been good about communication and reliability, which is why I continue to invest there. For most other platforms, I only bother with automated posting. Quality content, especially longer-form tutorials, fits poorly on platforms like TikTok that cater to shorter videos.
On a more encouraging note, a viewer named Black Russian commented that after watching my videos, he is no longer intimidated by Microsoft Access. I am glad to hear that because once you get past the initial setup and understand some design concepts, Access really is not that difficult. It is just not as immediately intuitive as Excel or Word, but with a bit of learning, it becomes much easier to use and understand.
OutBy50 posted about the Edge browser control and reminded everyone that automating interactions with web pages can be risky because websites update or change their interfaces frequently. If your code relies on a website's structure for data scraping or autofilling forms, small changes can break everything. I rely on automation myself for things like prepping YouTube videos, but I always keep a close eye out for changes that require me to update my scripts. Not all sites provide friendly APIs, so sometimes these workarounds are necessary, but they are fragile.
From time to time, I get questions about not being able to find the YouTube "Join" button for memberships. This is simply because some countries do not support YouTube memberships. If that is the case, you can always sign up directly on my website, which does not have as many geographic restrictions.
We also had a fun little confusion about the record set "clown," which, after a chuckle, turned out to be a reference to the record set "clone" in my fitness database series. Even if you are not interested in fitness, this series is packed with creative database design techniques and tricks, so I suggest checking it out regardless.
A common request is for a tree view control inside Access. Unfortunately, a native tree view control does not exist; you have to rely on ActiveX controls, which can be problematic when deploying to other computers due to version differences and dependencies. It's fine for personal projects on your own device, but I avoid them for distributed applications. If enough people express interest, I may create a quick video on how to use the tree view control.
Steven, I hope your recovery from surgery is going well, and I am glad my videos are providing some entertainment and support.
Mark B asked how to show a sublist when a particular option is chosen from a combo box. In Access, this is known as a cascading combo box, and I have several videos covering this topic. For example, if you select a state, the next box will only show cities from that state. Knowing the correct terminology is invaluable in finding resources and watching tutorials more effectively.
Project One brought up an issue after splitting a database: multiple copies of both the backend and frontend files being created for different users. If you split your database correctly, there should be one backend file with your tables, and each user should have their own local frontend file linked to that backend. If you are encountering problems, start over with one clean database, split it, and distribute only the frontends to your users, all linking to that central backend file. I cover this entire process in depth in my full database splitting seminar.
We also had a question related to my accounts payable TechHelp database about whether to keep consolidated payments in one table or maintain multiple tables for a general ledger. The answer depends on your goals and the level of complexity you want to introduce. My accounts payable database focuses only on the payables side, but you could expand it to include accounts receivable, and eventually, integrate the two for a general ledger if that suits your business needs. I also have a seminar focused on payables, and if enough people are interested, I may produce a video specifically about general ledger integration.
Before wrapping up, let me remind you about the Captain's Log on my website, where I post my thoughts and write articles daily. Recent topics range from running GPT models offline to the importance of employee training, logical fallacies, and the wisdom of not deleting old data but archiving it instead. I also share stories of dealing with platforms, thoughts on IT gremlins, and a tongue-in-cheek review inspired by a now-cancelled favorite show of mine.
Remember to check the homepage for the most recent content. Even though there is a lot of fitness-related content lately, the fitness series includes plenty of general Access instruction as well. While you're visiting, stop by my merchandise store. I am particularly fond of penguins, and if they ever add penguin merchandise, you'll definitely hear about it.
That wraps up Quick Queries number 52. I hope you learned something new. A complete video tutorial with step-by-step instructions on everything discussed here is available on my website at the link below. Live long and prosper, my friends.
Topic List
Monaco SQL Editor vs classic SQL Editor in Access
Breaking up complex Access queries into smaller queries
Backing up Access databases using Google Drive
Using Google Drive's version history for database backups
Concatenating text fields to generate product codes
Avoiding calculated fields in Access tables
Using autonumbers as primary keys in Access
Using GUIDs for merging data from separate databases
Replication IDs in Microsoft Access
Synchronizing disconnected Access databases with GUIDs
ActiveX tree view control in Microsoft Access
Distributing Access databases with ActiveX controls
Cascading combo boxes (dependent drop downs) in Access
Proper use of Access terminology (combobox, report, etc)
Frontend and backend structure after splitting Access databases
Troubleshooting multiple frontend/backend files after splitting
Consolidating payments in transaction table for payables
Limitations of the accounts payable sample database
Automating web page interaction using Edge browser control
Fragility of browser automation due to page updates
Scripting website interactions with SendKeys
|
