Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Comments for Access Data Encryption Lessons

Age	Subject	From
2 years	test program works How do I	Nathan Shepard
2 years	A goofy error	Nathan Shepard
2 years	Where do I put the code	Nathan Shepard
3 years	this is greek to me	Nathan Shepard
4 years	Download	Scott Adkins

Subscribe to Access Data Encryption Lessons
Get notifications when this page is updated

Intro

In this seminar, I will show you how to encrypt data stored in your Microsoft Access tables to help protect sensitive information from unauthorized viewing. We will talk about the limitations of built-in Access security, why table-level encryption is important, and how to build a simple encryption algorithm using VBA. I will walk you through creating customer tables, forms to display both public and encrypted data, and methods to scramble and unscramble information on the fly. This seminar is intended for users familiar with Access who want to add another layer of data protection to their databases.

Transcript

Welcome to another seminar brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. In this seminar, I'm going to show you how to encrypt the data in your table, 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're trying to encrypt by setting up a secure database.

Unfortunately, it is a limitation with Access. The tables have to remain open and unsecured. 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 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, Oracle, MySQL, or any kind of a secure database server.

My solution is to scramble the data. Take the information in the tables and basically make it unreadable to anyone but you. We'll create an encryption algorithm which will take meaningful data and scramble it all up. Then we'll 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. 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, VBA code, and stuff that I show should work with all the older versions of Access. The ribbon and 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.

As 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 shows the encrypted data.

Here is the encrypted data. Here are the public fields. Here are the encrypted fields. I'll put a new value in here, 3000. We're 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's 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 purpose of the seminar described in the video?
A. To show how to design forms in Access
B. To demonstrate how to backup an Access database
C. To teach how to encrypt data in Access tables
D. To explain how to convert Access databases to Excel

Q2. Which prior seminar does this video build upon?
A. Access Macros Seminar
B. Access Security Seminar
C. Access Forms Seminar
D. Access Reports Seminar

Q3. What limitation does Access have when it comes to securing tables?
A. Forms and reports cannot be encrypted
B. VBA code cannot be protected
C. Tables must remain open and unsecured to use data
D. Queries cannot be assigned permissions

Q4. What was the flaw with the older User and Group Level Security Wizard in Access?
A. It was too complicated for most users
B. It was not compatible with VBA
C. It was easily hacked and not very effective
D. It made the database very slow

Q5. What is Richard's solution for protecting sensitive data in Access tables?
A. Hide the tables from the navigation pane
B. Encrypt the database file with a password
C. Scramble the data using an encryption algorithm
D. Make tables read-only through property settings

Q6. What additional tool does Richard mention that would provide even more database security?
A. An Access add-in
B. A secure database server like SQL Server or Oracle
C. An advanced macro script
D. A password-protected Excel sheet

Q7. What is the role of the encrypted form in the seminar project?
A. To allow printing of sensitive data only
B. To display and interact with scrambled data
C. To convert data formats to CSV
D. To generate automatic emails

Q8. What happens when someone unauthorized accesses the tables directly after encryption is implemented?
A. They see only field names but no data
B. They see public data but not encrypted fields
C. They see scrambled data that is unreadable
D. They are denied access to the database entirely

Q9. What is created in the seminar to handle data scrambling and unscrambling?
A. New table relationships
B. A series of queries
C. An encryption module
D. A custom ribbon tab

Q10. What version of Access is specifically used in the seminar, although the material applies to other versions?
A. Access 2003
B. Access 2007
C. Access 2010
D. Access 2013

Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-B; 7-B; 8-C; 9-C; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on teaching you how to encrypt data within your Access tables. My name is Richard Rost and I'll be guiding you through the process.

This seminar works as a continuation of my Access Security Seminar. In that lengthy, seven-hour course, I go step-by-step through securing your database by managing user and group permissions. It covers how to create user and password lists, form groups, and dictate what access each group has within your database. With those tools, you can decide who is allowed to open forms, view particular reports, or execute specific queries. There is even a segment on tracking user activity through a system log to identify who is making changes to your records.

I also show you how to encrypt and protect the front end of your database. This includes encrypting all forms, reports, queries, macros, and Visual Basic code so nobody can examine or alter them without the required permissions. However, there is a major limitation: if someone knowledgeable enough about Access manages to open your back end tables directly, they will still see all of the data stored there without any protection.

This is the primary weakness of using Access without a dedicated database server like SQL Server, Oracle, MySQL, or other secure options where data can truly be locked down. Access on its own requires tables to remain largely accessible to users or else the database will not function properly. In older Access versions there was a user and group level security wizard, but it proved unreliable and was easily bypassed. As a result, the content of your tables tends to remain exposed.

My solution to this problem is to scramble or encrypt the data inside your tables so that only you or authorized users can read it. We will build a simple encryption algorithm to make that data unreadable in its stored form while ensuring that information can be decrypted and presented on demand. This works alongside the user security strategies from my other seminar, allowing you to manage who can see records with an added layer of data protection.

It is important to note that even with this method, someone could still access your tables and attempt to delete or change information. However, what they cannot do is read any sensitive details, which is the main objective for this lesson: to obscure information at the table level.

In this seminar I use Access 2010, and I'll move at a brisk pace since this is aimed at more advanced users. The principles and techniques work on most versions of Access, so even if your screen looks a bit different, you will be able to follow along.

To give you an idea of what we will create, we'll set up a simple customer table and develop two forms. The first is a standard customer form for public data or anything that should be easily accessible. The second form will handle encrypted data, demonstrating both the secure storage and on-the-fly decryption. Users will see only public fields by default, and when accessing sensitive details, they will need special permissions. We will control access to ensure that sensitive actions, such as closing the form, trigger the encryption process.

When viewing the encrypted records directly through the tables, all people will see is nonsensical data: meaningless numbers and scrambled text rather than anything confidential.

Everything discussed in this lesson revolves around building an encryption module to achieve this extra security. If you want detailed, step-by-step instructions with demonstrations, you can find the complete video tutorial on my website at the link below.

Live long and prosper, my friends.

Topic List

Encrypting data in Access tables

Creating a customer table for encrypted data

Designing a standard customer form

Designing a form for encrypted data

Controlling form access for encryption

Scrambling sensitive data in tables

Creating a custom encryption algorithm in VBA

Unscrambling encrypted data on the fly

Building an encryption module in Access