Transcript Chapter 10: Distributed Databases and the Internet

All Powder Board and Ski
Microsoft Access Workbook
Chapter 10: Database Administration
Jerry Post
Copyright © 2007
1
Access System Tables: Metadata
MSysObjects
lists all tables
Check to see
system tables
Tools/Options/View tab
2
Action
Action
Choose Tools/Analyze/Performance
Select all objects
Run the analysis
Add indexes if they are reasonable
3
Performance Analyzer
Select all items
Select all objects
4
Performance Analyzer Suggestions
Recommendations
are the highest
priority
Some suggestions
cannot be taken
because of
business rules
The optimize
button applies the
recommendation
5
Creating Indexes
Menu: View/Indexes
SQL: CREATE INDEX …
Single column indexes
6
Action
Action
Choose Tools/Database Utilities/Compact and Repair Database
Close the file but not Access
Run Compact and Repair again, this time enter a new name for the
compacted database to use as a backup copy
7
Backup and Recovery




Use Windows tools to copy the mdb file
Creating a compressed (zip) folder substantially
reduces the size
Use Tools/Database Utilities/Compact and Repair to
remove deleted items and fix minor errors
If a database is corrupted, you can sometimes recover
data and other items by starting a new database and
using File/Get External Data/Import to retrieve
whatever you can from the damaged file
8
Simple Security: Assign a Password
Close the database
and reopen it in
Exclusive mode
Tools/Security/Set
Database Password
Enter the password and verify it.
Close the database and reopen it.
9
Action
Action
Choose Tools/Security/Set Database Password
Enter test as a password
Choose Tools/Security/Encrypt
Close the database and reopen it
10
Encryption
Unencrypted data
Tools/Security/Encrypt/
Decrypt Database
Encrypted database
11
Action
Action
Choose Tools/Database Utilities/Make MDE File
Close the database and open the new MDE database
Try to edit a form
12
Protect Forms with an MDE File
Tools/Database
Utilities/Make MDE
File
Design view is disabled
for everyone
Keep the original file safe. It is the only way
to edit the forms, reports, and modules.
13
User-Level Security in Access
Database
Administrator
Assign permissions
Database Application
Form1 Form 2 Form 3 Form 4
Workgroup database
Usernames and
passwords
User 1
User 2
14
User Groups
Sales
table
Customer
table
Item
table
Sales clerks
S,U,I
S,U,I
S
Sales Managers
S,U,I,D
S,U,I
S
S,U,I
S
Rental Managers
Sales Managers
Assign permissions to groups
based on tasks, and assign
users to groups. Permissions
only have to be set once.
Employee changes are
handled by moving individuals
into or out of groups.
Sales clerks
Individual users
15
Action
Action
Choose Tools/Security/User-Level Security Wizard
Create a new workgroup
Select the Backup Operators group
On the users screen, set a password for your username
Add three users
Make sure you are in the Admins group
Close the database
16
User-Level Security Wizard
Every WID is unique
and is used to recover
the workgroup data if
the file is lost.
Avoid the first option. It
will force you to log in
every time you open
Access for any
database.
17
Predefined User Groups
18
Adding Users
19
Assign Users to Groups
Make sure that you
assign at least one
person to the
Admins group!
20
Action
Action
Open the database using the desktop link
Login with your username and password
Choose Tools/Security/User and Group Accounts
Select the Groups tab and add SalesClerks and SalesManagers groups
Add one of your new users to each group
Choose Tools/Security/User and Group Permissions
Grant specified permissions for the two groups to the Sale, SaleItem,
Customer, and Inventory tables
Assign appropriate permissions to the database and the forms
Close the database
Log in as a sales clerk and test the permissions
21
Customized Groups (after login)
Select the
Groups tab to
enter new
group names
Select users and
add them to
groups to grant
additional
permissions
22
Assigning Permissions to Groups
Assign by groups
New groups need to be able
to open the database
Click the Apply button
to activate changes
23
Implementing a Secured Database
Copy files:
1) Secured database
2) Workgroup database
3) Startup link
Edit the properties
for the startup link
"C:\Program Files\Microsoft
Office\Office11\MSACCESS.EXE"
“D:\FullPathname\AllPowder2003.mdb"
/WRKGRP “D:\FullPathname\Secured.mdw"
24