Microsoft Access 2000

Database and Tables

MELJUN CORTESMELJUN CORTES

rvg

Setting up a Database

To get started with Access 2000, we first create a database:

1) Start Access 2000.

2) In the Microsoft Access dialog box, select Blank Database, then click OK.

3) In the File New Database window, decide where to store the database.

4) Click Create.

rvg

Tables

The first object to be added to a database is a table.

The rows in the tables are called RECORDS.

The columns in the tables are called FIELDS. A field contains specific piece of data with a record.

rvg

Tables

PRIMARY KEY (or Unique Identifier) is a field that uniquely identifies each record in the table.

rvg

Creating Tables

Create Table in Design View.Create Table by using WizardCreate Table be Entering Data

rvg

Creating Tables

In creating a table, we describe the STRUCTURE of the table by describing the fields.

For each field, we indicate the following: Field Name - unique name within the table Data Type - indicates the type of data

which a field can contain Description - detailed description of the

field

rvg

Field Names

The rules for field names are: Names should be unique within a table, e.g., the

same names cannot be used for two different fields in the same table.

Names can be up to 64 characters in length. Names can contain letters, digits, spaces and

most punctuation marks. Names cannot contain periods, exclamation

point (!), or square brackets ([]).

rvg

DATA TYPES

TextMemoNumberDate/TimeCurrency

Autonumber

Yes/No

OLE Object

Hyperlink

rvg

TEXTUSE:Text or combinations of text and numbers,

such as addresses. Also, numbers that do not require calculations.

SIZE:Up to 255 characters

EXAMPLES:Phone numbers, Postal codes.

rvg

MEMOUSE:Lengthy text and numbers

SIZE:Up to 64,000 characters

EXAMPLES:Notes, Descriptions

rvg

NUMBERUSE:Numeric data to be used for mathematical

calculations, except calculations involving money.

SIZE:1, 2, 4, or 8 bytes.16 bytes for Replication ID (GUID) only.

rvg

NUMBERFIELD SIZE:Byte, Integer, Long Integer, Single,

Double, Replication ID

FORMAT:General Number, Currency, Fixed,

Standard, Percent, Scientific

rvg

DATE/TIMEUSE:Dates and times.

SIZE:8 bytes.

FORMAT:General Date, Long Date, Medium

Date, Short Date

rvg

CURRENCYUSE: Currency values. Use the Currency data type to prevent rounding off

during calculations. Accurate to 15 digits to the left of the decimal point

and 4 digits to the right.

SIZE: 8 bytes

FORMAT: General Number, Fixed, Standard, Percent, Scientific

rvg

AUTONUMBERUSE:Unique sequential (incrementing by 1) or

random numbers automatically inserted when a record is added.

SIZE:4 bytes.16 bytes for Replication ID (GUID) only.

rvg

AUTONUMBERFIELD SIZE:Long Integer, Replication ID.

NEW NUMBER: Increment by OneRandom NumberReplication ID (also referred to as GUID -

globally unique identifier)

rvg

YES/NOUSE:Fields that will contain only one of two

values.

SIZE:1 bit

FORMAT:Yes/No, True/False, On/Off

rvg

OLE OBJECTUSE: Objects created in other programs using the

OLE protocol, that can be linked to or embedded in a Microsoft Access table.

SIZE: Up to 1 GB (limited by disk space)

EXAMPLES: MS Word documents, MS Excel spreadsheets,

pictures, sounds

rvg

HYPERLINKUSE:Field that will store hyperlinks. A

hyperlink can be a UNC path or a URL.

SIZE:Up to 64,000 characters

EXAMPLES:www.geocities.com/valdomero214.html