Graphical User Interfaces (in Microsoft Access)

8.1 Getting Started

• MS Access consists of
  • A jet engine for managing the data according to the relational database model
  • A GUI for communicating with the engine

• Open Microsoft Access.
  • Select ‘New Database...’ from the ‘File’ menu
  • Open ‘Blank Database’ from the General folder
  • Insert a file name
  • Choose ‘create’

  The database window provides folders for handling tables, queries, etc.

  

8.2 Data Definition

• Pressing on the new command button, within the tables folder, opens a new table window.

  

• The table wizard option asks to create the table interactively under the guidance of the program. The design view option, used here, asks to create the table directly.

  

• The names and types of the desired attributes can be defined in the table window.

  

• The constraints for the attributes are specified in the field properties window.

  

• The table name can be chosen during saving.

  

• The primary key may be set through a pop-up menu (associated with the right button of the mouse).

  

  Q: Multi-field primary keys?

• As the tables are created, they can be accessed and modified through the database window.

  

  Q: SQL view?

8.3 Referential Integrity Constraints

• Specified via the relationship window

  

• Add the tables involved to the window.

  

• Select the primary key of one table, and drag its image into the other table. Set the image on the attribute that refers to the primary key.

  

• Set the properties of the relationship (e.g., cardinality, triggers).

  

• Press the create button.

  

8.4 Data Insertion

• Open the table of interest, and insert the desired data

  

8.5 Query Definitions

• Open the query folder and press the new button

  

• Choose the Design View option

  

• Choose the relations involved in the selection

  

• Choose the attributes involved in the query, and have the mark apply only to the projected attributes.

  

• The corresponding SQL code can be exposed through the View SQL entry of the menu.

  

• The result can be seen through the database view entry.

  

• The different queries should be saved.

  

8.6 Reports

• Reports are bound to underlying tables or queries.
• Choose new under the Report Folder, select the relation to be reported, and the style to be used.

  

• Save the report in the desired format.

  

• The report can be opened in Design View for modifications.

8.7 Others

• Table views can be created in the Forms folder
• Response to events (e.g., change of data, mouse actions) can be programed through macros (sequences of simple operations) or a subset of Visual Basic that is supported by Access.

8.8 Assignment #4

Due: We, Feb 11, midnight

1. Define a database in Microsoft Access having the following schema.
   Students(Name,SSN) 
   Courses(Title,Code) 
   Classes(Code,SSN) 
   

2. Provide data of your liking to each table.

3. Prepare a report showing for each student name the titles of the courses taken by the student.

Submit the .mdb file of the database. Use the departmental submit utility: submit XXX lab4 filename. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.

• Files are restricted to 1,048,576 bytes
• The submit program issues error messages to the student’s CSE email account

Notes:

• MS Access tutorial: FunctionX

• Saved files carry the .mdb extension name. To remove intermediate results from the files, compact the files: Access menu → Tools → Database Utilities → Compact and Repair Database.

• MS Access is available on the departmental Windows environment. Lab locations and hours can be accessed at http://www.cse.ohio-state.edu/cs/operations/LABhours.htm.

  Those who have a dedicated computer such as TAs will be able to connect using Remote Desktop to their machine. The RDP client mstsc.exe is is available for windows operating systems from microsoft.com. For the Linux or Solaris Operating System, there are also free versions such as rdesktop available online. For MacOS X, Microsoft provides an RDP client at http://www.microsoft.com/mac/otherproducts/otherproducts.aspx?pid=remotedesktopclient.

  If you have problems with your account, please contact help@cse.ohio-state.edu. Put the word PRIVATE in the subject header when sending a request that contains sensitive information, such as social security numbers. (If you fail to do this, any information included in the request will be viewable to anyone, anywhere on the web.)

• To submit Access file through Unix:
  1. Open Windows Explorer
  2. Open your Personal Folder
  3. Drag the 670.mdb file into your “w” drive
  4. Exit Windows XP
  5. Log into Unix
  6. Submit file as before through “Terminal” window

  The 670.mdb file can be compressed by choosing “Compact+Repair” in Access under the “Tools” options.

Q&A