1.2 Microsoft Access

MS Access is a relational database system for workstations that run the Microsoft Windows operating system. MS Access is typically used by individuals for data they use personally, but in some situations a single MS Access database may be used by a group of people or small department.

MS Access databases are stored in a single file that has a file suffix of “.accdb” or “.mdb”. Databases created using MS Access 2007 and later have a file suffix “.accdb”, and databases created using MS Access 2003 or earlier have a file suffix “.mdb”. We will be using databases where the files have names ending in “.accdb”. You need to use MS Access 2007 or later to open these databases. We have used Access available in Microsoft 365.

Our first sample database is in a file named Library.accdb; this database is available from the website associated with this text.

To use this database, you must first download the file containing the database, and then either:

• • • Navigate to its location in File Explorer, and open the database by double-clicking the file name.

• • • Start Access and then browse to the folder holding the database, select it, and open it.

Select Open in Access:

Then browse to the location of the database file and open the file:

When you open this database, you see a list of objects (figure 1.6) in the database; you will see three tables: Book, Loan, Member:

Double-click a table name and MS Access opens the table in Datasheet View; you can see the contents of Book in figure 1.7. The datasheet view for a table is easily obtained, but it is not a particularly user-friendly way to view and manage data in a table. We will learn other ways of handling data with MS Access Forms. The Book table has three fields (i.e., attributes): callNo, title, author. When we view a table we see data organized into rows and columns. The data in one row corresponds to one book; if there are 11 books, then we have a table of 11 rows.

The Book table contains one row for each book in the library. We can verbalize the content of a row as:

• • • The book identified by call number … is titled … and is authored by …

Substituting actual values from rows we can make explicit statements such as:

• • • The book identified by call number PC 14 V48 1965 is titled Medieval miscellany and is authored by Frederick Whitehead
    • The book identified by call number QA 76.76 A65P76 2011 is titled Programming Android and is authored by Zigurd R Mednieks

Knowing that books are identified by their call number and since the above statements use the conjunction ‘and’, the above verbalization can be expressed in an elementary form as:

• • • The book identified by call number … is titled …
    • The book identified by call number … is authored by …

Each of these expressions is considered elementary because each states one fact about a specific book. We cannot make these statements any simpler.

Of course, we can now substitute values from the table and obtain:

• • • The book identified by call number PC 14 V48 1965 is titled Medieval miscellany
    • The book identified by call number PC 14 V48 1965 is authored by Frederick Whitehead
    • The book identified by call number QA 76.76 A65P76 2011 is titled Programming Android
    • The book identified by call number QA 76.76 A65P76 2011 is authored by Zigurd R Mednieks

At this point, expressing verbalizations this way may seem trivial and unnecessary, but they do serve a purpose – they make it clear that the title and the author’s name serve only to describe a book, and that the call number identifies the book. An aim of a database designer is to understand data requirements in terms of these elementary forms.

We will have more to say about this in a later chapter.

Up to this point we have seen how to:

• • • open an MS Access database;
    • recognize the database comprises a number of tables;
    • open a table to see it displayed as a collection of rows and columns;
    • verbalize the information in a table.

Next, we will examine the basic features of MS Access that allow us to change, insert, and delete data.