Introduction to MS Access

Steve Shapiro
Computer Services Manager
Office of Research Services and Administration
University of Oregon
Region VI/VII NCURA Conference
April, 2011

1
What is a Database?
a comprehensive collection of related data
organized for convenient access, generally in a
computer

dictionary.reference.com/browse/database
This definition was found via a search of Googles database
Databases are all around us, and all of us interact with them
on a daily basis.
Who said you cant make your own?

2
Where does MS Access fit?
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
lists history for about 50 database programs

Popular Databases:

Oracle, Microsoft SQL Server, IBM DB2, FileMaker, Ingres, MySQL, Corel Paradox, Dbase III,
R:Base

MS Access is a low-end to mid-tier database application

It runs independently on workstations for single users
It runs on servers that can be accessed by multiple users at the same time on a network
It provides a user friendlier front end to more powerful database applications such as Oracle and MS SQL Server

3
What is MS Access?
MS Access is a relational database, meaning
that data is stored in multiple tables that are
related to each other.
PIs in one table, their awards in another table.
The database maintains a connection between the
tables using something called a key a number
that is the same in both tables.

4
History
Access is one of the few products originally developed
by Microsoft
Development began in the mid 1980s
Combined with other databases that Microsoft
licensed such as R:Base and FoxPro
Released in November 1992 as a single user application
for very small (<10mb) files
Became dominant database for windows when
competitors failed to transition to Windows
successfully.
Now a very stable and robust application, scaling from
1 to many users and up to 2g of data in each file
5
Todays Session
Will cover < 1% of MS Access capability
We wont talk about
Security
Advanced anything
Forms, queries, front/back ends, modules, macros
Interfacing with other databases
Advanced Data Validation
Questions on these and other topics are welcome!

6
Relational Database?

7
Keys relate information
in different tables

8
5 Major Components of Access
Access Database Objects

Tables
Queries
Forms
Macros
Modules

9
Tables
Tables hold the information, called data

10
Tables - Data Types
Text Use for text or combinations of text and numbers, such as addresses, or for numbers
that do not require calculations, such as phone numbers or postal codes (255 characters)
Memo Use for lengthy text and numbers, such as notes. Stores up to 63,999 characters
Number Use for data to be included in mathematical calculations, except money
Date/Time Use for dates and times
Currency Use for currency values and to prevent rounding off during calculations.
AutoNumber Use for unique sequential that are automatically inserted with a new record
Yes/No Use for data that can be only one of two possible values, such as Yes/No, True/False,
On/Off.
OLE Object Use for OLE objects (such as Microsoft Word documents, Microsoft Excel
spreadsheets, pictures, sounds,
Hyperlink Use for hyperlinks (hyperlink: Colored and underlined text or a graphic that you
click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on
an intranet. Stores up to 2048 characters.
Lookup Wizard Use to create a field that allows you to choose a value from another table or
from a list of values using a combo box

11
Table Encounter
The prospect of creating multiple tables almost always
intimidates beginning database users. Most often, beginners
will want to create one huge table that contains all of the
information they need, similar to an Excel spreadsheet.

When thinking about which fields to add to a table, a good

first guess is: What piece of information will only occur once?
Such as
A person will probably only have one first name, though it may change
First_Name is a good candidate for the PI table
A table needs at least on field that never duplicates in the same table
Two or more people can have the same first, last and middle names
A person may have more than one award (per name)
The award title is not a good candidate for the PI table, since we dont know how many awards a person
may have

12
Queries
Queries select and modify
specific data

Queries convert data to

information

They are used to populate forms

and reports

MS Access uses a visual query

wizard to help novice (and
advanced!) users construct
queries

13
Simple Queries From a Single Table
Select Award_Title from
Awards where (Award_Title
Like Exploring*) and
(Closed = False) Order By
Award_Date;

14
Simple Queries from Multiple Tables
Set up relationships
(Access may make you do this and if it does, will help you with
a wizard)

15
Simple Queries from Multiple Tables
SELECT
PIs.[First Name],
Awards.Award_Title,
Awards.Award_Date,
Awards.Closed
FROM
PIs
INNER JOIN Awards
ON
PIs.ID = Awards.PI_ID;

16
Action Queries
Queries can update, add or delete records
from a table

DELETE * FROM Pis WHERE (PIs.[First Name])="No Research";

17
Forms
Forms let you enter and display specific data
in a customized format.

18
Basic Types of Forms
Single Record

Datasheet

19
Form Controls
Bound Controls
Are directly attached to the data and will update
as you leave the field on the form
UnBound Controls
Have to be manipulated with program code
Calculated Controls
Do not exist in the data tables. They are derived
based on other controls or fields in the database

20
Types of Controls
Text Box: Displays and allows user to enter data
Label: Displays static text
Button: Does something by runnning macros or VBA Code
Combo Box: A drop down list of values
List Box: A list of values
SubForm: a form of related data within a form
Shapes: boxes, lines, images
Check Boxes: Yes/No or True/False
Option Groups: choose one option from a group
Toggle Buttons: enabled or not enabled
Tabs: for forms with lots of data, multiple tabbed pages
Charts: Display data in graphical format
More

21
Properties
What can a Control look like and how can it act?

22
Events Making Access Do Something

23
Visual Basic Code in a Form
Behind a button

Private Sub btn_Close_Click()

DoCmd.Close acForm, frm_PI_Awards
End Sub

24
Data Validation and formatting
In the Table

25
Data Validation
On the Form

26
Macros
Wizard driven tool to automate repetitive
tasks
Can be very simple or very complex

27
Reports
Reports display and print formatted data
Text
Form Letters, columnar reports, grouped reports
Graphics
Sub Reports
Export to other formats, such as spreadsheet,
word processing
Wizard driven or drive yourself

28
Designing a Report

29
Modules
Modules contain Visual Basic for Applications
program code as subroutines or functions
Visible from anywhere in the Application:
tables, queries, forms, macros and reports
Subroutines typically do something
Functions do something and return a result

30
VBA Code
Wizard and context assistants help write code
Almost, but not quite understandable
Public Function Activate_Detail_Form(My_Form As Form)
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("tbl_sys_Color_Scheme", dbOpenSnapshot)
rec.MoveFirst
My_Form.FormHeader.BackColor = rec!Detail_Header_BackGround_Color
My_Form.Label1.ForeColor = rec!Detail_Header_Font_Color
rec.Close
Set rec = Nothing
Set db = Nothing
My_Form.Repaint
End Function
31
Getting Started with Access
Database development is quite unlike most other
programs used to create information in a computer,
such as word processing or spreadsheet.

Database development requires prior knowledge

A beginning user opening Access for the first time

likely has no idea where to start. Unlike Word or
Excel, you cant just start typing

32
Create Ribbon
Starting point for all new objects in the
database

33
Conventions
Application developers like to label objects in their
applications in such a manner that when they go back to look
at it several years later, they can figure out what theyve done.
We use prefixes and suffixes when we name things:
Tables: tbl_Awards
Forms: frm_Awards
Buttons on forms: btn_Form_Close
Reports: rpt_Reports
Text fields within a table: PI_Name_txt
Integer Fields within a table: Award_Number_txt
Spaces in object names are allowed, but dangerous.
Use underscores _ or enclose object names in brackets
[tbl Awards] 34
Educational Materials
Office.microsoft.com
Templates Databases

My favorite Book:
Microsoft Access 2010 Bible
By Michael Groh

Search Engine: [Access 2010 and your question]

35
36