Transcript Introduction to DBMS Concept (MS ACCESS)

ITCS 122
INTRODUCTION TO DBMS
CONCEPT (MS ACCESS)
Working with MS Access Databases
and Tables
Understanding the MS Access Environment
Microsoft Environment
Browsing the MS Access
Ms Office Button


shown in the upper-left corner replaces the File menu from older versions
Using these commands you can do any of the following:










New Create a new database file.
Open - any existing database file on your computer or network.
Save - design changes for the database object that is open and has the focus.
Save As -Save a copy of the current object or save a copy of the current
database in 2007, 2002/2003, or 2000 Access format
Print Print the currently open object that has the focus or the object in the
Navigation Pane that has the focus using the Print dialog box or the Quick Print
Manage Compact and repair your database file, back up your database, or
open the Database Properties dialog box to review and change properties specific
to this database.
E-Mail Export the currently open object that has the focus or the object in the
Navigation Pane that has the focus in various formats and send to another
person. If the focus is on an object in the Navigation Pane, that object is exported
instead of the object currently open. You can choose to export and send the
object in the following formats: Excel, HTML, Rich Text Format, or as a Text File.
Publish -the database to a document manager server or package your
database as a CAB file and digitally sign it.
Close Database Close the currently open database and return to the Getting
Started screen.
Quick Access Toolbar
 This special toolbar gives you “quick access” to some
of the more common commands
 you will use in Access 2007, and you can customize
this toolbar to include additional Commands
 Save Saves any changes to the currently selected
database object.
 Undo Undoes the last change you made to an object or a
record.
 Redo Cancels the last Undo change you made to an
object or a record.
 At the right end of the Quick Access Toolbar is a
small arrow. Click that arrow, and you’ll see the
Customize Quick Access Toolbar menu
Home Tab
The Home tab has the following groups:
 Views. Most objects in an Access database have two or more ways
to view them. When you have one of these objects open and it has
the focus, you can use the View command in this group to easily
switch to another view.
 Clipboard. You can use the commands in this group to manage data
you move to and from the Clipboard.
 Font. You can change how Access displays text using the commands
in this group.
Home Tab
 Rich Text. You can design fields in your database to contain




data formatted in Rich Text. You can use commands in this
group to format text in a Rich Text field
Records. Use the commands in this group to work with records,
including deleting records and saving changes.
Sort & Filter. You can use these commands to sort and filter your
data.
Window. Use the commands in this group to resize windows or
select one of several windows you have open. Note that Access
displays this group only when you have set your database to
display Overlapping Windows rather than Tabbed Documents.
Find. The commands in this group allow you to search and
replace data, specific record, or select one or all records.
Create Tab
 contains commands that let you create new
database objects. Each group on this
particular tab arranges its specific functions
by database object type.
Create Tab
The Create tab contains the following groups:
 Tables. Use the commands in this group to create
new tables or link to a Microsoft Windows
SharePoint Services list.
 Forms. You can create new forms using the
commands in this group, including PivotChart and
PivotTable forms.
 Reports. The commands in this group allow you to
create new reports using available wizards or to start
a new report design from scratch.
 Other. Use the commands in this group to create
new queries or build macros or modules to automate
your application.
External Data Tab
This tab has the following groups:

Import. The commands in the Import group let you link to data or import data or
objects from other sources such as other Access databases, Microsoft Excel
spreadsheets, Windows SharePoint Services lists, and many other data sources
such as Microsoft SQL Server, dBase, Paradox, and Lotus 1-2-3.

Export. You can use these commands to export objects to another Access
database or to export data to Excel, a Windows SharePoint Services site,
Microsoft Word, and more.

Collect Data. These two commands allow you to update data in your Access
2007 database from special e-mail options using Microsoft Office Outlook 2007.

SharePoint Lists. Commands in this group allow you to migrate some or all of
your data to a Windows SharePoint Services (version 3) site or synchronize
offline data with an active Windows SharePoint Services site.
Database Tools Tab
Database Tools Tab
The Database Tools tab on the Ribbon includes the following groups:
 Macro. Commands in this group let you open the Visual Basic Editor, run
a macro, or covert a macro either to a shortcut menu or to Visual Basic.
 Show/Hide. Commands in this group activate useful information
windows. Use the Relationships command to view and edit your table
relationships. Click the Property Sheet command to open the Property
Sheet dialog box that displays the properties of the object currently
selected in the Navigation Pane. Click the Object Dependencies
command to see which objects are dependent on the currently selected
object. Select the Message Bar check box to reveal the Message Bar that
displays any pending security alerts.
 Analyze. Use the commands in this group to print a report about your
objects or run one of the two analysis wizards.
 Move Data. The two wizards available in this group allow you to either
move some or all of your tables to SQL Server or move all your tables to
a separate Access database and create links to the moved tables in the
current database.
Database Tools Tab

Database Tools. You will see a different set of commands in this group
depending on whether you have opened an Access 2000, 2002, or 2003
database (.mdb) or an Access 2007 database (.accdb). In both groups,
you find commands to run the Linked Table Manager, the Switchboard
Manager, make an execute-only version (.mde or .accde) of your
database, or manage add-ins. In an .mdb file, you can find commands to
encode/decode your database (encrypt it) and set a password that a user
must know to run your database. In an .accdb file, you can find a
command to create an encrypted version with a password.
 Administer. Access displays this group on the Database Tools tab only
