Introduction to Microsoft Access and VBA

Introduction to Microsoft Access

Overview

A database is a list of values stored somewhere. A computer database is a list or a group of lists stored in a computer disc such as a hard drive. Microsoft Access is an application used to create and manage computer databases.

Practical Learning: Starting Microsoft Access

• Start Microsoft Access

Starting a Project

Microsoft Access is is equipped with all the tools you will need to start a project, to end it, and to distribute it. As is the case for every application, to use Microsoft Access, you must launch it, which is done from the menu on the Start button of Microsoft Windows. When it starts, Microsoft Access displays two sections:

The left side displays a list of previously used files. The right side displays a list of example databases. To start with a project from scratch, you can click Blank Desktop Database. This would bring up a dialog box:

In the dialog box, specify the desired name of the file and click Create.

Practical Learning: Creating a Database

1. In the middle section, click Blank Desktop Database
2. In the right section of the screen, set the File Name to Exercise1
3. Click Create

The Microsoft Access Interface

The Quick Access Toolbar

The left side of the title bar displays the Quick Access Toolbar . The Quick Access toolbar containss other useful buttons.

The Work Area

To assist you with necessary operations, Microsoft Access is equipped with the Ribbon. By clicking File, you can access menu items to save or open one:

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

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 Structured Query Language (SQL)

Introduction

The primary language to perform the operations on a database is called the structured query language, or 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 these lessons, we will regularly write, "The SQL" instead of "The SQL language", as the L already represents Language.

As it is common with other computer languages, the SQL has with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it. Microsoft Access supports many ways of using, interpreting, or applying the SQL.

A SQL Statement

Code based on SQL is referred to as a SQL statement. In Microsoft Access, to open a window you can use to create or use a SQL statement, on the Ribbon, click Create. In the Queries section, click Query Design. This would display the Show Table dialog box. Click Close.

To open the window that allows you to write code:

• Right-click the middle of the Query window and click SQL View
• On the Ribbon, in the Results section of the Design tab of the Ribbon, click the SQL button
• On the Ribbon, in the Results section, click the arrow of the View button and click SQL View
• On the status bar of the Query window, click the SQL View button

This would display a window with a default line of code. This is referred to as the Query windowé In it, you can either edit its existing code or delete the default code and replace it with your own code.

When writing a SQL statement, the SQL is not case-sensitive. This means that the words Case, case, and CASE represent the same thing. This applies to keywords of the SQL or words that you will add in your statements.

After writing your SQL statement, you can execute it to see the result. To do this:

• In the Results section of the Ribbon, click the Run button
• In the Results section of the Ribbon, click the Run button
• In the status bar of Microsoft Access, click the Datasheet View button

After viewing the result, to return to the Query window:

• Right-click the title bar of the window and click SQL Code
• In the Results section of the Home tab of the Ribbon, click the arrow of the View button and click SQL Code

SELECT Something

The most fundamental operator used in the SQL is called SELECT. This operator is primarily used to display a value to the user. In this simple case, it uses the following formula:

SELECT Value;

The value on the right side of SELECT must be appropriate. It can be a number. Herei is an example:

SELECT 48;

When you write a SQL statement, the semicolon is optional. This means that you can omit it.

SELECT This AS

When you execute a SQL statement in the Query window, its results are displayed in a spreadsheet. To be able to recognize a value, the top section of a column of the spreadsheet displays a label, called a caption. For example, the above would produce:

If you create a simple SELECT Value; Microsoft Access assigns a default caption to the column. The SQL allows you to specify a caption that would be used for the value. This is done using the following basic formula:

SELECT Value As Caption;

The words SELECT and AS are required. As mentioned already, SELECT would be used to specify a value and AS in this case allows you to specify a caption of your choice.

The caption can be made of a word but the word cannot be one of the SQL's keywords. Here is an example:

SELECT 48 AS Age;

This would produce:

You can also use non-literal characters or digits in the caption. If the caption is made of a combination of words, you can concatenate them to create one word. Here is an example:

SELECT 24.85 AS HourlySalary;

This would produce:

If you want the caption to display different words, you can include them between an opening square bracket "[" and a closing bracket "]". Here is an example:

SELECT 25.05 AS [Hourly Salary];

This would produce:

Instead of displaying one column, you may want to display more. To do this, separate them with commas. Here is an example:

SELECT 42.50 AS [Weekly Hours], 25.05 AS [Hourly Salary];

This would produce:

Besides a number, the value of a SELECT expression can be a string. If it is, you can include it between single-quotes. Here is an example:

SELECT 'Martens, Laurent' AS [Employee Name];

In the same way, you can mix number-based and string-based columns.

SQL as Data Definition Language

