IST 220 – Intro to Databases

1. IST 220 – Intro to Databases Lecture 2 Touring Microsoft Access

2. Microsoft Access As A DBMS • Basic objects are provided to support common DBMS functionality • Multiple views are used to provide graphical interfaces to different users • User friendly graphical interfaces make define and implementation DB’s easy • Macros (and modules) facilitate convenient application capabilities IST 220 -- Intro To Databases

3. Main Objects In Access • Table – defines and stores data • Query – provides a custom view of data from one or more tables • Form – provides a convenient way to enter, display, and/or print data • Report – presents selected data in attractive fashion • Page – formats selected data in HTML file so that is can be displayed by a Web browser • Macro – built-in functions that can be customized to automate certain processes • Modules – VBA code for further automation IST 220 -- Intro To Databases

4. Objects & Their Relationships Macros Open/Filter Print/View/Filter Trigger Trigger Forms Reports Queries DA Pages Tables IST 220 -- Intro To Databases

5. Typical Usages • Administrators/Developers • Use all objects • End users with read/write permission • Use forms to display, add or modify data entries they are allowed to access • End users with read-only permission • Read data from reports • End users have no knowledge of macros IST 220 -- Intro To Databases

6. The Database Window IST 220 -- Intro To Databases

7. Tables • Tables contain columns and rows, or • Fields– store different types of data, and • Records– collect all the information of a particularinstance of the subject(or an entity) • No two records may be identical • A set of attributes which can uniquely identify a record is referred to as a primary key of the entity. • For two different records, their primary keys may have the same value. IST 220 -- Intro To Databases

8. Design & Normal Views • Design view is used during development • Normal view is used • By end users during operation • By developers during development, to make sure their product looks right • For tables, normal view is the datasheet view, in which data are represented in a tabular format IST 220 -- Intro To Databases

9. Table Window In Datasheet View IST 220 -- Intro To Databases

10. When Is Data Saved? IST 220 -- Intro To Databases

11. Table Window In Design View IST 220 -- Intro To Databases

12. Find & Replace IST 220 -- Intro To Databases

13. Data Validation • Data validation is a crucial part of any database system • Some simple validations are supported automatically by Access • A non-numeric value is entered to a field which is integer by definition • More sophisticated validation are implemented by user • Garbage In, Garbage Out(GIGO)– no system can produce valid output from invalid input IST 220 -- Intro To Databases

14. Forms • Forms are used to • Control and enhance the way the data looks • Perform extensive editing using macros • Link multiple forms or reports by using macros that are run from buttons on a form • Forms can be displayed in • Design view, or • Form view IST 220 -- Intro To Databases

15. Form Window In Form View IST 220 -- Intro To Databases

16. The Clubs Table IST 220 -- Intro To Databases

17. Queries • Queries can be used to select data from one or more tables • E.g., to select club names and addresses from the club table • The outcome of a query can also be represented by a table • Queries can be displayed in two views • Design view • Datasheet view IST 220 -- Intro To Databases

18. Filters & Sorting • Filter by selection • Filter by form • Specify multiple criteria simultaneously • Specify logical relationship among criteria • Logical AND or logical OR • Remove filters • Sorting in • Ascending order, or • Descending order IST 220 -- Intro To Databases

19. Example – Filter By Form Original data Filter definition Filtered data IST 220 -- Intro To Databases

20. Query Window In Design View IST 220 -- Intro To Databases

21. Reports • Reports help to format your data in an attractive fashion & be ready for printing • They can also help to carry out • Calculating • Grouping and summarizing • Features frequently used include • Page header and footer • Grouping and summarization IST 220 -- Intro To Databases

22. Report Window In Print View IST 220 -- Intro To Databases

23. Data Access Pages • Data access pages can publish your data as Web pages • To display them, Access is needed on the user’s local machine • Used by employees on the company’s intranet, to view and/or modify data • The two views are • Design view • Page view IST 220 -- Intro To Databases

24. Page Window In Page View IST 220 -- Intro To Databases

25. Macros • A macro cannot be displayed, it can only be run • No normal view, only design view available • A macro doesn’t relate to data directly, but to actions on data • Take effects only through forms and reports on which a macro is embedded IST 220 -- Intro To Databases

26. Objects & Their Relationships Macros Open/Filter Print/View/Filter Trigger Trigger Forms Reports Queries DA Pages Tables IST 220 -- Intro To Databases

27. Macro Window In Design View IST 220 -- Intro To Databases

28. Modules • Similar to macros, cannot be displayed, only triggered from other objects • Include a number of VBA procedures or functions to provide more power in more flexible ways IST 220 -- Intro To Databases

29. Objects & Their Relationships Trigger Modules Run Macros Open/Filter Print/View/Filter Trigger Trigger Trigger Forms Reports Run Queries Run DA Pages Data flow Action Tables IST 220 -- Intro To Databases