MS ACCESS

Microsoft Access is often used for mailing lists, phone lists, and other around-the-office
or home tasks for which a database would come in useful. Easy for non-programmers to
use, in the hands of a programmer it serves as a great tool for rapid application
development. Read the tutorials in this section to learn more about what you can do with
MS Access.

What Is the Importance of Microsoft Access?

Microsoft Access is a database management application used to enter, search, sort and
report on information. Microsoft Access is easy to use and tightly integrated with
Microsoft Office software applications. Access databases can be customized as complete
applications using development tools such as Microsoft Visual Basic and can scale up to
more advanced products including Microsoft SharePoint Portal and Microsoft SQL
Server.
Microsoft Access's interface makes it easy for new users to get started.

Ease of Use
1. Microsoft Access is a menu-driven database program that includes a graphical
user interface enabling novice users to enter, manage and report on their data.
Data can be entered using a familiar spreadsheet or table interface and the
program will automatically generate forms based upon the data and labels that
have been entered. Routine tasks such as searches, sorts and queries can be
automated with macros using a point-and-click paradigm. Reports can be
formatted by dragging and dropping fields on a graphical interface and the
program includes many templates that can be easily modified by end users.

Microsoft Office Integration

2. Microsoft Access is popular with Microsoft Office users since it is based upon the
familiar "drag and drop" method used with other Microsoft Office products .
Access information can be designed to tightly integrate with other Microsoft
Office products including Word, Excel, Project and Outlook. It is easy to import,
link and update data between these applications using dynamic data exchange and
object linking and embedding. Word documents can be easily customized with
contact and supplier records within an Access database, and Excel can be used as
a front end to enter and update Access database information in real time.

Customization
3. Microsoft Access users can easily create and modify database queries and reports
using the graphical user interface, and a menu-driven macro recorder to automate
 repetitive steps. Advanced users can customize Access applications using the
Visual Basic for Applications "VBA" programming language common to all
Microsoft Office applications. Access also includes many templates that can be
easily modified by end users.

Extensibility
4. Professional developers can develop complete, customized Access applications
using more advanced languages such as Visual Basic, Structured Query Language
(SQL) and C++. These custom applications can remain Microsoft Office
compatible and work with more powerful external systems including Microsoft
SharePoint Portal, Microsoft SQL Server or Oracle.

Benefits
5. Microsoft Access is important because it dramatically decreases the amount of
time required to exchange and leverage information between Microsoft Office
applications. Access provides database management functionality for novice end
users and is highly extensible with enterprise systems by professional developers.

References
• "Using Office Access 2007"; Robert Jennings; 2007

MS Access - its Advantages and Disadvantages

Article by Debasis Das (8,113 pts )

Edited & published by Eric Stallsworth (6,402 pts ) on Jul 1, 2009

Related Guides: MicrosoftMicrosoft AccessData Entry

There are very strong views about MS Access. Its detractors call it a toy database; good
for playing with but not for serious corporate use. Yet there are people with equally
strong opinions who swear by the product. They cite the ease of use in a whole lot of
situations. So what is it really like!

Introduction
Like most reasonably good products, there are two camps of opinion about MS Access.
There are many users who passionately dislike the product. Then there are those who
thorougly enjoy it. To properly evaluate the database software, one must separate the
emotional elements from the evaluation and put things in proper perspective. Access is a
fairly powerful engine that integrates well with other Office products. The interface is
nice and intuitive. It does cost a bit of money however, on the order of $200 or
thereabouts per desktop.

Advantages of MS Access
The first advantage that comes to mind is how familiar the interface is to most users. The
Office 2007 interface style is familiar, so users who have other Office products will not
be lost when dealing with Access.

There are two ways to view the database, no matter whether you are creating a new
one or opening something existing. The data view and the design view make it easy to set
up Access and start using it. You can enforce discipline when entering data through the
data entry forms. All kinds of rules to make sure you enter the right kind of data can be
implemented.