when you open an Access database file created in Access 2000, 2002, or
2003 (.mdb). The Users And Permissions command lets you edit and
define users and object permissions in the legacy security system no
longer supported in Access 2007 format (.accdb) database files. The
Replication Options let you manage the legacy replication features no
longer supported in Access 2007 format database files.
Collapsing the Entire Ribbon
 Collapsing the Entire Ribbon
 you can collapse the entire Ribbon by doubleclicking on any of the tabs. All the groups
disappear from the screen, but the tabs are
still available. You can also use the keyboard
shortcut Ctrl+F1 to collapse the Ribbon. To see
the Ribbon again, simply click on any tab to
restore the Ribbon to its full height or press
Ctrl+F1 again.
Navigation Pane
 a window that is permanently located on the left side of the
screen.
 open database objects appear to the right of the
Navigation Pane instead of covering it up.
 Easy access to the other objects in your database without having
to shuffle open objects around the screen or continually minimize
and restore object windows.
 view objects of different types at the same time
 Quickly Jumping to a Specific Object in the Navigation
Pane
 Click an object in one of the groups in the Navigation
Pane to select it and then press
 a letter key to quickly jump to any objects that begin
with that letter in that particular group
Navigation Pane Object Views
 Navigation Pane menu.
 you could select tabs to view each object
category, and each object type was sorted by
object name. The objects in each of the six object
types—Tables, Queries, Forms, Reports, Macros,
and Modules—are grouped together
 You can customize the Navigation Pane to display
the object list in many different ways
 provides commands under Filter By Group to
allow you to filter the database object list
Working with MS Access Databases
and Tables
Creating a database
Topic
Creating a New blank database
Create a table in design view and datasheet view
Saving and renaming a table
Switching Between Design and Datasheet View
Your Database must have the
following:
 Data Consistency
 Without data consistency, you could find that you
have all the data you could ever want, but you
can’t garner helpful information from it
 Data Integrity
 refers to the process of ensuring that a database
remains an accurate reflection of the universe of
discourse it is modelling or representing.
 there is a close correspondence between the facts
stored in the database and the real world it
models
Data Types
Data Type
Text
Memo
Used to store
Alphanumeric data (text
and numbers)
Alphanumeric data (text
and numbers)
Limitations/Restrictions
Stores up to 255 characters.
Stores up to 2GB of data (the size limit for all Access
databases), if you fill the field programmatically.
Remember that adding 2GB of data causes your
database to operate slowly. If you enter data
manually, you can enter and view a maximum of
65,535 characters in the table field and in any
controls that you bind to the field.
When you create databases in the Office Access
2007 file format, Memo fields also support rich-text
editing.
Data Types
Number
Date/Time
Numeric data
Number fields use a Field
Size setting that controls the
size of the value that the
field can contain. You can set
the field size to 1, 2, 4, 8, or
16 bytes
Dates and times
Access stores all dates as 8byte double-precision
integers.
Data Types
Stores data as 8-byte
numbers with precision to
four decimal places. Use this
data type to store financial
data and when you don't want
Access to round values.
Currency
Monetary data
AutoNumber
Unique values created by
Stores data as 4-byte values;
Access when you create a new
typically used in primary keys.
record
Yes/No
Boolean (true or false) data.
Access uses -1 for all Yes
values and 0 for all No values.
Data Types
OLE Object
Hyperlink
Stores up to 2GB of data (the size
limit for all Access databases).
OLE Object fields create bitmap
images of the original document
or other object, and then display
that bitmap in the table fields
and form or report controls in
your database. You must have an
Images, documents, graphs, and OLE server (a program that
other objects from Office and
supports that file type) registered
Windows-based programs
on the computer that runs your
database.
As a rule, you should use
Attachment fields for your .accdb
files instead of OLE Object fields.
Attachment fields use storage
space more efficiently and are
not limited by a lack of registered
OLE servers.
Stores up to 1 gigabyte of data.
You can store links to Web sites,
Web addresses
sites or files on an intranet or
Local Area Network (LAN), and
sites or files on your computer.
Data Type
Attachment
Any supported type of file
You can attach images,
spreadsheet files, documents,
charts, and other types of
supported files to the records
in your database, much like
you attach files to e-mail
messages. You can also view
and edit attached files,
depending on how the
database designer sets up the
Attachment field. Attachment
fields provide greater
flexibility than OLE Object
fields, and they use storage
space more efficiently
because they don't create a
bitmap image of the original
file.
Guidelines for choosing
Field Types
 Between Text and Yes/No fields
 Fields that can have only two are also called Boolean or
logical values. You can store this in a one-letter Text field,
using Y and N. This can be display on forms as a check box,
option button, or toggle button and can be switch from
custom format
 Text and Memo fields
 Text fields are limited to 255 characters — if you need more
than that, use a Memo field that contain over 65,000
characters of textual information
 Memo fields cannot be serve as a key and are more likely to
get to get corrupted like OLE Object fields and your
database may get indigestion
Guidelines for choosing
Field Types
 Text and Number (or Currency)
 When displaying a Number or Currency field, Access
drops any leading zeros (for example, 08540 becomes
8540 or $8,540).
 can format in many ways with control over the number of
decimal places
 can vertically align these fields on the decimal points,
which makes columns of numbers easier to read
 Number or Currency field values sort from smallest to
largest while text are sorted alphabetically
 in a Text field, Access sorts 55 before 6, because the 5
character comes before the 6 character
Working with MS Access Databases
and Tables
Creating a table
When to make a blank
database
 if you have data in another program that you
