Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

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: $59 - 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)
CustomerT
CustomerSecureT
One-To-One Relationship
Referential Integrity
Cascade Deletes
Cascade Updates

03. Customer Forms (11:36)
CustomerF
CustomerSecureF
Button to Open Secure Form
Without Security
VBA DoCmd.OpenForm
Add a Password
InputBox
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
Sharing
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)
ACCDE File
Distribution Copy
Save your ACCDB file!

07. Review (6:01)

 


 

 
 

Student Interaction: Access Split Database Seminar

Richard on 6/30/2012:  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 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 You can learn more about this seminar here: Microsoft Access Split Database Security Seminar
Richard on 6/30/2012: Now that you're finished with this seminar, make sure to check out my ACCESS SECURITY SEMINAR and ACCESS DATA ENCRYPTION SEMINAR for even MORE ways to protect your Access databases.
MUBEEZI M on 7/3/2012: Thank you Richard! I really enjoyed it!

For some strange reason, i can't find the back slash on my keyboard! Usually, i have to open Excel, type =CHAR(92) in a cell, then copy the resultant back slash into the VBA editor.
Is there something missing on my keyboard? Or is there a key combination that i have not yet learnt to utilise?

MICAH

Reply from Richard Rost:

Wow. I've never heard of that. Unless you have a foreign-language keyboard I don't know what the problem is. The backslash on ALL of my keyboards is on the same key as the PIPE symbol: |

Can you snap a picture of your keyboard and email it to me? amicron@gmail.com.


 Jerry on 7/7/2012: At 6:57. You opened the CustomerSecureF showing all secure information. You did this without using a password. I thought the purpose of this course was to show how to protect secure information? Please don't tell me that I can take another course. This one failled in its' stated objective. What gives?

Reply from Richard Rost:

Jerry, if your users have access to the NAVIGATION PANE they can open up any forms they want. You can HIDE the Navigation Pane on database startup (which is in Access Options > Current Database) so they can't. The password only pops up if you use the button to open the form. You COULD put the inputbox code in the OnLoad event of the secure form instead of the button, and then it wouldn't matter how you opened the form.

REGARDLESS of whether they can open the FORM or not, if they don't have security rights to the SECURED BACK-END TABLE database (which you set up at the WINDOWS level), then they won't see any data in the form or will get an error message. *I* have access to that back-end table, so the form opened.

Objective SUCCEEDED. :)

Mubeezi Micah on 7/8/2012: Dear Richard,

Actually, the backslash is together with the pipe symbol as you mention above.
When i press this key, i get # and if press the key together with the SHIFT key, i get ~.
On number 3 of my key board is the # key. When i press SHIFT+3, i get £.

I bought this computer from the Netherlands and i remember configuring Windows to English- United Kingdom.

Are there some additional settings that i may have to adjust?

MICAH

Reply from Richard Rost:

Try setting Windows to English-US.

Ricardo Tolliver on 3/25/2013: Q- After I've split my database, if the fornt and backend are on different computers how do I deliver changes for forms, reports and tables especially if the (Front End) can be on several different computers?

Reply from Richard Rost:

Well, you CAN keep a working copy of the Front End database on the SERVER for people to use. If your database isn't that big (a few MB perhaps) and your network is fast, it's OK to just have multiple clients running that file directly.

However, if you have the Front End distributed to lots of different workstations, then it's really up to you to distribute those changes. You can make it as simple as creating shortcuts to their database folders and they copying and pasting the new FE file yourself. Or you can email them a link and say "here, download this." I even went so far with one client to program an automated solution that would:

1. Check for a new version (number stored in a table)
2. If it was new, quit the database and launch a BATch file
3. Batch file copies new ACCDB file from server to local workstation
4. Batch file then re-launches database

It's completely up to you.

Richard

P.S. If enough people want to see how I created that automated solution, let me know. If I get a handful of people interested, I'll make a mini seminar out of it.

Deon Riley on 3/26/2013: Hi Richard,

Yea bring it on. I'll definately go for this as a mini seminar. You know me by now - anything Access.

Brent on 3/27/2013: I'm interested for sure.
Nick P on 3/28/2013: I'd like to see that.
Brauna Rosen on 5/17/2013: Hi,
I just need to confirm something with you: the back end resides on my computer but not on the shared file and the front end is on my computer's shared file and on the users shared file. Is that correct?

Reply from Richard Rost:

If you have two computers (PC1 and PC2) you can set up a SHARED FOLDER on PC1. In that folder, put your BACK-END database (tables). It doesn't matter where the FRONT-END files go. You can put them on the Desktops of PC1 and PC2 if you like. Each computer should have its own copy. Now, just link to the tables in your SHARED FOLDER, and you're all set.

Amar on 6/4/2013: Hi Richard,

Thank you for your split database seminar; it was very helpful. I plan on taking your security seminar as what I really want to know how to do is to lock down the navigation bar together - even keeping users from holding 'shift' and bypassing the startup.

I am using Access 2007 and created the ACCDE file version hoping that it would hide the design of all of the objects. While this was successful in hiding form designs, I noticed that the queries were still accessible to the users. I wanted to know what your thoughts are on designing the queries using either SQL or the QBE grid and then feeding the control source with the SQL code. This will keep the query from being created and will keep the user from accessing both the form and query design. Of course, this is a temporary solution that I would use until I take your security seminar.

