Access 2007-2019
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  
 
 
 

Microsoft Access Security Seminar
Proper Security for your Access Databases

 
This seminar will teach you how to properly secure your Microsoft Access databases. But that's not all. You will also learn how to build proper security into the workflow of your database so that you can control every aspect of a user's experience, and monitor their work as well in a database log.

You will learn how to properly secure your Access databases to control who can log in, who can edit data, and who can make changes to the database itself. But that's only the beginning. You will learn how to build a database where you can control every step in the "work flow" process of data entry, and create a system log so you can see who is doing what.

There are two more videos below showing
additional details. Scroll down to find them.

 


 

 
Seminars - Access Security
Description: Learn how to properly secure an Access database and control the "work flow" of information with user and group permissions.
Versions: I use Access 2007 in the videos, however the lessons are valid for all versions of Access back to Access 2000 and work just fine with Access 2010. I will show any differences between 2007 and 2003. The lessons on the User-Level Security Wizard are specific to Access 2003.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 313 very helpful
Running Time: 7 hours, 4 minutes
Cost: $199

 

This seminar has several main goals. You will learn how to:

1. Create user and group security inside your database
2. Manage the "work flow" of your database
3. Control who has access to which database objects
4. Send email notices from inside your database
5. Create a detailed system log to track users
6. Encrypt and secure your database files
 

You will begin by creating a user information table (with username, password, and other data) and a secure logon form. This will allow us to control who can log on to the database.

 

Next we'll set up a Group form so we can assign users to different groups, like SalesRep, ServiceTech, Manager, Admin, etc.

 

In the next lesson, we'll define a "work flow" for our database. This will control how information is entered by our users. For example, our database will be for service work orders. Our work flow will consist of a sales rep entering a work order, a service technician performing the work, a manager reviewing the details, the sales rep follows up with the customer, and then the manager closes the work order.

 

You will be able to control which users will be able to enter data into which fields. For example, only service technicians can enter data into the "service performed" field, but not the "service requested" field, which is the job of the sales rep.

 

We will control each step of the work flow process with buttons that signify that a user is done with his work, and the work order is ready for the next person. Here you can see a "service call completed" button that will then send this work order to the manager for review. This work order will then be locked out so the service tech can't edit it any further.

 

 

You will learn how to send Email notices from inside the database when needed. This is great if you have employees that don't check the database all the time, so as to say, "your assistance is required."

 

The database will log every step in the work flow procedure. You will be able to see who did what, and when.

 

We will also create a system log table, so we can log everything that a user does: what time he logs on, what data he adds, what records he changes, etc. You name it, we can log it.

 

You will learn how to build your own internal database security. For example, do you want sales reps to be able to edit other sales reps' customers? Do you want service techs to be able to see customer data if they're not working on that customer's work order? Who can edit, delete, or add new customer records? What information should be required for new customers? Some of these are things you just can't do with standard Access User-Level Security.

 

You will be able to hide or show buttons and forms based on a user's security level. For example, the menu that a system administrator might see...

 

...will be completely different from what a simple service technician sees. This will help to control who can do what in your database.

 

We will spend a lot of time learning how to lock down your database and secure it from intruders. We will split the database into front-end and back-end files, encrypt the database with a password, turn off the Navigation Pane (database window) to keep people from poking around in your objects, set a Startup Form, and disable the Bypass Key so people can't go to the Navigation Pane directly when the database starts up.

 

You will learn how to dynamically link to tables in your back-end database using VBA code to overcome some security problems.

 

We will encrypt the front-end database into an ACCDE file (MDE file for older versions of Access). This will effectively secure the form, report, and module design capabilities and prevent people from viewing your database in design mode.

 

For our Access 2003 users, we will spend some time going over the User Level Security Wizard. This wizard will secure an Access 2003 database and provide "OK" security if all you want is to keep Joe from Accounting from poking around in form design, and seeing data he's not supposed to. It's not hack-proof, however, but it does provide "quick and dirty" security in a pinch. We'll set up users, passwords, groups, and permissions.

 

We will encrypt the front-end database into an ACCDE file (MDE file for older versions of Access). This will effectively secure the form, report, and module design capabilities and prevent people from viewing your database in design mode.

 

 

