Transcript Lecture 6c

MIS309 Database Systems
Introduction to
Microsoft Access
1
Introduction
• In its simplest form, Access is a
database application
• It uses a relational database
management model, which means
you can relate each piece of
information to other pieces of
information by joining them.
2
Create Tables
• By using wizard
• By entering data
• By design view
– specify field name, data type
– set general attributes
– set primary key
3
Establish Relationships
• Click the relationships button on the
toolbar
• Tables are related by matching fields.
4
Types of Queries
• Select query
• Update query
• Append query
• Delete query
5
Create Queries
• By using wizard
– select table, fields
– Not convenient to design complex
query
• By design view
• By SQL view
– Input SQL sentence directly
6
Microsoft Access
7
Defining a New Data Base
8
Naming the Data Base
File
Name
OK
Change Name/Directory
9
The New Data Base
10
Tables
General Relational Data base Table
Fields
Row
or
Record
Id
214
215
320
Name
Jones
Age
25
Address
1 New St, Malvern
Key Field (Indexed)
11
Access Table
12
Define Fields in Table
13
Add The Name Field
14
Select Field Type
15
Set the Field’s Parameters
16
Name as Primary Key
17
Define the Image Field
Select
OLE
Object
18
Image Parameters
19
Click to Save the Table
Click
Yes
20
Name the Table
21
Table1 is now in the Database
22
Select Forms
Select
New
Form
23
Associate Form with Table
Relate
Form
To
Table
24
Create Form for Table1
Select
Form
Wizard
25
Select AutoForm
26
New Form -Run Mode
27
Close the Form
28
Save the Form
Select
Yes
29
Name the Form
30
Form is part of the Database
Form in
Database
31
Modify the Form
32
Delete the Current Title
Click
The
Title
and
Press
Delete
33
Select A in ToolBox for Title
34
Properties
35
Save the Change
36
Run The Form
Click Open
or
Double Click
The Form
37
Add an Image Record
38
Type in the Name
39
Select ClipArt File
40
Copy Butterfly to Clipboard
41
Paste Butterfly to Image
42
The Butterfly goes to Image
43
Enter Next Record
44
Insert Object
Right
Mouse
Click
45
Select Object Type
46
Select Graph
47
Graph
48
Clip Art
49
Cartoon
50
Add a “Next” Button
51
Name the Button
52
The Button Event
53
Exit Button
54
Name the “Exit” Button
55
The Exit Button
56
Event Coding
Sub Exit_Click ()
On Error GoTo Err_Exit_Click
DoCmd Close
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Error$
Resume Exit_Exit_Click
Basic Subroutine
defined for Event
Event Operation
Exit the Subroutine
Error processing
End Sub
57
Access database wizards, pages,
and projects
• Access' wizards are existing database structures
that only need data input.
58
Database Window
• The Database
Window
organizes all
of the objects
in the
database.
59
Design View
• Design View
customizes
the fields in
the database
so that data
can be
entered.
60
Datasheet View
The datasheet allows you to enter data into the
database
61
Create a Table in Design View
• Design
View will
allow you
to define
the fields
in the
table
before
adding
any data
to the
datasheet.
62
Adding Records
• Add new
records to
the table in
datasheet
view by
typing in
the record
beside the
asterisk (*)
that marks
the new
record.
63
Hiding Columns
• Columns can also be
hidden from view on
the datasheet
although they will
not be deleted from
the database.
64
Finding Data in a Table
• Data in a datasheet can be quickly located by
using the Find command.
65
Table Relationships
• Relationships can be established to link fields of
tables together.
66
Enforce Referential Integrity
67
Enforce Referential Integrity
• A line now connects the two fields in the
Relationships window.
68
Create a Query in Design View
69
Create a Query in Design View
70
Create a Query in Design View
71
Create query by using wizard
72
Create query by using wizard
73
Find Duplicates Query
74
New Query Example
Click New
75
Select Design View for New Query
Design View
76
Select the Members Table for the Query
Add Members
then Close
77
Select Fields for Query
Select Field
78
Selection Criteria
Criteria
79
View the Selected Data
View Selected data
80
The Selected Data
81
View Query SQL
SQL
82
The Query SQL Code
83
Save the Query
Save
84
The Saved Query
85
View the Query Data
86
Create Form by Using Wizard
87
Create Form by Using Wizard
88
Create Form by Using Wizard
89
Create Form in Design View
90
Create Form in Design View
91
Sample Form
92
Adding Records Using A Form
93
Form Controls
94
Reports
95
Reports
96
An Access Report with
All Sections Shown
97
Exporting Access Query to Excel
Workbook File
98
Reposition and Resize Controls
in Subreport
99
Query Data Exported to an
Excel Worksheet
100
Import Data from Other
Applications as an Access Table
• You can import data from a variety of other
applications, including Excel lists, as an
Access table.
• If you import data from an Excel list, be
sure that the range of the list is named
before attempting to import the data in the
list.
101
Import Spreadsheet Wizard Dialog Box
102
First Row Contains Column Headings
103
“Choose primary key” Option
104
Imported Trial Balance Table in
Datasheet View
105
Macros in Microsoft Access
•
Use to Open or Close Forms and Reports
•
Print Reports, or send to other Applications
•
Build Menus
•
Execute a command from a Menu
•
Display Messages or Sound Beeps
•
Start Other Applications
106
A Simple Button Menu Form
Open Form A
Open Form B
Exit
107
Button for Form A
108
Name the Button and Finish
109
The Button to Open Form A
110
Button Wizard gives VB Event Procedure
111
Event Procedure for the OpenA Button
112
Cancel Wizard to Define a OpenB
Wizard Off
OpenB
Open Form B
Adjust
Size
113
Define a Macro for OpenB OnClick Event
Click
Dots
Select
Macro
Click
114
Name the Macro
Open Form B
Click
115
Select OpenForm
Click
116
Macro: Open Form B
FormB
Click
117
System Check
Click
118
Add the Exit Button
Wizard
On
Click
Click
119
The Exit Button
120
Exit.OnClick Event Procedure
121
Run the Simple Menu
Click
122
Event Procedure - Form A
123
Event Macro - Form B
124
Form with Local Event Procedures
125
Local Event Procedures or Functions
Click
126
Local VBA Code
127
Module Area
128
Global Values and Procedures
129
How Big with Access
There are file size limitations with MS Access.
Tables
Queries
Forms
Reports
Code
Table Space
Code Space
95
29
511
185
500 (200,000 statements)
800Mb
60Mb
130
Garbage Collection
Regular Compacting is
required to keep size down.
(Tools/Utilities/Compact)
131
MS Access - Offers
Tables Database Table Definition/Entry
Queries
SQL Queries against Tables
Forms
Forms to be displayed
Reports
Reports to be Printed
Macros
Macro Commands
Modules
Visual Basic Code
132
End of Lecture
• MS Access is include with some
versions of MS Office. It can be
learned by experimentation.
• MS Access is not a full featured
database like Oracle but it comes
very close and is a good alternative
for the PC desktop environment.
133