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
