Microsoft Access

1. Microsoft Access A Hands-On Introduction Chapter 1

2. What is Microsoft Access? • A Windows software application. • An application development environment. • A data organizer. • A collection of Information tools. • A Relational Database Management System. • All of the above.

3. What is a Database? • A Database is a collection of information organized for a specific purpose. An example might be information about a movie collection. A telephone book is another example. In a given day you might use many objects that could be considered databases.

4. What is a Database? (contd.) • In Access, we work with what is called a Relational database. Access is an RDBMS. The term Relational means that each table in the database contains information about a single subject and only that subject. Each table however will typically be related to one or more other tables. An example would be a Customers table related to an Orders table. More on this later.

5. Types of Databases Flat file: like a spreadsheet Flat files are limited in their use Used for less complicated databases Waste disk space as the complexity increases Customer Ship method Invoice Invoice Item Quantity Unit cost date number number Mendenhall Steel UPS Blue 12/1/05 229901 B4500 2 $2.33 Mendenhall Steel UPS Blue 12/1/05 229901 C2134 4 $3.45 Mendenhall Steel UPS Blue 12/1/05 229901 Z2344 6 $342.33 Mendenhall Steel UPS Blue 12/1/05 229901 M4098 8 $12.98 Mendenhall Steel UPS Blue 12/1/05 229901 Q1243 10 $132.33 Mendenhall Steel UPS Blue 12/1/05 229901 P0918 12 $134.95 Gerrity Lumber FedEx 12/1/05 229902 P0918 1 $134.95 Gerrity Lumber FedEx 12/1/05 229902 M4098 2 $12.98 Gerrity Lumber FedEx 12/1/05 229902 C2134 3 $3.45

6. Types of Databases (Contd.) Relational: Contain information that spans multiple topics or subject tables. Use common information to link the subject tables. Use less disk space. Are more complex to design than flat-file systems. Are more efficient in terms of Data extraction.

7. Access as an RDBMS • With an RDBMS, you have complete control over how you define, work with and share data. • There are three types of capabilities provided; data definition, data manipulation, and data control. • You can use an Access database as either a desktop database serving one user or as a table sharing database on a network serving many users. • Access is also useful as a front-end to a client/server database such as SQL Server or Oracle. • The possibilities are endless!

8. Data Definition • You define what Type of data is stored in your database. For example text or numbers or a combination of both. • You define how the data is related. • You may also define how the data is formatted and how it is validated. • You also determine the level of data integrity desired.

9. Data Manipulation • In Access, you can work with your data in many ways. • You can select which data fields you want. • You can sort the data and filter it. • You can work with multiple tables using relationships that you have defined. • You can summarize data, update and delete data and also copy it to another database if you wish.

10. The Data Control feature allows you to define who may read, update and insert data. This functionality also allows you to define how data can be shared and updated by multiple users at the same time. A true RDBMS is designed such that if users are sharing data, no two users can be updating the same record concurrently. Another feature is the ability to create transactions which are multi-step processes where all steps either succeed or fail. This ensures data integrity. Data Control

11. When Is Access Appropriate? As a self-contained system A fully functional desktop database system for a single user or for multiple non-concurrent users on a standalone computer. As a networked multi-user system A multi-user system using Access MDB database files shared on a network file server. Supports record locking and refreshing. As a front-end to an external database system A front-end control system for ODBC-compliant databases. Supports Structured Query Language (SQL) calls. ODBC = open database connectivity

12. How Access interacts with Windows • Access is a visual design tool. -- Supports all Windows design standards. -- Drag and Drop support. -- Supports all Windows Menu standards. -- Is part of Microsoft Office so works seamlessly with all Office applications. -- Includes the Visual Basic Programming language.

13. How is Access visual? • Most Access objects are created with visual design tools. • These designers work in a way that could be described as a Computerized Erector Set. • Another set of tools available are called Wizards. Wizards simplify object creation via a multi-step question and answer process. • Wizards are a great tool for creating objects such as tables, queries, forms and reports. • The end result is maximum creation for minimal work.

14. To Summarize • How would you best describe Microsoft Access? • What are some examples of databases you might use in the everyday world? • What does RDBMS stand for? • What is the non-relational type database called? • Using Access as a front-end means what? • Access is part of what software suite? • Now that you have a general idea of what Access is and how it works with databases, let’s take a closer look at its parts. • We will learn about the major objects contained in Access and then drill down and discover how it works with data.

