Creating a database in MS Access
There are three ways to create a table: entering data straight in Datasheet view (Access guesses the types itself), writing the structure by hand through Create – Table Design, and using a ready template. In Design View each row gets a Field Name, a Data Type and (optionally) a Description. The primary key is marked with Table Tools – Design – Primary Key. In the field properties below you set the size (Field Size), Default Value, Required and Validation Rule, for example >=0 And <=100 for a score; if the rule is broken, the Validation Text message appears. The table is saved with Ctrl+S under a name, then records are entered in Datasheet View; the structure can be changed later in Design View again. After creating a database, keep it in a reliable place and make a copy.
| Field property | Job |
|---|---|
| Field Size | size |
| Default Value | starting value |
| Required | required |
| Validation Rule | validation rule (for example >=0 And <=100) |
| Validation Text | message when the rule is broken |
On a sports-day entry form the number of laps must be from 1 to 10. If someone types 100 by accident, the system refuses it and shows a message. A rule like this stops a wrong entry before it reaches the database.
App developers, designers of bank forms and data-entry supervisors block wrong values with rules.
On a paper form write a rule for one box, for example “books to borrow: a whole number from 1 to 3”, and ask a friend to try to break it.
| Field | Type | Property |
|---|---|---|
| BookID | AutoNumber | key |
| Title | Short Text | Required |
| Author | Short Text | |
| Year | Number | Validation Rule >=1900 And <=2100 |
| Price | Currency | |
| Available | Yes/No | Default Value Yes |
| Score field | Value |
|---|---|
| Field Size | Byte |
| Validation Rule | Between 0 And 100 |
| 85 | accepted |
| 120 | rejected |
“The rule breaker”: one pair sets a validation rule on a table, and the other pair enters a value that breaks it, reads Access’s message and explains the rule.