Microsoft Access

Microsoft Access Tutorial: MS Access with Example [Easy Notes]

What exactly is this Microsoft Access thing?
Database Management System (DBMS) offered by Microsoft, and its name is Microsoft Access. It is included in the Microsoft Office application suite and makes use of the Microsoft Jet Database Engine. In addition, you can get it by itself.

Microsoft Access provides the features of a database in addition to the programming capabilities necessary to develop screens that are simple to navigate (forms). It assists you in efficiently managing large amounts of data and analysing large amounts of information.

Important Terms and Basic Objects

Now, in this tutorial for Microsoft Access, we will learn about some fundamental concepts and objects in MS Access, including the following:

Database File:

It is a piece of data that stores the database in its entirety. Your hard drive or one of your other storage devices will be used to save the database file.

Datatypes:

The datatypes of each field are its individual properties. Each field is associated with a particular datatype, such as text, numbers, dates, etc.

table

A table is an object that stores data in a row-and-column format to store data. Tables are commonly used in databases.

In a database file, one table will typically have relationships with multiple other tables.

Each column must have Unique name

In a table, we can also define the Primary Key column.

Query

Queries select, sort, and filter data based on the search criteria provided in order to provide an answer to a question.

Your queries will display a subset of the available data based on the criteria and limitations you specify.

Queries are able to retrieve data from one or more associated Tables as well as from other Queries.

SELECT, INSERT, UPDATE, and DELETE are all possible types of queries.

Form

You can build a user interface for a database application by employing a database object known as a form. A form can be used to create a form.

Displaying live data from the table is made possible with the help of forms. The primary purpose of it was to simplify the process of entering or editing data.

Report

A report is an object that can be found in desktop databases. Reports are primarily utilised for the purposes of formatting, computing, printing, and summarising selected data.

You are even able to personalise the look and feel of the report.

Macros

Macros are like miniature constructs used in computer programming. They make it possible for you to programme commands and procedures into your forms, such as conducting a search, navigating to a different record, or executing a formula.

Modules:

Modules are essentially pre-written procedures (functions) that can be created with Visual Basic for Applications (VBA).

The Value of Microsoft Access

Now, in this tutorial for MS Access, we will learn the advantages of using the MS Access application, including the following:

Access provides a relational database management system that is fully functional in a matter of minutes.

Access makes it simple to import data from a variety of different sources.

Access is very adaptable and can be easily customised to meet the requirements of both individuals and businesses.

The online version of Microsoft Access is compatible with a wide variety of programming languages that are supported by the Windows operating system.

It is dependable and adaptable, and it can handle any difficult database tasks in an office or an industrial setting.

MS-Access gives you the ability to view, edit, query, and report on data that is stored in its original location, all while allowing you to link to that data.

Provides the ability to create tables, queries, forms, and reports, as well as connect with the assistance of macros.

You can extend Access’s functionality by utilising a straightforward programming construct known as a macro, which is called a macro in Access.

Access Online from Microsoft is capable of performing heterogeneous joins between a variety of data sets that are stored on a variety of platforms.

MS Access’s Drawbacks and Limitations

The disadvantages of using MS Access are listed here.

The database application Microsoft Access is helpful for use in the small to medium business sector. On the other hand, it is of no use to businesses of a significant size.

Compared to other database management systems such as Oracle or Microsoft SQL Server, it is not as robust.

Your information from the database is consolidated into a single file for your convenience. This may cause reports, queries, and forms to run more slowly.

The technical limit is 255 users who are logged in at the same time. In contrast, the limit in real life is only between 10 and 80. (depending on the type of application which you are using)

When compared to other Microsoft programmes, it has a significantly higher learning and training threshold.

Tutorial on How to Launch Microsoft Access

Note: We are making the assumption that you have the most recent version of Microsoft Access, which is included in the Microsoft Office 365 package.

There are two different ways to launch Microsoft Access.

Click the “Start” button within Windows.

Right-click anywhere on the desktop, then select the “New” option.

Now, let’s take a look at starting MS Access using both of these methods as we continue with this tutorial on Microsoft Access:

Option 1) Launch Windows by clicking the Start button.

1) Start by clicking on the icon that says “Windows.”

You will find a list of the programmes that have been installed.

Step 2: Locate the icon for Access.

Make sure that you click on the Access Icon.

A Guide to Using Microsoft Access