Access has become an industry standard in desktop use and the database engine is quite
powerful. Integration with voice recognition features makes data entry and menu
navigation quite easy. There are a large number of templates, including the ones you can
download online, which makes creating new databases quite easy. The ability to
customize them not only lets you get productive quickly, but you can also adjust things to
fit your specific needs. Connectivity options are a strong asset; Access databases can
connect to Excel tables, ODBC connectors, SQL Servers, and SharePoint Services sites
for live data. Tables created in these sources can be linked and used for generating
reports. These reports then give y

ou a better view/analysis of your data. Data harvesting from a large number of e-mail
addresses is possible too.

Multiple reports can be created for the same set of data. You are able to use the grouping,
sorting and filtering options to get more perspectives on the same data. Real time
previews let you create the different versions of the report that you may need for various
audiences.

Perceived Disadvantages
Access has been designed for desktop use, more like a personal database. It can support
multiple users over a workgroup alright; the total number of users (usually around 50 or
so simultaneous) is small however. That means Access is more useful for individual
departments or the SMB (small and medium business sectors). Access also has difficulty
dealing with databases larger than 2GB in size, though just to be safe one should limit
usage to about 1GB.

As you scale up the size, performance becomes sluggish (almost to the point of
unresponsiveness). Use of multimedia data, even your digital camera photos can eat up
space very quickly. Until the 2007 version came along, even the way the images and
other attachments used to be stored in Access databases caused a bloat. Though the
attachment field in Access 2007 takes care of that, the overall 2 GB space could limit you
very quickly. Many indicate that the SQL Server is a more real database, since it
competes with enterprise-level databases like Oracle.

Another difficulty pointed out by many is how publishing anything other than static files
is a problem with Access. It takes quite a bit of work to make data access interactive. You
could use Sharepoint but that represents a significantly large investment. Many believe
that the SQL in MS Access is not as robust as other databases. There's a very prevalent
belief that Access is oriented towards developers rather than end users. This opinion has
changed slightly with the introduction of Access 2007, but it's still there to a great extent.

Microsoft Access: Understanding Data Types

Many database enthusiasts, novice and expert alike, encounter problems with inaccurate,
duplicated or redundant data fields. Simply put they don't understand the relevance of
setting correct data types. Here we review relational database data types and how
important they are in your database design.

Understanding Data Types

Creating an effective test harness for a relational database must involve the population of
meaningful data that can contest your input procedures and outputs (queries, reports and
exports). As such ‘good data’ means populating your database with acceptable field
conditions to avoid potential data issues by choosing the correct data types and field
properties.

Data Types
Text - Fields that contain letters, numeric characters, spaces and various other symbols.
In use the text data type is so flexible and open to any possible input that it is probably
best to specify a fixed length.

Memo - Memo fields can contain the same items as text fields and are variable in length.
Able to contain up to 65,535 characters of data.
Number - Fields that store ‘meaningful’ numbers and enable you to perform
mathematical operations on the data stored within them. They can be 1, 2, 4 or 8 bytes in
size.

Date/Time - Date/time fields store dates and times in various formats. For example Long
Date: Wednesday, 30 July, 2008, Medium Date: 30-jul-08, Short Date: 30/7/08. Again,
calculations can be performed on the date/time field.

Currency - Currency or monetary values stored in a similar way to numeric data fields
but obviously such numbers are preceeded by a currency symbol. Here negative values
appear in brackets and you can configure currency fields to automatically round to a
specific number of decimal places.

Autonumber - Is a numeric value field which automatically increments each record as

you add more to the end of a table. This is a commonly used data type for primary keys
as it enforces quality data control of cell items.

Yes\No - Yes/No, True/False, On/Off data types which require input of one option from
two choices. As a result null values are not allowed.

OLE - Fields that contain objects that you place in the table from related programs that
support Object Linking and Embedding. They are very powerful and can seriously
enhance a databases aesthetics. Such fields can store up to 1GB of data which makes
embedding multimedia files and large offshoot databases highly practical.

Hyperlink - The hyperlink field simply is a holding place to store a hyperlink address
(URL) for quick launching to the Internet.

Lookup Wizard - Although not a traditional data type the Lookup Wizard allows users
to create fields for lookup purposes in other tables. These ‘masterfiles’ can then be
accessed across the board by any tables as drop-down options to choose from.

