MS Access

Getting started with Microsoft Access
This tutorial is neither complete nor comprehensive, but does illustrate some of the basic skills you need to know to construct a Microsoft Access application like the one you'll do for Assignment #1. The tutorial concentrates on the 'hows', not the 'whats' nor the 'whys'.  The latter are the focus of the course as a whole.

The tutorial contains quite a few screen capture images to illustrate what the tool looks like during various activities.  Please be forewarned that some of the segments could take a while to upload over slow communications links.  Hopefully the wait will be worth it. The tutorial is broken down into five sections:

1. Creating a Database 
2. Creating Forms   
   • Creating a combo box   
3. Creating Queries 
4. Buttons and Switchboards 
5. Macros

Create the Database

1. Since you'll be creating a new database, when you've started Access, click on the Blank Access Database option.  Then click 'OK'.
   
2. To create a new database you must provide a name and specify a directory for the database (.mdb) file.  Then, click on Create.
   

Create Tables

1. When the database has been created, it initially exists as a file, an empty shell.  The database becomes useful when we create so-called database objects within it.   In Access, the starting point for creating database tables, queries, forms, reports, macros, and modules is known as the 'Database Window', shown here.  To create a particular object, click on the tab for that object on the left (here, 'Tables').
   
   Access provides three options for creating a new table.  Create table by using wizard allows one to create a variety of common databases (e.g. customer, employee, order, etc.).   Create table by entering data allows one to quickly create a tabular arrangement of data without first creating the table's design.  The third, Create table in Design view provides the greatest amount of control over the design of the table.
   
   To create a table in design view, do one of the following:
   
   1. Double-click on Create table in Design view, or
   2. Click on New, and then select Design View in the New Table dialog box:
      
2. The design of a table requires the specification of four elements:
   1. The name of each column
   2. The data type of each column
   3. The column(s) whose value(s) will be unique for each row in the table (the Primary key)
   4. The name of the table.
3. Type in the Field Name, Data Type, and description for each field you wish to have in your table.  Notice that for each data type there are additional Field Properties you may set.  In the figure below, the CD_Category field is marked as the current one (by the black triangle on the left).  Therefore, the Field Properties pertain to the CD_Category field.
   
   In the Field Properties area we see that the following properties have been set:
   • The maximum length of the field is 30 characters.
   • The Caption property allows you to define a default label for this column on a Form.
   • The Validation Rule property allows you to define more precisely restrictions (constraints) on the values that may be inserted into this column. For example, to reduce variations in spelling, we are requiring that any value placed into the CD_Category column be spelled as one of the following options: Rock and Roll, Country, Jazz, Classical, Other.
   • The Validation Text property allows you to define an error message the user will receive should he or she enter in a value that is not valid according to the Validation Rule property.
   You should set the field properties appropriately for the other fields as well.
   