Check the window in the third step.

There will be a window for the MS Access Application.

A Guide to Using Microsoft Access

Steps 4) Press ‘Esc’.

The end result is that the Windows application for MS Access will launch.

A Guide to Using Microsoft Access

Option 2) Select ‘New’ from the drop-down menu on the desktop.

1) From the desktop, right-click, and select the ‘New’ option.

Step 2: Select the “Microsoft Access Database Option” from the drop-down menu.

A Guide to Using Microsoft Access

Step 3) A window for the MS Access application will appear down below.

A Guide to Using Microsoft Access

Step 4) Press ‘Esc’

As a consequence, the Microsoft Access windows application will launch.

A Guide to Using Microsoft Access

The Process of Developing a Database

Before we begin the process of creating a database, let’s take a moment to get a better understanding of what a database actually is, paying particular attention to MS Access.

Let’s get started by looking at some examples of real-world Microsoft Access databases:

There is a Bookcase in which the Books are stored.

We have iPods loaded with an extensive music collection, and there are countless different cases available for them.

In a similar vein, we have MS Access Database, which functions as a kind of storage facility for all of your interconnected Tables, Queries, Forms, and Reports in MS Access.

Databases, from a technical standpoint, store the data in a well-organized fashion so that it can be accessed and retrieved with ease.

A Guide to Using Microsoft Access

In SQL Access, creating a database can be done in one of two ways:

Develop a Database Using the Template

Make a New Database From Scratch

Let’s get into the nitty-gritty of each of these:

Develop a Database Using the Template

There are many circumstances in which we need to begin with some pre-made database template in order to fulfil the requirements that have been provided.

MS Access includes a large number of pre-made templates that are ready to be used for a variety of database requirements in which the data structure has already been defined.

You are free to continue customising the structure of the template in accordance with our requirements.

Examples of databases created with MS Access include Contacts, Student, and Time tracking, amongst others.

Creating a Database from a Template: Step-by-Step Instructions

Step 1: Open the MS Access application and navigate to the File menu.

A Guide to Using Microsoft Access

The following window will show up as the result: Below is a list that contains all of the Database templates.

A Guide to Using Microsoft Access

Step 2: We are able to pick any of the templates by clicking on them. For more information, please click on the Contact Template link.

A Guide to Using Microsoft Access

Step 3: A box labelled “File name” will appear on the screen with the standard file name.

A Guide to Using Microsoft Access

4) Give the new name you want to use.

Step 5 of the Microsoft Access Tutorial: Click the ‘Create’ button.

The following window will display once the Guru99 Contact Database has been successfully established thanks to the Microsoft Access Tutorial.

Taking Step 6 in the Microsoft Access Tutorial You have the option to select one of the items in the left navigation pane, click on it, and open it so that you can make more references and work on it.

When you click on the “Contact Detail” form, for instance, it will open in the form that is shown below.

A Guide to Using Microsoft Access

Make a New Database From Scratch
1) While the MS Access application is active, navigate to the File menu and select New.

A Guide to Using Microsoft Access

Step 2: Select the ‘Blank Database’ option with your mouse.

A Guide to Using Microsoft Access

Step 3: A box labelled “File name” will appear on the screen with the standard file name.

A Guide to Using Microsoft Access

Step 4: Type in the new name to be used.

Step 5 of the Microsoft Access Tutorial: Click the ‘Create’ button.

Microsoft Access Tutorial Result: Guru99 After the database is built, the window below will appear.

A Guide to Using Microsoft Access

Instructions for Creating a Table
In order to begin the process of storing information in the database, the first task in this Microsoft Access lesson is to create a Table to serve that purpose. After the table has been created, we are ready to proceed with the insertion of rows into it.

A Guide to Using Microsoft Access

There are two different approaches to creating databases using Microsoft Access.

Produce a Table Utilizing the Design View
Produce a Table Utilizing the Datasheet View
Make a Table Using the Datasheet View
Step 1) To begin, select the Create tab. After that, select Table from the Tables group.

A Guide to Using Microsoft Access

Step 2) The system will now display the table that was initially created with the name “Table1.”

Step Three of the Microsoft Access Tutorial: To rename a column, double-click on the column header and then type in the new column name.

A Guide to Using Microsoft Access

Take note that the ‘AutoNumber’ data type is assigned to the Course ID variable. As a result, this also serves as the Unique Key for the table.

