Introduction to Microsoft Access

A Database

Introduction

Microsoft Access is a computer application used to create and manage computer-based databases on desktop computers and/or on connected computers (a network). Like any other computer application, in order to use Microsoft Access, you must first acquire it then install it. Once it is installed, you can start it like any other application. Normally, to start it, you would click Start -> All Programs (or All Apps) -> Access 2016

Practical Learning: Starting Microsoft Access

• Start Microsoft Access

The Database Engine

Microsoft Access is a complex applicaiton with the ability to perform various types of operations (calculations, logic, graphic, communication, etc). To coordinate all these operations, it includes a group of internal programs that we will refer to as the database engine.

Creating a Database

Before creating a database, you should plan and design it. When you start Microsoft Access, it displays two sections:

There are various types of databases you can create. For example, you can use one of the sample databases that ship with Microsoft Access. The list of samples displays on the right side of the startup screen.

The fundamental or basic database is create using the Blank Desktop Database sample. To use it, click its button. This would bring up a dialog box:

A database is first of all a Windows file. When you decide to create a database, Microsoft Access always suggests a name for the database. Otherwise, specify a name of your choice.

A Microsoft Access database is primarily a Windows file. It must have a location, also called a path, which indicates how the file can be retrieved and made available. Microsoft Access suggests that the database be created in the Documents folder. If you want it located in another folder, you can click the Browse button . This would open the File New Database dialog box where you can select an existing folder or create a new one using the New Folder button. Once you have specified the name of the database and its location, you can click Create.

Practical Learning: Creating a Database From a Template

1. In the middle section, click Customer Service
2. In the right section of the screen, set the File Name to Rockville Technologies
3. Click Create.
   If necessary, click Enable Content

The Type of Database

When you create a database, if you specify only a name (and path), Microsoft Access would automatically add the .accdb extension. If you want to create a database that is compatible with previous versions, use the extension .mdb but you must explicitly add that extension.

Touch Screen Databases

A touch screen application is one that a user uses by touching some objects on the monitor. To create a touch screen database, you must first have a touch screen monitor. When creating a touch screen application, there are a few things you should take care of.

The Microsoft Access Interface

The Quick Access Toolbar

Microsoft Access is is equipped with all the tools you will need to start a project, to end it, and to distribute it. The left side of the title bar displays the Quick Access Toolbar .

By default the Quick Access toolbar is equipped with three buttons: Save, Undo, and Redo. On the right side of the Quick Access toolbar, there is the Customize button with a down-pointing arrow. This button is disabled if no database is currently opened. When a database is opened, if you click this button, a menu would appear:

The role of this button is to help you decide what buttons to display on the Quick Access toolbar. To add a button to the Quick Access toolbar, click the Customize button and click the desired button from the menu. If the available buttons are not enough or if you don't see a button you want:

• You can click the More Commands button
• You can right-click the Quick Access toolbar and click Customize Quick Access Toolbar

Any of these actions would open the Access Options dialog box:

 To add a command, click it in the middle list and click Add >>. Once you have selected the desired options, click OK.

The Work Area

Under the title bar, Microsoft Access displays an object named the Ribbon. Under File appears a menu with items such as Save and Open:

What the right section displays depends on what you click on the left side. For example, if you click File, the right side displays a list of available sample databases. The top right section displays a list of categories of sample databases:

If you click a category, a list of its related sample databases would display

To return to the previous window, you can click the Back button .

Database Management

Introduction

When you start Microsoft Access, it displays the number of recently used files on the left side:

The number of files can be decreased as low as 1 or increased as high as 50. To change this number, in the File section, click Options. In the left frame, click Client Settings. In the right frame and in the Display section, change the value of the Show This Number Of Recent Documents:

Database Properties

A database holds some internal pieces of information referred to as properties. To access the properties of a database, click File and click Info. Then click View and Edit Database Properties

Practical Learning: Using Microsoft Access

1. Click File
2. Click View And Edit Database Properties
3. From the Properties dialog, click the General tab. Examine the various sections. Notice the size of the current database
4. Click the Summary tab
5. Make sure the Title is set to Rockville Technologies (otherwise, set it).
   Click the Subject text box and type Business Regular Activities 