want to import into Access
 You want to make a database from nothing
 This involves the following
 Creating new tables, and then entering, pasting,
or importing data into those tables.
 Importing data from other sources, which creates
new tables in the process.
Table
 Add a table
 You can add a new table to an existing database
by using the tools in the Tables group on the
Create tab.
Table
 Process
 Insert a table, starting in Design view
On the Create tab, in the Tables group, click Table
Design
For each field in your table, type a name in the Field
Name column, and then select a data type from the
Data Type list.
Save
Table
 Creating a Table with look up
 Lookup help you define the characteristics of foreign
key fields that link to other tables.
 When define calculated you must to define the
properties for these fields. If the field in the
query is a foreign key linked to another table,
you can also set the Lookup properties.
 Lookup properties can use the in the query’s
Property Sheet pane to override them.
 It is useful when you want the user to pick from a
restricted value list—such as M or F for a Gender
field.
Table
 Build a table using one of the table templates
 built-in local templates
 Online template
 When you click one of the options under Template Categories
or From Microsoft Office Online, the center section of the
Getting Started screen changes to show graphics representing
of each of the database templates available in that category
 The first time you choose to download an online template,
Access 2007 displays the Microsoft Office Genuine Advantage
confirmation dialog box . Each time you download a template,
Access 2007 confirms that you have a valid and registered copy
of the 2007 Microsoft Office system. If you do not want to see
this dialog box again, select the Do Not Show This Message
Again check box. Click Continue to proceed with the download
and creation of your sample database.
Table
 Build a table using one of the table templates
 close the first table
 click the Create tab on the Ribbon, and then click
the Table Templates button in the Tables group
 Choose from five table template
 Five table templates





Contacts
Tasks
Issues
Events
Assets
Table
 Five table templates

Contacts - Use this table template when you need to track your
personal or business contacts. Key fields in this template include the
contact’s company, job title, and phone numbers.
 Tasks - Use this table template for keeping track of various tasks and
projects needing completion. Key fields in this template include start and
due dates for the task and percentage complete.
 Issues - Use this table template for recording various personal or
business issues. Some key fields in this template include the title of the
issue and the issue status
 Events - Use this table template as a personal organizer of your
appointments. This template includes fields for start and end
times of the event, the event date, and even the location.
 Assets - Use this table template for keeping track of your assets.
Key fields in this template include the acquisition date, the
original price of the asset, and the current price.
Table
 Saving your Table
 Access 2007 suggests a name for your new
database in the File Name text box and a
location to save the file beneath the File Name
text box
 You can select the drive and folder you want by
clicking the links on the left and browsing to your
destination folder
Table

Datasheet view
 is an empty datasheet, which looks quite similar to a spreadsheet








The first column is Access default field ID
You can rename the field directly from Datasheet view
a pencil icon on the row selector at the far left indicate that you’re adding or
changing data in that row.
Press the Tab key to move from column to column
Automatically saves what you typed
click the data you want to change and type over it or delete it
To delete column, click anywhere in that column and click Delete in the Fields &
Columns group of the Datasheet tab . Click Yes to confirm the deletion
insert a column, click anywhere in the column to the right of where you
want to insert the new column and then click Insert in the Fields &
Columns group of the Datasheet tab on the Ribbon

move a column, click the field name at the top of the column to select the
entire column, and drag the column to a new location
 You can also move the columns as a group.

Save this table by clicking the Save button on the Quick Access Toolbar
Table
 Design View
 You can modify your table
 To create a new table in Design view, click the Create tab on the





Ribbon and then click the Table Design button in the Tables group
the upper part of the Table window displays columns in which you
can enter the field names, the data type for each field, and a
description.
the lower-left section of the Table window set field properties.
the lower-right section of the Table window displays information
about fields or properties
In the Description
Field Properties allow you to set properties—settings that
determine how Access handles the field—and thereby customize a
field. The properties displays depend on the data type you select
Working with MS Access Databases
and Tables
Default value
Working with MS Access Databases
and Tables
Default value
Set a default value for a
table field
 Setting a default value
 In the Navigation Pane, right-click the table that
you want to change, and then click Design View.
 Select the field that you want to change.
 On the General tab, type a value in the Default
Value property box. The value you that you can
enter depends on the data type that is set for the
field. For example, you can type =Date() to insert
the current date in a Date/Time field.
 Save your changes.
Example of default value
Expression
Default field value
1
1
"MT"
MT
"New York, N.Y."
New York, N.Y. (note that you must enclose the
value in quotes if it includes punctuation)
""
A Zero-length string (zero-length string: A
string that contains no characters. You can use a
zero-length string to indicate that you know no
value exists for a field. You enter a zero-length
string by typing two double quotation marks
with no space between them (" ").)
Date( )
Today's date
=Yes
"Yes" is displayed in the local language of the
computer
Working with MS Access Databases
and Tables
Indexing
Indexing
 help Microsoft Office Access 2007 find and
sort records faster.
 stores the location of records based on the
field or fields that you choose to index.
 After Access obtains the location from the index,
it can then retrieve the data by moving directly to
the correct location.
Three values:
There can not be more than 32 fields in your table with
the Indexed property of either "Yes (Duplicates
OK)" or "Yes (No Duplicates)".
If a field is a Primary key set the Indexed property on those
fields to "Yes (Duplicates OK)"
Indexing
 Indexed
 Can be use in all data types except OLE
Object and Attachment
 You can ask that an index be built to speed