The structured query language comes in two broad parts. The data definition language (DDL) is the SQL part used to create the objects of a database.

Introduction to Forms and Reports

Forms Fundamentals

A form is a rectangular object that hosts other objects and allows a database user, also called a user, to interact with the application. There are various ways you can create a form. If you want to immediately use the form, on the Ribbon, click Create. In the Forms section, click Form View.

If you want to prepare the form to receive other objects, this is referred to as design. To open a form for design, on the Ribbon, click Create. In the Forms section, click Form Design. When a form is in design, you can add objects to it.

Practical Learning: Creating a Form

1. On the Ribbon, click Create
2. In the Forms section of the Ribbon, click Blank Form

Opening a Form to View

The Form View is the view used for regular operations on a form. To open a form in Form View:

• In the Nativation Pane:
  • Double-click the name of the form
  • Right-click the form and click Open
• If the form is opened in another type of view:
  • In the Nativation Pane, double-click the name of the form
  • On the Ribbon, click Home, Design, or Datasheet (this depends on the type of view). In the Views section (on the left of the Ribbon), click the arrow button under View, and click Form View
  • On the status bar, click the Form View button

Opening a Form for Design

The design of a form is done by displaying the object in Design View. To present a form in Design View:

• If the form is currently closed, in the Navigation Pane, right-click the form and click Design View
• If the form is currently opened:
  • In the Navigation Pane, right-click the form and click Design View
  • Right-click its title bar or tab and click Design View
  • Right-click it (somewhere on its body) and click Design View
  • On the Ribbon, click Home. In the Views section, click the arrow button under View and click Design View
  • On the right side of the status bar, click the Design View button

The Form View

If the form is currently displaying in Design View and you want to switch it to Form View:

• In the Navigation Pane:
  • Double-click the name of the same form
  • Right-click the name of the same form and click Open
• On the Ribbon, click either Design or Home, in the Views section:
  • Click the View button
  • Click the down-pointing button under View and click Form View

Practical Learning: Displaying a Form for Design

• Right-click the tab of the form and click Design View

Reports Fundamentals

A report is a rectangular object that allows a user to print some values, that is, to sent a designed object to the printing device. Like a form, a report hosts the objects that present the values to be printed. There are various ways to create a report. To start a simple report, on the Ribbon, click Create. In the Reports section, click Report View.

To start a report that will host some objects, which referred to as design, on the Ribbon, click Create. In the Reports section, click Report Design. If the report is currently closed or it displays in another view, you can switch the view using the same techniques applied to a form.

Practical Learning: Creating a Report

1. On the Ribbon, click Create
2. In the Forms section of the Ribbon, click Report Design

The Sections of a Form or Report

The Detail Section

The Detail section is the primary host of the controls on a form or report. The section is primarily characterized by its height. To visually change the height of the Detail section, click and drag the lower border of the Detail section:

The Form Header and the Form Footer

A form can be equipped with a Form Header and a Form Footer sections. To add these sections, right-click the middle of the form and click Form Header/Footer:

A report can be equipped with a Report Header and a Report Footer sections. To add these sections, right-click the body of the report and click Report Header/Footer:

You can heighten or reduce, sometimes completely, a section to your liking. This allows you to keep one section and hide the other.

Practical Learning: Creating the Sections of a Form

1. Click the Form1 tab
2. While the form is displaying in Design View, right-click the body of the form and click the Form Header/Footer

The Size of a Form or Report

The Width of a Form or Report

The width of a form or that of a report is the distance from its left border to its right border. All sections use the same width. To widen or narrow a form or report, click and drag the right border:

The Height of a Form or Report

The height of a form or report is controlled by its sections.

The Width and Height of a Form or Report

To change both the width and the height of a form or report at the same time, position the mouse on the lower-right corner:

Then click and drag left, right, up, down, or diagonally.

Automatically Sizing the Form to Fit Its Content

If you want to resize a form enough to show its whole content, on the Ribbon, click Home. In the Window section, click Size To Fit Form.

Controls Fundamentals

Introduction

A Windows control, or simply called a control, is an object positioned in the body of a form to allow a database user to interract with the database.

Microsoft Access supports various types of controls.

Adding a Control to a Form or Report

To manually add a control to a form or report, the form or report must be opened in Design View. When that happens, the Ribbon displays various objects in its Controls section. To add a control to a form or report, click it in the Controls section of the Ribbon, and click the desired section of the form or report. You can then move and position the control as you see fit.

Practical Learning: Adding a Control to a Form

1. In the Controls section of the Ribbon, click the Text Box
2. Click the wide area on the formu

Microsoft Visual Basic Fundamentals

Introduction

