MS Access is a all in one database solution provided as part of MS Office. Access was much more popular years ago when more powerful solutions such as Oracle an SQL Server couldn’t be effectively run on desktops. While Access’s time in the sun is definitely in decline, for someone new to concepts of databases, Access can be a great introduction.

Let’s start by creating a simple database. Open up Access and click on Blank Database

Give your blank database a name in the pop up window

By default, you’re new database will start with a Table1 with an ID column. Click the drop-down on the next column space to add a new column. For this example, let us select Short Text as our data type. Short text can handle any text up to 255 characters

After you set the datatype, you can click on the column name and rename it

Let’s add another column, set the datatype to number

Now click the X next to table1 and you will be prompted to name your table

Another way to build a table is through the table design feature

You can name your columns and select data types: Note AutoNumber is an auto incrementing datatype that works to provide you with an auto populating ID number

Access requires primary keys for all tables (something not required, but recommended in other systems like MySQL or SQL Server). To create a Primary Key, right click on ClassID and select Primary Key

Finally, let’s just use SQL to create a table. Select Create an then Query Design from the ribbon menu

Select SQL View

Put in the SQL below

create table teacher (
teacherID int primary key,
teacher_name varchar(255))

Click on the new teacher table in the left and you can fill in some data

Now let’s create a relationship between the Class and Teacher tables. Click on Database Tools > Relationships

Drag the Class and Teacher table into the blank sheet and click Edit Relationships

Click create new, select your tables and columns from the drop down, and select Enforce Referential Integrity and select the Create button

A Relationship line will appear, showing you your connection

Back to Main Course Page: Course