Objects in Access

Object

Purpose

SDLC

Mechanism

Table

Define the underlying structure: fields, type of data, properties

Data-entry design

·        Validation rule

·        Validation text

·        Input mask

·        Required

·        Default value

·        Format

·        Field size

Physical design

·        Indexed

·        Primary key

·        Unicode compression

Documentation

·        Tools à Analyze à Documenter

Query

Answer questions about the database

Physical design

·        SQL

Form

Customized screens for data input, update, and output

Interface design

·        Command buttons

·        Bound/unbound controls

·        Switchboard

·        Custom menu

·        Custom toolbar

·        Startup form (Tools à Startup)

·        Time interval property

Input design

·        Bound controls

Output design

·        Bound/unbound controls

·        Expressions

Report

Printed pages of information

Output design

·        Bound/unbound controls

·        Expressions

Data access page

Web page for data input, update, and output (provided the database is stored on an Internet or intranet server)

Systems design

·        Record toolbar

·        Group toolbar

·        Hotspot Image

Macro

Programs using a set of predefined actions or commands to perform a designated task

Physical design

·        Built-in actions

Module

A collection of Visual Basic programs stored as a group

Physical design

·        Procedures

·        Object browser template

·        VB editor

 

 

Object identifier: [Collection]![Specific Object]![ObjectName]

e.g., [Forms]![Form_Name]![Control_Name]

Data Field Objects

Data types

  1. Text
  2. Memo
  3. Number
  4. Date/Time
  5. Currency
  6. AutoNumber (≈ long integer)
  7. Yes/No
  8. OLE Object (graphics, audio, and video files)
  9. Hyperlink
  10. Look Up Wizard (link to a field in another table)

 

Types of data field

  1. AutoNumber: access numbers records automatically as new records are created
  2. Calculated: created from formulas; not editable
  3. Locked or disabled: not editable

 

Data integrity assurance:

1.      Use Table Analyzer: Tools à Analyze à Table

2.      Use linked table (in multi-user environment): File à Get External Data à Link Tables

3.      Use enforce referential integrity when joining tables (subdatasheet)

·        Two types of referential integrity maintenance rule:

o       Cascade update

o       Cascade delete

·        Two types of joins

o       Inner join: the default type.  Select only the records from both tables that have at least one corresponding record in the other table.

o       Outer joins: select all records in one table, and only the corresponding records in the other table.

4.      Create an MDE file: database objects are not editable: Tools à Database Utilities à Make MDE File

Query objects

Types of query

1.  Select: selects information from one or more tables based on criteria

2.  Total: produces summary totals in a select query

3.  Action: performs data update, delete, append, or new table action

4.  Crosstab: display summary data in two-dimension

5.  SQL: performs query using Structured Query Language

6.  Top(N): limits the number of records displayed to a specified number or percent of the total

7.  Find duplicate: shows any records in a table that have duplicate values in a field

8.  Find unmatched: shows records in one table that have no matching record in a related table

9.  Parameter: interactive query

 

Form objects

Types of form

1.  Columnar

2.  Tabular

3.  Datasheet

4.  Main/Subform

5.  Chart

6.  Pivot table

 

Report object

Types of report

1.  Columnar

2.  Tabular

3.  Main/Subreport

4.  Label

5.  Chart

** Groupings, totals, subtotals

 

7 sections of a report

1.  Report header

2.  Page header

3.  Group header

4.  Detail section

5.  Group footer

6.  Page footer

7.  Report footer

 

Control object, e.g., label, text box, line, etc.

Two types of control

1.  Bound: bound to a field in a table or query

2.  Unbound: not associated to a field

 

Macro

Use macros to:

1.  Run queries

2.  Open forms

3.  Print reports

4.  Open/close database objects

5.  Link forms

6.  AutoExec: automatically run each time the database file is opened

 

Macors can be invoked by:

1.  Pressing a key(s)

2.  Attaching to a command button

3.  Attaching to a toolbar button

4.  Responding to an event

 

Two types of macro

1.  Sequence

2.  Selection/Conditional

When to use VBA?

  1. Need faster processes
  2. Handle more complex processes that includes looping
  3. Cross application boundaries

 

Macro vs Module

 

2 sections to a module:

1.   Declarations contains descriptions of objects used in a module

2.  Procedures contains groups of commands that are executed when events happen, i.e., procedures are event-driven

An event is a change in the state of an object, e.g., a button on a form is clicked, or the next record is displayed in a form.

 

3 types of event procedures:

1.  User actions: user changes something in the interface

2.  Data retrieval: data is placed in a form or report

3.  Data update: data is changed in a table

 

3 kinds of procedures:

1.  General: not attached to an object

2.  Function: returns a value

3.  Sub: attached to an object