15. What are the major objects. • An Access database or MDB file contains seven major objects. -- Forms Visual interfaces for display and acceptance of data. -- Tables Created with simple point and click, drag and drop functionality.

16. Major objects (Contd.) -- Queries Objects that provide views of data from one or more tables. Created with sophisticated visual Query by Example tool. -- Reports Similar to forms but designed for hard copy output. Like Queries, also created with visual design tools.

17. Major objects (Contd.) -- Data Access Pages Similar to forms, however are accessed by user with a Web browser. -- Macros Provide some rudimentary programming. -- Modules VBA code that provides additional capability beyond the other main objects.

18. Major objects (Contd.) • More difficult design tasks must be done with manual coding. • Responding to special types of user input. • More complex data validation. • Creating buttons and toolbars that respond in special ways. • Bulk processing of records. • VBA modules can take your database to the next level.

19. Access as a Relational Design Tool • True relational model • Access implements a true relational database model. • Allows Access to function as a frontend to other client/server database systems. • Integrated data • All data tables and indexes are integrated into an Access file. • Integrated forms • Access databases contain not only the data, but also the forms and reports for that system in the same file.

20. The Windows Way • Windows programs are event driven • Windows is a multitasking operating system. • Windows uses an event-driven model to allow programs to respond to the user. • Access programming requires responding to Windows events using event procedures. • Some examples of events are: Mouse Clicks, a Keypress, selecting an item in a Listbox, a Form opening or closing. • The list is seemingly endless.

21. The Windows Way (Contd.) • Working with Access means working with objects. • Everything in Access is an object. • Tables • Forms • Queries • Reports • Pages • Macros • Modules • This object paradigm requires a new approach to programming to fully exploit the features of Access objects.

22. Where is the Data? Database (MDBFILE) Records in Table Tables in database Fields in Record

23. Tables • Tables contain data about a particular subject. -- Orders -- Order Items -- Vendors • A table is a container of records. -- A record contains data about one Order, one Order Item, one Vendor. • A record is a container of fields. -- A field for example in an Order record might contain one piece of information such as ShipDate. • All records in a table contain the same fields.

24. Fields • Each field contains information about a specific thing such as VendorNumber, OrderDate, ItemCost. • Each field contains a specific type of data such as Text, Currency, Yes/No, Numeric and more… • Some fields might contain small amounts of data and others many hundreds or even thousands of characters of data. • Access allows many different data types.

25. Creating order out of chaos • Data entered into a table winds up in what is called Natural Order. That is, each record is added just following the previous record. • Natural Order bears no resemblance to the way we typically need to view our data.

26. Creating order out of chaos (Contd.) • As you can see, natural order is not very useful so Access allows us to assign indexes to fields. This enhances speed of retrieval and sorting of records. Internal index Natural table order Table Record pointer

27. Indexes • Indexes provide: -- Quick retrieval of records. -- Quick sorting of records. -- Faster query response. • Indexes are actually internal tables that contain field contents and a record pointer. When the database is opened, Access places the indexes in memory for efficiency. • An index specified for the Last Name field in a Customers Table would be useful for rapid retrieval of a given customer record. • Indexes can be specified as unique or duplicates allowed.

28. Primary key • A field or combination of fields that uniquely identify a record. • A given table can only have one Primary key. • Multiple fields would only be needed in the event that one field cannot guarantee uniqueness. • Access does not require a table to have a Primary key, however most tables will have one. • Access can create one for you if necessary. • Although not a requirement, Primary Key fields are usually the first fields in each table. • Internally, Primary keys are really indexes that are required to be unique.

29. Primary keys in Table relationships

30. Primary keys in table relationships (contd.) • Primary keys are used to create relationships with other tables. • This is sometimes called a Parent/Child relationship. • The Primary key of one table is linked to a field in the second table. • The field in the second (child) table must be the same data type as the Primary key field of the first (parent) table. • This field in the child table is sometimes called a Foreign Key. • As you may have noticed in the relationships screen, the linked fields were spelled the same. This is not a requirement however, but a useful design goal. • These sorts of challenges are what make working with databases so interesting and enjoyable.

31. What we have covered • What is Access. • What is a database. • The ways we can work with data. • The visual emphasis of Access. • Access as a Relational Database Management System • The major Access objects. • Tables as data containers. • Primary keys and indexes. • Table relationships.