access to data values. You can also require
that the values in the indexed field always be
unique for the entire table.
Indexing
 Single-Field Indexes
 the default Indexed property setting for all fields
except the primary key is No
 If you want to set an index for a field Yes choices.
 In most cases, a given field will have multiple records with
the same value—perhaps you have multiple contacts in a
particular city or multiple products in the same product
category. You should select Yes (Duplicates OK) to create
an index for this type of field. By selecting Yes (No
Duplicates) you can have Access 2007 enforce unique
values in any field by creating an index that doesn’t allow
duplicates. Access 2007 always defines the primary key
index with no duplicates because all primary key values
must be unique.
Working with MS Access Databases
and Tables
Table relationship
Creating table relationship
 On the Database Tools tab, in the Show/Hide
group, click Relationships.
 If you have not yet defined any relationships, the
Show Table dialog box automatically appears. If
it does not appear, on the Design tab, in the
Relationships group, click Show Table.
Relationship
1. The primary key
2. This line represents the relationship
3. The foreign key
Creating table relationship
 Drag a field (typically the primary key) from
one table to the common field (the foreign
key) in the other table.
Creating table relationship
 Access draws a relationship line between the two
tables. If you selected the Enforce Referential
Integrity check box, the line appears thicker at
each end. In addition, again only if you selected
the Enforce Referential Integrity check box, the
number 1 appears over the thick portion on one
side of the relationship line, and the infinity
symbol (∞) appears over the thick portion on the
on the other side of the line, as shown in the
following figure.
Creating table relationship
Creating table relationship
 To create a one-to-one relationship Both of the common fields
(typically the primary key and foreign key fields) must have a
unique index. This means that the Indexed property for these
fields should be set to Yes (No Duplicates). If both fields have a
unique index, Access creates a one-to-one relationship.
 To create a one-to-many relationship The field on the one side
(typically the primary key) of the relationship must have a unique
index. This means that the Indexed property for this field should
be set to Yes (No Duplicates). The field on the many side should
not have a unique index. It can have an index, but it must allow
duplicates. This means that the Indexed property for this field
should be set to either No or Yes (Duplicates OK). When one
field has a unique index, and the other does not, Access creates a
one-to-many relationship.
Types of table relationships
 One-to-One Relationship
 This relationship have one, and only one, relationship — with each other.
 If both R and K have single-valued participation
 One-to-Many Relationship
 To represent a one-to-many relationship in your database design, take
the primary key on the "one" side of the relationship and add it as an
additional field or fields to the table on the "many" side of the
relationship. In this case, for example, you add a new field — the ID field
from the Customers table — to the Orders table and name it Customer
ID. Access can then use the Customer ID number in the Orders table to
locate the correct customer for each order.
 Many-to-Many
 To represent a many-to-many relationship, you must create a third
table, often called a junction table, that breaks down the many-to-many
relationship into two one-to-many relationships. You insert the primary
key from each of the two tables into the third table. As a result, the third
table records each occurrence, or instance, of the relationship
Why create table relationships?
1. Table relationships inform your query designs
2. Table relationships inform your form and report
designs
3. Table relationships are the foundation upon
which you can enforce referential integrity to
help prevent orphan records in your database.
An orphan record is a record with a reference to
another record that does not exist — for
example, an order record that references a
customer record that does not exist.
Why create table relationships?
1. Table relationships inform your query
designs
 To work with records from more than one table,
you often must create a query that joins the
tables. The query works by matching the values in
the primary key field of the first table with a
foreign key field in the second table.
Why create table relationships?
2. Table relationships inform your form and
report designs
 When you design a form or report, Office Access
2007 uses the information it gathers from the
table relationships you have already defined to
present you with informed choices and to
prepopulate property settings with appropriate
default values
Why create table relationships?
3. Table relationships are the foundation
 When you design a database, you divide your
information into tables, each of which has a
primary key. You then add foreign keys to
related tables that reference those primary
keys. These foreign key-primary key pairings
form the basis for table relationships and
multi-table queries
Referential integrity
 When you design a database, you divide your
information into many subject-based tables to
minimize data redundancy
 Example
 Suppose you have a one-to-many relationship
between Shippers and Orders and you want to delete
a Shipper. If the shipper you want to delete has orders
in the Orders table, those orders will become
"orphans" when you delete the Shipper record. The
orders will still contain a shipper ID, but the ID will no
longer be valid, because the record that it references
no longer exists
Referential integrity
 The purpose of referential integrity is to
prevent orphans and keep references in sync so
that this hypothetical situation never occurs.
Referential integrity
 After you have enforced referential integrity, the following rules
apply:
 You cannot enter a value in the foreign key field of a related table if
that value doesn't exist in the primary key field of the primary
table — doing so creates orphan records.
 You cannot delete a record from a primary table if matching records
exist in a related table. For example, you cannot delete an employee
record from the Employees table if there are orders assigned to that
employee in the Orders table. You can, however, choose to delete a
primary record and all related records in one operation by selecting
the Cascade Delete Related Records check box.
 You cannot change a primary key value in the primary table if doing
so would create orphan records. For example, you cannot change an
order number in the Orders table if there are line items assigned to
that Order in the Order Details table. You can, however, choose to
update a primary record and all related records in one operation by
selecting the Cascade Update Related Fields check box.
Working with MS Access Databases
and Tables
Field Validation
Working with MS Access Databases
and Tables
Field Validation
field validation
 Defining Simple Field Validation Rules
 Field Validation expression - consists of an operator