4. Choose one or more fields to be the Primary Key. You do this by clicking in the grey area to the left of your field definitions (holding the CTRL key and clicking selects individual fields if the Primary Key is to consist of more than one column.
5. From the Edit menu, choose Set Primary Key
6. From the File menu, choose “Save As...” and give your table a name.
7. Close the table by either choosing “Close” from the file menu, or clicking on the Close button in the upper right-hand corner of you window.
   
8. Repeat this table creation process to create another table called TRACKS with the following columns:
   • Track_No (Data type:  Number,  Field size: Integer)
   • Track_Title (Data type: Text, Field size: 80)
   • Track_Duration (Data type: Date/Time,  Format: Short time)
   • CD_ID (Data type: Number, Field size:  Long Integer).    CD_ID in the TRACKS table is what is known as a Foreign Key.  It is a column (or columns) within one table that is also a Primary Key column(s) in another table.   Within Access it is important to note that if the datatype of a Primary Key is Autonumber, then the data type of any Foreign Key that references that Primary Key must be Number, and the Field size must be Long integer.

Create Relationships

1. Fundamentally, relationships within a relational database systems are represented by using Foreign Keys to establish associations between data in one table with data in another.   However, Microsoft Access likes to have a little bit more explicit information about what columns are foreign key columns, so that it can make life easier for you when you create forms, and so that it can better enforce data integrity within the database.  You provide this information to Access via the Relationships... view.  
2. Get a graphical view of the tables and relationships by clicking on the “relationships” button on the toolbar, or by selecting “Relationships...” from the Tools menu.
   
3. In the Show Table window, click on the Add button (for each table) to place a graphical representation of the table into the relationships diagram.  Click on Close when finished to dismiss the Show Table window.
   
4. To tell Access that a particular field in one table represents a field from another table, click and drag from the field in one table to its counterpart in another field.  In the following image, I clicked on the CD_ID of the CD table, and dragged it to the CD_Id field of the Tracks table.  Access then offers me the opportunity to edit that relationship.
   
   The relationship between the CD table and the Tracks table is a one-to-many, since one CD is associated with (contains) many tracks, while each track is located on precisely one CD.  Checking the box to Enforce Referential Integrity means that you may not enter a Track whose CD_ID value does not match one of the existing CDs.  In other words, each Track must be associated with a known CD.   Click Create to complete the creation of this relationship.
5. When the "relationship" is created, Access will show a line between the participating columns.  Note that this graphical representation is simply a convenience.  The actual relationship is established by the data values within the tables themselves.
   

Simple Forms

1. From the Database Window, select the “Forms” tab.
2. Click on “New”
3. I usually prefer to let the Access wizard create a basic form for me.  Then I can modify it as I please. So, choose the Form Wizard.  Also, pick the table whose contents we wish to display in the form (CD, in this case).
   
   Figure 1: Choosing the Source of Data for a new Form
   Click OK to go to the next screen.
4. Access will lead you through dialog boxes that ask you:
   • what fields you'd like to see on the form
   • what layout you'd like to use
   • what background style you'd like to see
   • what title you'd like to see appear on the form
   To choose the fields you'd like on the from, click on a field in the the left-hand column, then on the arrow to move that field into the right-hand column:
   
   Figure 2: Choosing fields to put on a form.
   
   When you've chosen the fields you want on the form, click Next>
   
5. The Form Wizard will then ask you to choose a basic layout for your fields.  In this tutorial, stick with Columnar, but feel free to take a look at the others as well.
   
   Figure 3: Choosing the Form layout.
   
   Click Next when you are ready to move on.
   
6. The Style of a Form refers to the background pattern, the color and font of labels, etc.  Access has a number of standard styles to choose from.   Here we've chosen SandStone, for no particular reason.
   
   Figure 4: Choosing the Form style.
   
   Click on Next to continue.
   
7. Finally, provide a title for your Form, which will show up both in the title bar and on the form.   At this point you may see the completed form, or choose to modify it further before opening it.
   
   Figure 5: Providing a Title for your form.
   
   Click on Finish.
   
8. Once you’ve created a form (say, using the wizard), you are free to alter it. Clicking on controls will select the control.  The selected control will have black boxes (handles) around the edge:
   
   Figure 6: Selecting controls on a form.
   
   Double-clicking on a selected control will show you the properties of that control (another way to see the properties is to select the control, and then choose “Properties” from the “View” menu). You can change things like the color, the borders, and many, many other properties. Experiment.
   
   Figure 7: Control properties.
   
   
9. There are a number of ways you can look at a form:  in Design View (where you can make changes), in Form View (where you see what the form will look like to the user) and in Datasheet View (where the form looks like a table).  Choose your view from either the View menu, or by clicking on the icon that looks like this:
   
   Figure 8: Form views
   
10. To add additional controls onto the form, use the toolbox, shown to your right.
    1. If you want to create a control that asks the user to select from a list of items rather than type in a (possibly incorrect) entry, use a combo box or a list box.  Be sure the wizard is activated.  It will make life a lot easier.  The wizard will guide through the choices you need to make.  If you've created a list or combo box to replace an existing control (field) on the form, you will have to delete the old control.  On the form, the combo box control looks like this:
       
       Figure 9: Combo boxes.
       
    2. If you wish for more information on adding controls, see some examples.
11. If you want to add a control to the form that is based on some field in the underlying table (e.g. a text box), it is best to: a) bring up the field list (it’s in the “View” menu)
    
    Figure 10: The Field list.
    
    and b) drag and drop the field you want onto the form.
    
    Figure 11: Adding a field to a form from the field list.

Forms with data from more than one table.

A very common type of form is one that displays so-called "Master-detail" data.  Every day examples of such displays include your telephone bill, your credit card statement, the list of classes you are taking, etc.  The common characteristic of such arrangements of data is that for each "thing"  (master record) there are multiple associated detail records.  For example, on your telephone bill, some of the displayed data describes the customer who received the bill (Name, Account number, Telephone number, Address, etc.) and appears once.   The details of telephone calls appears on the form as multiple rows with similar format.  Each row shows the Date of a call, the City called, the Duration of the call, the Time of day, etc.   The master and the detail data are likely to be stored in separate tables in Relational databases.   These tables will have what is known as a one-to-many relationship.   One customer has many telephone calls, but each telephone call is billed to only one customer.  In this tutorial, one CD is associated with many tracks, while each track is associated with one CD. Because such master-detail arrangements of data are so common, and because in databases the master data and the detail data are likely to be stored in separate tables, Microsoft has developed an easy way to create a single form to display data both the master and the detail data. We will create what Access calls a Main Form/Subform construct.  A Main Form will display data from the table that is the one part of the one-to-many relationship (i.e. from CD), while a Subform will display data from the table that is the many part of the one-to-many relationship (from Tracks).  The Subform will be planted on the Main Form.

