Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  

Access Split Database Seminar
Set up a secure database using Windows File & Folder Security



In this seminar, you will learn how to set up a database using Microsoft Access with multiple back-end (table) files: one that every database user can access, and another for sensitive information, such as financial data, that only a small, select group of users can access. You will then learn how to use Windows file and folder sharing to secure the sensitive back-end database file so that only the users you select can read that data.

This seminar stands on its own, however you will benefit the most if you take this seminar after watching my Access Security Seminar. That seminar focuses mainly on locking down and securing your FRONT-end file (forms, reports, queries, VBA code, etc.) whereas this one focuses on hiding and securing your back-end data files.

Watch this video to learn more about this seminar:


Seminars - Access Split Database Security
Description: Set up a secure Access database using multiple back-end tables and Windows file/folder security with users and groups.
Versions: I will use Access 2010, however the lessons are valid for all versions of Access back to 2000 with some minor cosmetic differences.
Pre-Requisites: This course stands alone, however you would benefit greatly from having first taken our Access Security Seminar. You should have completed at least our Beginner-level Access courses before taking this seminar.
More Info: Full disclosure: if you've already taken the Security Seminar, about half of the material covered in this seminar will be a review for you. It was necessary to include some of the same material so that this seminar stands alone. The other half of the material, especially the seconds on the Linked Table Manager, setting up Shared Folders, and Windows File Sharing are new.
Running Time: 1 Hour, 8 minutes
Cost: $69.99 - Order multiple courses to receive a discount up to 50% off


This seminar will cover:

1. Set up multiple customer tables, one for normal data, one for sensitive data
2. Build two customer forms; the sensitive form requiring a password to open
3. Split your database into 3 files: front end and two back-end files for tables
4. Create shared folders on your server or Windows PC
5. Use Windows-level file & folder security to prevent access to the sensitive data
6. Move the back-end files to the server and relink with the Linked Table Manager
7. Encrypt your front-end database and create an ACCDE file

In Lesson 1, we'll talk about WHY you should split your database and what security benefits it provides. In our example, we'll build a customer database. Most of the customer information has to be accessible by all of the users of our database (name, address, phone number, etc.) however some of the information needs to be kept secure and ONLY visible to a select few (credit card numbers, balances, credit limits, etc.)


In Lesson 2, we'll set up a customer table, a secure customer table, and then we'll set up a one-to-one relationship between them.


In Lesson 3, we'll build a customer form and a secure customer form. We'll set up a command button to open the secure form but require a password via an inputbox. Users will need the password to open the secure form. Then we'll hide the navigation pane on database startup so that users can't just browse to your secure form and open it themselves.


In Lesson 4, we'll split the database into three files: one for our front-end objects (queries, forms, reports, modules, etc.) and then two back-end files: one for the normal customer data, and another for the sensitive, secure data.


In Lesson 5, you'll see how to set up shared folders on your network file server. I'll show Windows 2000 Server, but most versions of Windows Server work the same. I'll also show an example using just peer-to-peer file sharing on Windows 7 machines. We'll then move the back-end database files to these shared folders and use the Linked Table Manager to tell the front-end database where they went.


By using the techniques in JUST this seminar, you can reasonably secure your data against unauthorized access from users on your network. Coupled with the techniques shown in my Security Seminar and Data Encryption Seminar, you can really create a locked-down database without the need for a Database Server program. Of course, if you have any questions about whether or not this seminar is for you, please contact me.


Access Split Database Security Seminar - Outline

00. Intro (5:01)

01. Why Split Your Database (5:07)

02. Customer Tables (10:53)
One-To-One Relationship
Referential Integrity
Cascade Deletes
Cascade Updates

03. Customer Forms (11:36)
Button to Open Secure Form
Without Security
VBA DoCmd.OpenForm
Add a Password
Hide Navigation Pane

04. Splitting the Database (5:57)
Database Tools
Move Data > Access Database
Database Splitter
Copy and Rename Backend Files

05. Set up Shared Folders (14:21)
Windows Server
Share This Folder
Share Permissions
Full Control
Users & Groups
File Sharing in Windows 7
Linked Table Manager
Refresh Links

06. Encrypted Front End (8:43)
Distribution Copy
Save your ACCDB file!

07. Review (6:01)




You may want to read these articles from the 599CD News:


Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn