MS-ACCESS

2007
 INTRODUCTION OF ACCESS 2007
Ms-Access is a part of Ms-Office Package .Ms-
access is an Application Software which is
installed in computer according to our
requirement. In Ms-Access we can perform
any type of calculation. Here we can maintain
any type of details such as Student Details,
Employee details, product Details etc. Ms-
Access is an Example of RDBMS Application.
The extension of Ms-access is *.Accdb.
 INTRODUCTION OF RDBMS APPLICATION
• R.D.B.M.S Stand for Relation Database
Management system. In this application we
can maintain all the Records and can perform
any type of calculation. R.D.B.M.S application
Provide the facility to maintain any details in
it’s real format. For Example :- Number ,text,
currency etc. In this Application we can store
huge amount of data or information for long
time. Ms-access is an Example of RDBMS
application where we always store records in
 INTRODUCTION OF DATABASE
• The File of Ms-Access is known as Database.
A database is the Collection of Multiple
objects. The Objects are Table, Query , Form ,
Report and Macro.

• HOW TO OPEN MS-ACCESS 2007:-

Step1:-
Start All Programs  Microsoft Office
Microsoft office access 2007
Step 2:-
Start Search Ms-Access
X
Home Create

Blank
Data base Data base
Click(a) Sharp pvt ltd
Click(b) Create Cancel
 TABLE
Table is an object were we store the Records in
Ms-Access. At 1st we create the Design of
Table and then fill the records in table from
Database view.

There are two Types of Table view:-

1)Table Design View
2)Table Datasheet View
 TABLE DESIGN VIEW
It is an area where we create the
design of table in this view at 1st we
have to define field name and
according to field name we have
define it’s data type. If we define
wrong data type then we can not fill
proper data in data sheet view.
 TABLE DATASHEET VIEW
It is an area where we can
fill the records. In this view
we can not modify the
field name and data type.
 Save Student Table

Office
Micro Soft Office Access X

There is no Primary Key define?

Yes No Cancel
Student Table X
Name Roll Address
Vinit 1 Telco
Anup 2 Sakchi
Ravi 3 Baridih

Table Data Sheet View .

 DATA TYPE
Data type is an option which provides the facility to
define the data type of any field. After we define data
type in table design view, we fill records in Data sheet
view according to our requirement if we define wrong
data type then we can not fill proper data in data sheet
view.
There are many types of data types in Ms-Access :-
1. Text 2. Memo 3. Number
4. Currency5. Auto Number 6. Date/Time
7. Yes/No 8. Attachment 9. Ole Object
10. Hyper Link 11. Lookup Wizard
 TEXT
Text is an Alpha numeric data type
which accepts numbers and
Alphabets. In this Data type we can
fill Maximum 256 Characters only.
Example:- Student Name, Employee
name.
Memo – Memo is an alpha numeric Data
type which accepts alphabets and numbers
also. In this data type data can be filled
upto maximum 65536 characters. Example
– Address.

Number – Number is a data type which

accepts only numeric value. In this Data
type any text or character cannot be used.
example:- Roll number, Bank Account
number, phone number etc.
Currency – Currency is a data type which accepts only
number. When ever we fill the record in datasheet
View then it will automatically get converted into
Rupees Sign. Example:- Salary, DA, HRA, Admission
Fees, Monthly Fee etc

Auto Number – Auto number is a Data type which

accepts only numbers in serial order after we define
this data type in Design View the record in this field
will be automatically filled in Data sheet view.
Example –Serial number.

Date/Time – Date/Time is a Data type which accepts

only Data or Time. Example – DOB, DOJ, DOA etc.
Yes/No – Yes/No is a Data Type which is used to
Display Yes or No. when we define this Data
Type in Design view then a small box will appear
in data sheet view. If your answer is yes then
click the box other wise leave it blank.
Example – Indian, etc.
Attachment – Attachment is a data type which
is used to attach more then one files in a field.
We can attach Ms-Word, Ms- Excel, Ms-
PowerPoint, Wallpaper etc. In this data type.
After attaching any file we can directly open any
file from table data sheet view in Ms- access.
Example X
Field name Data types Description
Name Text
Id Number
Files Attachment

Table design View

Emp Table X
Right Click
Name ID
Vinit 101 Manage
Neha 102 Attachment
Anup 103 Double click
 Attachment X

Attachment Add Click

v

Open

Close

OLE Object – OLE Stands for Object linking

Embedding. This is a data type which provides
the facility to insert any object. After we insert
the object we can directly open it from table
data sheet view in Ms- excel.
Emp Table X
Field name Data type
Name Text
ID Number
Photo Ole Object
 Emp Table X
Name ID Photo
Vinit 101
Right Click
Neha 102
Anup 103 Insert Object

Table Data Sheet View

Insert Object X
Create New Bitmap Image Ok Click

Create From File E:\Wallpaper Cancel

Browse
 HYPER LINK
Hyperlink is a data type which is used
to create the links of any information.
Using this data type we can create a
link with Ms- Word file, Ms- Excel file
etc. After we create the link we can
directly open any file from table data
sheet view in Ms- Access.
 Emp table X