A Guide to Using Microsoft Access

Step 4) If you want to add a column, go to the ‘Add & Delete’ group and click on any of the categories there. You also have the option of adding a column by clicking the button that says “Click to Add.”

To see an example, go to the “Add & Delete” group and select the “Short Text” option.

A Guide to Using Microsoft Access

Step 5: Field1 will be used as the default name for the newly inserted column.

A Guide to Using Microsoft Access

Step 6: After clicking on Header, rename the heading to “COURSE NAME.”

Step Seven of the Microsoft Access Tutorial To save the table, use the keyboard shortcut “Ctrl + S” and then enter the new table name.

Step 8 of the Microsoft Access Tutorial If you choose the tab labelled “Name and Caption,” you will have the option to save a new name, caption, and short explanation for any column. To access it, click on it.

A Guide to Using Microsoft Access

Step 9: A box labelled “Dialog” will appear. Please add the following, then click the “OK” button.

“Name” is the actual name of the column that is being discussed here.
This is the user view name of the column, and it is called “Caption.”
“Description” is the short description of the column name, and it is located here.
The result of completing the Microsoft Access Tutorial is that a Name, Caption, and Description have been created.

A Guide to Using Microsoft Access

Create Table in Design View Step 1) To begin, select the Create tab from the toolbar. After that, select Table from the Tables group.

A Guide to Using Microsoft Access

The Table dialogue box will display at this stage. Enter the Field Name, Data Type, and Description for each individual Field.

A Guide to Using Microsoft Access

Steps 3) Select the Course ID you want to use as the primary key, then click on the ‘Primary Key’ button. The course ID will be preceded by a key icon, as demonstrated in the following example:

Step Four of the Microsoft Access Tutorial: Press the Ctrl and S keys simultaneously. Simply enter the table’s name and then click the OK button.

The Conclusion of the Microsoft Access Tutorial:

A Guide to Using Microsoft Access
How to navigate between the Datasheet view and the Design view
Now, in this tutorial for Microsoft Access, we will learn how to switch between the Datasheet view and the Design view:

Simply clicking the “View” button located in the top-left corner of the Access Ribbon (which is displayed in the Home/Help/Design Tabs) will allow you to toggle between the datasheet view (also known as the spreadsheet view) and the design view. And then select the View that you want to display by clicking on it.

Take, as an illustration, the scenario in which you want to transition from the “Design view” to the “Data Sheet” View of the just constructed table known as “Course strength.”

1) Select the Datasheet View option from the menu.

A Guide to Using Microsoft Access

Result:

A Guide to Using Microsoft Access

Inserting Data Using MS Access
Both the Course and Course Strength tables have been established by you.
There are two different approaches to adding data. The first is the Datasheet view, and the second is the Form view. In this section, we are going to learn how to add data using the Datasheet view.
There is no need to store the data by hand because it is not necessary. Access will immediately store the data once you click on additional rows in the table.
The only thing you have to watch out for when adding data is making sure that you enter the appropriate data type. For example, if a column is specified as a number, then we can only add numbers and not text to that column.
Let’s begin by opening the Access database that you developed previously so that we may add some data to your table.

1) Go to the table labelled “Course,” and pick it.

A Guide to Using Microsoft Access

Steps 2) From the ribbon, choose the Datasheet view option, and then add some data by entering the values in It. Data that has been updated will be saved automatically.

A Guide to Using Microsoft Access

Now let’s imagine that you wish to eliminate the fourth entry that has the course name “My SQL.”

Step 3: To select the row, click on the column on the leftmost side of the screen, and then right-click on the row. The choices menu will display, and the ‘Delete Record’ option will be available for selection.

A Guide to Using Microsoft Access

Step 4: A popup window will open, asking you to confirm that the record should be deleted. To continue, just hit the “OK” button.

The Conclusion of the Microsoft Access Tutorial:

Tutorial on Microsoft Access providing a summary of views:
Datasheet View:

appears in the view, which enables you to enter raw data into the database table that you are working with.

Design view:

The view is displayed, which gives you the ability to put fields, data types, and descriptions into your database table.