Thank you for all of your excellent videos!

Reply from Richard Rost:

For advanced users (and databases that I create for myself and for clients) I use as FEW queries as possible. I prefer writing SQL in my forms and reports unless the query is complex.

An ACCDE will not hide the table data or structure, or query structure. It just locks down your forms, reports, macros, and modules.

James Gray on 1/15/2014: Rick,
I had an intersting thought about splitting data from the front end. Is it possible to have your data files reside in "the cloud" i.e., on an online data backup location? That way each user would just have to have the front end on thier machines and transmit data to and from the backup system? Just thinking outside the box and trying to avoid having to buy and set up an LAN/server for my ambulance service.

Lauro Cecilio on 2/1/2014: Lesson3 CustomerForms of AccessSplitDatabase: I followed the steps to the letter in the CodeBuilder (clock 8:51mark) - used apple too as my password; could not get the command button to prompt a password
what did I do wrong?

Option Compare Database
Private Sub Command31_Click()
X = InputBox("Password:")
If X <> "apple" Then Exit Sub
DoCmd.OpenForm "CustomerSecureF", , , "CustomerID=" & CustomerID
End Sub

Reply from Richard Rost:

Your code looks fine to me. Perhaps add a:

Dim X as String

in there and see if it helps. Are you getting an error?

John Miller on 2/10/2014: By using the technics in this seminar does this change the 2GB limit of Access or do need to add the 2 or more back ends together?
lauro c on 2/15/2014: There was no error generated when the Command Button is clicked
I don't understand your instruction to add Dim X as a string - - - where do i actually add it?
sorry, no idea re: code building

>>>>>>
Lesson3 CustomerForms of AccessSplitDatabase: I followed the steps to the letter in the CodeBuilder (clock 8:51mark) - used apple too as my password; could not get the command button to prompt a password
what did I do wrong?

Option Compare Database
Private Sub Command31_Click()
X = InputBox("Password:")
If X <> "apple" Then Exit Sub
DoCmd.OpenForm "CustomerSecureF", , , "CustomerID=" & CustomerID
End Sub

Reply from Richard Rost:

Your code looks fine to me. Perhaps add a:

Dim X as String

in there and see if it helps. Are you getting an error?

Robert Baxter on 4/23/2014: Great, practical tutorial! But I have one question. Pardon me if this has already been asked:

At 9:20 you purposely try to input the wrong password to check the code. But pw is visible. Is there a way to add an input mask, like "****" to prevent anyone looking over the users shoulder from reading the pw they just typed in? Thanks!

Reply from Richard Rost:

Not in an InputBox, but you can create your own form with a textbox control. Set the input mask on that to Password and it will provide the desired results.

David Koehler on 6/21/2014: excellent, excellent presentation

Reply from Richard Rost:

Thanks. :)

David Koehler on 6/21/2014: When it comes to setting up windows share properties, arent you also interest in turning off offline caching

Reply from Richard Rost:

That's a good idea, yes. I don't believe "offline caching" is the default setting though - but I could be wrong.

Gergely Papp on 10/31/2014: Hi Richard!

Thanks for the thorough seminar. I've checked several forums and in general people don't recommend copying the backend database to a shared folder on a NAS server. I've just learned that Synology can manage mySQL. Could it be an appropriate solution for a small company (with 5 client computers in various locations) to use the Synology server as SQL server.

Thanks For your help!

Gergely

Reply from Richard Rost:

I don't generally recommend an Access-only solution like this for high-traffic databases. However, I've successfully set up 20- and even 50-user databases with JUST split Access databases, so it depends on the thruput and network speed.

I've never used Synology, so I cannot comment on their product. However, I recommend you take a look at SQL SERVER EXPRESS. It's a free version of Microsoft's powerhouse SQL Server database, and it's a GREAT back-end solution. The Express edition is FREE and handles most small- and mid-sized business needs.


Gergely Papp on 11/17/2014: Thanks a lot! So what would you combine Access with in this situation?
Carolyn on 6/8/2015: Can you set up the shared folders on the cloud? For example, Box or Dropbox?

Reply from Alex Hedley:

Yes there is usually a right click option on the folder or a share button link.

Mary Franklin on 2/18/2016: Rick, I used your seminar on workorders to make my database. I've split the database now but I can't open my linked PDF's in my workorder file that was already there. But I can add a linked PDF.Can you help me!
Mary

Reply from Alex Hedley:

Are they stored in the db or outside?

Fran on 3/5/2016: They are in a folder outside of database but on same computer as database folder. I wanted to put copy of front end on different computer's and the backend on network/server. Everything works except this linked pdf. I really don't know vba coding that's why the seminars are so great! If front and backend stay on network/server with the pdf folder everything works great! I guess I could make a copy of front end for each person on network/server?

Reply from Alex Hedley:

Do the machines have access to this folder? If you try and navigate to that folder from the machine is it accessible?
The front end would be copied to the individuals machine and the back end stay on the server
Are you using the full server name and not a mapped letter?

Ravi Chandiran on 11/12/2016: Richard,

I am interested on this topic.

Thank You,

Ravi Chandiran

Reply from Alex Hedley:

Which topic?

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP