Quick Queries #76
By Richard Rost
14 days ago
Access Security Myth: Can You Really Secure Tables?
In this video, we cover a variety of Microsoft Access topics based on viewer questions, including creating menu searches for part numbers with wildcard filtering, setting up combo boxes that search as you type, managing advanced search forms and filter boxes, handling changes when copying web data, troubleshooting multi-version Access databases in shared offices, customizing templates like Northwind, understanding trusted locations, differences between multi-select drop downs and multi-valued fields, resolving error messages, sequential lottery numbers, working with rich text, and strategies for database backups. We also touch on moving windows with keyboard shortcuts, basic database security, and answer other community questions.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, menu searches for part numbers using wildcard filtering, combo boxes that search as you type, fitness database trick, advanced search forms, filter boxes, Amazon UI changes web scraping, multiple Office versions shared database, customizing Northwind template, trusted locations disabling, multi-select drop downs, multi-valued fields, pound error message troubleshooting, sequential lottery numbers, rich text tables vs forms, backup strategies deleting old backups, extract business card info AI
Intro In this video, we cover a variety of Microsoft Access topics based on viewer questions, including creating menu searches for part numbers with wildcard filtering, setting up combo boxes that search as you type, managing advanced search forms and filter boxes, handling changes when copying web data, troubleshooting multi-version Access databases in shared offices, customizing templates like Northwind, understanding trusted locations, differences between multi-select drop downs and multi-valued fields, resolving error messages, sequential lottery numbers, working with rich text, and strategies for database backups. We also touch on moving windows with keyboard shortcuts, basic database security, and answer other community questions.Transcript Today we're going to talk about that, plus answers to your questions from YouTube, my website forums, emails, and much more, including menu searches for part numbers using wildcard filtering; combo boxes that search as you type, including a cool trick from my fitness database that you should not be skipping; advanced search forms and filter boxes; what happens when Amazon or any website changes their UI when you're copying web page data; dealing with multiple versions of Access in a shared office; why templates like Northwind can be a nightmare to customize; trusted locations and what disabling them really does; multi-select drop downs versus multi-valued fields; troubleshooting the dreaded pound error message; sequential lottery numbers that actually happened; working with rich text in tables versus forms; and backup strategies - when to delete old backups without shooting yourself in the foot. All that and more in today's Quick Queries video.
First off, let's start in the forums today on my website. We got Tim, one of my Gold members. He asked how to make a menu search for part numbers so users can type in a few characters and still find matching records, instead of needing to enter the exact part number. Tim, this is a great question. I get asked all the time - exact match queries are fine for computers, but humans don't tend to type in part numbers perfectly. The best fix is to put a search box on your main menu or sub menu, wherever it is, and use a LIKE wildcard search so users can type in just part of the part number and get matches.
From there, I would open a continuous form filtered by what they type, so they can see the results immediately. Of course, you can double-click on any one of those items to open up that specific item, such as your non-conformance issues. I'd start with this video so you're familiar with wildcard searches. You can make a query that is based on what the user types in; you just have to know how to grab it from the form. I do that in this video, where you type in Florida, open customers, but your query here, instead of an exact search for the state, you'll just use a wildcard search for that conformance number. Then you can use this query to feed a continuous form, like my customer list form in the TechHelp database.
There are some other options for you. I have a filter combo as you type, or you could feed a combo box with all those items. Depending on how many items you have though, this might get slow, but it is a good option. For this, you kind of have to start at the beginning of the part number, whatever you're using. We'll call it part number from now on, because conformance issue number takes too long to say.
This is for everyone who's skipping the fitness database series. In part 29, we do something where you can type in any part of the item - the food items we have here - like if I typed in CA track for chicken, and it doesn't necessarily have to be just at the beginning; it could be anywhere in there, and that'll filter this combo box based on that. Then you click the add button, and it adds it. So for those of you who are skipping the fitness database series, there are all kinds of cool tricks like this in there.
Also, Access Developer 26 goes into all kinds of cool search form techniques where we can set up individual fields. You could say credit limit less than this. Basically, it is a filter box on the form that the users can control. So you could say part number LIKE, and then they type that in, and then this gets required based on what they type in over here. You can include the customer name, and all kinds of things. This is a really cool lesson. I love this lesson.
I have the full search seminar, which covers pretty much anything you can think of when it comes to searching your database. It's got everything in it. It's got the kitchen sink. It's almost 10 hours long and it's loaded with lessons. So check it out. I'll put a link down below.
Next, I commented in a previous video that I don't like the tab controls, mostly because when they first came out, they were really buggy and put a bad taste in my mouth. But one of my students, Gold member Lisa, posted this, and I just wanted to showcase it because you can do some really cool stuff with the tab control. She's made this look really neat. I don't absolutely hate tab controls 100 percent. They have a place, especially for beginners. They are a good way to break down a large form into logical sections like you've got their watch list, shopping cart, purchases, and so on.
Me personally, I prefer using subforms and dynamic loading. It involves a little VBA, but from a usability standpoint, they look pretty much the same.
My criticism mostly comes in on the advanced end. Once you start doing heavier programming, I find it personally easier and more flexible to use a subform. You can swap out the form inside the subform control, and it tends to behave more predictably in code. You get finer control over what's loaded and when. Tab controls, for example, load all of those objects, all of those combo boxes, all at once when the main form loads. Whereas with a subform object, you can load different things at different times, so it's not all taken at once, especially when you're pulling stuff over a network.
For a small local database, it's not a big deal. But if you're pulling stuff down over a SQL server online, it'll go slow if you have a few hundred combo boxes on ten different tabs.
I always believe in using the right tool for the job, and if this is working for you, fantastic. Very cool design, by the way, and I love your background pattern.
Next, my good friend, moderator, and colleague Alex, who's been my right-hand man for years, is commenting on my copy webpage data video that I did last week about how we can open a webpage, copy all the data, paste it in our Access database, and then analyze it. I use it for simple shopping results, like checking the prices between Amazon and Walmart to see who's got a cheaper price on the same products.
Alex asked a great question: for copying page text from Amazon in Access, what happens when Amazon changes their website layout or user interface? Excellent question. Honestly, that's the million dollar question with any kind of web scraping or copy-from-the-web method. The website can always change. The good news is in my method, we're not relying on the buy button being in an exact spot or parsing specific HTML tags. Someone else mentioned using the DOM (document object model); they can change that too, like what's the span ID or things like that.
We're just doing the simple, human approach: open the page, select all, copy, paste. It's simple. So if Amazon rearranges the page layout, it still works because the text is still there.
The only time that it really breaks is if Amazon starts rendering key information as images. Some sites do that; they'll render the price as an image or they hide it behind a script in such a way that it doesn't get copied as readable text.
In that case, we could use OCR (optical character recognition) to grab the image, or there's other techniques, but honestly, that's a whole different level of headache. I don't think they care that much as far as just grabbing quick prices. They want you to grab their prices. But as long as you can see normal text on the page, there's no reason Access and a little automation, maybe even with AI, can't grab it and stick it in your database.
Don't abuse it. Don't try to scrape every product on Amazon. But if you want to look up 20 or 30 products for your own price database, they don't care.
Here's a question that came in through TikTok. Yes, TikTok. I post some of my shorts on TikTok, and once in a while I'll get a couple of likes. This is the first question I think was ever submitted by TikTok.
This viewer says they have about 20 people sharing one Access database, but everyone is on different versions of Office and the front end doesn't work for some users. How do they make it run smoothly across all versions?
The short answer is the data can flow smoothly, but the app part might not be right unless you standardize. Your back end tables are usually fine as long as you're using a split database and everyone connects to the same back end - ideally with SQL Server, but you can use just Access back end tables. That's fine.
But the real trouble in your case is going to be the front end file. Different Access versions do not support the same features, so the oldest version in the group basically sets the ceiling for what you can safely use.
My first recommendation is get everyone on the same version. Microsoft 365 and Office subscription is usually the cheapest, simplest way to do that in the long run. It's like eight bucks a month. It's not expensive at all.
If you can't standardize and are stuck with the versions you have, then you have to design for the lowest common denominator. Don't use features that only exist in newer versions like Metro charts and that kind of stuff. Keep your references consistent. Avoid ActiveX controls because they behave differently across all versions - like tree view and all that stuff.
Make sure each user has their own copy of the front end - that's important. Sharing one front end over the network is going to cause weird corruption and version issues. I've talked about this in many, many videos.
If you do all these things, 20 users is totally doable, especially if not everybody's online at the same time. Make sure your network is consistent. Make sure your database is split correctly and everybody has their own front end. That's the most important thing, especially if you have different versions of Access running around.
You might have to make an Access 2007 copy for that one person. Take your new database, save it, load it on their machine, do a debug compile, make sure everything compiles. Run through it, make sure all the objects work. If something doesn't work, you're going to have to delete it from their copy. Then, when you update, you're going to have to update their copy too. That's why it's important to make sure everyone's on the same version.
Honestly, this is one of the complaints that I get from people and why they're switching to web-based databases. If you have an office with 20 or 30 people and they all have different versions of Microsoft Office, using the same database can be tricky.
Running it in a web browser is another way to avoid that. Another option is to use a service like Access Database Cloud, where you don't have to worry about different versions. You could just give everybody an Access Database Cloud account, and then they will connect to the database in their web browser running in the cloud. You don't have to worry about software versions or updates. That's the easiest solution.
Next, I got an email from one of my longtime Gold members, Peter in the UK. I'm not going to put the whole email up here because we went back and forth, and it is kind of long. Basically, Peter wrote to me and said that Access is such an outlier word - Word and Excel are everywhere, but you never see mention of Access in computer magazines. And yeah, that's true. It's the red headed stepchild.
He also mentions that the Microsoft templates like Northwind are supposed to be quick and easy databases that people can use almost straight away, but changing them is not easy, especially because changes can easily mess up all the global and startup macros.
His point was it's very, very unlikely that anyone would have a business operating just like Northwind. In his case, he just wanted a simple change and instead of automatically setting the shipping date to today, he needed to prompt the user for delivery date. Sounds simple, but he said that that's the kind of thing that turns into a headache with templates. He summed it up perfectly by saying he's having fun unraveling Northwind, but quote, I'm just a nerd, not the average user.
Honestly, he's right on a couple of different points. First, Access is not Word or Excel. It's a completely different animal. Most of you realize that it requires a completely different way of thinking. With Word and Excel, you can sit down and just start typing and you'll have a document or a spreadsheet and there are very few rules to follow. You can be productive immediately. Access isn't like that. It needs a little preparation, planning, and setting up your tables, that kind of stuff.
Templates like Northwind are not really meant to be starter databases. They're feature demos. They show you what Access can do. Honestly, it's a great product. I dug through the new Northwind myself - it's solid. It's very well built. A lot of smart people worked on it and it does a very good job of showcasing what Access is capable of.
That being said, the moment you try to bend a template like that to your real world business, you often find yourself fighting it. You have macros, hidden logic, design decisions you did not make. That's not a flaw so much as it's a side effect of how much is packed into it.
I do not personally agree with all the design choices they made. I use different naming conventions, for example, but that's just a matter of opinion and style. I have my way of doing things; they have theirs.
When I first started learning Access way back in the 1990s - I think I started with Access 2.0, which was around 1992 or 1993. I had the Access Bible, which is a fantastic book, even today. I did a book review on the latest one - I think it was the Access 95 Bible or 97 going back. Back then, Access tutorials like mine did not exist yet. YouTube wasn't even around. We had books - that's it. I learned Access from a book because the book would walk you step by step through building different things.
The Northwind database that came with it at that time was a fantastic database. It was not great for learning Access directly, but it was useful as a reference. If you saw a feature and thought, how did they do that, you could tear it apart and figure it out. The combo box code is in the after update event, stuff like that. I learned from a combination of reading books and tearing apart the Northwind database.
That's what I suggest you do now. Books, my videos, obviously, and tear apart other databases; my databases, the Northwind. It's a great database. That kind of stuff.
As far as building your own database, do not try to start with Northwind as a template because there are lots of features in there that you might not understand or even need. That's why I try to keep my TechHelp free template, the blank database that I have, as simple as possible. It's got customers, contacts, orders, that's it, and very few bells and whistles are baked into it. I use that simple database to add more features to it one at a time, but I do not put them all into the starting template. That way, you're not fighting my features. You can pick the features that you want for yourself.
Starting templates should be a clean starting point. You don't want to reinvent the wheel every time, but you want the basics in there, like a simple customer table, an order table, a basic report, that kind of stuff. You can be a nerd or you can be an average user - either one works. You do not have to be a full-blown developer to use Access, and that's why Access is so powerful - because average people can use it. So many people just take my beginner lessons and that's enough for what they want - they want to store their baseball card collection or whatever.
Learning a little bit of development gives you superpowers. You learn a little bit of VBA, and your database goes to a whole other level with just a little bit of extra code.
Try building it yourself instead of wrestling with a massive template that was never designed specifically for your business. Build your own, use Northwind as a reference - it's a great database. Tear it apart, see what they did - "Oh, I can do that in my database." Take that feature now and build it into yours.
That's what I suggest you do with my databases too. None of my stuff is really meant to be finished production quality. It's a starting place for you.
Next, we're going to head over to YouTube. One quick note: When I do the YouTube questions, I work from the most recent ones going back. So if you posted a question two weeks ago and I don't get to it, it might not make it into a video for a while. If you posted a question a few weeks ago and I haven't responded to it yet, post it again. Don't post a reply to that one - post a new one.
On YouTube, this only goes for YouTube, not my website, because on my website, all the moderators check them too. But on YouTube, it's just me and I do as many as I can. There are some that are years old that I've never even seen. Sorry, so many - they got to add that 25th hour to the day before I can get to all the questions.
So today, what do we have? We got a ton. Ton is asking, is there a way in Access to store data in tables that prevents clients from seeing the tables, like by hiding them or password protecting them? Not really, no.
If they're going to have access to the data in the tables, then they need full access to the tables themselves. Password protecting them is going to do nothing because they need the password to work with the data. Hiding tables isn't real security. If someone has the database file, they can usually still get to the data with the right tools.
The proper solution is to split the database and secure the back end, or better yet, store the data in SQL Server where you can control the permissions. You can hide things for convenience, but for real security, you need user-level access control, which you can really only get from SQL Server. I have an SQL Server seminar online available right now. This one focuses on using your Access database with an online SQL Server from a web provider like Winhost, my preferred provider.
I have a full SQL Server course coming out very soon. It teaches you how to set up the SQL Server Express free version on your network and hook up your Access database to it. That is coming very soon. Make sure you're on my mailing list, and as soon as it's ready, you'll get notified.
Next, this viewer is asking what actually happens in Access when you disable trusted locations and whether that means Access will treat everything as untrusted and lock down macros and other content. Great question. You basically got it: trusted locations are like your safe list for Access. If you disable them, then Access treats everything like it came from the internet - macros, VBA, active content all get blocked or heavily warned by default, unless you explicitly trust the file instead of the location.
It's more secure, but it's also more annoying. If you're working on your own databases, I'd suggest leaving trusted locations on, but keep them to one specific development folder or one server folder, and make sure people don't put things in that folder - don't download stuff to your dev folder.
I haven't actually tried this myself in a few years, but I can remember from way back when they first started trusted locations, if stuff isn't in trusted locations, it doesn't work. Good question.
Next, Shooter 505 is talking about my time video where I teach how to calculate a total time, which will give you hours and minutes. He says, "But I need another field on the form that says duration, same thing for a report, that basically duplicates the form." Duplicating the form is easy - use the same calculations, but what you're looking for is time as a duration, not as another time.
Check out this other video that I have - I'll put a link down below that teaches you how to work with time as a duration. In the future, if you have a question about something, do a Google search for that key phrase plus "Microsoft Access duration," and if one of my videos doesn't show up, let me know and I'll make one. I just did a search for "Microsoft Access time duration," and three of my videos are in the top four spots. Try those out. The one you just watched, try this one, try this one. There are lots on my website too.
That is my goal: to own all of the keyword phrases for Microsoft Access when you search on Google.
Next, I want to give a thank you to... I don't know how to pronounce your name - Leka? Leka? I don't know. Not even going to try. But thank you, you sent me five euros - every little cup of coffee helps. I greatly appreciate that. You said that I saved your life.
This is my move windows with the keyboard - something that frustrates me all the time too, especially if I build a database, or if I'm working on something on one computer, and then I disconnect the external monitor and take my laptop somewhere else and then open it up and the window is on the other monitor still that's not connected. Windows still thinks it's up there. Knowing how to move windows around with the keyboard is definitely a good skill to have. So, thank you again for the tip. I appreciate that.
Next, we have Movey Move saying, "I bet Lex Podcast bro, Friedman also uses Microsoft Access." I have to be honest, I had to look up who Lex Friedman is, but I wouldn't be shocked if he uses Microsoft Access. Access is everywhere. It's the quiet little database that could, doing all the little work behind the scenes while everyone argues on podcasts. If Lex is watching, I'm available for consulting.
Next, we have Ruth. She's new to Access and wants a drop down that supports selecting multiple items, like choosing several options that apply to one customer or one piece of equipment. Great question, Ruth.
A combo box in Access is single-select only. I know you've seen those on web pages where you can drop a box down and pick multiple items with check boxes. Access does not do that. You have a couple of options.
For multiple selections, you really want to use a list box. You store the selections in a related table using a many-to-many relationship. I've got videos on all of this. My Access Developer Level 15 class covers multi-select list boxes, and it does involve a little bit of programming. If you're new, it might be a little advanced for you. Now, doing that with multi-select list boxes is a bit more advanced.
There is a feature called multi-valued fields built into tables. Microsoft put this into Access to do exactly what you want to do. But if you're ever planning on getting beyond the basics with Access, don't use them because they're not properly implemented for a properly relational database. So, if you're okay with just a simple beginner tool to give you what you want, where you can select items like that, OK, but just be aware - I warned you - when you start to scale your database up, if you decide to get into more development, if you decide you want to add more people or upgrade to SQL Server or any of that stuff down the line, you're going to have problems with these.
They meet a purpose, and they're OK for beginners, but just be aware that if you get past the beginner stage, these are going to give you headaches. You have been warned. I explain in this video all the pros and cons.
Next, I have only one proper reply to this, and that's Chad. His eyes are open.
Next up, Dr. Xavier the kitty cat. "Hello Richard, thanks for your videos and guidance. I try to use the same formula as you taught, but my subform field showed error instead of numbering. Please help."
First of all, I'm not sure this will work in a subform. I haven't watched this video in the years since I released it, so you'll have to try it with a main form for just a single form. The dreaded pound error message usually means Access can't evaluate the expression. The control name could be wrong, the field name doesn't match, make sure your spelling is right, the control is in the wrong place. Double check that you didn't accidentally make it a text box bound to a field that can't accept the value. There's not enough information here for me to help.
Check out my online troubleshooter. Maybe it's just something simple, probably spelling in this case, but again, try it without putting it in the subform. Subforms can behave their own ways sometimes.
It's funny because of all the Microsoft Access videos that I have, I've got two videos that are not Access related. There's one for Excel about seeing lottery numbers, where you type in the winning numbers and then all of your numbers and it will highlight the ones that match. Not extremely complicated.
The other one is a Word video that teaches how to remove page breaks and stuff. This one: how to add number variance to this lotto program, how to add all the variants from Lotto 649 into Excel and generate them when you want. Sounds like an excellent idea for a project. Let me know when you're finished with it.
There's all kinds of stuff you can do with this. People always ask me, "Can you save all the history of all the numbers and generate lists of hot and cold numbers?" There's no reason to do that. It's a myth. You have exactly the same chances of the same numbers coming up every draw, unless the machine is rigged or, you know, the number six ball is heavier than the rest of them or something like that.
Statistically, hot and cold numbers are meaningless. Gamblers always are worried about, "These numbers are hot, they're due to hit soon." No, that's not how math works.
The only thing that I can tell you that is statistically valid: I play the Powerball and the Mega Millions. I do the thing where I spend like, you know, I'll do one play, I don't play the Mega Ball extras and multipliers - those are all scams. Just play the regular numbers, and I do the thing where you can play the next 20 drawings in a row so I don't have to keep going back to the store twice a week.
The only statistically important thing is lower numbers below 31 - if you win, you have a higher chance of splitting the jackpot because more people, myself included, play dates and birthdays. So if you hit and your numbers are all birthdays, the likelihood of you splitting the pot with someone else is higher than if you play the high numbers. Again, mathematically, your chances of winning are all the same; it seems weird, but you have the same chance with a random set of numbers as with 1, 2, 3, 4, 5.
Weird, but that's math. I don't know or think that has ever happened before, because the odds are astronomical to get any set, but it would be interesting to see if a series of sequential numbers has ever hit. I'm going to do the math on that one.
I asked ChatGPT if that's ever happened, if anyone has ever gotten six sequential numbers on a lottery drawing. Apparently, South Africa's National Power Draw in 2020 came up with five consecutive main numbers: 5, 6, 7, 8, 9, and the Power Ball was 10, which caused a big stir and fraud accusations. The lottery investigated and said it was legit, so I guess it's possible.
Xavier says, "When I use rich text, I only set the property on forms and reports, not on tables. Don't ask me why. I prefer to see the raw text in tables." That's perfectly valid. If you want to see the HTML codes, like bold or paragraph tags, you can leave the table set to plain text and then just do rich text on the forms. Sometimes when I'm building a database for myself, I'll do the main note field as rich text and then a small box next to it with plain text so I can Shift+F2 into it and see what's going on.
The benefit of setting the table to rich text is that any forms or reports you design after that will know that you've got rich text, but if you don't mind going in and changing that setting yourself, it's fine. The data is the same either way. Access doesn't really care, that's all just for you and how it displays it.
Adoris says, "Last weekend I thought I saw something from you about automating bank records. I looked at your site but haven't found it. I've been sick lately so was that a fever dream?" No, it was not a fever dream.
I made a post in my Captain's Log about a money management program that I'm working on. This was 11 days ago now. I posted a cool little picture of me drinking my coffee, because basically it will connect to your accounts. It uses one of those financial aggregator services, and I'm still experimenting with a couple different ones to see which one I prefer before I make a recommendation. I had some quirks with the one I was using, but I got the second one working really well.
I'm not naming names until I'm certain which one I'm going to pick, because I don't recommend any services or products that I don't personally use myself, so I've been using it for about two weeks now. Essentially, this service connects to all your bank accounts and credit card accounts, and once a day it will update your balances and transactions. Instead of logging into all those sites or checking all your emails, you just open an Excel spreadsheet, click a button, and it'll pull all the data in. Save the spreadsheet, and then we can use Access to pull all that data into an Access database once it is in your Excel sheet.
Yes, I've tried a million ways to automate it just from Access, where it opens up Excel in the background, but they're using a custom add-in, so I don't think we can automate it yet. They said they're working on adding that, but for now, it's not bad - it saves me about a half hour to 45 minutes a couple times a week. Instead of having to log into American Express, Capital One, my local bank, or others, this makes it one click - well, OK, three clicks, and it's much faster.
Yes, it's a project I'm working on. You were not dreaming, it's coming. I'm probably going to use it for another week before I put together a video and make a final recommendation, but it's working pretty well for me. Stay tuned. More is coming.
I just wanted to share this one from James: "The application of creative genius has never been restricted by the hands on a clock." I love that.
Talking about my extracting data from a web page video, I can see how this could be used for extracting business card info and parsing it into tables. Yes, very cool, especially when you mix it with AI. If you pull up someone's profile page or business contact page, load all that data into a field, tell the AI, "Hey, I need you to extract the user's email address, phone number," it is excellent for doing stuff like that. This is a great idea.
Finally for today, one more question. I've seen from the logs that you tend to fall off at about 30 minutes - that's why I keep the TechHelp videos to about 10 minutes, and the Quick Queries to about 30 minutes. After 30 minutes, the number of viewers still here goes way down, so people get bored. I'll save whatever I have next for the next video.
This user, sancho23 or sanch023, I don't know, wants more information on backup strategies, specifically when it is safe to delete older backups to save space. This is a big question, and it is something that you have to answer for yourself. For me personally, deleting old backups absolutely makes sense once you have a solid rotation in place.
What I personally do - and I talk about this in my backup template - is keep the oldest backup, the previous three days' backups, and then any database backup that is from the first of the month. So, going backwards, I've got the last three days (in case something happens today, I get yesterday's backup). I have three days ago, in case I need to go back, and if not, I can go back to the first of the month (for example, January first). Before that, if you discover you made a major change three months ago, you have November first backup. I save every monthly backup, the oldest backup, and the last three days, but you can custom-tailor that to whatever you want. If you want more, store more.
I do an incremental backup of my server. I do a full backup of the system once a month using Macrium, and then I save the past 30 days of incremental backups. The full backup is like 20 or 30 gigabytes, and the incremental backups are about a gigabyte each; it's all sitting on a six-terabyte drive, so the drive space is meaningless. Then I have a script that copies all that up to my cloud server daily, copying the newest one. Once a month, it's got a big file to back up - it still only takes a couple hours. Nightly, it's just that small one-gigabyte file. You need to come up with a strategy that works for you. Check this out: I have a backup template available where you can back up your Access databases. Access can back itself up, but it won't tend to back up any files that are open, so if you know it might not back up its own front end, and it definitely won't back up any tables that are open in the background. You have to do this at 4 AM when no one's on the database, make sure everyone is out of it, and don't have any tables open. Make sure you're using unbound forms - that's what I do.
This backup template is a standalone program, so if you have your database running 24 hours, you can have it shell out to the backup template, close itself, the backup template will wait, then you back up the database with the backup template, and the backup template will start up your main database again. It's all up to you, however you want to do it. I have a video that talks more about backing up your Access database. Backups are important. I just spent the whole day recently going through all my backups and making sure I have a solid backup plan, and I'm doing my best to automate as much as I can. I've got more videos on this coming out soon because I just wrote some scripts that do some pretty cool stuff.
So that's it for another Quick Queries. Before you go, I have a couple things to mention as always: hit that like and subscribe, get on my mailing list if you want to be notified when I release new stuff. Access Day 2026 in Redmond, Washington - registration is now open. I just registered myself today. I'll be going. I'm not a presenter; I'm just going to hang out, meet cool people, and watch some presentations. I went last year; it was a lot of fun. They had some really good presentations by some Access experts, MVPs, and people from the actual Microsoft team that work at Microsoft. So, come check it out and hang out.
Don't forget to stop by my website and check out the What's New page, the home page. I'm always adding new videos, updates, templates, random bits of Access goodness here and there, so it's worth a quick look to see what you might have missed. Also, be sure to check out my Captain's Log where I post my thoughts about whatever I happen to be thinking about that day - sometimes Access, sometimes science, sometimes Star Trek, sometimes penguins, sometimes Captain Kirk riding a bull or Mister Spock using 20th-century technology to build a subspace transceiver, that kind of stuff. I also have a YouTube channel now that I started just for the Captain's Log. I haven't done too many videos yet, but I'm thinking about it. Be sure to subscribe to that, give me a few subs on that page.
Check out the merch store, get your coffee mugs, t-shirts, hats, all that good stuff. Pick up a copy of my Access Beginner Level One book - it's on Amazon, Kindle, print versions, all that.
So that's going to wrap up today's Quick Queries. Today's big takeaway: hiding tables in Microsoft Access is not security. If you are serious about protecting your data you need proper architecture, definitely a split database, a secured back end, and ideally SQL Server with real permissions. Anything else - if the user can use the database, they can get to the tables, they can download it, they can delete stuff. They need full read/write access in order to work with it, which means they can get the whole thing. Keep that in mind. I've always said an Access database is good for a single user, or a small group of trusted users. You can have three, five, or ten people in your office and they're all relatively trusted, but just be careful because you get that disgruntled employee - there goes your back end file. Keep that in mind.
If you like today's video, post a comment down below, let me know what you thought, and of course drop your questions for next week's episode. I hear from a lot of people who post in the comments that they're not a member, but that doesn't matter. I answer questions from members and non-members alike. If it's a good question, it'll get answered, and if it's a really good question, it'll make the video. But, like I said earlier, if I don't see it for a week it might float away to the back, so post it again and I'll see it.
So that's your Quick Queries video for today, brought to you by Access Learning Zone. Hope you learned something. Live long and prosper, my friends. I'll see you next time. Enjoy your weekend.Quiz Q1. What is the recommended way to allow users to search for part numbers in Access without needing an exact match?
A. Use a LIKE wildcard search in a query based on a search box input
B. Require users to enter the full part number each time
C. Use only combo boxes with exact matches
D. Have users scroll through the whole list manually
Q2. What is an advantage of feeding a continuous form with a query based on a wildcard search?
A. It shows immediate filtered results as you type
B. It only works for exact matches
C. It cannot be double-clicked to select items
D. It is slower than loading all records at once
Q3. Why might using a combo box for "search as you type" become slow if you have many items?
A. Combo boxes require loading all items into memory
B. Combo boxes only accept numbers, not text
C. Combo boxes cannot be filtered at all
D. Combo boxes require a separate table for every item
Q4. What is demonstrated in the fitness database regarding combo box filtering?
A. Filtering works for any part of the text, not just the front
B. Filtering only works for the first character of a word
C. Combo boxes cannot be filtered as you type
D. Super advanced filtering requires SQL Server
Q5. When developing Access databases, why might subforms be preferred over tab controls for advanced users?
A. Subforms offer more flexibility and predictable behavior in code
B. Tab controls require the use of SQL Server
C. Subforms cannot contain combo boxes
D. Tab controls load data only when selected, making them always faster
Q6. If you copy data from a web page like Amazon into Access, what potential issue could break your process?
A. The site renders key information as images instead of text
B. The internet connection speed is too high
C. Access requires the data in XML format only
D. The prices are too high for Access to display
Q7. What is the best practice for sharing an Access database across multiple users in an office?
A. Use a split database with each user having their own copy of the front-end
B. Have users share one front-end over the network
C. Only use the Access web app version
D. Disallow any updates to the back-end database
Q8. What is a common problem when trying to customize Microsoft's Northwind template for your own business needs?
A. Changing the template can easily break macros and global logic
B. The template is too simple for most real businesses
C. Northwind uses only SQL Server, which is hard to change
D. The database has no customer table at all
Q9. What is the main difference between using Access and applications like Word or Excel, according to the video?
A. Access requires planning and table setup, not just starting to type
B. Access cannot display text or numbers
C. Word and Excel have more security features than Access
D. Access databases can only be created by programmers
Q10. What is the recommended way to learn Access, according to the presenter?
A. Combine reading books, watching videos, and exploring sample databases
B. Only use pre-built templates and avoid making changes
C. Rely exclusively on online Microsoft documentation
D. Focus only on macros, avoiding VBA and table design
Q11. If you want to prevent clients from accessing data directly in Access tables, what is the most secure approach?
A. Store data in SQL Server and control permissions there
B. Only hide the tables in the Navigation Pane
C. Password-protect the Access file
D. Use multi-valued fields in every table
Q12. What happens in Access when you disable all trusted locations?
A. All macros and VBA code are disabled unless you explicitly trust each file
B. The user interface turns off all ribbon menus
C. The database deletes itself for security
D. Only reports and forms are blocked
Q13. Regarding multi-select drop-downs and multi-valued fields in Access, which statement is correct?
A. Multi-valued fields are problematic for advanced development and scalability
B. Combo boxes in Access natively support multi-item selection with checkboxes
C. List boxes cannot be used to store multiple selections
D. Multi-valued fields are the best way for large, relational database designs
Q14. What is a recommended troubleshooting step when you get the pound (#) error message in Access forms?
A. Double-check field names and control sources for spelling or placement errors
B. Reinstall Access immediately
C. Copy the entire table to Excel first
D. Use only single-letter names for all controls
Q15. Which statement about lottery number "hot and cold" myths is supported by the video?
A. They are a statistical myth; every number has the same chance
B. Hot numbers are guaranteed to be drawn more often
C. Cold numbers are always due, so you should play them
D. Statistical tracking of number history increases your odds significantly
Q16. What is the benefit of setting a field as rich text only on forms/reports but not in tables?
A. You see raw HTML codes in tables but formatted text in forms
B. It prevents all use of formatted notes in reports
C. It disables rich text completely in the database
D. Access requires all fields to be set to plain text
Q17. When automating bank data imports, what approach is discussed in the video?
A. Use a financial aggregator service to download data to Excel, then import to Access
B. Access can always directly log into every bank via VBA
C. Only manual data entry is possible for transactions
D. Access has a built-in online banking integration feature
Q18. What is a sound backup retention strategy for Access databases, as suggested?
A. Keep the oldest backup, last three days, and one backup per month
B. Only keep the last backup and delete all others
C. Keep all backups forever, regardless of space
D. Backups are not necessary for Access databases
Q19. What is the key takeaway regarding hiding tables in Access for security?
A. Hiding tables is not real security; use proper database architecture instead
B. Hiding tables makes Access completely secure
C. Password-protecting tables is enough for most cases
D. Encrypting the database ensures field-level security
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
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'm taking time to answer a wide variety of questions submitted through YouTube, my website forums, emails, and elsewhere. We'll be exploring menu searches for part numbers using wildcard filtering, setting up combo boxes that can filter as you type, and one neat trick from my fitness database series that I think everyone should see. Other topics include advanced search forms, what happens when websites like Amazon change their interfaces while you're trying to copy data, handling multiple versions of Access in an office, why templates like Northwind can be tricky to customize, understanding trusted locations in Access, the differences between multi-select drop downs and multi-valued fields, troubleshooting the pound error message, stories about sequential lottery numbers, working with rich text in both tables and forms, and strategies for managing backups so you don't accidentally delete something important. There is plenty more as well, so let's dig in.
Starting off, one of our Gold members posted in my website forum about creating a menu search for part numbers. Instead of making users type the exact part number, he wanted to allow them to type just a few characters and find matches. I get this question all the time, since exact matches are ideal for computers, but people rarely input perfect values. The solution is to use a search box tied to a wildcard search, set up using the LIKE operator. You can put this search box on your main menu or wherever it's convenient. The search then drives a continuous form showing filtered results as you type, so users get immediate feedback. Typically, you can allow users to double-click a result for more details, such as opening a specific item.
If you are not familiar with using wildcards, I recommend reviewing one of my foundational videos on how to set up a basic query driven by user input. You'll want to make sure the user's input is being captured from a form and used in the query. Once you've got that framework, you can feed the results into a continuous form, a bit like how I handle customer lists in my TechHelp database.
Another popular approach is a combo box that filters as you type. This works especially well when your dataset isn't huge, since feeding a massive combo box can become slow. In this setup, the combo can be filtered based on partial matches and doesn't require users to start at the beginning of the part number. For those of you who have skipped the fitness database series, there's a segment in part 29 where I show how a combo box can filter items containing any part of what you type, not just the start. After filtering, you simply click to add the selected item—just one of many useful tricks found in these lessons.
If you're looking for more advanced search capabilities, my Access Developer 26 lesson shows how to build search forms with filter boxes where users can enter specific criteria for any field—say, "credit limit less than..." or "part number like..." which then filters the main list accordingly. It gives users a lot of control and is one of my favorite lessons.
For those who want a deep dive, my full search seminar provides just about every searching technique imaginable for Access. It's a nearly ten-hour course, and I highly recommend checking it out if you want to master database searching.
Switching topics, I recently brought up my aversion to tab controls, which stems from bad experiences when they were first introduced. However, one of my Gold members shared an example of a tab control design that looks great and is highly functional—tab controls do have their place, particularly for breaking complex forms into logical sections for beginners. Personally, I prefer using subforms with dynamic loading, as they provide more flexibility and better performance when forms get complicated. Subforms allow you to load content as needed, not all at once, which is especially important when working with a network or SQL backend. For local, single-user databases, it's less of an issue.
Another question I received came from Alex, one of my forum moderators, about my video on copying web page data into Access. His concern was about what happens if Amazon or another site changes their web layout. My method relies on the simplest approach: copying all visible data as a user would, not scraping specific HTML elements or using the DOM. This means even if the layout changes, as long as the needed data stays visible as text, it will continue to work. If they start hiding critical info behind images or scripts that don't allow for copying, we'd have to consider more advanced methods such as using OCR, but that's a whole different process. In general, this hands-on approach is reliable for capturing info for your own reference.
On TikTok, someone asked how to run an Access database with about 20 users when they each have different versions of Office, and some have problems with the front end. The key issue here is consistency. The backend—meaning your data tables—will generally be fine as long as everyone connects to the same backend, ideally via SQL Server, but Access tables can work too. The trouble typically comes from differences in the front end. Newer features aren't always backward compatible, so your oldest version essentially sets your limit for features. My strong advice is to standardize everyone on the same Office version, which is affordable these days via a Microsoft 365 subscription. If you can't do that, then only use features available to everyone and avoid version-specific features, such as new charts or ActiveX controls, which can behave inconsistently.
Also, always make sure each user gets their own copy of the front end to prevent corruption and version conflicts. Sharing a front end over the network causes headaches. If one user is stuck on Access 2007, you may need to maintain a separate copy just for them—but this means double the work when you update your database. This is why web-based databases are gaining popularity. Services like Access Database Cloud eliminate the software version issue and let everyone connect through a browser.
Peter from the UK emailed me about how rare it is to see Access discussed compared to Word or Excel. He brought up Microsoft templates like Northwind, which are great for showcasing Access features but notoriously hard to customize. The reality is that templates like Northwind are more about demonstrating Access's capabilities than serving as a plug-and-play business database. Once you try to adapt them for your use, you run into complex macros, hidden code, and unfamiliar design decisions, which makes even simple changes a real challenge.
Back in the early days, I learned Access by studying books and tearing apart the Northwind database. That's still a great way to learn: mix tutorials and references with practical experiments. However, don't try to use Northwind as the foundation for a new database—there's too much complexity and too many features you may not need. That's why the blank databases I provide are as simple as possible, allowing you to add features one at a time, avoiding the tangle of someone else's code.
Moving over to YouTube, I tackle questions in reverse chronological order. If you asked something weeks ago and haven't heard back, submit it again as a new question. With so many questions, especially on YouTube, I don't always have time to see them all, but I do my best to respond.
One user asked if you can hide tables or password protect them to keep clients from seeing them. In reality, if someone has access to your database and needs to work with the data, they're going to have access to the tables. Hiding tables or password protecting them within Access is not true security. For actual protection, use a split database and keep your backend on SQL Server with proper user permissions. Otherwise, if users can access the data, they can copy or even delete it.
Another viewer asked about Access's trusted locations and what happens if you disable them. Trusted locations are essentially a whitelist; when disabled, Access treats all files as potentially risky, blocking macros and other content unless you manually trust each file. While more secure, it tends to be a hassle if you're developing your own work—so keep your trusted locations limited and secure.
A question came in about displaying time as a duration in a form or report, which is a little different than just showing time-of-day. I have videos specifically on displaying durations that you should check out. If you ever can't find something on my site, try searching for the key phrase plus "Microsoft Access" and see if my content pops up. If not, let me know and I can create a new tutorial on the subject.
Thank you as well to those who support my work, whether by sending a tip or simply appreciating a helpful trick for Windows or Access. For example, knowing how to move windows with the keyboard is a lifesaver when monitors get rearranged.
Questions about multi-select drop downs pop up often, especially from people new to Access. Combo boxes are single-select only in Access, but you can use list boxes for multi-select functionality and store the selections in a related table using a many-to-many relationship. There is a multi-valued field option in tables, which mimics this behavior, but I generally discourage using it if you plan to expand your database or move it to SQL Server, as it does not follow relational database principles and will cause problems as you become more advanced. Use list boxes and relationships for a scalable solution.
Another question touched on troubleshooting the dreaded pound error message in subforms. Often, it's related to a mismatch in control or field names, or the control being placed incorrectly. Try testing the expression on a main form first or consult my troubleshooting guides.
I also fielded comments about lottery number programs in Excel and about storing rich text. If you want to see the raw HTML in a table, keep it as plain text in the table view and switch to rich text on your forms. Changing the data type in the table just helps new forms and reports detect it as rich text automatically.
Elsewhere, I shared news that I'm working on a money management project using a financial aggregator that updates your balances and transactions in Excel, which you can then import into Access. I'm still testing different services and will make a video once I'm confident in my recommendation.
On the topic of backups, someone asked when it's OK to delete old backups to save space. The best approach is to have a system: keep the oldest backup, the last three days, and all backups from the first of each month. Incremental daily backups and a monthly full backup (with cloud copies) are a smart strategy. Your own system may differ, but make sure you have what you need to restore from any kind of disaster. Remember, Access cannot back up open files, so schedule your backups for off-hours and ensure everyone is out before running them.
As a final note, hiding tables in Access is not real security. Protect your data with proper architecture: use a split database, secure your backend, and ideally move to SQL Server if you need true permissions and safety. Access is best for single users or trusted small groups; just one disgruntled person can wreak havoc, so always keep that in mind.
If you enjoyed today's Quick Queries, please join the conversation by leaving a comment or question for next time. These videos welcome both members and non-members; I pick questions based on their value to the community. Get on my email list to stay up to date, and if you're interested in events like Access Day, consider stopping by to learn, network, or just enjoy some good presentations.
Please check out my website for new videos, database templates, updates, and my Captain's Log blog where I talk about Access, science, and whatever else is on my mind. Don't forget about the merchandise shop and my Access Beginner Level One book, available in multiple formats.
That's all for today's Quick Queries from Access Learning Zone. 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 Menu search for part numbers with wildcard filtering
Continuous form filtered by search box input
Combo box search as you type, filtering anywhere in string
Advanced search forms with dynamic filter boxes
Handling menu and search UI changes on external web pages
Copying and pasting webpage data into Access
Dealing with Amazon and other site UI layout changes when copying data
Strategies for working with multiple Access versions in the same office
Access database splitting best practices (front end/back end)
Managing user compatibility across Microsoft Office versions
Access Database Cloud as an alternative for versioning issues
Customizing and modifying Microsoft Access templates (Northwind example)
Downsides of customizing complex Access templates
Building simple starter databases vs using feature-rich templates
Using subforms vs tab controls for advanced forms
Security options for hiding or protecting Access tables
Explanation of trusted locations in Access and their effects
Multi-select list box vs multi-valued field feature in Access
Pros and cons of multi-valued fields for beginners
Troubleshooting #Error messages in Access forms and subforms
Sequential lottery numbers case study and probability discussion
Working with rich text fields in tables versus forms in Access
Fetching and importing bank and credit card records using third party aggregators
Combining Excel automation with Access for data import
Backup strategies for Access databases and when to delete old backups
Recommended rotation and retention method for database backups
Automating Access database backups with templates and scriptingArticle In this article, I want to walk you through some of the key topics and user questions that come up frequently when working with Microsoft Access—whether you're building databases for yourself, your office, or your business. I'll cover things like building effective search forms, using wildcard filtering, best practices for sharing databases among users with different Access versions, thoughts on templates like Northwind, dealing with website changes when copying data, security best practices, backup strategies, and more.
Let's start with a common question: creating a search feature for part numbers that doesn't require users to type the exact number every time. This can make your database much more user friendly, especially when users may only remember part of a product code or want to search by a partial match. The best way to set this up is to place a text box on your menu or form where the user can type in any part of the part number. In your query, use the LIKE keyword and wildcards to find records that match. For example, if your part numbers look like ABC1234, and someone types 123, your query should return any items with 123 anywhere in the part number.
To do this, you would use a query criteria like this:
Like "*" & [Forms]![YourFormName]![txtSearch] & "*"
Replace "YourFormName" with your form's actual name and "txtSearch" with the name of your search box. This approach will filter the data in a continuous form, so as soon as the user enters a value and runs the search, they'll see all matching items immediately. You can enhance the experience by allowing users to double-click a record to view more details about it.
There are other ways to create search experiences in Access as well, like setting up filter boxes or combo boxes that search as you type. Combo boxes are great for smaller lists because they offer auto-complete functionality, but with very large lists, they can slow things down. In situations where you want to filter as you type—not just at the start of the string—you can use VBA to dynamically update either a list box or combo box so it returns matches where the search text could be anywhere in the field, not just at the beginning. For example, in a fitness database tracking foods, if you type "CA" you might want to see "Chicken" as a result, even though "CA" is in the middle of "Chicken". For that, your VBA code can requery the combo or list box with a SQL statement using wildcards.
For even more advanced search forms, you can set up a form with filter boxes for various fields (like part number, customer name, credit limit, etc.) and then construct a dynamic WHERE clause based on what the user enters, only filtering by the fields they've filled out. This approach gives users greater control and a more flexible search.
When it comes to copying data from websites like Amazon or Walmart, one concern is what happens if the website's design changes. If you're using the simplest approach—opening the page, selecting all, copying, and pasting the text into Access—that method is actually very robust because you're not relying on specific HTML structures or element IDs. As long as the key information remains as selectable text on the page, you'll be able to get the data, regardless of minor layout changes. The only time this method breaks is if the site starts displaying key info as images or hides content behind scripts that prevent copying. In extreme cases like that, using OCR or more sophisticated scraping with code becomes necessary, but for most basic price checks or comparison shopping, copy and paste is sufficient.
If you're managing a database with many users on different versions of Access, compatibility can become a real headache. The golden rule is that the oldest version you support sets the feature set and reference library you can use throughout your application. To keep things running smoothly, it's best to get everyone on the same Access version—an Office 365 subscription makes this relatively easy and affordable. If that's impossible, avoid features only available in newer versions and ensure that every user has their own copy of the front end of the database. Never share a front end file over the network between users—this is a recipe for corruption and strange bugs. Always split your database into a front end (forms, queries, reports, VBA) for each user, and a shared back end (tables) on your network.
If you have outliers on old versions—say, Access 2007—you might have to create a special version of your front end for that user. Make sure to check it and debug compile on their machine. Whenever you update the main front end, ensure that all users, including those on older versions, are given compatible updates.
Frustration with versioning and deployment is one reason some users consider moving to web-based solutions or cloud-hosted Access services. Services like Access Database Cloud can take the version headaches away, allowing users to connect via a browser without worrying about what version of Access is installed locally.
On the topic of templates, Microsoft's Northwind sample database is widely known but not always the best starting point for real business applications. Northwind is designed as a feature demo. It's full of complex macros, design choices, and business logic that can be tough to untangle if you only need a simple tweak or different workflow. While it's great to study and take ideas from—such as how they handle combo box events or navigation—building your own database from scratch, or from a much simpler starter template, is usually better for most users. Templates like Northwind serve as references and learning tools, not direct foundations for your business application. That way, you don't fight against a lot of pre-built automation or logic that doesn't fit your needs, and you avoid hidden dependencies you might not realize exist.
If you want to hide tables or protect your data, know that Access isn't designed for data security; hiding tables or password protecting them offers little real protection, since anyone with the file can open it in Access (or another compatible tool) and access the data. The only way to actually secure your data is to store it externally—such as in SQL Server—where you can set real user-level permissions. In Access itself, you can split the database and store the back end in a limited access location, but it's not true security. For serious security where users should not access all the tables, move your tables to SQL Server and control access there.
Regarding trusted locations: these are essentially safe folders where Access allows all macros, VBA, and other content to run without security warnings. If you disable trusted locations, Access treats every database as if it has potentially unsafe code, warning you or even blocking macros and code from running. This is more secure but dramatically less convenient. It's usually best to keep trusted locations enabled and limit them to a specific folder dedicated to your database projects.
A question that comes up frequently for new users concerns how to create drop downs that allow selecting multiple items. In Access, a standard combo box only allows single selections. To select multiple items (for example, assigning several services to a customer), use a list box set to allow multiple selections. To relate the selections properly, store the data in a separate related table—a many-to-many relationship.
You may notice that Access tables also allow so-called "multi-valued fields" which store several values in a single cell. Although this seems handy, it is not a true relational database feature and tends to cause big problems as your database grows or if you ever want to migrate to SQL Server or more advanced Access development. For beginners, multi-valued fields can be a quick fix, but I strongly advise against them if you intend to scale up. Stick to proper related tables and multi-select list boxes for a future-proof design.
If you ever encounter the #Error message in a calculated field or subform, it usually means Access cannot evaluate the formula. This could be due to a typo in the control name, a field that doesn't exist, being in the wrong context (like a subform that doesn't have access to the referenced control), or binding the control incorrectly. Double-check your spelling, field names, and where the control is located. Sometimes you'll need to experiment and simplify to find the root cause.
On the topic of rich text vs plain text in tables and forms: setting a field's TextFormat property in the table to Rich Text means all newly created forms and reports will treat that field as rich text (e.g., allowing bold, italics, and colored text), whereas setting it only on the form will display the formatting there but not in the table datasheet directly. The underlying data is the same; it just changes how Access presents it to you. Some people prefer seeing HTML tags in the table (plain text), while others want to see the formatted text everywhere.
Backups are another critical area—especially if you're supporting business users. There's no universal rule for when you can delete old backups to save space, but a solid rotation strategy can help. Typically, keep the three most recent backups (for daily or short-term disasters), keep every first-of-the-month backup (for long-term archiving), and always keep your original oldest backup (in case of major changes). Back up when no one else is using your database and the files are closed—Access cannot reliably back up open files. Consider running a backup template that closes the main database, backs up the file, then reopens the main database for users. Automate this process as much as possible, and always test your backups to ensure they really work when you need them.
Finally, remember that for users to work with data in Access, they must have access to the underlying tables. If a user can use your database, they can potentially gain access to, download, or delete your tables. This is why Access is best used within a small group of trusted users unless you move your data to a more secure platform like SQL Server.
These topics cover some of the most common challenges and opportunities in Microsoft Access, from search and filtering to security and backups. Whether you're a beginner building your first app, or an intermediate user refining an office tracking tool, focusing on good design practices—clear searching, proper relationships, splitting your database, cautious use of templates, robust backup strategies, and realistic views of Access's security model—will set you up for success. Keep browsing for more tutorials, consider sharing your questions on forums or in the comments, and take advantage of the many resources available both online and in books. With consistent practice, you can build powerful, flexible data tools tailored exactly to your needs. |
