All Powder Board and Ski Microsoft Access Workbook
All Powder Board and Ski Microsoft Access Workbook Chapter 9: Database Administration Jerry Post Copyright © 2003 1
Access System Tables: Metadata MSys. Objects lists all tables Check to see system tables Tools/Options/View tab 2
Performance Analyzer Select all items Select all objects 3
Performance Analyzer Suggestions Recommendations are the highest priority Some suggestions cannot be taken because of business rules The optimize button applies the recommendation 4
Creating Indexes Menu: View/Indexes SQL: CREATE INDEX … Single column indexes 5
Backup and Recovery Use Windows tools to copy the mdb file Creating a compressed (zip) folder substantially reduces the size l Use Tools/Database Utilities/Compact and Repair to remove deleted items and fix minor errors l 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 l l 6
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. 7
Encryption Unencrypted data Tools/Security/Encrypt /Decrypt Database Encrypted database 8
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. 9
User-Level Security in Access Database Administrator Assign permissions Database Application tials creden Workgroup database Usernames and passwords Form 1 Form 2 Form 3 Form 4 logi n User 1 User 2 10
User Groups Sales table Customer table Item table Sales clerks S, U, I S Sales Managers S, U, I, D S, U, I S Rental 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 Managers Sales clerks Individual users 11
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. 12
Predefined User Groups 13
Adding Users 14
Assign Users to Groups Make sure that you assign at least one person to the Admins group! 15
Customized Groups (after login) Select the Groups tab to enter new group names Select users and add them to groups to grant additional permissions 16
Assigning Permissions to Groups Assign by groups New groups need to be able to open the database Click the Apply button to activate changes 17
Implementing a Secured Database Copy files: 1) Secured database 2) Workgroup database 3) Startup link Edit the properties for the startup link "C: Program FilesMicrosoft OfficeOffice 10MSACCESS. EXE" “C: Full. PathnameAll. Powder 2002. mdb" /WRKGRP "C: Full. PathnameSecured. mdw" 18
- Slides: 18