Download Microsoft Access Tutorial - Lecture Notes | CSE 412 and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
Microsoft Access Tutorial
Introduction
This tutorial introduces you to the basic concepts of developing database applications in Access by examining the sample Company Employee Administrative Database (COMPANY). This tutorial will guide you through an examination of 6 database objects in the sample COMPANY database that are the basic building blocks of Access applications. Along the way you will learn the basics of these objects and receive pointers to additional Access techniques that you can further explore on your own.
Discovering Access
The basic building blocks of Access applications are known as database objects. There are 6 basic database objects in Access—tables, queries, forms, reports, macros and modules. All Access database applications are built from these database objects. Tables, queries, forms and reports have properties that govern their behaviors and their general appearance. The rest of this section will examine one instance of each of the 6 objects in the COMPANY database. You will also examine some of the common properties that are applicable to them.
To prepare for examining the COMPANY database:
- Start Access and open the COMPANY database.
- When you see the Main Switchboard, close it by selecting Close from the control menu at the top left corner. (Note: The Main Switchboard is programmed to refresh its time display every 60 seconds. After refreshing, the Main Switchboard will be displayed on top of all other windows and it will cover the window in which you are working in. This is quite disturbing while you are examining the system. So keep the Main Switchboard closed while you are examining the system.) Note that each window has a control menu at the top left corner. Selecting close on this menu closes the window. You may also close the current window by selecting close on the File menu. Although the latter option is not available for closing the Main Switchboard or by double-clicking the upper left-hand corner of the window.
- Select Unhide from the Window menu to open the Unhide Window.
- Click OK and the Database Window shown in Figure 1 will appear.
Microsoft Access Tutorial
Figure 1 Database Window of COMPANY database.
You are now ready to examine the system. Follow the steps marked by the symbol Ö.
Microsoft Access Tutorial
It is not likely that you will need to set all the properties of one single field but you will probably use most of the properties in one database. We suggest that you to take time to learn all the field properties after you finish this tutorial.
In addition to field properties, a table also has its own properties that function in ways similar to field properties.
Ö Select Properties from the View menu and the Table Properties Sheet will appear. Ö Click on the Validation Rule … box. Ö The value in the property box is too long for the box to display. Press Shift+F2 now to open a Zoom Box to see its value. Ö Click Cancel to close the Zoom Box. (Note: You can use Shift+F2 to zoom into any field or box using a Zoom Box.) Ö Select Properties from the View menu again to close the properties sheet. Ö Select Datasheet view from the View menu to open the table in datasheet view. You should see a table with records in it. You can edit the records in the datasheet view by typing in a cell on the datasheet. Ö Close the datasheet to return to the database window.
Access supports the definition of relationships between tables. A relationship specifies the fields that can be equijoined together to derive related data from the tables specified by the relationship. Access uses relationships for various purposes. The most important use of relationships is to enforce referential integrity.
Ö Select Relationships from the Tools menu to open the Relationships Window.
The Relationships Window lets you create, edit and view relationships in a database. The visual display of the relations in a Relationships Window also lets you visualize a database’s relational design with referential integrity. Each box in the Relationships Window represents a table. Each line between two tables represents a reference relationship. When referential integrity is enforced, the cardinality ratio will also be shown on both ends of a line (see the one-to-many cardinality ratio between EMPLOYEE.SSN and DEPENDENT.ESSN). Notice that the cardinality ratios in a Relationships Window is not the same as the cardinality ratios in the database’s ER model. The cardinality ratios in a Relationships Window represents the cardinality after an ER model is mapped into tables. For this reason the cardinality ratios in the Relationships Windows can only be one-to-one and one-to-many, as many-to-many relationships in the ER model must be mapped into two one-to-many relationships using an intermediate table (e.g., the WORKS_ON table is needed to represent the many-to- many relationship between an EMPLOYEE entity and a PROJECT entity in the ER model). You can edit and view information of any relationship using the Relationships Dialog box.
Microsoft Access Tutorial
Ö Double click the line between DEPARTMENT.DNUMBER and PROJECT.DNUM to view the relationship in a Relationships Dialog box.
In the Relationships Dialog box, you can edit and view the related fields. You can also get information about whether referential integrity is enforced. If referential integrity is enforced, you can edit and view its cardinality ratio and its cascade update and cascade delete options. Cascade update means that if the key of a primary table record is changed, the corresponding foreign key of the related records in the related table will be updated as well. Cascade delete means that if a primary table record is deleted, all related records in the related table will also be deleted. From the Relationships Dialog box, you can open a Join Properties Dialog box to edit and view the join type (equijoin or outer join) of the relationship.
Ö Click the Join Type button in the Relationships Dialog box to view the join type in a Join Properties Dialog box. Ö Click Cancel to close the Join Properties Dialog. Ö Click Cancel to close the Relationships Dialog. Ö Close the Relationships Window.
This concludes our visit to the table object. Remember that features such as the Zoom Box (Shift+F2) and the use of F1 to invoke the context-sensitive help are applicable to other objects in most cases. Try them when you explore the other objects.
Microsoft Access Tutorial
Ö Select Parameters from the Query menu and the Parameters window appears. The window shows that Department Name, which is used as a criterion in the DNAME field is a parameter to the query. Ö Click Cancel to close the Parameters window. Ö Select SQL View from the View menu and the SQL definition of the query appears. You can ignore what is in there now since you will learn SQL later in the semester. Note also that some SQL queries have no QBE equivalents because SQL is more expressive than QBE. Ö Select datasheet from the View menu to view the query result but before it appears, the query will prompt you for the actual parameter. Assume you what to know who works in the Research Department. Type in “Research” without the quotes and press Enter. The query result datasheet appears. (In this particular case, the query is updatable and you can edit the data on the datasheet.) Ö Close the datasheet and return to the database window.
This ends our visit to the query object.
Microsoft Access Tutorial
Form Objects
A form is like a paper form to fill in data but forms in Access also let you enter data into a database, view data in a database and print the form with the data displayed. A form can have exactly one data source (known as a bound form) or no data source (known as an unbound form). In most cases you will use bound forms with data sources from either tables or queries. A form has three views—design view, form view, and datasheet view. The design view lets you edit the form design, the form view lets you view or edit data, and the datasheet view opens the datasheet of the form’s data source.
Ö Click the Form tab on the database window. Ö Select Department and click the Design button to open it in design view.
On the form design view, you can see some text labels, boxes, and buttons. These objects on the form are known as controls and the design view lets you layout these controls visually. You can drag controls around, resize them, add more controls to, or delete controls from the form as if you are using a drawing package. Controls, however, provide more than just pictures. Their visual effects give the look that the application’s user sees. Their behaviors define the way in which the application responds to user inputs. For example, a text box control is a rectangular box (visual effect) that can display text (behavior) and sometimes can let you type in text (behavior). Every control has properties that let you customize its look and define its behavior. Of all the objects we will discuss in this tutorial, controls have the richest set of properties.
Ö Click on the New button on the top part of Department form. Ö Select properties from the View menu and a properties sheet appears showing some of the buttons properties.
Control properties are categorized into 4 categories: The data properties govern how a control uses data from its data source, if any. The format properties govern how a control looks. The event properties govern how a control responds to user inputs. The other properties are properties that are not categorized in the previous three property categories. The all properties list all the 4 categories of properties.
Ö Select Data tab, Format tab, Event tab and Other tab in the properties sheet to see the four categories of properties defined for the button control. Pay particular attention to the value of the On Click Event Property. We will return to this feature when we discuss macro objects later in this tutorial. Ö Select the All tab and you will see a list of all the properties you just saw.
Microsoft Access Tutorial
Report Objects
A report is a document that one uses to organize and present data from the database in a meaningful way. A report in Access is very much like a form. You can use the same controls on a form in a report. Reports can have exactly one or no data source, even though the former case is more common. Data sources can be either tables or queries. Unlike forms, reports cannot be used to update data in a database and they have only two views— design view and print preview view. The design view lets you edit the report design and the print preview lets you preview the printed report on screen.
Ö Click the Report tab on the database window. Ö Select Project Progress and click the Design button to open it in design view.
You will notice that the design view of a report resembles a form’s design view. As mentioned earlier, controls available to forms are available to reports as well. However, you won’t normally see controls such as combo boxes and buttons because their uses are justified by their behaviors that are unavailable in reports.
Ö Click on the Sub Project Progress box on the Project Progress report. Ö Select Properties from the View menu.
You will notice that control properties are categorized in the same way as they are in forms. However, there are no event properties for controls on a report and the rest of the properties are just a subset of what the corresponding controls have on a form. This is because a report is read-only and we only need the properties that govern a control’s appearance but not its behavior.
Ö Browse through the Data, Layout, Event and Other Properties, noting that there are no Event properties available.
There are also properties for reports that can be used to customize the report as a whole. With the exception of the Record Source property, most of the report properties are not often used.
Ö Select Select Report from the edit menu. Ö Browse through the Data, Layout, Event and Other Properties. Ö Close the properties sheet. Ö Select Print Preview from the File menu to preview the printed report. Ö Close the Project Progress report.
This concludes our visit to the report object.
Microsoft Access Tutorial
Macro Objects
A macro is an operation that carries out a series of actions in Access just like a function carries out a series of instructions in a programming language. A macro is just another high-level way to “write code” when developing Access applications and it can be used in place of Access Basic code (the programming language in Access) in most cases. Like other programs written for Windows, macros are event driven and they are run in response to events such as a user clicking a command button.
Ö Click the Macros tab on the database window. Ö Select Department Form and Click the Design button to open it in design view.
The top half of the macro sheet has four columns—Macro Name, Condition, Action, and Comment. Macro Name is the name of a macro. Each macro is read from top to bottom. An entry in the macro name column marks the beginning of a macro and the end of the previous macro if any. Note that the Department Form macro that you have selected in the database window is in fact the name of a macro group (a collection of macros). Macro groups provide the facility to package related macros into a group so that they are easier to maintain. The COMPANY application groups macros according to forms and the Department Form macro group you just opened represents all macros related to the Department form. Entries in the Action column specify the operation to perform in a macro. An action can be performed conditionally based on the expression in the Condition column on the same row. An action will be performed if and only if the expression specified in the Condition column evaluates to true. If no condition is specified, the action is performed by default. An ellipsis in the Condition column indicates the action in this row uses the condition of the previous row. Note that the condition and the macro action on one row only gives you if-then flow control. If you need if-then-else flow control, you need to explicitly specify the negation of the if condition and the else part macro action on a separate row. The Comment column lets you document your macros. You can toggle the display of Macro Name and Condition columns by selecting the Macro Names and Conditions in the View menu.
Some macro actions need arguments to run. The bottom half of a macro sheet displays the arguments that are applicable to the current macro action selected on the top half of the macro sheet. In most cases, not all arguments are required and you may leave out the optional ones.
Ö Click on the OpenForm action of the New macro. Ö Examine the arguments and comments of the OpenForm action.
Microsoft Access Tutorial
Module Objects
A module is a collection of procedures written in Access Basic, the programming language in Access. Access Basic provides a richer set of operations and better flow and error controls for Access applications. However, Access Basic requires a little more effort to learn than macros. We do not require you to use Access Basic to develop your project. In fact, 99% of the COMPANY application is implemented using macros. For this reason, we will only have a brief discussion of Access Basic here and we advise you to refer to books about Access or Access Basic for more information.
Access Basic code is written in units called procedures. A procedure contains a series of statements that perform the function of the procedure. There are two kinds of procedures—Functions and Subs. A Function always returns a value and therefore it can be used as an expression. A Sub does not return a value and it cannot be used as an expression.
Access Basic procedures are stored in modules and there are two kinds of modules—form or report modules and global modules:
Each form or report has its own built-in module that stores Access Basic procedures you write to handle events of the form or report, and other private procedures of the form or report. Form and report modules are part of the form or report definition and you can only use a form or report module within that form or report. If you delete a form, its module is deleted. If you copy a form from one database to another, the module goes with the form.
Ö Click on the Forms tab on the database window. Ö Select the Employee form and click the Design button to open the form in design view. Ö Select Code from the View menu to view the Employee form’s module declaration section. Ö Click on the down arrow of the left Combo Box, to see the procedures defined. Ö Close the module. Ö Close the Employee form.
Global modules are separate objects in a database and you can use them anywhere in the database. They are listed in the database window when you click the Module tab.
Ö Click the Modules tab on the database window. Utility is a global module.
Microsoft Access Tutorial
Ö Select Utility and Click the Design button to view the module’s declaration section. Ö Click on the down arrow of the left Combo Box, to see the procedures defined. Ö Close the module.
This concludes our brief discussion of module objects.