1. In the Database Window, click on the “Forms” tab.
2. Click on the “New” button.
3. Choose the "Form Wizard" option and select the table that will provide data for the Master portion of the form (CD, in this case):
   
   Figure 12: Creating a new form.
   
   Click OK.
4. In the next dialog box, select the columns of master data you wish to see on your form:
   
   Figure 13: Choosing fields for a form.
   
   At this point, we are proceeded exactly as before, when creating a form based on a single table.
   
5. To include columns as well from the detail table (Tracks), choose the Tracks table from the combo box on this same dialog box.  Do not click Next yet.
   Figure 14: Choosing fields from a second table for a subform.
   In the screen-shot above, I have selected the Tracks table, seen the list of available fields from the Tracks table, and chosen two of them (Track_No, Track_Title) to be included on my master-detail form.
   Now, click Next >
6. Access now asks you to confirm the layout of the data from the two underlying tables.
   
   Figure 15: Choosing subform style for the main/subform.
   
   Choosing the Form with subform(s) option makes all of the fields you've chosen visible at the same time on the same form.  Choosing the Linked forms option creates a button on the main form which, when clicked, will bring up a second form with the associated detail records.
   Click Next >
7. A further decision you must make is how you wish the detail data columns to appear.  Datasheet makes them appear in a single table.  Tabular gives each data element its own textbox on the form.
   
   Figure 16: Choosing a layout for the main/subform.
   
   Click Next >
8. As for the form based on a single table, you are then asked to choose a background style, provide a title for your form and for the subform containing the detail data, and whether you want to view the form directly or modify its design.  The end product appears below:
   
   Figure 17: The main/subform.

Adding Controls from the Toolbox
The toolbox offers a quick means of creating a number of useful controls.   These include drop-down boxes (combo boxes), buttons, list boxes, radio buttons, toggle switches and others.  If the wizard is enabled, then the basic procedure is to click on the the control you wish to create on the toolbox, and drag or click again on the form approximately where you wish to place the control.  We will now examine some examples of control creation.

Drop-Down (Combo) Boxes

1. To create a drop-down (combo) box, first click on the combo box icon .  
2. Then, click on the form approximately where you wish to place the combo box.
3. If the wizard is enabled, you will be given a choice of options, as shown in the dialog box shown here.
   
   Figure 1: Choosing the source of combo box values.
   
   At  this point you have three options for populating your combo box with values.  First, you can have the combo box run a query when a user clicks on the combo box.  The query will run and populate the combo box with completely current data.  This type of combo box is very helpful if the values in the combo box are likely to change on a regular basis.  For example, if you had an application in which you had to pick an employee from the set of all current employees, then such a dynamic combo box would be appropriate, showing always the current set of employees.  This is most flexible option. Second, you can type in the values you wish at the time the combo box is created.  This option, currently selected in the figure, is appropriate for cases in which the options do not change frequently.  'Gender' is a clear example.   The third option will not only allow a user to pick one of the permitted values, but will then find a record that match the value chose.   This provides a fast 'record lookup' function.
4. In our current example, we choose to type the values in manually.  This option brings up a window in which we are given the opportunity to specify a) how many columns will be in the combo box, and b) the values we wish to be in the combo box.  Note that even if you specify multiple columns, only the left-most column will be displayed until the user clicks the combo box.
   
   Figure 2: Typing in values for the combo box.
   
5. When we click the Next> button, we are again given a choice.
   
   Figure 3: Choosing to associate the combo box with a table column (field).
   
   What do we do with the value the user selects?  One option is to do nothing other than store that value to be used later, such as in a query.  The other option, shown selected here, is to store the value in a table column.  If we choose this option we must also select, from the combo box shown here, the column into which the value is to be stored.  
6. Finally, we must provide a label for this combo box when it appears on our form.
   
   Figure 4: Providing a label for the combo box.
   
7. The finished combo box appears below.
   
   Figure 5: The finished combo box.