This seminar is perfect for anyone who wants to learn how to secure an Access database, prevent someone else from accessing parts of the database they shouldn't, assign permissions so you can control who does what, and create a work flow strategy in your database.

This seminar is long (over seven hours) but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish.

Click here for another video showing my step-by-step lesson plan guide to what's covered in each lesson of this seminar in detail.

AccessLearningZone.com
Click to Play - Step-By-Step Lesson Plan


Click here for a video walk-thru of exactly what this database does, and see how the flow of information going into the database can be tightly controlled from sales rep to service tech to manager.

AccessLearningZone.com
Click to Play - Database Walkthru

 

All of the sample database files for this seminar are available on my Web site (instructions on where to download them are in the course videos). They are available in Access 2007 and 2000 formats.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first lesson absolutely free.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Security Seminar Outline

00. Introduction (8:00)

01. Database Setup, Login Form (12:23)
Create a New Database
Trusted Locations
Set Overlapping Windows
Setup UserT Table
Access User Level Security
Why ULS is Not Very Secure
"Good Enough" Security
Create Login Form
Popup & Modal Forms

02. Login Form Code (11:42)
Docmd.Quit
Check for blank username/password
DLOOKUP Username and Password
NZ Function

03. Main Menu (9:43)
Exit Database Button
UserID, Username Fields on Main Menu

04. User Groups (14:35)
GroupT Table
Group to User Cross Reference Table
Junction Table
UserF Form
GroupF Subform
Adding Users to Groups

05. Checking Group Permissions (12:35)
Open User Form Button
Code to Check if User in Group
Is the User an Admin
Defining Your Own Function
Public Function
Return a Boolean
Global Modules

06. Service Process (9:06)
Define Service Processes
Create CustomerT Table
CustomerF Form
StatusT Track Work Order Status
ServiceT Detail on Service Call

07. Service Form (10:40)
Build Service Form
Create Combo Boxes

08. Service Listbox (11:23)
Listbox Service for Current Customer
OnCurrent Event
Modify SQL for Listbox to Format
Format Function in SQL

09. Sales Rep Security 1 (10:51)
Security: Who can Browse Customers
Assigning Customers to Sales Reps
Query to Show Only Sales Reps
Sales Rep Combo Box for Customers

10. Sales Rep Security 2 (10:43)
AllowAdditions, AllowDeletions, AllowEdits
Setting Properties in VB Code
Change Properties Based on User Group
Change Properties Based on SalesRepID

11. Locking Fields on Form (8:56)
Locking All Fields on a Form
For Each Control Loop
RGB Color Function

12. Unlocking Needed Fields (7:08)
Unlock Fields for Sales Reps
Unlock Fields for Service Techs

13. Create Service Order 1 (10:08)
Create New Service Order Button
Enable/Disable the Button
Get Default Value from Another Form

14. Create Service Order 2 (14:01)
Requery Service Listbox OnGotFocus
Open a Specific Service Order
Set a Default Sales Rep
Security Opening Service Orders

15. Assign to Service Tech (10:27)
MsgBox vbYesNoCancel
Lock Fields Once Assigned to Tech

16. Unassigned Service List (12:47)
UnassignedServiceQ Query
service List Form

17. Accepting Service Call (7:33)
Accept Service Call Button
Perform Service

18. Unassigned or My Calls (7:58)
Show Unassigned Service Calls
Show My Service Calls Button
Show ALL Service Calls
Dynamic SQL in RowSource of ListBox

19. Service Completed (11:32)
Mark Service Completed
Show Status on Service List
Show New, Open Service Calls

20. Hide Buttons on Main Menu (14:30)
Show/Hide User Form Button
Show/Hide Browse Customers Button
Open Service List Show Different Things
Private v. Public Form Functions

21. Fixing Some Problems (8:01)
Fix Problem with Sales Reps
Fix Missing Button Problem
Force Service Details to be Entered

22. Manager Review (12:29)
Manager Review 1 Button
Show Service Calls to be Reviewed
Assign For Followup Button
Hiding All Buttons on ServiceF

23. Followup, Closing Call (11:38)
Sales Rep Followup Completed Button
Manager Closed Button

24. Sending Email Notices (13:00)
Sending Email using Microsoft Outlook
Docmd.SendObject

