MS Access Advanced

1. MS Access Advanced Instructor: Vicki Weidler Assistant:

2. 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

3. Unit 1: PivotTables & PivotCharts

4. PivotTable Interactive table that enables you to organize, summarize, & compare large amounts of data

5. PivotChart Displays data as a chart & allows you to analyze data graphically

6. Unit 2: Creating Advanced Forms

7. Functions Date () Current date DateAdd Perform date calculations i.e. DateAdd (“d”, 3, Date())

8. Grouped Controls Two or more controls placed in a group Generally organize related controls together in a group Change properties of multiple controls simultaneously

9. 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

10. Improving the User Interface • Tabs • Read-only forms • Opening a form at startup • Switchboard forms • Subforms

11. Unit 3: Creating Macros

12. 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

13. Unit 4: Creating Advanced Macros

14. Advanced Macros • Data validation • Data-entry • User-decision • AutoKey • AutoExec

15. Unit 5: Exploring SQL

16. SQL Statements SELECT Field_name FROM Data_source WHERE Criteria Example: SELECT Product_ID, Product_name, Unit_price FROM Product WHERE Unit_price > 2

17. Matching Data from Related Records SELECT [Source1].[Field_name], [Source2].[Field_name] FROM Source1, Source2 WHERE [Source1].[Common_field]=[Source2].[Common_field]

18. Unmatched Data from Related Records SELECT [Source1].[Field_name] FROM Source1 WHERE [Common_field] not in (SELECT [Source2].[Common_field] FROM Source2)

19. 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

20. Unit 6: Internet Integration

21. Internet Integration • E-mail addresses • URLs • Other files • Data access pages

22. Unit 7: Managing Databases

23. Performance Analyzer • Analysis results • Key • Analysis notes

24. 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

25. 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

26. Compact & Repair Utility • Fragmentation • Increases size of database • Compacting • Repairing

27. Database Replication • Avoid data loss • Replica set • Synchronized • Design master • Changes to structure in Design master only; changes to data in both master & replica

28. 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

29. 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

30. Unit 8: Security Fundamentals

31. 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

32. 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

33. 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

34. Special Group Permissions

35. 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

36. User-Level Security Wizard • Create a new user • Add user to required group • Creates copy of database before setting security permissions on it

37. 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

38. 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

39. 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

40. 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

41. 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.

42. 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

43. 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

44. 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

45. 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

46. 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

47. Conclusion • Resources • Questions & Answers • Evaluations • Thank You!!!