Field name Data type Description
Name Text
ID Number
Bio-Data Hyperlink

Table design View

Emp Table X
Name ID Bio Data Right Click

Anup 101 Hyper link

Vikash 102 Edit hyperlink
Suraj 103
 LOOK-UP WIZARD
Look-up Wizard is a data type which is
used to create the list of any field.
When we create list in Table design
view then that list will be displayed in
table data sheet view when we fill the
records. After we create the list we can
just select the word and fill the record
very easily.
Emp. Table X
File name Data type Description
Name Text
ID Number
Post Look-up Wizard

Table design View

Look-up Wizard X
Number of Column 1
MD
MGR
PO
Clerk
Book Next Cancel

Then Click on Finish Button

Emp. Table X
Name Id Post
Vikash 1 MD
Sunil 2 MGR
Vinit 3 PO

Table Data Sheet View

Field Properties:-
Field properties is an option of table which is
used in design view of any table. Using this
option we can set field size default value and
also apply validations.
Field Size :-
Field size is an option of field properties
which is used to set the size of any field. In
number data type if we want to file more
then nine digit number then we convert it’s
field size from long integer into double.
 EMP. Table X
File Name Data Type Description
Name Text
ID Number
Post Text
Phone number Number
Field Properties
Field Size Double
------- --------

Table design View Byte

Long
Double
Emp. Table X
Name ID Post Phone No.
Raj 101 MD 9835146109
Vikash 102 MGR 8842983420
Suresh 103 PO 9083847349

Table data Sheet View

 DEFAULT VALUE
Default value is an option of
field properties which is used to
set any value as default. What
ever we set in default value it
will automatically fill in data
sheet view .
 Emp. Table X
File name Data Type Description
Name Text
ID Number
Post Text
Field Properties
Default value Worker
------------ --------------

Table Design View

 Emp. Table X
Name ID Post
Ram 1 Worker
Vinit 2 Worker

Table Data Sheet View

 VALIDATION
Validation is an option of field properties
which is used to set the limitation of any
field. After we set the validation we cannot
fill wrong value in table data sheet view.
We can define validation in text, number
and currency data type.
We can set validation in validation rule and
write any message in validation text.
 Emp Table X
File Name Data Type Description
Name Text
ID Number
Post Text
Field Properties
Validation Rule MD or MGR or PO
Validation Text Fill Current Post

Table design View

A. Text Data Type
Post MD or MGR Or Clerk
B. Number and Currency data type
D.O.B  >=#06/05/1993# and
<=#07/09/2000#
or
<=Date()
Salary >=20000 and <=50000
or
Between 20000 and 50000
 PRIMARY KEY
Primary key is an option of Ms-access
which is used to make any field unique.
After setting primary key in any field, we
cannot fill duplicate values in that field.
That means it will accept only one record
at a time. For defining the primary key, first
we have to select the field in which we can
set primary key.
Sharp Pvt Ltd: Data Base 1 X
Home Create Design

-------- -------
Primary Key
--------- --------
All Object Emp Table X
Field Name Data Type Description
Right Click Name Text
ID Number
Post Text
Primary Field Properties
Key ------ --------
------- --------
 RELATIONSHIP
Relationship is an option of Ms- access which is used
to create relation between two tables. After creating
the relationship we cannot fill wrong entry in table.
When we create relationship we must have minimum
two table where one is called master table and
another is called transaction table. The table in which
we set primary key and fill the record first, is called
master table and related table is called transaction
table. Transaction table will accept only those records
which is already filled in master table. After creating
relationship whatever we modify in master table, that
will be automatically modified in transaction table.
REQUIREMENT OF RELATIONSHIP
1. Minimum two table
2. One table must have Primary key
3. Data type must be same in both the tables
4. Data type should not be auto number
5. Transaction table must be empty
6. Tables must be closed

There are Two types of Relationship:-

• One to many
• One to one
 ONE TO MANY
In this relationship we have two tables
where one table has primary key and
second table does not. The table where we
define primary key and fill the record first is
called master table and related table is
called transaction table. In master table,
one record will be accepted only once but
in transaction table same record will be
accepted more than one time.
 ONE TO ONE
In this relationship we have two tables
and both tables have primary key. It
will accept one record only once.

1. Database tools Relationship

 Show Table X
Table Query
Emp. Table Add
Salary table
Retirement table Close

Relation Ship X
Salary Table
Emp. Table Name
Name ID
ID
1 Salary
Post Retirement table
Address 1 Name
ID
DOR
 Click
Edit Relationship X
Table Query Table Query Create
ID ID
----- ----- Cancel

v Enforce Referential Integrity

Case code Update related Fields
Case Code delete Related records
Relationship type One to Many
 QUERY
Query is an object of Ms-access which is used to filter
the records from table. Using this option we can
display the record according to the condition, update
the records and can also perform the calculations.

There are three types of query in Ms-access:-

1. Select query
2. Parameter query
3. Update query
 SELECT QUERY