Microsoft Access-based forms
You can design a user interface for a database application by using something called a form, which is an item in the database.
The primary purpose of it was to simplify the process of entering or modifying data.
A user can pick their information for a form from one or more tables.
Forms can also be used to control access to data, such as determining which users can see which data fields or rows and which users see which fields or rows.
Forms feature a view called the Form View.
Assist you in displaying live data and simplify the process of creating fresh data.
In order to comprehend the form, let’s begin by making two new records in the contact table (from the prebuilt Contact Database discussed here)

Step 1: From the left navigation menu, select the table labelled “Contact.”

Step 2: Generate two rows by entering some pertinent data into each of the two rows that you just created.

A Guide to Using Microsoft Access

design a form, and then we will discuss how forms can be used to facilitate the straightforward presentation, modification, and addition of new data.

Instructions for Creating a Form
As can be seen in the following list, the form can be created in four major ways:

Wizard of the Forms Form
Form for Splitting Multiple Items
Let’s go through the process of creating the form step by step, starting with the first possible option:

1. Construct utilising the Form Wizard
By selecting this option, the user will be able to create the form with the help of the wizard and select the column from the available list of column forms in a format known as legacy Select window.

1) Select the ‘Form Wizard’ option from the menu.

A Guide to Using Microsoft Access

Step 2) The System menu will appear at the bottom of the screen.

A Guide to Using Microsoft Access

Step 3: Choose the columns that you want to be present in the completed form.

A Guide to Using Microsoft Access

The end result is that ID was chosen.

A Guide to Using Microsoft Access

Step 4) Continue selecting all essential columns as described in the previous step, Step 3, and then click the “Next” button.

A Guide to Using Microsoft Access

Step 5: A layout selection box will open, giving the user the opportunity to select a particular form layout from a variety of options. Just hit the “NEXT” button.

A Guide to Using Microsoft Access

Step 6: Give the form the name “Contact Form,” then click the “Finish” button when you are done.

A Guide to Using Microsoft Access

The end result is that the Contact Form object now exists, and it displays all of the columns that were present in step 4 of the Selected column list.

A Guide to Using Microsoft Access

2. Construct by Using the Form
It is the most straightforward method of producing the form, which will:

In “form view,” all of the columns will be populated with data from the table you’ve selected by default.
The user has the ability to manually delete any columns that are not required.
1) First, we will need to select the table that will serve as the basis for our form, and then we will need to click on the “Form” button.

A Guide to Using Microsoft Access

Step 2: The window shown below will now display.

A Guide to Using Microsoft Access

Step 3: Right-click on any cell that you do not want to appear on the final forms, and then hit the ‘Delete’ button on your keyboard.

A Guide to Using Microsoft Access

The ‘Fax Number’ field has been removed as a direct consequence of this action.

Step Four of the Microsoft Access Tutorial After pressing “Ctrl+S,” enter “Contact Form2” as the new name for the form. Just hit the “OK” button.

A Guide to Using Microsoft Access

The end result is that there is a new form located in the “Forms” section with the name “Contact Form2.”

Create a form by selecting “Multiple Item” in the Microsoft Access Tutorial 3.
It is another type in which all of the records that have already been produced will be displayed in the Form with the opportunity to create a new record.

1) Select the ‘Create’ tab to begin the process. After clicking on the ‘More forms’ option, select the ‘Multiple Items’ option.

The Conclusion of the Microsoft Access Tutorial: The window will display directly below the many lines.

Step 2 of the Microsoft Access Tutorial: Press the Ctrl and S keys simultaneously. After giving the new form a name, click the “OK” button.

The Conclusion of the Microsoft Access Tutorial: Within the “Forms” area, there is now a new form that can be found with the name “Contact Form Multiple Item.”

4. Create a form by using the “Split from” option in the Microsoft Access tutorial.
This form is a hybrid of a simple form and a split form in the sense that it allows the user to view both the form and the datasheet in a single window.

Whatever the user enters in the Form view is immediately displayed in the Datasheet view, and vice versa.

1) From the ‘Create’ tab, select ‘More forms’ and then ‘Split Form’ from the drop-down menu that appears.

The Conclusion of the Microsoft Access Tutorial: The Form and the data sheet will appear together in the window that will open below.

A Guide to Using Microsoft Access

Step 2: To rename the form, hit “Ctrl-S” and then type in the new name. Just hit the ‘OK’ button.

The Conclusion of the Microsoft Access Tutorial: Within the “Forms” area, there is now a new form that can be found with the name “Contact Form Split.”

A Guide to Using Microsoft Access

