1 mis309 database systems introduction to microsoft access
TRANSCRIPT
1
MIS309 Database Systems
Introduction toMicrosoft Access
2
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.
3
Create Tables
• By using wizard• By entering data• By design view
– specify field name, data type– set general attributes– set primary key
4
Establish Relationships
• Click the relationships button on the toolbar
• Tables are related by matching fields.
5
Types of Queries
• Select query• Update query• Append query• Delete query
6
Create Queries
• By using wizard– select table, fields– Not convenient to design complex
query
• By design view• By SQL view
– Input SQL sentence directly
7
Microsoft Access
8
Defining a New Data Base
9
Naming the Data Base
FileNameOK
Change Name/Directory
10
The New Data Base
11
Tables
Rowor Record
Fields
General Relational Data base Table
Id Name Age Address214 Jones 25 1 New St, Malvern
215
320
Key Field (Indexed)
12
Access Table
13
Define Fields in Table
14
Add The Name Field
15
Select Field Type
16
Set the Field’s Parameters
17
Name as Primary Key
18
Define the Image Field
SelectOLEObject
19
Image Parameters
20
Click to Save the Table
ClickYes
21
Name the Table
22
Table1 is now in the Database
23
Select Forms
Select NewForm
24
Associate Form with Table
Relate FormTo Table
25
Create Form for Table1
Select FormWizard
26
Select AutoForm
27
New Form -Run Mode
28
Close the Form
29
Save the Form
SelectYes
30
Name the Form
31
Form is part of the Database
Form inDatabase
32
Modify the Form
33
Delete the Current Title
ClickThe TitleandPress Delete
34
Select A in ToolBox for Title
35
Properties
36
Save the Change
37
Run The Form
Click OpenorDouble Click The Form
38
Add an Image Record
39
Type in the Name
40
Select ClipArt File
41
Copy Butterfly to Clipboard
42
Paste Butterfly to Image
43
The Butterfly goes to Image
44
Enter Next Record
45
Insert Object
RightMouseClick
46
Select Object Type
47
Select Graph
48
Graph
49
Clip Art
50
Cartoon
51
Add a “Next” Button
52
Name the Button
53
The Button Event
54
Exit Button
55
Name the “Exit” Button
56
The Exit Button
57
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 End Sub
Basic Subroutinedefined for Event
Event Operation
Exit the Subroutine
Error processing
58
Access database wizards, pages, and projects
• Access' wizards are existing database structures that only need data input.
59
Database Window
• The Database Window organizes all of the objects in the database.
60
Design View
• Design View customizes the fields in the database so that data can be entered.
61
Datasheet View The datasheet allows you to enter data into the
database
62
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.
63
Adding Records
• Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record.
64
Hiding Columns
• Columns can also be hidden from view on the datasheet although they will not be deleted from the database.
65
Finding Data in a Table• Data in a datasheet can be quickly located by
using the Find command.
66
Table Relationships • Relationships can be established to link fields of
tables together.
67
Enforce Referential Integrity
68
• A line now connects the two fields in the Relationships window.
Enforce Referential Integrity
69
Create a Query in Design View
70
Create a Query in Design View
71
Create a Query in Design View
72
Create query by using wizard
73
Create query by using wizard
74
Find Duplicates Query
75
New Query Example
Click New
76
Select Design View for New Query
Design View
77
Select the Members Table for the Query
Add Membersthen Close
78
Select Fields for Query
Select Field
79
Selection Criteria
Criteria
80
View the Selected Data
View Selected data
81
The Selected Data
82
View Query SQL
SQL
83
The Query SQL Code
84
Save the Query
Save
85
The Saved Query
86
View the Query Data
87
Create Form by Using Wizard
88
Create Form by Using Wizard
89
Create Form by Using Wizard
90
Create Form in Design View
91
Create Form in Design View
92
Sample Form
93
Adding Records Using A Form
94
Form Controls
95
Reports
96
Reports
97
An Access Report with All Sections Shown
98
Exporting Access Query to Excel Workbook File
99
Reposition and Resize Controls in Subreport
100
Query Data Exported to an Excel Worksheet
101
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.
102
Import Spreadsheet Wizard Dialog Box
103
First Row Contains Column Headings
104
“Choose primary key” Option
105
Imported Trial Balance Table in Datasheet View
106
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
107
A Simple Button Menu Form
Open Form A
Open Form B
Exit
108
Button for Form A
109
Name the Button and Finish
110
The Button to Open Form A
111
Button Wizard gives VB Event Procedure
112
Event Procedure for the OpenA Button
113
Cancel Wizard to Define a OpenB
Wizard Off
AdjustSize
OpenB
Open Form B
114
Define a Macro for OpenB OnClick Event
ClickDots
SelectMacro
Click
115
Name the Macro
ClickOpen Form B
116
Select OpenForm
Click
117
Macro: Open Form B
FormB
Click
118
System Check
Click
119
Add the Exit Button
Click
WizardOn
Click
120
The Exit Button
121
Exit.OnClick Event Procedure
122
Run the Simple Menu
Click
123
Event Procedure - Form A
124
Event Macro - Form B
125
Form with Local Event Procedures
126
Local Event Procedures or Functions
Click
127
Local VBA Code
128
Module Area
129
Global Values and Procedures
130
How Big with Access
There are file size limitations with MS Access.
Tables 95Queries 29Forms 511Reports 185Code 500 (200,000 statements)
Table Space 800MbCode Space 60Mb
131
Garbage Collection
Regular Compacting is required to keep size down.
(Tools/Utilities/Compact)
132
MS Access - Offers
TablesDatabase 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
133
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.