and a comparison value
 To define a simple check on the values that you allow in a field,
enter an expression in the Validation Rule property box for the
field
 Access performs this validation for data entered in a Table
window in Datasheet view, in an updateable query, or in a
form. You can specify a more restrictive validation rule in a
form, but you cannot override the rule defined for the field in
the table by specifying a completely different rule in the form.
 You can specify multiple comparisons separated by the Boolean
operators
Remember
 When you set the Required property to Yes and
the user fails to enter a value,
Access2007 displays an unfriendly message:
“The field ‘<tablename.fieldname>’ cannot
contain a Null value because the Required
property for this field is set to True. Enter a value
in this field.”
Access recommend that you use the Validation
Rule property to require a value in the field and
then use the Validation Text property to generate
your own specific message.
Comparison Symbol
field validation
 To validate a Text, Memo, or Hyperlink field against a
matching
 LIKE comparison operator – provide a text string as a comparison
value that defines which characters are valid in which positions.
 wildcard characters, can use to define positions that can
contain any single character, zero or more characters, or any
single number
field validation
 You can also specify that any particular position
in the Text or Memo field can contain only
characters from a list that you provide.
 You can specify a range of characters within a list
by entering the low value character, a hyphen,
and the high value character, as in [A-Z] or [3-7].
 If you want to test a position for any characters
except those in a list, start the list with an
exclamation point (!). You must enclose all lists in
brackets ([ ]).
 Page 862-873
Field Validation
Setting field Validation
1. Go to design view
2. choose the field to set validation
3. On the General tab go to validation Rule
4. Set the validation
5. Save table
Working with MS Access Databases
and Tables
Input Mask
Defining Input Masks
 Input mask - assist you in entering formatted
data
 You can define an input mask for Text,
Number (except Replication ID), Date/Time,
and Currency data types
 You can use an input mask to do something as
simple as forcing all letters entered to be
uppercase or as complex as adding parentheses
and hyphens to phone numbers. You create an
input mask by using the special mask definition
characters
 Input mask definition character
Setting Input Mask
1. Go to design view
2. choose the field
3. On the General tab go to Input mask
4. Click the Input Mask wizard button
5. Set the input mask
6. Save table
Working with MS Access Databases
and Tables
Look up wizard
Look up
Working with MS Access Databases
and Tables
Navigating the data
Keystroke for editing
Working with MS Access Databases
and Tables
Hyperlink and Attachment
Entering and Editing
Hyperlinks
 When you type something into a hyperlink field
in a datasheet, the text you type instantly turns
to a hyperlink — blue, underlined text that you
click to go to whatever site the link refers to.
 four different parts:
 The underlined text you see in a datasheet or form
 The address that the hyperlink links to (the only
required part)
 The sub-address that the hyperlink links to
 A screen tip — text that appears in a small box
when the cursor hovers above the hyperlink
Entering and Editing
Hyperlinks
 simply type the address or path of the page
or file you want to link to or paste it from your
Web browser or Windows Explorer
 enter a hyperlink using the Insert Hyperlink
dialog box
 Ctl+K to enter or edit in the Insert Hyperlink
dialog box.
Using the Attachment Data
Type
 To create an Attachment field in your table,
use one of these options:
 Insert a field based on the Attachment field
template into your datasheet.
 Define the field data type as Attachment using
the Data Type option in the Data Type and
Formatting group of the Datasheet tab on the
Ribbon, or using the Data Type drop-down list in
Table Design view.
Working with MS Access Databases
and Tables
Entering Special Character
Entering special characters
1. Choose Start➪All Programs➪
2.
3.
4.
5.
Accessories➪System Tools➪Character
Map.
Browse to find the character you need
Double-click the character or select it and
click the Select button to display it in the
Characters to Copy box.
Click the Copy button.
Return to Access and click the Paste
button or press Ctrl+V.
Working with MS Access Databases
and Tables
Autospelling
Checking Your Spelling
 check your spelling in a datasheet or form by
clicking the Spelling button in the Records
group on the Home tab of the Ribbon.
 Using AutoCorrect for Faster Data Entry
1. Click the Office to display the File menu.
2. Click the Access Options button on the bottom
bar of the File menu.
3. Click Proofing in the navigation portion of the
Access Options window.
4. Click the AutoCorrect Options button.
Working with MS Access Databases
and Tables
Clipboard
Clipboard
 The Clipboard task pane displays the clips
that you cut or copy, along with an icon to
show you what type of clip it is (from Access,
Excel, Word, and so on). Paste any clip — not
just the most recent one — at the cursor’s
position by clicking the clip. Delete a clip from
the Clipboard by right-clicking the icon and
choosing the Delete option from the shortcut
menu. The Paste All button pastes all the
stored items at the cursor’s position.
Working with MS Access Databases
and Tables
Importing or linking data
Importing data
 basic way to move information is cutting and
pasting
 To copy or cut and paste data, follow these
