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.

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.

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

|