creating the database

new db First task is to create the database file and store it in the appropriate folder.

In Access the entire database is contained in a single file. That is different from most other DBMS where the objects making-up the db are stored in different files within a common folder.

The file in Access 2007 format will have the extension .accdb (in Access 2003 and before it was .mdb).

Note that in Access you cannot edit the individual objects of the database; for example you can't copy/paste a table object from one database to another.

However, that does make it easy to copy the entire database from one place to another. It is treated the same as any other file.

You can backup your db by simply copying the file to an external hard drive or USB device.

By the way, the Create DB button with the location specifier, is in the lower right-hand corner of the New File screen.

Now, all that you have is an empty shell to contain the objects of the database. Those objects (tables, forms, reports, etc) will be created next to implement the model that was defined in the design stage.

creating tables

As you can see from the design diagram, you'll need 6 tables to store the data for the Sales system.

We'll create the first one together and then you can download the other 5.

Once you've created the database file you'll automatically be in the Create table screen.

You could start defining the fields right here but I'll suggest that you do it from Design mode because that gives you more control over the content.

To get to Design mode, just click on the View button on the left and select Design View.

We covered all the details of table creation in a previous lesson. It was for Access 2003 but it hasn't really changed since then. You can take a look at that tutorial before finalizing this table:
Create table tutorial

When you ask for Design View you'll be asked to name the table. Call it Products.

Now, in Design View create the fields as shown in the diagram. Save the table frequently as you work.

importing data

For the other tables, you can get them from here: ProfsrFinancial database

After you've extracted the database file you will want to move the tables to your own database. As mentioned before, you can't copy/paste tables between databases. You have to use the Import function in External Data.

The tables contain test data and all relationships that are needed to develop the other objects in the app.

If you're not clear on relationships, go back to the tutorial mentioned previously.

Next we'll look at Queries.