Simple Security
By Richard Rost
6 years ago
Simple Security for Microsoft Access Databases. Create ACCDE File.
In this video, I will show you how to protect your Access databases with security that's good enough for most trusted work environments. You will learn how to hide sensitive objects (tables, forms, etc.), hide the Navigation Pane, disable the full Ribbon, and encrypt your database into an Execute-Only version (ACCDE).
Ruben from Toronto, Canada (a Platinum member) asks: I trust my employees. I have a small 5-person, family-run business. I'm not worried about someone intentionally sabotaging my database. However, I'd like to secure it just enough to keep people from seeing things they shouldn't (like financial data or sensitive customer info). Plus I wouldn’t want someone accidentally "breaking" something. Any tips you can give? I know you have a whole Security Seminar, but I don't need Fort Knox here.
Members
I'll show you how to password protect your sensitive forms using an InputBox in your buttons to prompt the user for a password. I'll also show you how to minimize the Ribbon and the Navigation Pane, and also maximize Access (the entire application window).
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Addendum
- There is now a way to hide your back-end folder! See Hide Folder.
Links
Intro In this video, I will show you some simple security steps you can take to protect your Microsoft Access databases. We'll talk about how to hide sensitive tables and forms, disable the navigation pane, limit access to the full ribbon, and save your database as an ACCDE file to prevent users from making design changes. These basic methods are designed for small business settings where you trust your users but want to keep certain information, like financial or customer data, more secure and minimize accidental changes.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In this video, I'm going to show you some simple security measures you can take to protect your Microsoft Access databases.
Today's question comes from Rubin from Toronto, Canada, one of my platinum members. Rubin says, I trust my employees. I have a small five-person family-run business. I am not worried about someone intentionally sabotaging my database. However, I'd like to secure it just enough to keep people from seeing things they shouldn't, like financial data or sensitive customer info, things like credit card numbers, that kind of stuff. Once that information is entered into the database, you don't really need anybody else looking it up. Plus, I wouldn't want someone accidentally breaking something. Any tips you can give? I know you have a whole security seminar, but I don't need Fort Knox here.
And that's perfectly understandable, Rubin. I do have a security seminar where I cover how to lock down Access completely - the most secure you can possibly make it. That does involve a lot of programming and it's a little complicated, but I show you step-by-step.
However, as long as you're not worried about anybody intentionally messing things up, and you just want to keep people from breaking stuff, like you said, or maybe seeing some sensitive information, credit limits, and stuff like that, there are a few steps that I can show you to protect your database well enough for the average workplace. Here are my top simple security tips.
First, we're going to hide any sensitive tables. We're going to hide the navigation pane so they can't go poking around. They have to follow your form menus that you make for them. We'll disable the full ribbon that turns off some options you might not want them having. Then we're going to encrypt the database to an ACCDE file that will protect them from making any design changes or getting into your VBA code or your form design.
Here's my customer template. This is part of the blank customer template that I have available on my website. You can download it for free - I'll put a link down below the video. Let's pretend this is your company database. Let's open it up. Real simple, very basic. We have a main menu and you want to control how the users interact with your database using these kinds of menus like this.
I'm not a big fan of the switchboards. I like making my own menus using blank forms that have no data in them. We've got a simple customer list, a customer form, and of course you'd have other things in order like order entry and contacts and all that kind of stuff. Maybe an admin menu just for you. I cover how to set these up in my beginner classes. Access Beginner Level 7, in fact, is where I cover making a main menu form.
In my customer data, if you look at the customer tier, I've got all the basic information - address, city, state, all that stuff. The credit limit is in here. You might even have a field in here to store their credit card number and their expiration date, especially if you have recurring memberships.
What I suggest you do is put sensitive information like that in a separate table. For example, let's create Table Design. In here we would have an ID for this table and we'd have a Customer ID to link it back to the customer table. This is called a foreign key. We'll make a one-to-one relationship here, or a one-to-many if you want to store multiple credit card numbers. That's fine too. I've got tons of free videos on relationships on my website and on YouTube. I'll put some links down below. Watch those first if you don't know how to make relationships. It's not really relevant for this particular example, but I like to show you how to do it.
In here you'd have the credit card number. I'll just put CCN and the expiration date like that. Maybe the CCV code, whatever else you need, billing address. Save this as my credit card table. Now you can set up a one-to-one relationship or a one-to-many relationship. You can put a subform on here with your credit card information, whatever you want to do.
The point of this lesson is to show you how to hide this from prying eyes. Let's say you're the only one that handles customer billing. Nobody else in the office needs to see credit card numbers. You put it in, you make all the charges, and you're the only one that can look this stuff up.
What we're going to do now is we're going to hide this table. Right-click, Table Properties, Hidden, and then hit OK. Notice now it disappears. That table is still there, but it's hidden. You can't just browse to it in case someone does get into your navigation pane, which I'm going to show you how to turn off in just a minute. They won't see it there. Of course you want to hide any queries or forms that are also based on that.
Now how can you go in there and find it? Right-click anywhere in the navigation pane and go to Navigation Options and turn on Show Hidden Objects. Now, this is on the system level. This is not for the database. This won't affect anybody else if they have this database on their computers and you're using a multi-computer setup on your network, for example. This only will work on your computer. The others won't see them. Hit OK. And there it is. You can see it's kind of grayed out there. But now you can open this up and come in here and work with it.
Generally you'll have a form that you'll be working with, so the table and the form, you'll make them both hidden. And then you just have to open up the form. So that's the first step. Someone would have to know to come in here to turn Navigation Options on and then Show Hidden Objects. Usually most Access users don't know that, especially if they're not Access developers. They're just using your database.
So that's step one. Step two is hiding this navigation pane altogether. Now you can minimize it. That's what I do when I'm working with the database because I still want to be able to get in here but I don't want to have to see it. So I minimize it. But let's hide it altogether.
To do that, we're going to go to File, then Options, then Current Database. So this is on the database level. This is affecting the database file itself. So you'll do this to the front-end file that you're going to put on their computers. All these changes affect the current database file. So slide down here and then Display Navigation Pane, turn that off. That will hide the navigation pane.
Now this is kind of confusing because Navigation Options are right here. I double-checked this just a minute ago. I copied this database file to my other computer just to make sure. This Navigation Options is for your computer. So if you turn this on or off, this affects your computer. It shouldn't be under Current Database. Access guys, change that - this is confusing in here. So I'm going to cancel that. This here is for the database file. Sorry if that's confusing, I didn't make it that way. This is like saying here, here's a shortcut to open up this other option. So you can do that.
The next thing is right down here - Allow Full Menus. This turns off all of the options on the ribbon, including going to Design View, which they won't be able to do in a minute anyways once we encrypt the database, but it gives them a limited set of options on the ribbon, like sorting and filtering and things like that.
You can also turn off Default Shortcut Menus. I don't usually go that far. That's when you right-click on something and it gives you that pop-up shortcut menu. You can turn that off too. I'll just show you how it works. But I don't usually do that, but you can.
And yes, you can create your own custom ribbon. That's another class altogether. I won't talk about that today, but you can set up your own ribbons too.
Hit OK now. This time you will definitely have to close and reopen the database. So shut it down, open it up. And now when it opens up, look, you're in a much better place, security-wise. They have a very limited set of menu options and there's no navigation pane over here. So they can't go poking around. They have to use your form menus.
And if they right-click - I'm right-clicking now - you don't get any menus in here, so you can't right-click and go to Design View here either.
Now, one thing I do have to mention at this level is it is possible to bypass all of those startup options. Let me close the database down. If you hold down the Shift key on your keyboard and then double-click on this file, when Access loads up, it bypasses all of your start options and your start menu and an AutoExec macro if you have one of those. So all those options that we just set are null and void if the user knows Access and knows enough to hold down the Shift key when they start the database.
Again, this is good enough security for the average user in your workplace that you trust. In my security seminar, I do show you how to disable the bypass key so they cannot do that. However, that involves a little bit of programming, so I'm not going to cover that today. But if you do want to know how to do that, I cover it in the security seminar.
But for the normal user, if you close that, if you open it up, you give them the shortcut on their desktop to do their work for the day, this is all they get. They can do refreshing, they can do some finding - that's it. Also, keep in mind, you will have to use the bypass key - hold the Shift key down, double-click - to get into your secret stuff.
Now the last step is to encrypt the database. What does that mean? Well, that means just taking this database file and removing all design access to it. It encrypts it, it compiles it into machine code, basically, so there's no VB code that they can access, and they can't get into design view of any of the queries, reports, forms, modules, or macros. They can see the tables, though. So encrypting the database does not hide the tables.
I'm assuming that if you're in a multi-user network situation, you have your tables in a split database on the back end, on a server somewhere, which is how you should have it set up. Again, I've got videos on that on my website and on YouTube on how to split your database. I'll put some links down below. If you're not sure what that is, that video will explain it for you.
Now, how do we encrypt this? It's really easy. File, Save As, make ACCDE. That's Access database encrypted, or an executable only, but it basically stands for encrypted. I just googled it, I looked it up; it is execute only as what Microsoft has it listed as. So it's Save As, hit Save. Now, close your database. And now you should have two copies of your database. Keep this guy around, your ACCDB. If you want to make design changes in the future, you'll need to use this, and in fact, this is the file that you can use to work with as well on your computer. This is the guy you're going to give everybody else, the encrypted or the execute only file. I've always called it an encrypted file, so it's a bad habit.
So let's open this guy up now, and you can see there it is. Now I turned off all of the navigation pane and the right-click menus. But let's use the bypass key on this one and see what we can get into. I'm going to hold the Shift key down, double-click. Even if I bypass your startup options, I still can't come in here and go to Design View on any of these forms. You can still see the tables in here. That's something that you really can't get around with Microsoft Access. The only way you can really super secure tables like that is to use SQL Server or another database server program.
In the security seminar, I show you different ways you can kind of hide the tables better. You could also simply not link to these. If you have a linked database where you are the only one that has access to the credit card information, just put those in a separate database file and don't link to them for the other databases. But now I can't create stuff, too. I can't create forms, I can't create reports, I can't create new modules. You can create queries and tables, but the encrypted executable only file will prevent people from going in here and messing with your design.
So those are my quick top four simple ideas to secure your database when you're not worried about someone intentionally damaging your database. You just don't want people going in there and poking around and seeing something they're not supposed to see. These four things should get you by. Like I said, I do have a full security seminar that shows you how to lock everything down on your database. You can set up a user log-on form, put people in different groups, track which users do what in the database, audit their actions, set up a system log table, show or hide different buttons on your menus depending on who's logged on, like people can't normally see the admin button, and lots of other goodies like locking down that bypass key.
I'll put a link to the security seminar down below the video. If you have any questions about it, let me know.
Want to learn more about simple security? In the extended cut video, I cover how to password-protect buttons. So, a couple lines of code, a little bit of programming, but it's not hard. I'll show you how to use an input box to prompt the user for a password. You can set up your own form, it's got all the things on it that you want, and then if the user tries to click on that button, they'll be asked for a password. That, in conjunction with the other stuff that I just showed you, is good enough to make it so that your menuing system is also protected from people who are just clicking around to see what they can click on.
Plus, I'll show you a couple more enhancements. You're going to want to keep using the ACCDB file that you made for yourself, so here's some nice little things. I'll show you how to minimize the ribbon on startup - it just snaps it shut and gets it out of your way. I like to do that for myself. Minimize the navigation pane - you want to leave the navigation pane open for you while you're developing, but it's nice to just have it so when your database starts up, it snaps shut. So there's a couple lines of code to do that too. And then I'll show you how to maximize Access on startup, the whole application. That's handy for you; me, particularly, I have some pretty big forms, so I like to have it so when I open up my database, the whole thing just maximizes to the full screen. And that's good for your users too, because if they're just only working with Access, you can have it maximize when it starts up and then they don't have to worry about seeing anything else and any of the distractions on their desktop.
So that's in the extended cut for members. Silver members and up get full access to all of my extended cut videos.
How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is one of the first steps recommended for securing sensitive data in an Access database?
A. Store sensitive data in a separate hidden table
B. Delete sensitive records after entry
C. Print all sensitive data for backup
D. Save sensitive data in a Word document
Q2. Why should the navigation pane be hidden in a Microsoft Access database?
A. To make the database look more professional
B. To prevent users from exploring tables and objects directly
C. To improve database performance
D. To enable automatic backups
Q3. What happens when you set a table to Hidden using Table Properties?
A. The table is deleted permanently
B. The table is visible only to users with admin rights
C. The table is hidden from the navigation pane unless "Show Hidden Objects" is enabled
D. The table's data is encrypted
Q4. How can users purposely bypass startup security settings in Access?
A. By repairing the database
B. By holding down the Shift key while opening the file
C. By resetting their password
D. By renaming the database file
Q5. What is the main benefit of compiling a database to ACCDE format?
A. It allows for more rapid data entry
B. It hides VBA code and restricts design changes
C. It increases network speed
D. It allows for automatic data backups
Q6. After encrypting a database to ACCDE, which of the following remains true?
A. Users can still edit VBA code
B. Queries, reports, and modules can be edited
C. Table design remains inaccessible, but data is viewable
D. Users can still view table data but cannot change the design
Q7. What should you do with your original ACCDB file after creating an ACCDE file?
A. Delete it to prevent unauthorized access
B. Give it to all users for backup
C. Keep it secure for making future design changes
D. Upload it online for remote access
Q8. What is a recommended way to further secure sensitive information, such as credit card data, from most users?
A. Store it in the same table as non-sensitive data
B. Leave it unprotected if you trust your users
C. Put sensitive data in a separate table and do not link it to the main database for non-authorized users
D. Store it in a spreadsheet
Q9. What does hiding the navigation pane and disabling the full ribbon accomplish?
A. Makes the database run faster for all users
B. Prevents users from running any queries
C. Prevents users from accessing or modifying database objects outside of form menus
D. Encrypts all data in the database
Q10. What additional security step can you take to protect specific buttons on your forms?
A. Add a password prompt to the button's click event
B. Remove the button completely
C. Hide all form buttons
D. Move the button to a report
Q11. What is a limitation of Access security mentioned in the video, even after applying all recommended steps?
A. Users can still open the database in Excel
B. Users can always see tables if they use the bypass key
C. Encryption automatically hides all data
D. Hidden tables cannot be recovered
Q12. What kind of relationships might you use to link a sensitive data table to your customers table?
A. Many-to-many or one-to-one relationships
B. Many-to-one or one-to-many relationships
C. Only one-to-one relationships are allowed
D. No relationships should be used
Q13. What is the purpose of a "Security Seminar" offered by the instructor?
A. To teach advanced VBA programming only
B. To demonstrate how to completely lock down and secure Access databases
C. To show how to build web applications
D. To explain how to use Access templates
Q14. What does the "Allow Full Menus" option control in Access database options?
A. The color of the menus
B. Which menus appear on the ribbon for users
C. How tables are printed
D. Which reports are exported
Q15. What is a recommended way to handle sensitive data if only one person (such as the business owner) should access it?
A. Store sensitive data in a standalone, unlinked, secured database file only accessed by that person
B. Share all database files via email
C. Write down sensitive data in a notebook
D. Give access to all employees equally
Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-D; 7-C; 8-C; 9-C; 10-A; 11-B; 12-A; 13-B; 14-B; 15-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 Today's video from Access Learning Zone is all about implementing straightforward security measures in your Microsoft Access databases. This question comes from one of my regular viewers who runs a small, family-owned business. He trusts his employees and is not concerned about intentional sabotage, but he does want to make sure that sensitive information, such as financial data and credit card numbers, stays private. On top of that, he wants to prevent accidental damage to the database. He refers to my comprehensive Access security seminar but mentions he is not looking for advanced, complicated security. He just needs some practical steps to keep things reasonably protected.
I can certainly help with that. For businesses that do not need heavy-duty security, there are plenty of adjustments you can make in Access that are quick and effective. While I do offer a full seminar dedicated to locking down Access databases to the maximum extent, including disabling startup keys and adding user-level security with programming, what I am going to cover here today will be sufficient for offices where the main goal is to keep things tidy and shield sensitive fields from casual viewing or accidental upset.
Here are my main tips for simple Access security:
First, take your most sensitive information and store it in a separate table. For instance, if you have a table for customers, consider placing data such as credit card numbers, expiration dates, and billing addresses in a new, dedicated table. Link this new table to your customer table using a foreign key. You might do this as a one-to-one relationship if each customer only has a single credit card, or as a one-to-many for those with multiple cards. This compartmentalizes the data and makes it much easier to control visibility.
Once you have created your separate table, you should hide it. In Access, you can make a table hidden so that it does not show up in the navigation pane for everyday users. Remember, this only affects how Access displays the table on your computer. If you're in a networked environment and have multiple front-end files, each user controls their own display of hidden objects. To make a table visible again, you need to adjust your local Navigation Options and choose to show hidden objects. Most end-users will not know how to do this, especially if they are not Access developers.
The next step is hiding the navigation pane entirely for your users. If you hide the navigation pane, people cannot snoop around in your tables, queries, forms, or reports. Instead, users will have to use your custom menu forms to navigate the database. This is done from the File, Options, Current Database area. There, you can disable the display of the navigation pane and also turn off Full Menus to limit what appears in the Access ribbon. With Full Menus off, users are limited to basic sorting and filtering commands, and they lose access to design views and advanced controls. You may also choose to disable Default Shortcut Menus, which would remove the context menus that appear when users right-click in the database.
Be aware that some menu options in Access, like Navigation Options, affect only your local installation, while display options in the Current Database section actually impact the file itself for anyone who opens it. After making these changes, you will need to close and reopen the database for them to take effect. Once you do, users will only see your menu forms and a reduced set of ribbon controls. They will not be able to browse objects directly, right-click into design views, or make unwanted modifications.
However, it is important to understand the Access startup bypass feature. Holding the Shift key while launching the database will override all of your startup settings and allow a knowledgeable user to see everything. For most environments where you trust your users, this is not a concern. For more robust protection, such as disabling this bypass key, some VBA coding is required, and I cover that topic in detail in my advanced security seminar.
The last essential step for basic security is to distribute your database as an ACCDE file. This format complies and locks your database, removing all access to form, report, and module design. Users cannot see your VBA code or modify your forms, reports, or queries in design view. Do keep in mind, however, that ACCDE files do not hide your tables themselves. If your tables contain highly sensitive info, you may consider moving them to a backend database, not linking them for users who do not need access, or using a dedicated database server like SQL Server for even stronger security.
To create an ACCDE, use the Save As menu and choose the ACCDE format. Make sure you keep a copy of your original ACCDB file in a safe place in case future design changes are needed. The ACCDE is the file you distribute to your users. Even with the SHIFT-bypass trick, no one will be able to view your VBA, form or report designs, or make structural changes. Table data, though, may still be accessible depending on how your database is structured.
Summing up, these four steps – separating and hiding sensitive data, removing the navigation pane, limiting ribbon and shortcut options, and compiling your database to ACCDE – are a practical set of safeguards for small businesses or offices that just need light to moderate security and do not face deliberate malicious threats.
If you are interested in a comprehensive approach to Access security, including enforcing user-level logins, tracking user actions, auditing changes, tailoring form buttons based on user permissions, and permanently locking out the Shift key bypass, consider enrolling in my security seminar.
In today's Extended Cut, I will show you how to add password protection to buttons on your forms, using just a bit of VBA. You will learn how to have Access prompt for a password before executing sensitive actions. I will also demonstrate how to have Access automatically minimize the ribbon, hide the navigation pane on startup for developers, and maximize the Access window when you launch your database, making the workspace better for both you and your users.
Silver members and above have access to all Extended Cut lessons and exclusive member perks. For details about membership and its benefits, visit my website.
As always, free TechHelp lessons will continue for everyone. If you want to see the complete step-by-step video tutorial for everything I discussed, you can find it on my website at the link below.
Live long and prosper, my friends.Topic List Hiding sensitive tables in Access
Hiding the navigation pane
Disabling the full ribbon and menu options
Encrypting the database to ACCDE format
Separating sensitive data into linked tables
Setting up one-to-one and one-to-many relationships for sensitive data
Hiding objects (tables, forms) from users
Bypassing startup options with the Shift key
Limitations of ACCDE for hiding tables
Distributing databases securely to users |
