Lesson 3 - How to create the database
The new "MikeVideo" database
In this lesson we're going to discuss how to build an Access database from scratch.
When you load the Access software, you have to specify whether you are using an existing application or creating a new one. If this is your first session, you want to create a new database.
To your keyboards. START ACCESS!The Properties at the bottom will usually be acceptable for the data type you selected.
And, because you want to learn something from the ground up, you won't use the wizards, pages and projects which are the database-creation templates.
Once you've mastered the concept, then you can use the shortcuts and let Access guide you through the process.
And again, to make it interesting, just choose General Database.
Choose a name for the database and store it in a folder.
In Access, a database, no matter how many tables or forms or reports it contains, is stored in a single file. That file has the extension: .mdb (for Microsoft database). When you want to move or copy or delete the database, all you have to do is work with the one file.
So far, you have an empty database.
The first object you create to define the database is the table.
A table is the description of an entity. It is used to store the data. When you create the table you first describe its structure.
A table in Access is sort of like a spreadsheet in Excel: it consists of rows and columns. Only, it is more organized than a spreadsheet and that organization will later allow you to retrieve the data in all kinds of ways.
The design view of the table is meant to define the structure.
You name all the columns (in technical jargon they would be called attributes of the entity). Remember to use the prefix for the table, in this case c_ because it's the Customers table.
Then, choose a Data Type for each column.
The Data Type defines what kind of data will be contained: text, numbers, URL, dates, etc. This lets the system know how much memory to reserve and also, what you are allowed to do to the data. For example, if you specify that a column is a "Number" type, you won't be allowed to put "XYZ" in it.
To see the data types available, click on the Data Type column and do F1 (the Help function). Here's what you should get:
And here are the basic rules on how to use them:
But, to simplify things later there is one property you should indicate: Caption.
The Caption is the name that will show for the column on forms and reports and so on. If there is no caption, the Field Name is used. So, if you want forms to show "Customer ID" instead of c_custID, define the caption.
Now, complete the Table, as defined in the model from Lesson 2, adding all the fields required.
Before you save the Table, make sure the Primary key is identified. To do that, click on the grey button to the left of c_CustID and that should select the whole row. Then click on the key in the toolbar. That should put a little key symbol on the grey button, identifying that field as the Primary key.
Save the table and, if this is the first Save, it will ask you to name it (don't keep Table1 as a name!).
Finally, repeat the whole process with the Movies table and you will be well on your way.