ms access advanced instructor: vicki weidler assistant:
TRANSCRIPT
Overview
• Create PivotTables and PivotCharts • Build forms based on joined tables • Automate data entry • Use grouped controls • Design subforms • Create and run macros • Write simple SQL statements • Work with hyperlinks and data access pages • Explore database utilities • Encrypt and decrypt a database • Protect a database with passwords and user-level
security• Discuss modules, VBA, & MDE files
PivotTable
Interactive table that enables you to organize, summarize, & compare large amounts of data
Grouped Controls
Two or more controls placed in a group
Generally organize related controls together in a group
Change properties of multiple controls simultaneously
Option Group Controls
Make data entry easier on forms
Each control represents a numeric value
Can bind control to a field in a table & store it or use for calculations on a form
Improving the User Interface
• Tabs• Read-only forms• Opening a form at startup• Switchboard forms• Subforms
Macros
• Database object that automates a common task or set of tasks
• Stores and runs a set of actions sequentially
• Associate macros to events so that when an event occurs, macro is executed
• Attach macros to command buttons
SQL Statements
SELECT Field_nameFROM Data_sourceWHERE Criteria
Example:
SELECT Product_ID, Product_name, Unit_priceFROM ProductWHERE Unit_price > 2
Matching Data from Related Records
SELECT [Source1].[Field_name], [Source2].[Field_name]
FROM Source1, Source2WHERE [Source1].[Common_field]=[Source2].[Common_field]
Unmatched Data from Related Records
SELECT [Source1].[Field_name]
FROM Source1WHERE [Common_field] not in (SELECT [Source2].[Common_field]FROM Source2)
Attaching an SQL Statement to a Database Object
To query data while working in a form, make want to attach an SQL statement to a form control
Database Splitter
• Split into front-end & back-end• Back-end contains tables & front-end
contains user-interface objects • Back-end on centralized network• Copies of front-end on users’ computers• Increases speed of data retrieval• Ensures users always updating same database
Setting Permissions for Split Databases
• Permissions to read-only, add, delete, or edit
• Set permissions for back-end• Will also apply to front-end• Note: Read-only back-end, users will not
be able to modify data using linked tables or any other database object on front-end
Database Replication
• Avoid data loss• Replica set• Synchronized• Design master• Changes to structure in Design master
only; changes to data in both master & replica
Synchronization
• Not automatic; must synchronize at regular intervals
• New objects created after replication are not updated during synchronization
• Must export new object from original database (Design master) to replica
Encryption
• Secure database to prevent other programs (i.e. viruses) from accessing it
• MS Access compacts it & makes it indecipherable
• Users can still access database objects
Password Protection
• Authorized users can access all objects in database
• Open database in exclusive mode when setting the password so others cannot modify it
• Use when only a few users have to access the database
User-Level Security
• Vary type of permissions given to each user depending on their level of responsibility
• Assign permissions to a particular user explicitly or to a group of users
User-Level Security Cont.
• Provide each user with a different user account
• Assign users to a user group when several people perform similar operations, then specify permissions for the entire group
• When creating a database, there are 2 groups: users & admin
Special Group Permissions
Read-Only Users Only view data; cannot modify it
New Data Users Only add new data; cannot modify existing data
Full Permissions Add, modify, & delete data; can also modify design of database objects; cannot modify table relationships
Security Wizard
• Creates a workgroup info file• File stores permission details for
users for each database object• Permissions become effective
when user logs into MS Access with the appropriate user name
User-Level Security Wizard
• Create a new user• Add user to required group• Creates copy of database before
setting security permissions on it
User & Group Accounts
• Create new user & group accounts• Name & PID (personal ID) for each
account• Use dialog box to assign a user to a
group• To log on as a different user, must
close MS Access & start again to log on
User & Group Permissions
• Modify permission set for a user• After workgroup information file is
created• Set permissions for each object
for each user or group
Modules & VBA
• Macros best for simple tasks• Macros separate from database objects• Difficult to manage many macros• Modules help manage multiple automated
tasks more efficiently
• Modules extend capabilities of the database
Modules & VBA Cont.
• Modules are objects containing VBA code• VBA = Visual Basic for Applications• Language for writing programs that work with
Windows applications• Create set of instructions for computer to
perform specific actions
• Can program forms, reports, command buttons & other controls
Modules & VBA Cont.
• Write code to display message boxes, perform calculations, add or edit records & close forms
• VBA is part of MS Office Suite• MS Access has a VBE (Visual Basic Editor) to
write VBA code for modules• Unlike macros, VBA code is built into the design
of a form, report, etc.
Modules & VBA Cont.
• When objects are moved, underlying code moves with it
• When managing a large database, using code is easier to maintain & decreases number of objects you need to work with
MDE Files
• Used when you want others to use database• If an MDE file, users cannot view or edit VBA
code• CANNOT view, modify, create, import, or
export forms, reports or modules in Design• CAN work with tables, queries, data access
pages & macros in all views
MDE Files Cont.
• CAN import or export them to non- MDE databases
• MDE file functions same as any other database, but smaller in size since VBA code is removed
• Always save a copy of original database first so you can change design when needed
MDE Files Cont.
• To save a replicated database as an MDE file, must first remove replication
• Also, cannot replicate a database after saving it as an MDE file
• Should be in MS Access 2002 (or higher) format before saving as an MDE file
Summary
• Create PivotTables and PivotCharts • Build forms based on joined tables • Automate data entry • Use grouped controls • Design subforms • Create and run macros • Write simple SQL statements • Work with hyperlinks and data access pages • Explore database utilities • Encrypt and decrypt a database • Protect a database with passwords and user-level
security • Discuss modules, VBA, & MDE files
•Resources•Questions & Answers•Evaluations•Thank You!!!
Conclusion