Creating queries

Query-by-example (QBE)

1. From the Database Window, select “Queries”
2. Click on the “New” button
3. (The wizard in this instance isn’t very helpful, so just click on the “Design View” option)
   
4. In creating a query, one needs to specify at least three things:
   • the tables involved in the queries
   • the columns that will either be part of the output shown to the user OR will be subject to some sort of criterion needed to determine which rows will be displayed in the result
   • the conditions (criteria) rows must satisfy to be included in the result
   In the “Show Table” dialog box, you will specify which table(s) contain the data you want to query over, by choosing a table and clicking on the Add button.  If multiple tables are involved in the query, repeat for each table.
   
   When all relevant tables have been selected, click on Close.
   
5. The Query design should now be displaying the tables you have chosen in the top portion of the window, and an area for further defining the query in the lower portion.
   
6. Suppose we wanted to show the album titles and track titles for all Beatles albums aquired after 1/1/1994.  We will first specify the columns of output (CD_Title, Track_Title) we desire.  We then specify any additional columns on which we will define criteria (CD_Artist, CD_DatePurchased).  We will specify these three columns by making these column names appear in the first two Field: fields in the lower portion of the window:
   
   There are three ways to do this, all yielding the same result:
   • double click on the columns desired in the tables shown in the top portion of the window;
   • click and drag a column from the top portion of the window into some field: field;
   • click on the combo box within the field: field and select a column name.
   Notice that the Show box is checked for CD_Title and Track_Title, but not for CD_Artist or CD_DatePurchased.  This is because we want CD_Title and CD_Track to appear in the output, but CD_Artist  or CD_DatePurchased .
   
