Intro to Access Application Development

1
Intro to Access Application Development

• ATiB 406 Fall 2003

2
Preview

• Data integrity
• Form Design Principles
• In class form building challenges
• Next time is advanced reporting and a bit on
  automation
• Then, learn about SQL Server
• Access vs. SQL Server
• Migrating an Access database to SQL Server

3
Good Access Resources

• "Application Development in Microsoft Access
  2000" by Baldwin and Paradice, Course Technology,
  2000 (ISBN 076007108X )
• Files on Server
• Copy to your own folder
• Fix linked tables (Ill show you at end of class)
• Chapters 5-8 cover programming the Access and
  ADO/DAO object models
• The whole book is really useful
• Adamski, J.J., Finnegan, K.T., and Hommel, C.,
  Access 2000 - Comprehensive Enhanced (New
  Perspectives), Course Technology, 2001.
• I use this in MIS 200 300
• Lots more exist, browse at Borders or Barnes and
  Noble
• I also put all my slides and databases from MIS
  200 on the course web
• covers Access from basics all the way through
  macros

4
Sample Database Overseas.mdb

• Available from course web
• Simple app for managing summer jobs for overseas
  students
• You can use this as example to see how various
  form design and development tasks were done
• Well also talk about the ATiB406-Forms-1.mdb
  application

5
Access Application Development

• Access is great tool to learn to develop simple
  and complex database applications
• Applications consist of tables, queries, forms,
  reports, macros and modules
• Youve already done some basic app development
• Today is mostly review
• Well build on these concepts throughout the term
• SQL for advanced reporting
• Client/server development Access ? SQL Server
• Web enabled database applications (Prof.
  Sugumaran)

6
Building on ATiB406-Forms-1.mdb

• Creating a database application to serve as a
  management information system for ATiB
• Logical database design
• ER diagram
• Database schema
• Physical database design
• Access tables
• Basic queries
• Queries help with the design
• Example of working backwards from the end product
• SQL review
• Now, review of form and report development
• Forms provide way for user to interact with our
  app
• Forms help us maintain data integrity (see
  Appendix slides on data integrity youve
  probably already covered this)

7
Important Development Concepts

• Objects
• the things we manipulate in our application
• methods controlling behavior
• properties controlling appearance
• can be manipulated manually and via VBA
• Ex tables, forms, reports, controls, queries,
  etc.
• Events
• things that happen as user interacts with your
  application
• start the application, open/close a form/report,
  push button, add a record, delete a record, and
  so on....
• our application may be designed to respond to
  certain events and do something
• Ex what happens when user opens Overseas.mdb?
• Automation
• processes or actions that are done via macros or
  Visual Basic code
• usually user does not know about many of the
  actions occurring as they interact with a
  database
• Ex database may be compacted everytime user
  closes it
• Automation often occurs in response to user
  initiated events and often involves manipulation
  of database objects

8
Forms for viewing, entering, editing data

• Forms usually have a record source
• Table or query (a bound form)
• Unbound form has no record source (example?)
• Sections
• Form header info at top of form
• Detail shows data for current record in record
  source
• Form footer info at bottom of form
• Each section populated with controls
• Form properties multiple tabs to organize
  properties
• Format controlling aesthetics
• Data control record source and control source
• Event link object to VBA procedure or macro
• Other misc. such as name of control or type of
  form

9
Basic Form creation steps

• Create new form using wizard or form designer
• Set RecordSource property for bound form
• Place controls on the form in appropriate
  sections
• Start with rough design and then refine
• Set properties of form, controls, and sections so
  form looks and behaves as desired
• Iterate and refine your design

10
Maintain table data using a form

• Not only can you view your data in a form, you
  can also make modifications to the data right in
  the form.
• be wary of letting users change primary key
  values
• Once you have navigated to the record you want to
  change, you can make your changes.
• When you move off the record, the changes are
  made directly to the table.
• no need to save
• Access will allow you to add, modify, and delete
  records in forms view.

11
Form Design Principles

• Lay out controls in logical groups and try not to
  clutter form with too many controls
• Aesthetically pleasing
• dont mix and match numerous colors, fonts
• alignment, size
• Set tab order so user can tab quickly from field
  to field in logical order
• View Tab order
• Use control types appropriate for the data type
• option buttons for choosing one of a small number
  of categories
• list boxes and/or combo boxes for lists
• check boxes for Yes/No
• Use input masks, default values and data
  validation rules to help user enter data
• Consistent design across forms in the application

12
Design and create a custom form

• You can create a custom form by modifying an
  existing form or creating a new form. Either way,
  the custom form is created in the Form Design
  window.
• You can create a simple form or a much more
  complex form with multiple pages.
• You place objects on the form, called controls,
  that will be either bound or unbound controls.
• Many different kinds of controls, some better
  suited for certain data types than others
• Bound controls are linked to a particular field
  in the underlying table.
• When you edit data in the control, you are
  affecting the value in the field in the
  underlying table
• Unbound controls are not linked to any particular
  field.
• An example of an unbound control is a label
  control that is on the form simply to provide
  some information on the form

13
Control Toolbox
Text box usually bound to a field or a
calculation
Frame and Option Buttons (pick 1 of n)
Label (never bound)
Check box (yes or no)
List box Combo box
Command Button (initiate an event)
Subform
14
In class exercise 1 a simple form for Sponsor
information

• Download ATiB406-Forms-1.mdb from course web
• Ive added CompanyURL field to the Sponsor table
  design with a hyperlink data type
• Added some data as well
• Create a simple form to allow user to enter,
  edit, view sponsor data
• Include a header with some sort of graphic
• Include a footer that displays the current date
  in bottom right and the words ATiB Portal in
  the bottom left