25. Admin User Combo (11:51)
Make the UserID a Combo Box
Quick Change to Another User

26. Locking the Database 1 (9:43)
Split the Database
Front End v. Back End Databases
Encrypt Database with Password
Open Exclusive
Linked Table Manager
Get External Data
Create Links to Backend Tables

27. Locking the Database 2 (13:28)
Backup Your Database
Turn off Navigation Pane
Turn off Database Window
Show Startup Form - LoginF
Application Title
Display Navigation Pane
Allow Full Menus
Allow Shortcut Menus
Hold SHIFT Key to Bypass Security
VBA Code to Shut Off Bypass Key
AllowBypassKey
Database CreateProperty
Properties Collection

28. Locking the Database 3 (10:09)
Import Linked Tables Bypasses Security
Create Admin Menu

29. Locking the Database 4 (13:55)
Global Const
Dynamically Link to Tables
Destroy Links to Tables

30. Locking the Database 5 (14:45)
Create a Recordset
OpenDatabase
OpenRecordset
Read User Info Before Linking Tables

31. Locking the Database 6 (17:09)
OnClose Event
Terminate Table Links
The Navigation Pane Shows
The Database Window Shows
Link Tables using TableDefs

32. Creating an ACCDE File (9:11)
Make ACCDE File
Make MDB File
Source Code (Design) is Gone
VB Debug Compile to Find Errors

33. AC2003 User Level Security 1 (10:36)
User Level Security Wizard Part 1

34. AC2003 User Level Security 2 (12:43)
User Level Security Wizard Part 2
Create a Workgroup File
Securing Objects
Selecting Security Groups
Creating User Accounts
Creating an Unencrypted Backup
Logging on with User Permissions
User and Group Permissions
Add or Delete Users
Add or Delete User Permissions

35. Other Database Properties (8:40)
Show All Database Security Properties
VBA Code to Change Properties
StartupShowDBWindow
StartupShowStatusBar
AllowBuiltinToolbars
AllowFullMenus
AllowShortcutMenus
AllowDefaultShortcutMenus
AllowToolBarChanges
AllowBreakIntoCode
AllowSpecialKeys
AllowBypassKey

36. Database Log (17:02)
Track Employees with a Database Log
Create LogT Table
Create LogIt Function
Track User Logons
Track Data Changes
OldValue Property

37. Review (2:59)
Review Topics



MAIN CONCEPTS COVERED

Creating your own security inside the database
- Making a user table
- Logon form
- User groups with permissions

Controlling the "work flow" in your database
- Sales rep enters a work order
- Service tech performs the work
- Manager approves it
- Sales rep follows up
- Control who can enter data into what fields
- Buttons to move to the next step in the process
- Locking access to data once that step is completed
- Sending an Email notice with Outlook for the "next step"
- Creating a system log to watch everything your users do

Determining which users can access what forms, fields, data
- Sales reps can't edit each others customers
- Service techs can't see customer data
- Users can't bypass the "work flow"
- Managers can edit everything
- Admins can supervise and change access rights
- Who can browse customers?
- Who can see service reports?
- Showing or hiding buttons/forms based on access rights
- Preventing record edits, deletions, or additions
- Forcing users to enter required data

Properly Securing Your Database
- Split the database into Front-End and Back-End files
- Encrypt Back-End database with a password
- Turn off Navigation Pane, full menus, toolbars, etc.
- Disable the Bypass Key for startups
- Dynamically link to tables using VBA code on database startup
- Destroy those links when database closes
- Compile your database into an ACCDE (MDE) file
- Access 2003 User Level Security Wizard
- Working with Access 2003 users, groups, and permissions
- Advanced database properties in VBA such as AllowBreakIntoCode

Programming Topics
- Using DLOOKUP to lookup usernames & passwords
- Many-to-many relationships with junction tables (users/groups)
- Locking fields on a form based on user rights
- For Each control loops
- Creating your own global modules with public functions
- Dynamic SQL rowsource for listboxes
- AfterUpdate, OnCurrent, OnGotFocus events
- Use the RGB function to set color in VBA
- Getting a value from a form using Forms!Formname!Field notation
- Create and edit databases in VBA
- Define global constants
- Create and work with RecordSets to read user info
- Use a recordset to read from an external database with a password

 


 

 

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
Change Email
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