You can create a completely functional database using only Microsoft Access. In some cases, to get a more complex database, you have to write code. To support this, Microsoft Access ships with, and installs, a programming environment named Microsoft Visual Basic. This is a (smaller) variant of the popular Microsoft Visual Basic language and environment but has everything necessary to create and manage a complex database.

Practical Learning: Starting Microsoft Access

1. Start Microsoft Access
2. In the list of files, click Exercise1 from the previous lesson (if you don't see it, find and open it)

Opening Microsoft Visual Basic

In order to access Microsoft Visual Basic, you must first create or open a database in Microsoft Access. Then, you can open Microsoft Visual Basic from Microsot Access. You have various options. To launch Microsoft Visual Basic:

• On the Ribbon:
  • Click the Create tab. In the Macro & Code section, click the Visual Basic button. In some cases, in the same Macro & Code section, you can also click either the Module or the Class Module button
  • Click the Database Tools tab. In the Macro section, click the Visual Basic button
• Display the form in the Design View. Depending on what you are trying to do, right-click either the form itself or the control on it and click Build Event... In the Choose Builder dialog box, either double-click Code Builder or click Code Builder and click OK

Any of these actions would open Microsoft Visual Basic:

Practical Learning: Opening Microsoft Visual Basic

1. On the Ribbon, click Database Tools
2. Click the Visual Basic button

Primary Accessories for Programming and Code Writing

Introduction

When using a database, you are in fact using two applications to create a final product. Microsoft Access is used to design the necessary objects for your product. This means that Microsoft Access is used for its visual display of objects. On the other hand, Microsoft Visual Basic is used to handle code that enhances the functionality of your application.

The Compiler

The code you will write is made of small instructions written in plain English, in a version referred to as the Visual Basic language. The instructions you write must be translated in a language the computer can understand. This is done by a program called a compiler. That program works behind the scenes so you will almost never be concerned with it (but you should know that it exists.

Comments

A comment is a piece of text in a code section that the database engine would not consider when reading your code. As such, a comment can be written any way you want.

In Visual Basic, the line that contains a comment can start with a single quote. Here is an example:

Private Sub Form_Load()
    ' This line will not be considered as part of the code
End Sub

Alternatively, you can start a comment with the Rem keyword. Anything on the right side of rem, Rem, or REM would not be read. Here is an example:

Private Sub Form_Load()
    ' This line will not be considered as part of the code
    Rem I can write anything I want on this line
End Sub

Comments are very useful and it is strongly suggested that you use them regularly. They can never hurt your code and they don't increase the size of your database. Comments can help you and other people who read your code to figure out what a particular section of code is used for, which can be helpful when you re-visit your code after months or years of not seeing it.

Fundamentals of Modules

Introduction

A module is a file that holds programming code or pieces of code in a Visual Basic application. There are three types of modules you will use.

A Module from a Form or Report

From Microsoft Access, if you use a form or a report to open its code, Microsoft Visual Basic automatically creates a new module for the form or report and displays it:

Practical Learning: Creating a Form-Based Module

1. To return to Microsoft Access, on the main menu of Microsoft Visual Basic, click File and click Close and Return to Microsoft Access
2. On the Ribbon, click Create
3. In the Forms section, click Form Design:

   

4. Right-click the tab of the form (Form1) and click Save
5. Set the name as Central
6. Click OK
7. To start a form module, with the form opened in Design View, on the Ribbon, in the Tools section of the Design tab, click the View Code button
8. To return to Microsoft Access, on the Standard toolbar, click the View Microsoft Access button

A Module from Scratch

As another category, you can create a module that is independent of any form or report. You can then write any code you want in that file. To create a module:

• In Microsoft Access, on the Ribbon, click Create. In the Macro & Code section, click Module
• In Microsoft Visual Basic:
  • On the main menu, click Insert -> Module
  • On the Standard toolbar, click the arrow of the Insert Module button and click Module

The names of modules are cumulative. This means that the first module would be named Module1; the second would be Module2, etc. It is a good idea to have names that are explicit especially if your application ends up with various modules. To give a custom name to a module, you must save it. This would prompt you to name the module. You can accept the suggested name or type your own and press Enter.

Practical Learning: Creating an Independent Module

1. In Microsoft Access and on the Ribbon, click Create
2. To start a module, on the Macro & Code section, click the Module button

Introduction to the Events of a Control

An event is an action that occurs on a control. Examples of actions include clicking a button, or typing text, or moving the mouse. Except for the label (or for static controls), every control supports some events. When an action occurs, the control is said to fire the event.

A default event is the event the control is more likely to fire. For example, a button is more likely to be clicked; so the default event of a button is the click event.

There are various ways you can launch an event for a control. As one option, in Microsoft Access, right-click the control on a form or report and click Build Event... In the Event Builder dialog box, click Code Builder and click OK. If you are already working in Microsoft Visual Basic, in the Object combo box, select the control. In the Procedure combo box, select the control.

Practical Learning: Introducing Controls Events

1. Right-click an unoccupied area of the form and click Build Event...
2. In the Choose Builder dialog box, click Code Builder
3. Click OK
4. In the Object combo box of Microsoft Visual Basic, select Form
5. Close Microsoft Visual Basic
6. Close Microsoft Access
7. When asked whether you want to save, click No

Microsoft Visual Basic Child Windows

Introduction

The Microsoft Visual Basic application is equipped with various windows you can use to create and manage the necessary objects. The windows are dockable. This means that they can be moved on the screen to other locations.

The Project Window

The Project window displays the coding segments for the objects of your project. If the Project Explorer is not displaying and you need it, on the main menu, click View -> Project Explorer. The Project window is usually positioned on the left of the window. To move it, click its title bar under the Standard toolbar, hold your mouse down and drag it to the desired location. To position it back to its previous location, double-click its title bar. To expand or collapse the folders tree, click the Toggle Folders button.

Introduction to the Properties Window

In the Microsoft Visual Basic Environment, the Properties window is used to specify or change some characteristics:

The Code Editor

The Code Editor is the area where you will write code. It is the largest section of the Microsoft Visual Basic programming environment. It is mainly made of three sections:

On top, there are two combo boxes. To know the name of a combo box, you can position the mouse on it and a tool tip would come up:

The Object combo box allows you to select a particular object and access its actions. The Procedure combo box allows you to select an action, related to the object in the Object combo box.

The Code Editor is equipped with a vertical and a horizontal scroll bars.

There are two small buttons on the left side of the horizontal scroll bar. The Full Module View button is used to display the code with an object. The Procedure View button will display the procedures associated with the database.

The Options Dialog Box

The Code Editor uses default colors to show the code. To customize these colors, you can use the Editor Format property page of the Options dialog box. To access it, on the main menu, click Tools and click Options...:

The Immediate Window

The Immediate window is used to test code when necessary. To display it, on the main menu of Microsoft Visual Basic, lick View -> Immediate Window

Practical Learning: Using Microsoft Visual Basic Windows

1. To display the immediate window, on the main menu of Microsoft Visual Basic, click View and click Immediate Window
2. To use it, in the Immediate window, type ?now and press Enter

   

3. To return to Microsoft Access, on the Standard toolbar, click View Microsoft Access

Microsoft Access Databases

Introduction

There are various types of databases you can use in Microsoft Access. You can create a database from scratch. You can use some objects that ship with Microsoft Access to create a database. You can open either a database you previously created or one made by someone else.

Visually Creating a Microsoft Access Database

There are various ways you can create a database. To visually start a database from scratch, after launching Microsoft Access, click Blank Desktop Database. In the right section, accept or change the name of the database. If you want to create a contemporary database, either omit or add the .accdb extension. If you want to create a database that is compatible with earlier versions of Microsoft Access, you must add the extension .mdb.

After specifying the name, to specify a folder of your choice, under File Name and on the right side of the name of the database, click the Browse button . This would open the File New Database dialog box. You can click the arrow of the Save In combo box to select a drive such as (A:), (C:), etc. After selecting the drive, you can either select an existing folder or create a new folder by clicking the Create New Folder button on the right side of the Save In combo box. You can also use a directory on the network as the repository of the new database.

Creating a Database Using a Template

Microsoft Access ships with a few sample databases you can use and customize. To create a database from a template, after launching Microsoft Access, in the middle section, locate and click the desired sample.

Deleting a Database

If you have a database you don't need anymore, you can delete it. To delete a database, in My Documents, in Windows Explorer or another file management application:

• C can click the database to select it and press Delete
• Right-click the database and click Delete

A warning message would be presented to you to confirm what you want to do.

Closing a Database

You can close a database without closing Microsoft Access. To do this, on the Ribbon, click File and click Close.

Closing Microsoft Access and Visual Basic

The version of Microsoft Visual Basic used in Microsoft Access is "For Applications". It is related to Microsoft Access. When you are in the Code Editor of Microsoft Visual Basic, you can get back to Microsoft Access either from the View Microsoft Access button on the Standard toolbar or by clicking the Microsoft Access button on the Taskbar. The shortcut to get back to Microsoft Access is Alt + F11.

You can close Microsoft Visual Basic any time and keep Microsoft Access running. To do this, on the Standard toolbar of Microsoft Visual Basic, click the View Microsoft Access button to get back to the database. On the other hand, if you close Microsoft Access, Microsoft Visual Basic will be closed also.

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 the Office Button 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: Ending the Lesson

• To close Microsoft Access, click File and click Close