15
In class exercise 1 a simple form for Sponsor
information
Form Header
User cannot edit, why?
Form Footer
Current date time
16
Control how form elements look through Properties

• Each control has a slew of properties
• Select control then select Properties from View
  menu

17
Just a few of the many Properties for Text Box
Controls (as well as many other types of Controls)

• Control Source the name of the field that the
  text box is linked to in the table underlying the
  form
• Default Value value before user enters anything
• make life easy on the user
• Validation Rule logical expression restricting
  what can be entered into the text box
• GIGO
• Enabled True means user can enter info into the
  text box
• why would you ever have this set to False?
• Tab Stop False means control is not part of the
  tab order
• Border Style, Border Color Border Width, Fore
  Color, Back Style, Back Color
• Font Name, Font Size, Font Weight
• remember, dont go crazy with formatting

18
Add Form Headers and Footers

• To create a form header, you must add a form
  header and form footer section to the form.
• To add these sections to your form, select Form
  Header/Footer from the View menu.
• If you do not want to have one or the other (the
  header or the footer section), you can reduce its
  height to zero, which will eliminate it from the
  form's display
• Once you have these sections, you can add labels
  to them to document the form.
• When creating a title on the form, you will often
  want to change its font size and weight to make
  it stand out on the form.

19
Form Design view with header and footer sections
shown
Add header and footer sections by clicking View
on the menu bar and then clicking Form
Header/Footer.
To eliminate the Header or Footer sections, drag
the bottom line of that section so that its width
is zero
20
In class exercise 2 a little more complex form
for Sponsorship information

• Lets create a form to allow a new Sponsorship to
  be added
• What defines a Sponsorship?
• What must exist before adding the Sponsorship?
• Create a new blank form
• Set Sponsorship table as RecordSource
• Quickly drag all fields from Field List onto the
  blank form
• Now, how might we improve the data entry design
  for AID and SPID fields?

21
Exercise 2 Sponsorship form
These two combo boxes allow the user to select
Class name and Sponsor name. However, whats
actually stored in the Sponsorhip table?
22
Forms with a main form and a subform

• You can create a form with a subform on two
  tables that have an established relationship.
• One to many (e.g. Employer to Position, Customer
  to Order, Sponsor to Sponsorship to Project)
• When the relationship between the tables is a
  one-to-many relationship,
• the main form will consist of data from the
  primary table
• the subform will consist of data from the related
  table.
• By selecting two related tables in the Form
  Wizard, you can produce a form with a subform.
• The form with subform is a great way to display
  data for tables that have a one-to-many
  relationship.

23
An example of a Form with subform
The main form name appears in the title bar.
Overseas.mdb
The subform.
The main form.
The subform navigation bar.
The main form navigation bar.
24
Creating complex main forms with subforms

• While Form Wizard is OK way to get started, we
  will also learn to build them without the wizard.
• Last semester you did a tutorial that introduced
• main form with subform
• use of many types of form controls
• finding records using a combo box
• automating printing
• youll also get glimpse of VBA
• Ive reposted it on the course web for your
  reference

25
Exercise 3 Main form - subform

• Create something like the main form subform
  example on the next slide
• Its shows lists of students for each ATiB class
• See hints on last slide
• Due in 2 weeks
• Let me show you where the files are for all
  databases referred to in these slides

26
(No Transcript)
27
Hints for Exercise 3

• First create a form based on Student table
• This form will become the subform
• Change its default view to Datasheet
• Now copy frmSponsor that you already created to a
  new form called frmATiBStudent
• Change record source to ATiBclass table
• Delete old fields on form and add those from
  ATiBclass
• Lay them out nicely (see previous slide)

28
Hints for Exercise 3 - continued

• Add a subform object from the toolbox to the
  detail section of the form
• Wizard may start, cancel. Lets do it manually
• Change Source Object property for subform object
  to the student subform that you already created
• Check the Link Child Fields and Link Master
  Fields properties for the subform object make
  sense what should they be? What field links the
  two tables involved in this form?
• Play around with the formatting to make it look
  nice
• Also recommend you look back at the tutorial I
  gave you last year on this topic as it includes a
  few more complexities

29
Appendix Database Integrity Constraints

• Integrity constraints are rules the database must
  adhere to
• Entity integrity no null primary keys
• We talked about primary keys last term
• Referential integrity
• Every foreign key value in a table must have a
  match in the primary key value in the related
  table
• Ex No SponsorshipID in the Project table for a
  SponsorshipID NOT in Sponsorship table (need SPID
  in Sponsor table before SponsorshipID as well)
• Domain integrity
• Rules governing the values that attributes can
  take
• Ex In Access, can set Data Validation rules

30
Appendix Using referential integrity

• When dealing with related tables, you need to
  decide if you want to enforce referential
  integrity.
• Referential integrity allows you to maintain the
  integrity between related tables.
• The rules associated with referential integrity
  specify that when you add a record to a related
  table, there must be a matching record in the
  primary table.
• If you choose to enforce referential integrity,
  you can insure that you will not have orphaned
  records (records that have no matching record in
  the primary table).

31
Appendix Use cascaded updates and cascaded
deletes

• In addition to referential integrity, you can
  also tell Access to implement cascaded updates
  and cascaded deletes.
• If you choose cascaded updates, making a change
  in a field that is common to two related tables
  will cause the update to be made in both tables.
• Cascade deletes is similar. If you delete a field
  that is common to two tables, the deletion will
  take place in both tables.
• You should carefully consider whether you want to
  implement these features, as they can have
  dramatic effects on your data.

32
Appendix Setting relationship options
The Edit Relationships dialog box is where you
can determine the type of relationship, and set
referential integrity and cascade update/delete
options.
The common fields do NOT have to have the same
name. They just must represent the same
information.