6. Click the Author text box and type Lucien Durand
7. Click the Manager text box and type Christine Lotts
8. Click the Company text box and type Rockville Technologies, Inc.
9. Click the Category text box and type Project Management
10. Click the Keyword text box and type project, budget, management
11. Click the Comments text box and type: This software product is used by the company to manage a project used to build a computer network for a corporate customer. For more information, contact Lucien Durand or Christine Lotts. You can also refer to the web site.
12. Click the Hyperlink Base text box and type http://www.rockvilletechnologies.com

    

13. Click the Statistics, Contents, and Custom property pages and review their contents
    The Statistics tab gives you statistics about your database and its summary access
    The Contents tab shows a list of the components that are part of your database
    The Custom tab shows, and allows you to customize, the field names associated with your database
14. When you have finished working with the database Properties, click OK (if you click Cancel, any change you have made will be discarded)

Closing a Database

You can close a database without closing Microsoft Access. To do this, in the File section, click Close Database.

The Size of a Database

A database is primarily a computer file, just like those created with other applications. As such, it occupies space in the computer memory. To know the size of a database, you can right-click it in Windows Explorer or My Computer and click Properties.

Practical Learning: Checking the Size of a Database File

1. From the resources that accompany these lessons, open the Altair Realtors1 database
2. To check its size, click File, and click View and Edit Database Properties
3. In the Properties dialog box, click the General tab and notice the value on the right side of the Size label
4. Click Cancel to close the Properties dialog box

Compacting and Repairing a Database

As mentioned already, once you have created a database file, it occupies a certain amount of memory space that can grow or shrink without your direct intervention:

When you add an object to the database, the database's file grows as needed. When you remove an object, the memory space it was occupying is left empty. This also applies when you keep removing objects:

The computer is supposed to recuperate the space those previous objects were using. Unfortunately, that is not always the case. Most of the time, that space is left empty but cannot be accessed by the computer (by other applications). This means that the memory space cannot be made available to other applications. To recover this memory space, you can compact the database. When this is done, the file is shrunk to occupy only the necessary amount of space and free the unused sections:

To compact a database, you have many options. To compact and repair the database that is currently opened, and to compact it only once, on the Ribbon, click File. In the middle section, and click Compact & Repair Database.

To compact a database every time you close it, click File and click Options. Then click the Compact on Close check box:

And click OK.

Practical Learning: Compacting a Database

1. The Altair Realtors1 database should still be opened.
   click File then click View and Edit Database Properties
2. Notice the size of the database. Click Cancel
3. Click Compact & Repair Database
4. When the operation has been completed, click View and Edit Database Properties
   Notice that the database size has been reduced. Click Cancel to close the Properties dialog box

Introduction to Database Tools

The Ribbon

Under the title bar, Microsoft Access displays a long bar called the Ribbon (or ribbon), whose contents and sections depend on what is currently going on in Microsoft Access. The Ribbon is a long bar made of various tabs, also called property pages:

If the Ribbon is taking too much space on your screen, you can reduce its size. To do this, under the system buttons, click the Collapse  The Ribbon button .

To access a tab:

• You can click its label or button, such as File, Home, or Create
• You can press Alt or F10. This would display the access key of each tab:

  

  To access a tab, you can press its corresponding letter on the keyboard
• If your mouse has a wheel, you can position the mouse anywhere on the Ribbon, and role the wheel, up or down

We will refer to each section by its title. Some sections of the Ribbon display a button . If you see such a button, you can click it. This would open a dialog box or a window.

If you need to narrow the Microsoft Access interface, drag one of its borders:

In this case, when you need to access an object, you can still click it or click its arrow. If the item is supposed to have many objects, a new window may appear and display those objects:

From this:

To this:

The Structured Query Language

The universal or the most popular language used to perform operations on a database is called the Structured Query Language and abbreviated SQL.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in "A SQL statement" instead of "An SQL statement". Also, in our lessons, we will regularly write, "The SQL" instead of "The SQL language", as the L already represents Language.

Like most other database environments, Microsoft Access supports SQL. Like every computer language, the SQL has its syntax, grammar, and rules. The SQL is equipped with keywords that tell it what to do and how to do it.

In order to use the SQL or to write SQL code in Microsoft Access, on the Ribbon, click Create. In the Queries section, click Query Design:

A dialog box will come box. Simply click Close. This will display a blank window. Right-click the middle of the window and click SQL View. You can then write your SQL Code.