7. We are interested not in all CDs, but only in Beatles CDs.  Therefore we are interested only in those CDs satisfying the criterion that the CD_Artist = "Beatles" and CD_DatePurchased > 1/1/1994
   
   Notice that the contents of the Criteria field can show an equality (the value in a row must match this one exactly) or an inequality.  Note also that Access has a peculiar format whenever dates are involved.  Surround the date with pound signs (#) for Access to interpret it as a date value.
   
8. To run the query, select Run from the Query menu, or click on the exclamation point.
9. If you wish Access to prompt you for a criterion each time you run the query, put, in hard brackets, the question you wish Access to prompt the user with, e.g. [Please enter Artist:] could be used in place of "Beatles" to make the system more flexible.
   
   Each time the query is run, the user would be prompted with the question, "Please enter Artist:"
   
   This technique is actually a "kludge", taking advantage of Access behavior when it doesn't recognize a parameter value, and is not recommended for full production systems.  However, as a short-term measure to add flexibility to a query, it works.

Buttons and Switchboards

The main switchboard is the initial form the user sees when starting the database application.  It provides a selection of buttons that will initiate the main activities the user might want to carry out.  Typically, these buttons open other forms that are more specialized, run queries, etc.  In short, the switchboard provides the user with a sense of the overall organization of the application.    Access has a wizard that will create switchboards for you, but I've never been particularly happy with the flexibility or functionality of these switchboards.   Consequently, I'm not going to cover it in this tutorial.  Rather, I prefer to build my opening screens from scratch.  In the process, we'll learn to create buttons.

1. Since the main switchboard (and you might even give this name to the form) is just a form with buttons on it, you create the main switchboard in the same way you create any other form. The only difference is that you don’t base this form on some underlying table or query. This form might be easiest to create without the wizard.
   
   Without choosing a table or query where the object's data comes from, click on OK
2. Because you chose no underlying source of data and are not using the wizard, you'll be looking at a bare-bones form:
   
   Let's add some buttons.

Buttons

1. Be sure you are in design view in form (the one you are now creating).
2. Be sure the wizards are turned on.
3. From the toolbar, click on the “Command Button” icon.
4. With the mouse, click on your form where the button is to be located.
5. The wizard will then kick in to ask you a series of questions about what you want your button to do when it is clicked.
   
   If you want the button to open a form, such as the one you have designed for data entry, use the “Form operations” category, and the “Open form” activity.
6. Access will then ask you to pick the form to be opened when your button is clicked:
   
   Click on Next>
7. In the window, the wizard asks you to specify which rows of data will be displayed in the form.  Many times you'll want all rows.  For other forms, you may want just a subset, based on some criteria you can specify.
   
   For our purposes, choose Open the form and show all the records.  Then click Next >
8. Each button can have an icon or text to describe its function.
   
   Click Next >
9. Finally, the wizard asks for a name to be used internally to refer to this button.   This name will not appear on the form where the user can see it, but will be helpful to the application developer as they customize the button's properties.
   
   When done, click on Finish
10. When you’ve come to the end of the wizard, feel free to resize or reposition, etc. the button. You may also select the button (so the handles are visible) and then double-click to bring up the properties list, where you can make other changes.

Building and using macros

What are macros?

Macros are one of the exciting things that make things happen in Access. Conceptually, macros are just a sequence of actions that are grouped together so they can all be executed together as a unit. For example, there may be a set of actions you want to happen when you click on some button (see below) on a form. You might want a button click to result in some form being opened and the contents of the form updated. These two actions (opening the form, setting the value of a control on the form) can be grouped together in a single macro. The button is then set so that the macro executes when the button is clicked. When the macro executes, both activities (in sequence) execute. The types of activities that can be done from within a macro are almost limitless: opening and closing forms, updating controls, executing queries, etc.

Defining a macro

1. From the database window, select the ‘Macro’ tab.
2. Click on ‘New’ to create a new macro
3. In the ‘Action’ column you will put in, one below the other, the sequence of actions you wish to be invoked when this macro is executed. For example, let’s say you wish to create a macro that will enable you to add a new record on a form, but will first place the cursor at a field you specify (two actions).
4. In the action column, pick ‘GoToRecord’ as the action you want.
   
   You will notice that at the bottom of the screen there are various fields you can fill in to tell Access more about what kind of record you want the system to go to. In the ‘Record’ field, select ‘New’. The macro will now cause Access to go to a new record in the currently active Form/Table
   
5. Put the cursor on the next line of the ‘Action’ column. This time, select ‘GoToControl’. Then, in the field at the bottom of your screen, type in the name of the control on which you wish the cursor to be placed.
6. Save the macro and give it a name
7. Exit the macro

Getting system events to kick off a macro

There are a number of ways to invoke a macro. Look in the property list of any control. Under ‘Event Properties’ you will see things like: ‘On Enter’ ‘On Exit’ ‘On Click’ ‘On Dbl Click’, etc.

These are all events that the system recognizes. In response to such an event, you can specify that you want a particular macro to execute.

1. Creating a button that will execute a macro when clicked. Create a button as described above. When the wizard asks you to select a category, select ‘Miscellaneous.’ In response to ‘When Button is Pressed:’ select ‘Run Macro.’ And so on...
   
2. Alternatively, you may specify the macro in one Event Property fields of any existing control. For example, if you have an existing button on your form, in the ‘On Click’ property of that form, you may select the macro from the combo box associated with that Event Property field.
   

Other uses of the Macro

The setvalue action

The setvalue action is used to assign a value to a control on, among other things, a form.  The value can take the form of a constant, a computed value, or the value of some other control.  In the current example, we would like to create a macro that will open the CD form, and for a new record put an initial value of "Jazz" in the combo box that enables a user to pick the CD category. To use the setvalue action, begin with the Macro in design view. You are now prompted for two arguments:  Item and Expression.   Item refers to the control that is to receive the new value.  Expression is the new value or, more precisely, an expression that resolves to some value.   Specifying an Item The Item must be specified using the full naming syntax which, in general looks something like this:   Forms![form name]![control name]  While you can type this in, it is in many cases more convenient to use something called the Expression Builder to create it.  To use the Expression Builder, click on the Elipsis (looks like three dots:   ...) on the right-hand side of the Item argument field. You will see the following: What you will do is click your way forward until you find the control you want, and the "paste" it into the expression field where you see the cursor in the above window.  In the current example,

1. double-click on Forms
2. double-click on All Forms.  Double-clicking on All Forms gives you a list of all forms in your database.  Double-clicking on Loaded Forms gives you only those forms currently open.  The latter can be more useful if you have a large database and are working on only a few forms at a time.
3. single-click on the CD form.  At this point you will see all of the controls on that form in the middle window.  
4. single-click on the cmbCategory control.  This is the name I have given to the combo box on the CD form that lists categories.

In the right-hand column, you now see all of the properties of the control you selected in the middle column.   For right now, we are, however, only interested in the value of the combo box.   We will choose as our "expression" the value of the cmbCategory combo box by now clicking on the Paste button.   You will now see the full name of the combo box appear in the expression window. When you click 'OK', this expression gets placed into the Item argument field in the Macro design view. For the expression, we wish to enter a constant ("Jazz").  The rule is that we should enclose constants in quotation marks.  If we leave out the quotation marks, then Access will interpret our entry as the name of some other control and cause an error. Now, save the macro.