Select query is a query which is
used to filter the records from
table. Using this option we can
display the record from any one
table field or more then one table if
there is relationship between both
the table. In select query we have
to define “AND” , “OR” condition.
Create Query design

Show table X
Table Query Add
Emp. Table
Close
Salary Table
Start Save Select Query
 PARAMETER QUERY
Parameter Query is a query which is
used to display the record from table. In
this query we can set parameter value in
any field. After we set parameter value
whenever we run that query, it will ask
for the parameter value. After we define
parameter value it will display the
record according to that parameter.
Create Query design

Show table X
Table Query
Emp. Table Add Click
Salary table
Close
Save Emp. Para Query
 UPDATE QUERY
Update Query is a query which is used
to update the record of table. Using
this query we can easily change or
modify the information of table. Using
update query we can also perform
calculations.

Create Query design

Show table X
Table Query
Emp. Table Add
Salary table click
Close
 Update

Right Click
Build

Table Name
Update to
Criteria
OR
 Expression Builder X
[Salary Table] ! [Salary] * 15/100
Ok
Cancel

+ - * / Paste
Click (a) + Query Name
Click (c)
+ Table ID
Click (d)
Click (b) + Emp Table Salary
- Salary table DA 15%

Save as Update Query

 FORM
Form is an object of Ms- Access which is used to create
form of Table or Query. After creating the form all the
records of table will be display in the form view. When
we fill new records in form then it will automatically
be displayed in table. When we fill new record in table
then that record will automatically be displayed in
form. After creating the form we can easily format the
form using different- different option.
There are two types of form view :-
1. Design View
2. Form View
1.Design View – In this view we can
format the form using different-
different options such as header,
footer, background etc.

2.Form view – in this view we can fill

the records and can display all the
formatting in this view
 Sharp Pvt Ltd Database -- X
Home Create

Click

Form
All Object Emp Table
Click
Emp Table Name Neha
Salary Table ID 101
Design
Post MD
View
Name ID Post Salary
Design Neha 101 MD 50,000
View
 Sharp Pvt Ltd Database -- X
Home Create Design
a/d ……….
Arial 22
View Aa ………… (for text)
B I U
A Label ……. Image

All Object Emp. Table X

Emp. Table Employee Form
Name Name
Salary Table ID ID
Post MD
Footer
 REPORT
Report is an Object of Ms-Access which is
used to print all the records of table. Using
this option, we can create the report of
table. After creating the report all the
information will be displayed in different
number of pages.
Sharp Pvt Ltd --- X
Home Create
------- -------
Report Click
------- ------- v

------- -------
All object Emp. Table X
Emp. Table Name ID Past
Emp. form Neha 101 MD
Anup 102 Mgr
Vinit 103 PO
 MACRO
Macro is an object of Ms-access which is
used to create the button of all objects like
Table, Query, Report etc. In other words we
can directly operate any object from
macro. In macro, we can also create the
maximize, minimize, restore, quit buttons.
We can also display message before
opening any object.
Create Macro
Macro : 1 X
Action Comments
Open table

Table Emp. Table

View Data Sheet
Data Mode Edit

1.CTRL + C
2.F12 or CTRL + S
3.CTRL + V
How to Display Message
Macro : 1 X
Action Comments
Msg Box

Message Click to open objects

Title Hello
 SWITCH BOARD
Switch board is an option of Ms-Access which is
used to add all the macro buttons in switch
board. After we add the macro buttons we can
directly operate that object. In Ms- Access we
can create any number of switch board and
store maximum eight buttons in a single switch
board. We can also create a link between two
switch boards.
1. Database tools Switch board manager
2. Switch board manager X
Main switch board(default)
Click (e)
Click (d) Emp. Switch Close
New Click (a)
Edit Click (g)
Delete Click (f)
Make as Default Click (d)

New Switch board X

(b)
Switch board Name
Emp. Switch

OK Cancel
Edit Switch board Page X
Switch board name
Emp. Switch Close
Item of Switch board page New
Edit
Delete
Move up
Click Move
down
Edit Switch board Item X
Text Emp. Table CTRL + V
Command Run Macro Click
Macro Emp. Table
CTRL + C
OK Cancel 2 times

How to Create link between switch board :-

Edit Switch board item X
Text Salary switch
Command Go to switch board
Switch board Salary switch

Ok Cancel
 START-UP
Start-up is an option which is used to add any object
in the starting page . Whenever we open our data
base then the required object will automatically open
which is being added in start-up.
Access Option
Access option X
Display
Office Current Switch board
Click (a)
Data base None
Form
Ok Cancel
 SECURITY
Security is an option which is used to
Protect our data base with a password.
To define the password we 1st have to
open our data base in exclusive mode.
Open

Office
Open X
---------- My document
--------- Sharp Pvt ltd
Click
--------- ------------
--------- -------------
File Sharp Pvt ltd open
Open
Cancel Open inclusive

Click

Data base Encrypt with password

Encrypt with password X
Password
******

Verify
******
Ok Cancel