A few pointers when choosing the relevant data type

• When storing money and numeric values which require a high degree of accuracy
choose Currency.

• When storing text up to 255 characters or numbers that will not be used in
calculations choose Text.

• When storing numbers that may require calculation choose Currency or

Number.

• When storing large amounts of text, i.e. over 255 characters, choose Memo.

• When storing dates and times, its self explanatory, choose Date/Time.
 • When storing whole numbers of considerable length choose Number with
Integer in the fieldsize property.

• When storing long whole numbers choose Number with Long Integer in the
fieldsize property.

• When storing single precision numbers that include decimal places choose
Number with Single in the fieldsize property.

• When storing double precision numbers that may have decimal places choose
Number with Double in the fieldsize property.

• When storing unique, random numbers choose AutoNumber with Random in

the NewValues property.

• When storing unique, incremented ID numbers such as key fields choose

AutoNumber with Increment in the NewValues property.

• When storing globally unique identifiers such as those used within replicated
databases choose AutoNumber with Replication ID as the field size.

In the following part we will discuss the importance of Field Types and how they can be
used to shape the effectiveness of your databases.

Microsoft Access: Understanding Lookup Fields

Relational database design offers many great advantages over other forms of database
design as it embraces relationships and lookup tables. Here we discuss the importance of
using lookup fields in designing a database and how clever use of lookup tables can
enhance a databases performance.

Understanding Lookup Fields

When you need to design a specific table to perform data validation or to make life easier
for data input purposes it is strongly advised that you use Lookup Fields. With a Lookup
Table consisting of Lookup Field data items you can use a table with only one or two
fields such as a customer or contact ID and name, and then use these Lookup Fields over
and over on multiple input screens.

The Microsoft Access Lookup wizard makes this process incredibly straightforward so
that you can either

• Create a lookup value list which displays values from an existing table or query.
• Create a fixed set of values which are setup in their own right purely for the
purpose of creating subsequent fields.
For example these ‘masterfiles’ of quick-link data could hold information pertaining to
personal demographics - sex, gender, religion, ethnicity, etc, or business details such as
geographical location, buying categories, budgets, etc. Lookup fields are very powerful
not just for slick information retrieval but also help keep a database running at an
optimum level of performance because they reduce the duplication of effort and data
storage. Lookup tables can be used to drive all underlying information and in conjunction
with Primary and Foreign Keys make a database easier to use from an input clerk,
administrator and output point of view as their contents can be accessed by all of your
tables.

Creating a field that looks up data from another table in

Microsoft Access
1. Open the table in Design view.

2. If the field you want to use as the foreign key for a lookup field already exists, click in
the field’s row.

3. In the Data Type column click the arrow and select Lookup Wizard.

4. Click the option ‘I want the lookup column to look up the values in a table query’.

5. Click next and from the subsequent screen select the table or query containing the
values you wish to lookup.

6. Click next and from the subsequent screen choose the fields you wish to use for a
lookup list.

7. Click next and you will be asked by the lookup wizard to choose a label. Enter a
relevant label, i.e. keeping the same as the field name.

8. Click finish. To see the settings of the lookup click on the Lookup tab in the field
properties.

Microsoft Access will now create the lookup field and sets the field properties based on
the choices you made within the wizard. Once you've added this lookup list you can
amend the entries, add to them, and add the field to any form. Access will perform this
action automatically by copying its definition into the form. However be aware that if
you do change the definition of a lookup, changes will not be reflected in any subsequent
form that the lookup is attached to unless you delete the field from the form and add it
again.

Creating a value list field in Microsoft Access

1. Open the table in Design view.
2. In the field name column type the name for the field.

3. In the Data Type column click the arrow and select Lookup Wizard.

4. In the first dialog box, click the option that indicates ‘I will type in the values that I
want’.

5. Click next and enter the number of columns you want to see followed by the text
entries that you want the lookup list to hold.

6. Click next and set the label as you wish, remember it is probably best to set the label
name to the same as that of the field name to save on confusion later.

7. Click finish. To see the settings of the lookup list click on the Lookup tab in field
properties.

