Access Data Encryption Seminar
Make the data in your Access tables unreadable
Welcome
In my Access Security Seminar, I teach you everything you need to know to lock down and secure your Access front-end database. You can set up users and groups, control who has access to what forms and reports, keep a log tracking activity, and lock down your database design so nobody can make changes or see your VBA code.
The problem is that your back-end database file (your tables) has to remain open and exposed. Anybody who knows Access can find it and poke around in your data. Unless you have a database server (like SQL Server or Oracle) on your network, you have no other way of locking down your data. Anybody who knows Access can see anything. Not good.
In this seminar, I teach you how to hide your data by encrypting it. If anybody happens to find your Access tables, all they will see is unreadable gibberish. Meanwhile, the database will seamlessly translate that gibberish into real data for the users of your database who have access to view and edit it. Oh, and as a side note, you can also use this technique to send encrypted emails to people too.
 Resources
Main Seminar Goals
- Learn how to scramble data in your tables
- Hide sensitive information
- Convert text characters to their ASCII codes
- Perform mathematical obfuscation on your data
- Secure the form that handles the encryption / decryption
Topics Covered
We will begin by building a simple customer table that will hold all of our information. The public information (name, address, etc.) that doesn't need to be encrypted will just have a single field. The private data (credit limit, notes, etc.) will have an extra field for the encrypted value.
We'll set up a public customer form - again with just the data that everyone can see (in this case, just the customer's name). We'll make a button to open a private form that has the secret data. Now, I show you how to control WHO can gain access to these records inside the database in my Security Seminar. In that Seminar you learn how to set up user and group accounts, and control who can see what forms, reports, and data.
Then we'll build a second form to show the private data. There will be VBA code that runs when the form opens that decrypts (unscrambles) any hidden data (in this case, the credit limit and notes). Then when the user clicks the "Save & Close" button, those fields are encrypted again and the data saved to the table. I'll show you how to lock this form down so the user MUST click on your Save button to close it, which runs your code.
Now if anyone happens to come across your back-end database file that contains your tables, all they will see is gibberish in the table itself. The actual data has been wiped, and they see garbage.
This solution isn't perfect. Someone can still erase or edit your data. That's one of the problems with Microsoft Access... the back-end tables aren't secure. This solution, however, will allow you the best possible means for keeping people from viewing sensitive data in your database without having an actual database server. Of course, if you have any questions about whether or not this seminar is for you, please contact me.
Pre-Requisites
This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video.
Version
I am using Access 2010 in this seminar, however the lessons are perfectly valid for all versions of Access. It's currently 2022 and I've made sure everything works fine in Access 2019 and Office 365.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
Keywords
microsoft access data encryption, hide data, encrypt table
Intro In this seminar, I will show you how to encrypt data stored in your Microsoft Access tables using a custom encryption algorithm. We will talk about the limitations of Access security, review key points from the Security Seminar, and focus on how to scramble sensitive information in your tables so it remains unreadable to unauthorized users. I will walk you through creating a customer table, building forms to display both public and encrypted data fields, and implementing an encryption module with VBA code that works in any version of Microsoft Access.Transcript Welcome to another seminar brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In this seminar, I am going to show you how to encrypt the data in your Access tables. This seminar can be considered a follow-up to my Access Security Seminar. The Security Seminar is a seven-hour long, full seminar that covers lots of material. Basically, I show you how to lock down your database so you can control who has access to what inside the database.
We set up user and group security inside the database, where you create a list of users and passwords. You can set up groups. You can control which objects inside your database they have access to, who can open what forms, who can view which reports, and who can run what queries. You control all their access inside the database. You can even create a system log to see who is doing what and who is changing what data. I show you how to encrypt the front-end database, so your forms, reports, your Visual Basic code, and all that information is encrypted and locked so no one can see it.
The problem, unfortunately, is that if you have a user who is fairly savvy and knows Access and he gets into your back-end database table, he can see all the information that you are trying to encrypt by setting up a secure database. Unfortunately, it is a limitation with Access. The tables have to remain open and unsecure. Back in some older versions of Access, there was a thing called the user and group level security wizard, but it was not very good and it was easily hacked. The tables have to be pretty much open for people to be able to use that data, and that data in the tables is unsecured unless you have a database server program like Microsoft's SQL Server or Oracle or MySQL, or any kind of a secure database server.
So my solution is to scramble the data. Take the information in the tables and basically make them unreadable to anyone but you. We will create an encryption algorithm which will take meaningful data and scramble it all up, and then we will make it so that you can unscramble that data on the fly.
In conjunction with the security tips that I teach you in the Security Seminar, which basically is how to control people in the database to see what records they have access to, that plus this will give you a totally secure database. It still does not lock down the tables. People can still get in there and delete data or try to change things, but at least they will not see sensitive information, and that is the goal for today's class: to scramble this information, so even if someone does get at your tables, they cannot see the information.
I will be using Access 2010. This is a more advanced seminar, so we will be going relatively quickly. This will work with any version of Access. All the events and the VBA code and stuff that I show should work with all the older versions of Access. The ribbon in the table design itself will look slightly different, but you should be able to figure it out regardless of what version of Access you have.
Just a quick preview of the finished product: we are going to create a customer table - real simple. We are going to create two forms: a standard customer form with all the information that you want publicly visible by anybody or at least anybody who has access to view your customers. Then we will make a second form that says "Show the Encrypted Data."
Here is the encrypted data. Here are the public fields. Here are the encrypted fields. I will put a new value in here, 3000. "We are called yesterday." Whatever. We will control access to this form so people have to use your button to close it and that will scramble the data.
Now, if someone is able to get into the tables, this is all they will see: a zero credit limit, scrambled notes, and scrambled credit limit over here.
That is what we will be doing in today's class by building this thing over here called an encryption module.
Thank you.Quiz Q1. What is the main focus of this seminar?
A. Encrypting data in Access tables
B. Creating forms in Access
C. Importing data into Access
D. Building relationships between tables
Q2. What limitation does Access have regarding table security?
A. It completely locks tables from all users
B. Tables remain open and unsecured to users who can access them
C. Tables are only encrypted if using Access 2016 or later
D. Only queries are left unsecured in Access
Q3. What additional software can provide true table-level security in a database environment?
A. Microsoft Word
B. SQL Server or Oracle
C. Adobe Acrobat
D. Google Sheets
Q4. What is the solution presented in this seminar for protecting sensitive data in Access tables?
A. Hiding the tables in the Navigation Pane
B. Scrambling (encrypting) the data within the tables
C. Renaming the tables to obfuscated names
D. Compacting and repairing the database
Q5. What will the encryption algorithm do in this seminar?
A. Convert the data into images
B. Scramble meaningful data and allow it to be unscrambled on the fly
C. Remove all duplicate records
D. Create backup copies of each record
Q6. Which version of Access is being used in the seminar?
A. Access 97
B. Access 2003
C. Access 2010
D. Access 2019
Q7. What type of objects can you control user and group security over in Access, according to the video?
A. Only tables
B. Only forms and reports
C. Forms, reports, queries, and other database objects
D. Only queries
Q8. What will students create in this class as part of the encryption approach?
A. A basic customer table only
B. Two forms, one for public and one for encrypted data
C. A new custom ribbon
D. Macros for exporting data
Q9. What does the seminar instructor say about users gaining access to back-end tables?
A. They will not be able to change any data
B. They will not be able to see any information at all
C. They might see scrambled data but cannot read sensitive information
D. They will receive an automatic warning and be logged out
Q10. Why does scrambling the data help improve database security, according to the seminar?
A. It prevents the database from being opened at all
B. It allows sensitive information to remain protected even if someone accesses the tables
C. It makes the database run faster
D. It prevents data loss from power outages
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on how to encrypt data in your Microsoft Access tables. This seminar is designed to build upon the concepts presented in my Access Security Seminar, which is a detailed, seven-hour course covering various strategies for protecting your Access databases. In that previous seminar, I explained how to lock down your database by managing who can access different database objects, including setting up users and groups with unique passwords. You also learn how to assign permissions for viewing or editing specific forms, reports, and queries. Additionally, I cover ways to create a system log to track user activity and changes, and how to encrypt the front-end components of your database, such as forms, reports, and VBA code, so unauthorized users cannot access them.
However, a limitation with Access is that even with front-end security in place, savvy users who can reach your back-end database are still able to see the information stored in your tables. In older versions of Access, there was a user and group level security wizard. Unfortunately, this was not very secure since it could be easily bypassed, and Access generally requires tables to remain open for data access. This means sensitive information in your tables remains exposed, unless you work with a secure database engine like Microsoft SQL Server, Oracle, or MySQL.
To address this vulnerability, my solution is to encrypt, or scramble, the information stored in your tables so that it becomes unreadable to anyone who does not have permission. In this seminar, I teach you how to create an encryption algorithm that transforms meaningful data into a scrambled, unreadable format. Then, I show you how to unscramble that data as needed for users who have authorized access.
When you combine these encryption techniques with the security measures taught in the Security Seminar, you can create a much more secure Access application. Although this approach does not prevent people from deleting or altering data in your tables, it does prevent them from viewing sensitive information, which is the main objective of this class.
In the seminar, I use Access 2010. Since this is an advanced topic, the material is presented quickly, but everything covered is compatible with older versions of Access as well. You might notice slight differences in the ribbon or table design depending on your Access version, but the VBA code and principles remain the same.
To give you a sense of what we will build, I start by demonstrating a simple customer table and then create two forms. The first is a standard customer form, which displays information that can be accessed by anyone permitted to view customer data. The second form is set up to show the encrypted data. On this form, the public fields and the encrypted fields are side by side. When entering new values, such as an updated credit limit or notes, we control access so that users need to close the form with a designated button. Doing so will scramble the sensitive data.
If an unauthorized person tries to access the tables directly, all they will see are scrambled values, such as mixed-up notes and credit limits, rather than the actual data.
The main focus of today's training is teaching you how to construct this encryption module and use it to protect your data.
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 Encrypting data in Access tables
Scrambling table data with an encryption algorithm
Creating a customer table for encryption practice
Building standard and encrypted customer forms
Displaying public versus encrypted fields on forms
Developing an encryption module in VBA
Unscrambling encrypted data on the fly
Controlling form access for secure data handling |