You can make changes to the existing record using the Form.
One of the benefits of using forms is the ability to make changes to the form’s values and data without leaving the form itself.
When compared to the format of rows in the table, forms have improved visibility of the fields that the user has selected, and the user can directly edit the information. This makes the system more user-friendly.
These values, which were altered via forms, will immediately be reflected in their respective original tables as well.
Take a look at the following to learn how to alter the value in the form:

1) Start by clicking on the “Contact Form” link.

Tutorial for Microsoft Access Step 2: Modify some of the values on the ‘Contact Form’ value

Step 3 of the Microsoft Access Tutorial: Double-click on the table labelled “Contact.”

The Conclusion of the Microsoft Access Tutorial: A value that has been updated can be found in the record of the initial table.

A Guide to Using Microsoft Access
Include a record in a Form’s submission.
Forms provide additional flexibility by allowing records to be added.
When compared to adding entries in a row form, this method of adding records is more appealing and user-friendly than the traditional method.
Here, we will take the example of Split from ‘Contact Form Split’ that was developed earlier in the process of working with Microsoft Access databases.
First, open the “Contact Form Split” file.

A Guide to Using Microsoft Access

Step 2) To add a record, select the ‘New (blank) Record Icon’ from the drop-down menu.

A Guide to Using Microsoft Access

As a consequence, a new record window will emerge in:

Both a Form View and a Datasheet View are available.
A Guide to Using Microsoft Access

Step 3: Compile the information from the Form by hand. Please take note that any data entered into the split form will immediately be mirrored in the data sheet located below it.

A Guide to Using Microsoft Access

Step 4) Do a double click on the table labelled “Contact.”

The Conclusion of the Microsoft Access Tutorial: The ‘Contact’ field in the new record has been updated. Table created from the Multiple-Item Contact Form (Contact Form Multiple Item) form.

A Guide to Using Microsoft Access

Report
In MS Access, an object known as a report is used to format, calculate, and print selected data in an ordered manner. Reports are created for this purpose in MS Access.
It includes information that was taken from tables as well as information that was already present in the report’s design.
Reports are useful because they enable you to present all of the information included in your database in a way that is simple to read.
Let’s have a look at an example of the ‘Contact’ database’s default report in MS Access called the ‘Phone Book.’

1) Navigate to the “Report” area and click on the “Phone Book” link. The ‘Phone Book’ report, which is an integral part of the system, will now open.

It will show the contact’s name, as well as their home, business, and mobile names.

for each record that can be found here.

Step 2 of the Microsoft Access Tutorial Suppose that we now wish to make some changes to the report. Moving from the ‘Home’ location to the ‘Home Number’ location. Use your mouse’s right button to select the “Design View” option.

A Guide to Using Microsoft Access

The Report will initially load in the Design view when the system does so.

A Guide to Using Microsoft Access

Step 3: After making any necessary changes to the name, save your work by using “Control-Shift-S.”

A Guide to Using Microsoft Access

Step 4) Under reports, make a double click on the word “Phone book.”

The end result is that the label is changed from “Home” to “Home Phone.”

A Macro is a time-saving feature in Microsoft Access that allows you to add functionality or automate routine activities. This tutorial will walk you through how to use the Macro feature.
Utilizing a macro allows for the automation of processes that are performed on a regular basis.
It can add a method that is both quick and easy to follow in order to link all of the other objects together and produce a straightforward database application.
Let’s take an example from a Microsoft Access database of the ‘Contact’ database’s default macro, which is called ‘AutoExec.’

1) In the box labelled “Report,” select the “AutoExec” option and click on it. The ‘Welcome’ Macro, which is pre-installed in the system, will be opened automatically. It will display a Welcome note along with other information about to contacts Database, provided that the database’s code is entered into the default ‘AutoExec’ macro.

A Guide to Using Microsoft Access

Step 2: If you want to view the code that is used to create this macro, click the right mouse button.

A Guide to Using Microsoft Access

Result:

Microsoft Access Tutorial Module
It is a grouping of user-defined functions, declarations, statements, and procedures that are kept together as a single unit for storage purposes.
It’s possible that this is a stand-alone object with its own set of functions, which you may access from anywhere in your Access database.
An example of a database created in MS Access using the ‘Contact’ table

1) Start by selecting the’modMapping’ button.

The Conclusion of the Microsoft Access Tutorial:

A Guide to Using Microsoft Access