Microsoft Access will now create the fixed value list and set field properties based on the
choices you made within the wizard. Once you've added this lookup list you can amend
the entries, add to them, and add the field to any form. Access will do this by
automatically copying the definitions to the form in question. However be aware that if
you do change the definition of a value list field these changes will not be reflected
within the form until you delete the field from the form and add it again.

In the following part we will discuss setting up Relationships Between Tables and how
powerful relational database interfacing can provide a whole new dimension of database
interconnectivity that enhances your entire information flow.

Understanding Field Properties

Field properties are an important and powerful part of any database design build. The
integrity of your data hinges on setting field properties to the correct value. For example
you may wish to display a full data item or just an abbreviation, you may set currencies to
display with a specific number of decimal places, or a date and time stamp to appear in 8
or 10 character format. When you create fields Access allows you to display a list of
properties based on the data type of that particular field. These field properties are
different depending on the data type item chosen.

The following data type items are related to the corresponding Field Type properties.

Text and Memo data type field properties

Field Size - Used to vary field lengths, defaults to 50 characters.
Format - Allows you to create custom formats for your text string, i.e. '@' = text
required, '&' = text is not require, '<' = forced lower case, '>' = forced upper case.

Input Masks - Used to ensure that all data entered into a field was the correct data entry
pattern. Forces the user to enter values in an exact format.

Caption - A naming convention given to a field to help the end user input the relevant
details.

Default Value - A self explanatory field type, automatically populating the field with a
pre-defined default string.

Validation Rule - Allows you to set limits to values to maintain data integrity.

Validation Text - Used in conjunction with the Validation Rule will display a warning
message if the user has not met certain validation rule criteria when populating the field.

Required - Simply a value that denotes whether a field should be populated, i.e. is or is
not mandatory.

Allow Zero Length - Allows you to control the way that blank fields are handled.

Indexed - The crucial field property for expedient queries, sorting and field
manipulation. Indexed fields hold all tables and relationships together.

Number data type field properties

Field Size - Allows you to limit the range of digits allowed within the field. The default
setting being Long Integer and for decimal places set this should be set to Single or
Double.

Format - Enables numeric and date/time fields to display the contents of a field in a
specific format.

Decimal Places - Simply enough, sets the number and currency field to a chosen set of
decimal places.

Yes/No - Can contain one option from two choices. Yes/No, True/False, On/Off.

In the following part we will discuss the importance of Lookup Fields and how they can
be used by multiple tables to populate highly valuable data, quickly via a simple set of
masterfiles (Lookup Tables).
Field data types available in Access (MDB)
Note The information in this topic applies only to a Microsoft Access database (.mdb).

The following list summarizes all the field data types available in Microsoft Access, their
uses, and their storage sizes.

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, part numbers, or
postal codes.
Stores up to 255 characters. The FieldSize property controls the maximum
number of characters that can be entered.
Memo
Use for lengthy text and numbers, such as notes or descriptions.
Stores up to 65,536 characters.
Number
Use for data to be included in mathematical calculations, except calculations
involving money (use Currency type).
Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The
FieldSize property defines the specific Number type.
Date/Time
Use for dates and times.
Stores 8 bytes.
Currency
Use for currency values and to prevent rounding off during calculations.
Stores 8 bytes.
AutoNumber
Use for unique sequential (incrementing by 1) or random numbers that are
automatically inserted when a record is added.
Stores 4 bytes; stores 16 bytes for Replication ID (GUID).
Yes/No
Use for data that can be only one of two possible values, such as Yes/No,
True/False, On/Off. Null values are not allowed.
Stores 1 bit.
OLE Object
Use for OLE objects (such as Microsoft Word documents, Microsoft Excel
spreadsheets, pictures, sounds, or other binary data) that were created in other
programs using the OLE protocol.
Stores up to 1 gigabyte (limited by disk space).
Hyperlink
Use for hyperlinks. A hyperlink can be a UNC path or a URL.
Stores up to 64,000 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— -choosing this option in the data type list
starts a wizard to define this for you.
Requires the same storage size as the primary key that corresponds to the Lookup
field— -typically 4 bytes.