To see the result of SQL Code, you must execute it. To do this:

• On the Ribbon, click the Run button
• On the Ribbon, click the View button
• On the Ribbon, click the arrow of the View button and click Datasheet View
• On the status bar of the window, click the Datasheet View button

Help

Introduction

The left section of Microsoft Access has the Navigation Pane. It shows the objects of a database by categories. It has a Shutter Bar Open/Close Button used to minimize the Navigation Pane:

To expand the Navigation Pane again, click the Shutter Bar Open/Close Button or click the bar itself.

Tables and Objects in the Navigation Pane

The top section of the Navigation Pane displays an All Access Objects caption:

When you create a table (we will study what a table is in the next lessons), a section labeled Tables gets created. Here is an example:

In the same way, as you create more tables, their names appear under Tables in the Navigation Pane:

In other lessons, we will learn what other objects can be created in a database. When you create such objects, the Navigation Pane adds sections for their categories. Here are examples:

Managing a Category

When you create the objects, they are added in the Navigation Pane in the order you add them. If you want to display in reverse alphabetical order, right-click the name of a section, position the mouse on Sort By, and decide:

If you want to minimize a section, which is referred to as collapsing, click its bar. If you want to hide a category, right-click its category header and click Hide. To hide all categories except a particular one, right-click the section header of that category and click the Show Only option (such as Show Only Tables, or Show Only Forms, etc). To select what categories to hide or what categories to reveal, right-click an empty area in the Navigation Pane and click Category Options. Then use the check boxes to decide.

Managing the Categories in the Navigation Pane

If you want to expand all categories at once, right-click any section header and click Expand All. In the same way, if you want to collapse all categories, right-click any section header and click Collapse All.

The Navigation Pane gives you ample flexibility on how the objects appear in it and how the categories are organized. For example, you can show the tables only, the forms only, or all objects. One way you can decide is to right-click an empty area of the Navigation Pane and click Navigation Options. This would open the Navigation Options dialog box:

The Navigation Options dialog box allows you to do many things. For example, to decide what categories to show or hide, put or remove check marks next to their name in the right list. The dialog box also allows you to create new categories.

Microsoft Access Help

Introduction There are various types of assistance you can get in Microsoft Access. One of the ways you can get help is by clicking the Help button on the title bar.

Tool Tips

One of the types of help you can get is through small boxes called tool tips.

Context-Sensitive Help

Context-sensitive help refers to help provided on a specific dialog box on the screen. Context-sensitive help is also referred to as "What's This". To get context-sensitive help, press F1. To use help, click the question mark button .

Practical Learning: Using Context-Sensitive Help

1. In the File section, click Options

   

2. To get context-sensitive help on a dialog box, on the Options dialog box, click the What's This button 
3. Close the Access Help window
4. To close the Access Options dialog box, click Cancel

Help On This Error

Sometime you will get an error on something you are working on. This type of error displays a square box with an exclamation point:

If you position the mouse on it, a down pointing arrow is added to the right side of the button and if you click the button or its down pointing arrow, a menu would appear. One of the options on the menu is labeled Help on This Error:

You can then click Help on This Error to get some information on how to fix the error.

Topical Help

In various sections of our lessons, we will use an object called the Property Sheet, which allows you to change the characteristics of an object (a control):

Some options of the Property Sheet could be difficult to figure out, especially if they are not explicit and if you are not familiar with them. Fortunately, to get help for any item of the Property Sheet, click it and press F1. The Help window would come up and would display one or more options on the topic you had clicked. If you see a link with the same name of the item you clicked, you can click that link and its explanation would display.

Internet Help

Although help on the Internet is provided in web sites, web pages, newsgroups, support groups, etc. A web site is dedicated to Microsoft Access at http://www.microsoft.com/access. You can get help at http://support.microsoft.com.

Microsoft Access Exit

Since Microsoft Access shares the same functionality you are probably familiar with from using other applications, you can close it easily.

• To close Microsoft Access, you can click File and then click Exit Access
• To close Microsoft Access from its title bar, you can click its Close button
• To close Microsoft Access like any regular window of the Microsoft Windows applications, you can press Alt + F4
• To close Microsoft Access using mnemonics, you can press Alt, F, X

Practical Learning: Closing Microsoft Access

• To close Microsoft Access, click the Close button on the top right corner of the window