steps:
1. Select the data or object that you want to cut or
copy.
2. Choose your favorite method (Ribbon button or hot
key) to cut or copy what you selected.
3. Move the cursor to the place where you want the
item to appear.
4. Choose your favorite method (shortcut menu,
Ribbon button, hot key) to paste the item.
Moving data from Excel to
Access
1. In Access, open the database to which you want to copy the data.
2. In Excel, open the workbook and display the worksheet that contains
your data.
Make sure that the first row of data makes adequate field names (you can
always change them later).
3. Select the data in Excel and press Ctrl+C to copy the data to the
Clipboard.
4. Click any table in the Navigation Pane and press Ctrl+V to paste the
data into a new table.
5. When Access asks if the first row of your data contains column headings,
click the Yes button.
Access creates a new table from the Excel data with the same name of
the Excel worksheet that contained the data.
Importing or Linking to Data
 Applications compatible with Access
 Microsoft Access databases (versions 2.0, 7.0/95,
8.0/97, 9.0/2000,10.0/Access 2002, 11/Access 2003,
12/Access 2007) and Project (versions 9.0/2000,
10.0/Access 2002, 11.0/Access 2003)
 dBASE versions III, IV, 5, and 7 (linking requires
updated ISAM drivers
 Paradox, Paradox for Microsoft Windows 3.x, 4.x,
5.0, and 8.0 (linking requires updated ISAM drivers
available from Microsoft TechnicalSupport,
www.microsoft.com)
Importing or Linking to Data
 Microsoft Excel spreadsheets, versions 3.0, 4.0, 5.0,







7.0/95, 8.0/97, 9.0/2000, 10.0/ 2002, and 11/2003
Lotus 1-2-3 spreadsheets (linking is read-only) in .wks,
.wk1, .wk3, and.wk4 formats
Microsoft Exchange
Delimited text files
Fixed-width text files
HTML versions 1.0 (if a list), 2.0, 3.x (if a table or list)
XML documents
SQL tables, Microsoft Visual FoxPro 2.x, 3.0, 5.0, and
6.x (import only), and data from other programs and
databases that support the ODBC protocol
Importing or Linking to Data
 Import: Make a copy of the data in Access.
(Copying and pasting is the simplest form of
importing.)
 Link: Keep the data in another file and tell
Access to get the data each time it is needed.
factors to consider

Storage: doubling the storage required because you are storing the data
in Access as well as in its original format.
 Customization: If the data is stored in a format other than Access
and you want to define a primary key, enforce referential integrity,
change field names, and/or customize field and table properties, you
should import the data.
 Maintenance: If a system is in place to update data in another format,
leaving the data where it is and linking to it makes sense, unless you’re
prepared to create a system to update it in Access. However, if the data
is not analyzed in its current format, moving the data to Access and
creating a system for updating it there makes sense.
 Accessibility: If you’re leaning towards linking to the data, will the
data always be available when you need it? Is it likely to move, or will
you need it when you are traveling or not on your usual LAN? If the data
is not accessible, Access will not be able to use that data for queries,
reports, and forms.
Getting external data
 Factors to consider

Are fields stored in columns and records in rows? This is relevant to
text and spreadsheet files.

Does the data you need begin at the top of the file? For text and
spreadsheets, Access expects to see one row of names and then
the data

Is all data within a field of the same type? If not, the field imports as a
Text or Memo field, which can’t be used in mathematical
equations.

Is the number of fields in each row the same? This is of particular
concern in a text file. If necessary, add null values to make your
data line up.

Are the field names in the data you are importing identical to the
field names in the Access table? When you append data, the
field names you’re importing must be identical to the file you’re
appending to.
Importing or Linking to Data
1. In Access, open the database that you want to add external data
to.
2. Display the External Data tab on the Ribbon and click the button
for the kind of data that you’re importing.
There are buttons for Access, Excel, SharePoint List, Text File and
XML file. In Get External Data dialog box, specify the name of the file
that contains the data you’re importing or linking to.
3. Use the File Name box to specify the source of the data. Click the
Browse button to navigate to your data file.
4. Choose how you want to store the data in the current database.
5. Click OK.
Importing text or
spreadsheet data
1. In the first wizard window, select the
Delimited option or the Fixed Width option
to describe how your data is divided up,
and then click the Next button.
 2. Further define where one field ends and
the next begins in the second window.
When done choosing your options, click the
Next button.
Importing text or
spreadsheet data
3. In the next window, click a column in the
displayed data to change properties for
that field, and then click the Next button.
4. In the next window, either select a primary
key field, let Access create a new
AutoNumber field as the primary key, or
specify that the field doesn’t have a
primary key field. When you’ve finished
your selections, click the Next button.
Importing text or
spreadsheet data
 Create new table
 If you choose to store the data in a new table, Access
creates a table and adds the imported data to it. If a
table with the specified name already exists, Access
overwrites the contents of the table with the imported
data.
 Append to an existing table
 add the data to an existing table, the import process
appends the data to the specified table.
 As you proceed, remember that most append
operations fail because the source data does not
match the structure and field settings of the
destination table.
Importing text or
spreadsheet data
 To avoid this, review the following
 First row - If the first row of the source text file does not
contain field names, make sure the position and data type of
each column matches those of the corresponding field in the
table
 Missing or extra fields - If one or more fields are not in the
destination table, add them before you start the import
operation. However, if the destination table contains fields
that don't exist in the source file, you need not delete them
from the table as long as they accept null values.
 Primary key - If the table contains a primary key field, the
source file must contain a column that contains values that are
compatible with the unique primary key field
 Indexed fields - If the Indexed property of a field in the table
is set to Yes (No Duplicates), the corresponding column in the
source text file must contain unique values.
Setting Table Design Options
 Click the Microsoft Office Button
 Click Access Options to see all the custom
settings offered (advance, current database,
datasheet, object designer, popular)
Creating a Default Template
for New Databases
 Access 2007 allows you to create your own default database
template for use with all new blank databases
 Creating a custom blank database template saves you time by
not having to continually set your personal Access options and
VBE options each time you create a new database
 Procedure


open a new blank database
name this new database as Blank (Access 2007 will use this template fi le
for all new databases)
 place this file in a specific subfolder in the Microsoft Office folder:
\Program Files\Microsoft Office\Templates\1033\Access
 Click OK
 Click the Create button
 close the table1 and do not save it
Creating a Default Template
for New Databases
 Open the Access Options
 Select all the options you want to set for any
new databases in the various categories of
the Access Options dialog box
 After you have defined all the settings you
want, close the database and exit Access.
Printing a Table Definition
 Go to Database Tools tab
 Click the Database Documenter in the
Analyze group
 Select the which document and types of
objects you want to document
 Click the Options
 select what you want reported
Database Limitation
 As you design your database, you should keep in mind
the following limitations:
 A table can have up to 255 fields.
 A table can have up to 32 indexes.
 A multiple-field index can have up to 10 fields. The
sum of the lengths of the fields cannot exceed 255
bytes.
 A row in a table, excluding memo fields and ActiveX
objects, can be no longer than approximately 4
kilobytes.
Database Limitation
 A memo field can store up to 1 gigabyte of characters,
but you can’t display a memo larger than 64 kilobytes
in a form or a datasheet.
 An ActiveX object can be up to 2 gigabytes in size.
 There is no limit on the number of records in a table,
but an Access 2007 database cannot be larger than 2
gigabytes. If you have several large tables, you might
need to define each one in a separate Access database
and then attach them to the database that contains
the forms, reports, macros, and modules for your
applications.
Building a Form
Forms and Object-Oriented
Programming
 OOP

In object-oriented system, an object is defined as a subject that has
properties, and you can invoke certain actions, or methods, to be
performed on that subject.
 Objects can contain other objects. When an object incorporates another
object, it inherits the attributes and properties of the other object and
expands on the object’s definition
 Form
 Define actions on tables or queries, and the fields you include
in forms initially inherit the underlying properties, such as
formatting and validation rules, of the fields in the source
tables or queries.
 You can define different formatting or more restrictive rules,
but you cannot override the rules defined for the tables
Form
 Forms can contain other forms, called subforms
 It define actions on other tables, queries, or forms
 Events that occur in forms and subforms can
trigger macro actions or Microsoft Visual Basic
procedures by clicking a command button on a
form
 Also, you can define macros or Visual Basic
procedures that execute when an event occurs,
such as clicking in a field, changing the data in a
field, pressing a key, adding or deleting a row, or
simply moving to a new row in the underlying
table or query.
Types of Dialog Box
 Modal
 one that the user must first close in order to have
access to any other framed window or dialog box of
the same application
 Modeless
 user does not have to close it in order to continue
using the application that owns the dialog box
 look like a regular dialog box or a tool window
Building a New Form
 Click the Blank Form command in the Forms
group on the Create tab.
 By default Access opens a blank Form
window in Layout view with the field list
displayed on the right
 You can click on the field list and drag and drop it
onto your form
 To switch to Design view, click the arrow
under the View button in the Views group and
click Design View
Form - Understand Layout and
Design view
 Layout view
 visually-oriented than Design view.
 Here, each control displays real data. As a result,
this is a very useful view for setting the size of
controls, or performing many other tasks that
affect the visual appearance and usability of the
form.
Remember:
 Certain tasks cannot be performed in Layout view and
require switching to Design view. In certain situations,
Access displays a message telling you that you must
switch to Design view to make a particular change.
 Layout view
 visually-oriented than Design view.
 Here, each control displays real data. As a result,
this is a very useful view for setting the size of
controls, or performing many other tasks that
affect the visual appearance and usability of the
form.
Form - Understand Layout and
Design view
 Design view
 Gives more detailed view of the structure of the
form.
 Here you can see the Header, Detail, and Footer
sections for the form.
 You cannot see the underlying data while you are
making design changes;
Form - Understand Layout and
Design view
 Design view
 Certain tasks that perform more easily in Design
view:
 Add a wider variety of controls to the form, such as
labels, images, lines, and rectangles.
 Edit text box control sources in the text boxes
themselves, without using the property sheet.
 Resize form sections, such as the Form Header or
the Detail section.
 Change certain form properties that cannot be
changed in Layout view (such as Default
View or Allow Form View).
Form Design Tools Contextual
Ribbon Tabs
Form Design Tools Contextual
Ribbon Tabs
 Control Group Button
Logo
Title
Insert Page Number
Text Box
Date & Time
Label
Button
Combo Box
Form Design Tools Contextual
Ribbon Tabs
 Control Group Button
List Box
Bound Object Frame
Subform/Subreport
Option Group.
Line
Check Box
Rectangle
Option Button
Macro
 automate frequently-used tasks
 many are created with VBA and are written
by software developers.
 Some macros pose a potential security risk. A
person with malicious intent can introduce a
destructive macro, in a document or file,
which can spread a virus on your computer.
Should I use macros or
should I use VBA code?
 Two concerns for choosing macro or VB
code:
 Security
 Functionality
 use macros whenever possible and resort to
VBA programming only for operations that
cannot be accomplished by using macro
actions.
 When you add programming to an object or
control, you should consider using the
following in this order of preference:
 A macro containing only actions that don't require
granting trusted status to the database in order to
run
 A macro containing actions that do require
granting trusted status to the database in order to
run
 A VBA procedure
 When you build a macro, you can switch
between the shorter list of actions that don't
require granting trusted status to the
database in order to run and the longer list of
all macro actions by clicking Show All
Actions in the Macro Builder. Clicking Show
All Actions also shows the complete list of
arguments for the RunCommand macro
action
When to use Macro
 In addition to the increased security and ease
of use that macros provide, you must use
macros if you want to:
 Assign an action or set of actions to a key. This
requires creating a macro group named AutoKeys.
 Carry out an action or a series of actions when a
database first opens. This requires creating a
macro named AutoExec.
When to use VBA programming
 You should use VBA programming instead of macros if you want
to do any of the following:

Use built-in functions, or create your own - By using VBA code, you can
create your own functions either to perform calculations that exceed the
capability of an expression or to replace complex expressions. You can
also use the functions that you create in expressions to apply a common
operation to more than one object.functions
 Create or manipulate objects - If you want to manipulate the definition
of an object in code. By using VBA, you can manipulate all the objects in
a database, in addition to the database itself.
 Perform system-level actions - By using VBA, you can check to see
whether a file exists on the computer, use Automation or Dynamic Data
Exchange (DDE) to communicate with other Microsoft Windows-based
programs, such as Office Excel 2007, and call functions in Windows
 Manipulate records one at a time - You can use VBA to step through a
set of records, one record at a time, and perform an operation on each
record. In contrast, macros work with entire sets of records at one time.
Enable or disable macro
 Click the Microsoft Office Button =>Access
Options.
 Click Trust Center => Trust Center Settings
=> Macro Settings.
 Click the options that you want:
 Macro option

Disable all macros without notification - Click this option if you don't
trust macros. All macros in documents and security alerts about macros
are disabled
 Disable all macros with notification - default setting, macros is
disabled, but you will received security alerts if there are macros present.
This way, you can choose when to enable those macros on a case by case
basis.
 Disable all macros except digitally signed macros – unlike the second
option, if the macro is digitally signed by a trusted publisher, the macro
can run if you have already trusted the publisher. If you have not trusted
the publisher, you are notified. That way, you can choose to enable those
signed macros or trust the publisher. All unsigned macros are disabled
without notification.
 Enable all macros (not recommended, potentially dangerous code
can run) - allow all macros to run. This setting makes your computer
vulnerable to potentially malicious code and is not recommended.
Assignment
1.
2.
3.
4.
5.
6.
Using student information and address list table
(following rules and format in slide # 79-80)
Create a suitable form for insertion of 100 records (gather
from other section)
Create a query for duplicate record, unmatched and
crosstab
Create a report that will print all records per month of
Birthday
Create a report that will print all students that have land
line, mobile no, email add and facebook_username
Make a pivot table that will filter field by age showing
name, Birthday, landline and address by rows
Exercises
1. Using the student information and address list
table
2. Insert a new form and using line chart show:
record population by age
2. Population of students with mobile and landline no
3. Population of students with facebook account
1.
3. Make a another form name main menu where
you will connect each form, report and pivot
table using command button and macro
Creating and Working
with Simple Queries
Queries
 Queries define actions on tables, and the
queries then become new logical tables
known as recordsets
 a query doesn’t actually contain any data. It
work with the data fetched by the query as
though it were a table
 Queries inherit the integrity and
formatting rules defined for the tables


Field lists where the fields for the tables or queries you chose for this query.
design grid, in which you do all the design work. Each column in the grid
represents one field that you’ll work with in this query. A field can be a simple
field from one of the tables or a calculated field based on several fields in the
tables.
Selecting Data from a Single
Table using Query
 Queries allow you to find data easily in multiple
related tables
 Procedure
 click the Query Design button in the Create tab .
 Open the Conrad Systems Contacts database
 click the Query Design button.
 Select tblContacts on the Tables tab of the Show Table
dialog box and then click Add to place tblContacts in
the upper part of the Query window.
 Click Close in the Show Table dialog box to view the
window
Selecting Data from a Single
Table using Query
 use the first row of the design grid to select fields—the
fields you want in the resulting record set, sort by, and
test for values
 also generate custom field names (for display in the
resulting record set), and you can use complex
expressions or calculations to generate a calculated
field.
 The second row shows you the name of the table from
which you selected a field or show it using Table
Names in the Show/Hide group
 this row provides valuable information when building a
query that fetches data from more than one table or
query.
Selecting Data from a Single
Table using Query
 In the Sort row, specify whether the selected or
calculated field will be sort in ascending or in
descending order
 use the check boxes to indicate the fields that will be
included in the recordset
 You can add field to the design grid so that
you can define criteria, but clear the Show
check box beneath the field to exclude it
from the recordset.
 use the Criteria row and the row(s) labeled Or to
enter the criteria you want to use as filters.
Selecting Data from a Single
Table using Query
 Specifying Fields
 Using the keyboard, you can tab to a column in
the design grid and press Alt+Down Arrow to open
the list of available fields. (To move to the
design grid, press F6.) Use the Up Arrow
and Down Arrow keys to highlight the field
you want, and then press Enter to select
the field.
 Drag the field from one of the field lists in the
upper part of the window to one of the
columns in the design grid
Setting Field Properties
 a field that is output by a query inherits the
properties defined for that field in the table.
 Different property that can be define
 Description property - the information that is displayed
on the status bar when you select that field in a
Query window in Datasheet view
 Format property - how the data is displayed
 Decimal Places property - for numeric data other than
integers
 Input Mask property
 Caption property - the column heading
 Smart Tags property