Quick Queries #54
By Richard Rost
3 months ago
View Users in Split DB, Merge Columns in Combo Box
In this Microsoft Access tutorial I will show you how to identify which users have a split database open by checking the lock file, explain why combo boxes display only one field when closed, demonstrate methods for hiding tables in the navigation pane, disable default shortcut menus, and discuss troubleshooting Access crashes with recent updates. I will also answer questions about using unbound combo boxes to temporarily store values for later use.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, split database, backend lock file, laccdb, identify users, navigation pane, accde file, hide tables, custom right click menu, mouse down event, shortcut menus, ribbon customization, combo box multiple columns, query concatenation, system log, access crash troubleshooting, automation with Word and Excel, unbound combo box, retain value
Transcript
Welcome to another Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Quick Queries are basically a bunch of questions and comments and stuff that get posted on my website and on my YouTube channel. We just go over them all and have fun. So let's get to it.
We got a question from N269: On a split database, can you determine if anyone is using the split data?
Enjoying these extremely interesting nuances to Access. Thank you.
Yeah, actually you can. If you want to see who's currently got access to the backend file - with a split database Access creates a lock file.
Here's my server folder for example. Now I have multiple different databases with different split files in them, but for example one is my 599 CD Sales Tables database that includes, like, the customer table. Actually, now that doesn't include the customer table, that's in this one. This includes some other order information and stuff like that.
But here is a lock file. It's an LACCDB file. If this file exists that means someone has that table open. If you want to see who it is, open it up in Notepad. It's just a text file. Double click on it to open it up. It opened up on my other screen; I'll bring it up here and look.
I can see that, now, it's not perfect, but you can see right there: SERVER ADMIN and PICARD ADMIN. I have it open on my server, and my local workstation's name is Picard and in both cases I'm ADMIN. So you can see who's got it open.
I just logged out on my Picard machine and now if I open it back up again, let's see, there you can see it's just the server now because I logged out of Picard. That's all those files are.
Now, I wouldn't go try to manipulate those files yourself, but if you want to read it, it's text, and you can just look for the machine names. You'll know what user is in it.
In the old days, when Access actually had user-level security built into it, you could assign people different usernames, but now everyone will just show up as ADMIN. So you'll just be able to rely on the computer name that you see in there.
But it's still pretty cool, especially if you know what computer names are allowed on your network. You can open that up and say, hey, if I see Picard in there, I know Picard's in it, and you know who's on that machine.
There are lots of different ways you can do it. I have my own methods of seeing who's in the database, and I cover all of that in my Security Seminar. I show you how to set up username and password logons, lock down your database, control who's got access to what, keep a system log of who did what, all that stuff. So check it out if you want to learn more.
Last month I accidentally posted a video with something different; a Quick Queries was, I think, copying timeclocks or something like that, and one thing I love about you guys is at least 50 people pointed it out to me. So thank you very much. I'm not annoyed at all.
I appreciate it if you find any problems with my videos. Post a comment and let me know, but I would also ask: look and see if someone else posted the same comment first. There's no need to tell me 18 times. It's like that scene from Office Space. If I mess up, I got 15 bosses to let me know about it - 15, or whatever number it was. It sounds like someone's got a case of the Mondays.
Oh, in case you missed it, I recently got myself a copy of the stapler - Milton's stapler - from Office Space. I posted about it in my Captain's Log. If you're not following the Captain's Log, it's just a page on my website where I try to post something every day, or at least a couple times a week - different stuff. It's not all computer related. Some of it is, some of it's Access related, philosophy, science, and whatever else I happen to be thinking about, so check it out. I'll put a link to that down below too.
Next up, Fred Wilde: Why won't Access display multiple columns in a combo box? The wizard can select several fields, but the combo box will not display them. Odd.
Fred, you are literally posting the question on the video that answers that question - the Multiple Fields in a Combo Box video.
I do agree, though, that this should be easier for beginners to figure out. They should make an option in the wizard itself: Would you like to see these as one field when the box is closed?
Normally, if you pull in, let's say, first name and last name, you will only see one of those, like here's last name first. So, when the box is closed, you see one of them. You can see multiple columns when the box is open, or you click on it and open it up, you can see a bunch of stuff.
But if you want those to appear together, you have to use a query before that, which I show you in this video. We use a little query and a little concatenation to put together last name and first name in one field, then you use that query to feed the combo box.
I know it's a couple more steps, and again, I wish that the Access team would make this easier for beginners, but that's what I'm here for - to teach stuff like that. They do not want to put me out of a job. If Access was too easy, I'd have nothing to do all day long, and I like what I do.
Next up, Bright Mends asks: How do I hide only tables without the forms being hidden?
Part of what I try to teach you is that you do not want your users having any access whatsoever to the navigation pane. I think in my simple security video I show you how to hide it, because if they can get in here, they can start doing crazy stuff.
You want to give them an ACCDE file - so it's encrypted. "Execute only" is what it technically stands for, but I call it encrypted. You are going to lock this down, so this is completely hidden and they cannot get in there. You are going to give them access to whatever forms you want them to have access to by making buttons for it on a menu. Again, I cover all this in my videos.
Now, with that being said, you can hide these objects, but anybody who knows how to unhide them can easily unhide them. For example, when I hide a table, right click, Hide in this Group, or right click, go to Properties, and then you can hide it there, and now it's hidden.
But anybody who knows how to go to the navigation options - right click, Navigation Options - they can show hidden objects and now there they are. So it's one of those features that's good enough to kind of hide it from Aunt Sally who does not know Access, but anybody who kind of knows Access can easily figure out where that stuff is.
That's why in my seminars I show you how to lock all that stuff down so they cannot get in there at all.
Next up, Mark wants to know: Is there a way of making the form pop up only with a right click in the location of the mouse as opposed to a control?
Yeah, sure. You would have to disable the default pop-up menu, which is not hard to do, and then just pop up your own control wherever that is. Let me show you.
If you want to disable the pop-up menus for the whole database you can go into Options and then under Current Database you can find, let's see, right here, Allow Default Shortcut Menus, you can turn that off.
But if you want them in most of your database, but you just want to disable them on a particular form, so this thing does not pop up, then you can use a little bit of code in the unload event, for example. All you have to say is Me.ShortcutMenu = False.
Now the right click shortcut menu for that whole form will be disabled, but just that form. So if I right click, nothing happens. I'm right clicking now. But it still works out here.
You can capture it so that, also, you cannot go into design mode in here either now. So you have to come back out here and right click out here in design. That's why I do not like turning it off for the whole database.
Let's say you wanted to have a right click menu of your own for the notes box. You can do it for any box you want, or even the background of the form, but you have to do it that way.
In here, let's bring up this guy's properties and you're going to look for the Mouse Down event. That's what happens when you click either one of the buttons over that field.
Now, how do you know which button it is? There's the Button, there's the Shift, and then there's the X and Y. Button is which button you click on, so here's a little DoRoll MessageBox Button. Left click is one, and right click is two. Four is the mouse scroll wheel button.
Now that we know that, we can say, if it's two, we can just say "If Button = 2," then that's a right click, and just pop up your message, pop up your form, whatever you want to do here. I'll just say MessageBox "Here's your menu."
Shift, by the way, will track if the user is holding down the Shift, Control, or Alt keys, so you can do a Control Right Click. X and Y, obviously, are the coordinates.
Now I can right click and there's your menu. Of course you'd position it up here. There's a lot you can do with it.
I cover building custom right click pop-up menus, like you can see over here: there's Rick's function, Jose's function. It's pretty involved. It does involve using the Ribbon; you have to make basically custom Ribbon stuff. It's part of my series on customizing the Ribbon and the right click pop-up menus.
It's an advanced developer lesson, but if you want to learn how to do that, check it out. You could use that technique that I showed you just to do the same thing, and mix that with the pop-up menu video that you were talking about.
This guy that on a right automatically will pop up over the control. So, yeah, kind of got most of it there.
Honestly, in my 20 plus years of building databases as an Access consultant, I think I had maybe two clients who really wanted a custom Ribbon and custom pop-up right click menus. Most of them just do not care. I have never used it for my own purposes either. I mean, I have taught it and I have used it for clients, but very rarely do people actually need that.
I have started encountering Access crashing as soon as I open any text or Excel file after last week's update. Well, yeah, you have to roll back after that if it's still doing it, even after you've rolled back to a previously known issue sometimes.
From what, and this is purely anecdotal, I have no proof of this, but I believe that sometimes when you install an update, it installs new pieces in there that even a rollback does not get rid of. There are some system level things that might get updated. You might need to completely uninstall Office and then install an older version, because I have had that happen too where it just kind of corrupted things. I would roll back and it still was corrupted, and I knew it was working fine that morning.
But it could also be your database. It could be something changed. I do not know without sitting down behind your computer. I really cannot tell you, that's one of the problems.
So what do we do in a case like this?Well, we will run down my troubleshooter. Start from the top, run down it, and hopefully it will figure out your problem.
There is a video here you can watch that covers most of my troubleshooting steps. I try to arrange these in the order you should try them, from easiest and most likely to fix problems, to the more difficult ones. Some things in the middle you should check, like just rebooting the PC. Sometimes you would be surprised how many times I have had problems that I could not figure out why it was behaving that way. Reboot the PC, give Windows a fresh boot. I do not mean just put it in standby and wake it back up again. I mean do a system restart, and that fixes problems, believe it or not. So give it a try, run down the troubleshooting list. There is a lot of stuff on here to try.
E-tallic guys says, of course, I would like more Word lessons, I mean actual developer lessons where you could teach us how to intensively use Word together with Access. I am planning on making some. I have been wanting to completely redo my Word and Excel classes, and PowerPoint. I have just been so busy keeping up with just doing the Access videos. I need to clone myself, make three of me.
Well, I am going to try and get ahead on the Access TechHelp videos because I am still going to release, even when I start doing more Word and Excel, Access is always going to be my focus. So I am still going to release at least three or four Access videos a week. But I want to start adding in some Word and Excel too. There is a lot you can do with automation between Access and Word and Excel, and lots of cool stuff.
If you want to see more Word and Excel videos, post a comment down below, and the squeaky wheel will get the grease. The more people that want it, the more likely I will be to make a new video on that topic.
Here is an older comment from Shadow Dragon that I missed from a couple months ago. Do you plan on doing something special for Quick Queries 42? Yeah, I missed a golden opportunity there, and yeah, 42, that would have been funny. But I will do something special for Quick Queries 1701 and Quick Queries 2112. So look for something special for those.
If I do one a week and my math is right, 1701 will be in 1647 weeks. So that is 31 years from now. That is the year 2056. We will get there. We will definitely get there. Mark my words.
Like I said before—I think I mentioned this last week—I have to make it at least to see the next Halley's Comet in 2061. I will be, what, 89 years old, 88 years old. I will still be doing this, folks. I am not going anywhere.
I just love it when you guys quote movie quotes back to me. I love it. I am a big movie quote guy, especially from the nerdy movies that I like. Who is your daddy and what does he do? I am a big Schwarzenegger fan.
Back to a real question: Can you think of any examples when you would use the "Remember the value for later use" when building a drop-down box? Yes, all the time.
You are going to store the value in a table or query when you want to pick something and have it stored in the table that is underneath it. For example, in a TechHelp query or TechHelp template, if you have an order, this is a bound combo box because you are going to pick a customer and it stores it in the table.
Now, when might you want to just pick a value and then do something with it later? Well, the perfect example is something that I showed in the member version. You go to your customers, you go to your orders. Down here, we have a combo box where we can pick a product. Now, this is not bound to the table. It just sits there. It is an unbound combo box.
If you want to use the wizard to build it, you would use that option: "Just remember the value for later use." Why? Because we are going to do something with it. We are going to make a button that will add it to the product to the invoice here. That is what this is—it is an unbound combo box and it does not save its value directly in the table, because we want to copy it up here.
The reason why we are doing that is because we have a product table that has products, a cost, a markup, and a unit price, and this cost can change from time to time. So we do not want to store this record in the order, we are going to copy the data up here because this might change; we might even give the customer a better price. But we can pick a product, like a photon torpedo, and add it, and now it is copied up here. So if the original photon torpedo changes, it does not adjust all of your existing orders.
That is when you would use a combo box that does not save it in the table. I hope that answers your question.
That example is covered in the Members edition of the invoicing video, which is the extended cut that you can get by being a Silver Member. You can watch that whole thing—how long is that? That is a pretty long video. Okay Members, oh, sorry about that—that is another hour-long video, where we go through lots of stuff, so it is really good.
With that, we are going to call to a close this, or this court is now no longer in session. What do they say? Court is adjourned at the hour. Court is—the Quick Queries court is adjourned for the week.
I hope you learned something. I had fun. Live long and prosper, my friends. I will see you next time.
TOPICS:
Determining users of an Access split database via lock files
Reading LACCDB lock files to identify active users
Understanding limitations of user identification in modern Access
Displaying multiple fields in a combo box using concatenation
Using queries to combine fields for combo box display
Hiding tables in the Access navigation pane
Limitations of hiding navigation pane objects in Access
Disabling default shortcut menus on forms
Using the ShortcutMenu property to disable pop-up menus
Customizing right click actions with the MouseDown event
Identifying mouse button clicks with MouseDown event arguments
Displaying custom messages or forms on right click
Troubleshooting Access crashes after updates
Basic Access troubleshooting steps
Using "Remember the value for later use" in combo boxes
Examples of unbound combo boxes for temporary selection
COMMERCIAL:
In today's video, we're talking about common Microsoft Access questions from my viewers. We'll cover how to check who is using a split database by looking at lock files, show you why combo boxes only display a single field when closed, and explain how to combine fields using a query. You'll also learn ways to hide your tables from users, how to create custom right-click menus in your forms, and what to do if Access starts crashing after an update. Plus, we discuss practical uses for the "Remember the value for later use" option in combo boxes and touch on requests for more Word and Excel lessons. 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. In a Microsoft Access split database, what file indicates that someone has the backend open?
A. An ACCDE file
B. A LACCDB lock file
C. A TXT log file
D. A MDB backup file
Q2. What information can you typically find inside the Access lock file (LACCDB)?
A. The names of users with detailed usernames and passwords
B. The current Access database version number
C. Machine names and login names accessing the backend
D. All database tables and their structure
Q3. In modern versions of Access, why do most users in the lock file appear as ADMIN?
A. Because user-level security is still operating
B. Because there are only ADMIN-level logins in a split database by default
C. Because Access no longer uses custom usernames for most databases
D. Because ADMIN is short for administrator mode which all Access users must use
Q4. Which of the following statements about hiding tables in the Navigation Pane is TRUE?
A. Hiding tables prevents all users from ever seeing the tables, no matter what
B. Hidden tables cannot be unhidden by anyone, including the database owner
C. Anyone who knows how can unhide hidden tables using Navigation Options
D. Hidden tables are encrypted and inaccessible without a password
Q5. What is the main purpose of distributing an ACCDE file to users?
A. To allow users to modify the design of forms
B. To distribute an encrypted and locked-down execute-only version of the database
C. To give users direct access to the navigation pane
D. To allow users to edit VBA code
Q6. What is necessary to make a combo box display both first name and last name together in its closed view?
A. Change the Row Source Type to Table/Query and select two fields
B. Use a query with concatenation to combine the fields before feeding the combo box
C. Simply select multiple columns using the combo box wizard
D. Set the combo box to unbound and list both fields
Q7. If you want a form in Access to react to a right-click only, which event do you typically use?
A. Click event
B. Mouse Down event
C. Change event
D. Mouse Hover event
Q8. In the Mouse Down event arguments, which value indicates a right mouse click?
A. 0
B. 1
C. 2
D. 4
Q9. What Access feature needs to be disabled to prevent the default shortcut menu from appearing throughout the entire database?
A. Allow Default Macro Actions
B. Allow Default Shortcut Menus in Current Database Options
C. Enable Trusted Documents
D. Enable Navigation Pane
Q10. What is a key limitation of hiding database objects in Access for security?
A. Advanced users can easily reveal hidden objects using settings
B. Objects cannot be filtered once hidden
C. Data in hidden objects is permanently deleted
D. Hidden objects no longer work in queries and forms
Q11. In Access combo box wizard, when would you choose "Remember the value for later use"?
A. When the combo box must be bound to a table field
B. When you want to display multiple columns at once
C. When you want to select a value for temporary actions, like adding a product to an invoice
D. When you want to hide the combo box from users
Q12. Why is it preferable to use an unbound combo box to select a product to add to an invoice, rather than binding it directly to a field in the invoice table?
A. So that product information can be dynamically updated for all past invoices
B. To allow selecting products that change frequently but preserving the selected value at the time of sale
C. To make combo box design easier
D. Because unbound combo boxes are always faster
Q13. What is a recommended first step if Access is crashing after an update?
A. Immediately rewrite all your macros
B. Seek a new database application
C. Reboot your PC to see if the problem resolves
D. Delete all recent data tables
Q14. What is the advantage of using custom right-click pop-up menus and custom Ribbon controls in Access?
A. They always improve database performance
B. They can provide specialized workflows and user interfaces for advanced users
C. They enable users to change table structures
D. They are necessary for all databases
Q15. According to the video, how common is it for clients to need custom Ribbon or right-click pop-up menus in Access?
A. Very common, almost every client requests them
B. Somewhat common, about half of clients want them
C. Rare, only a few clients ever request them
D. Never, no client has ever requested this feature
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-A; 11-C; 12-B; 13-C; 14-B; 15-C
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 take some time to answer a selection of your recent questions and comments about Microsoft Access. Quick Queries are a fun opportunity for me to reply to things posted on the website and YouTube channel. Let's jump in and tackle several interesting topics from this week's submissions.
The first question comes from a viewer asking whether it's possible to determine who is using the data in a split Access database. The answer is yes. When using a split database setup, Access creates a lock file (with an LACCDB extension) alongside the backend database. If this file exists, it indicates that someone currently has the backend database open. You can identify which computers have the file open by looking inside the lock file with a text editor. The file contains the computer names of connected users. Keep in mind that since Access no longer enforces user-level security as it did in older versions, most users will simply show up as ADMIN, and you'll have to rely on the machine name for identification. This method is useful if you know which computers belong on your network. I also cover advanced tracking methods in my Security Seminar, including how to set up your own login system, enforce security, and keep an audit log.
I also want to thank everyone who lets me know if I've posted the wrong video or made an error. I truly appreciate your feedback. Before commenting, though, please check if someone has already mentioned the same issue to avoid duplicate notifications.
The next topic concerns combo boxes in Access. Fred asked why Access will not display multiple columns in a combo box, even though the wizard allows you to pick several fields. This is a common question. By default, when you set up a combo box and select multiple fields (for example, first name and last name), the box only shows one field when it is closed, even though both are visible when it is open. If you want both fields to appear together when the box is closed, you need to create a query that concatenates the fields into one combined value and use this query as the source for your combo box. While it takes a few extra steps, I walk through this process in more detail in my dedicated video on combo boxes.
Another question comes in about hiding tables in the navigation pane without hiding forms. My recommendation is to prevent users from having any access at all to the navigation pane, especially if security is a concern. The best approach is to give your users an ACCDE file, which is a compiled and locked-down version of your database. You control which forms are available through buttons or menus. While you can hide individual objects in the navigation pane, anyone familiar with Access can easily unhide them using the navigation options. Locking down the entire interface is a stronger solution, and I show you how to set this up in my training videos.
Mark asks how to make a form pop up at the location of a mouse right-click rather than from a particular control. This is possible, but it takes a few steps. First, you can disable the default shortcut menu for either the entire database or just a specific form. To capture a right-click on a control, use the Mouse Down event and check which mouse button was pressed. Button value 2 corresponds to the right mouse button. You can then display a message or custom pop-up at the mouse location. Creating sophisticated right-click menus requires working with the Ribbon and writing additional code, which I cover in my advanced developer lessons. In practice, custom right-click menus are rarely requested by clients, but it's good to understand how to implement them if needed.
A viewer wrote in with a recent issue: Access crashes when opening text or Excel files after a recent update. Unfortunately, sometimes even rolling back an update does not resolve new problems, because certain system components may remain changed. In these cases, a complete uninstall and reinstall of Office may be necessary. Sometimes the problem is with the database itself. My best recommendation is to work through my troubleshooting checklist, which I lay out in an accessible video. Start with the simplest fixes, such as rebooting the computer, and proceed to more involved steps as needed.
Another question concerns whether I will produce more developer-level Word lessons or cover Word and Excel automation with Access. This is something I'd like to do when time permits. Access remains my primary focus, but there are many ways to integrate Access with Word and Excel, which I plan to cover in future videos. If you are interested in these topics, be sure to leave your feedback in the comments. The more interest there is, the more likely I am to prioritize those lessons.
A comment from a couple of months ago asked whether I plan on doing anything special for Quick Queries episode 42, referencing a well-loved science fiction number. I admit I missed the opportunity, but I will make an effort to do something fun for episode numbers 1701 and 2112 in the future. At my current pace, that could take several decades, but I plan on sticking around.
Moving on, someone asked about the "Remember the value for later use" option when building combo boxes in Access. This option is handy when you want to select a value in a combo box, use it to trigger an action, or process the selection later, instead of writing it directly to a table. For instance, in my invoicing template, we use an unbound combo box to select products for an invoice. Clicking a button copies the product details into the invoice, rather than directly linking to the product table. This avoids problems if product prices change after an order is placed. The full step-by-step example is included in the extended, members-only cut of that video.
That brings us to the end of this week's session. I hope you found these questions and answers helpful. If you want full video tutorials with step-by-step instructions for everything discussed here, you can find them on my website at the link below.
Live long and prosper, my friends.
Topic List
Determining users of an Access split database via lock files
Reading LACCDB lock files to identify active users
Understanding limitations of user identification in modern Access
Displaying multiple fields in a combo box using concatenation
Using queries to combine fields for combo box display
Hiding tables in the Access navigation pane
Limitations of hiding navigation pane objects in Access
Disabling default shortcut menus on forms
Using the ShortcutMenu property to disable pop-up menus
Customizing right click actions with the MouseDown event
Identifying mouse button clicks with MouseDown event arguments
Displaying custom messages or forms on right click
Troubleshooting Access crashes after updates
Basic Access troubleshooting steps
Using "Remember the value for later use" in combo boxes
Examples of unbound combo boxes for temporary selection
|
