Microsoft Access Tutorial

What is MS Access?

MS-Access is application software for managing the databases. It is released by Microsoft in November 1992. It actually stores the data on the basis of the relational Jet Database Engine. It mainly provides the graphical user interface for accessing the data and the tools for software development.

It helps the users for managing the related data more efficiently than Microsoft Excel or other applications of spreadsheets. Users can easily analyze the large amount of data stored in it.

It is not used to create large or enterprise-level databases but mainly used to create small databases. For the large databases, Microsoft provides the SQL server database engine.

Uses of MS Access

Following are the important uses of Microsoft Access:

• Those users who have a basic knowledge of computers can easily create and manage the database in Access.
• It is a software program which can be used in schools for making the schedules.
• Any user can easily insert the data in the database of MS Access.
• It is also used to create user-friendly front ends.
• In MS-Access, repetitive tasks can be easily performed by the help of macros.
• Any user can access the data or information quickly with the help of commands.
• It allows users for developing web applications. Various software developers also use this software for developing the application software.
• This software works with the SQL Server, DB2, and Oracle databases. Because these three databases support the (ODBC) standard.
• Any user can easily import data from and export data to word processing files, or spreadsheet files.
• It is also used to link directly to those data which is stored in following different databases and applications:
  • Microsoft Excel
  • Text
  • Hypertext Mark-up Language (HTML)
  • Outlook
  • dBase
  • Extensible Mark-up Language (XML)
  • Paradox
  • Oracle
  • Microsoft SQL Server
  • Postgre SQL
  • MySQL.

Features of MS Access

Following are the different features or characteristics of Microsoft Access:

• Any user can easily create the framework (i.e. tables) which stores the data or information in the database.
• There is a Tell Me Function in MS Access, which allows you to create queries, tables, reports, and forms easily.
• Any user can easily import the information in the created table of MS Access from the other applications of Microsoft.
• The users who work with MS Access can easily create a report from the table which is selected by them. For creating a report, users need to click on the Report Wizard icon available in the toolbar.
• One of the important features of MS Access is Forms.
• Another important feature of MS Access is the template. Any user can use the templates which are available in software MS access.
• Queries can also be considered as an essential feature of MS Access. It helps for processing the data in the tables, and for displaying the data to the users.
• Microsoft Access 2010 introduced the new feature which allows the users to publish the web databases to SharePoint sites, after developing them.
• A powerful feature also exists in this application software. This feature helps the developers by creating custom solutions for their databases using the VBA (Visual Basic Application) Code.
• The users who work with Microsoft Access can easily view their reports in following different ways:
  • Print preview
  • Design View
  • Layout View
  • Report View
• It also introduced the multi-valued fields. This feature allows you to select and store more than one values in one field.

Versions of MS Access

Following are the different versions of Microsoft Access:

Access 1.0

• Access Version 1.0 is the first version which was released by Microsoft in November 1992.
• This version supports the Windows 3.0 operating system.
• It uses version 1.0 of the Jet Database Engine.

Access 1.1

• Access 1.1 is the next version, which was released in May 1993. The basic programming language of Access is introduced in this version of MS Access.
• It supports the Windows 3.1x operating system.
• It uses version 1.1 of the Jet Database Engine.
• This version also improved the linking of data with other applications of Microsoft.

Access 2.0

• Access 2.0 was released in April 1994. The command bars introduced in this version of MS Access.
• This version supports Microsoft Windows 3.1. It also supports other versions of Windows such as Windows XP, Windows 2000, Windows 98.
• It requires a minimum of 4 MB RAM (Random Access Memory).
• It uses version 2.0 of the Jet Database Engine.

Microsoft Access 95 or Version 7.0

• Version 7.0 of Access was released on 24^th August in the year of 1995. It is also called as Access 95 because it became a part of MS Office 95.
• In this version, (VBA) Visual Basic Application replaces the Basic programming language of Access.
• It supports Windows 95 or later operating systems.
• It requires a minimum of 8 MB RAM (Random Access Memory).
• ActiveX controls are supported.
• It uses version 3.0 of the Jet Database Engine.

Microsoft Access 97 or Version 8.0

• Version 8.0 of Access was released on 16^th January in the year of 1997.
• It uses version 3.5 of the Jet Database Engine.
• It requires a minimum of 16 MB RAM (Random Access Memory).
• Hyperlinks are introduced or added in MS Access, after its release. 
• It supports the Windows NT 3.51 SP5, Windows 95, Windows NT 4.0 SP2, and Windows XP.

Microsoft Access 2000 or Version 9.0

• Version 9.0 of Access was released on 7^th June in the year of 1999.
• It uses version 4.0 sp1 of the Jet Database Engine.
• It also requires a minimum of 16 MB RAM (Random Access Memory).
• The shared IDE (Integrated Development Environment) with the Visual Basic 6.0 was introduced in this version.
• In this version of MS Access, following different features are introduced:
  1. Data Access Pages
  2. Pivot charts or Pivot tables
  3. Visual Source Safe Integration
  4. Smart tags
  5. Package Solution Wizard
• This version also gives the following database securities:
  1. Record locking
  2. ActiveX Data Objects
  3. Access Data Projects Connected to the SQL (Structured Query Language) Server.

Microsoft Access 2002 (XP) or Version 10.0

• Version 10.0 of Access was released on 31^st May in the year of 2001.
• This version provides the Form Conditional Formatting of Fields feature.
• It also supports version 4.0 sp1 of the Relational Jet Database Engine.
• It also supports the XML language for linking or importing data from tables in a database.
• It requires a minimum of 72 MB RAM (Random Access Memory).
• It supports the following Windows operating systems:
  1. Windows 98
  2. Windows 2000
  3. Windows XP
  4. Windows NT 4.0 SP6

Microsoft Access 2003 or Version 11.0

• Version 11.0 of Access was released on 27^th November in the year of 2003.
• It also supports the same version of the Relational Jet Database Engine, which is used in MS Access 2002 and MS Access 2000.
• It supports the following Windows operating systems:
  1. Windows 2000 SP3
  2. Windows XP
  3. Windows Vista
  4. Windows 7
• In this version, following features are introduced:
  1. It provides the themes of Windows XP.
  2. The databases are created in MS access with the help of a digital signature.
• It requires a minimum of 128 MB RAM (Random Access Memory).

Microsoft Access 2007 or Version 12.0

• Version 12.0 of Access was released on 27^th January in the year of 2007.
• It supports version 12 of ACE (Access Database Engine).
• In this version, the ACCDB database format was introduced.
• This version replaces the database container with the search bar and navigation pane.
• It requires a minimum of 256 MB RAM (Random Access Memory).
• This version also introduced various features. Some of them are as follows:
  1. Tabbed interface
  2. Ribbons
  3. Quick Access Toolbar
  4. Report output to PDF
  5. Split forms
  6. Web browser Control
  7. Datasheet totals
  8. Report View

Microsoft Access 2010 or Version 14.0

• Version 14.0 of Access was released on 15^th July in the year of 2010.
• It supports version 14 of ACE (Access Database Engine).
• In this version, the 64-bit version of Access was introduced.
• It also requires a minimum of 256 MB RAM (Random Access Memory).
• It provides the data macros for the ACCDB database format.
• With the help of this version, any user can easily access the web applications using SharePoint list.
• When any table is updated in this version, then all the reports referencing the table are also changed or updated.

Microsoft Access 2013 or Version 15.0

• Version 15.0 of Access was released on 29^th January in the year of 2013.
• It supports version 15 of ACE (Access Database Engine).
• With the help of this version, any user can easily access web applications using SharePoint 2013 SQL Server.
• It requires a minimum of 1 GB RAM for 32 bit and 2 GB RAM for 64 bits.

Microsoft Access 2016 or Version 16.0

• Version 16.0 of Access was released on 22^nd September in the year of 2015.
• It supports version 16 of ACE (Access Database Engine).

Microsoft Access 2019 or Version 16.0

• MS Access 2019 was released on 24^th September in the year of 2018.
• It also requires a minimum of 1 GB RAM for 32 bit and 2 GB RAM for 64 bits.
• Any user can easily visualize the data with new charts in this version.

Components of Microsoft Access

Following are the seven main components, which comes under the MS access

• Tables
  It is the main component of the MS Access software. In the MS Access database, tables are mainly used for storing the data or information in the form of rows and columns.
  The Access tables which contain the data or information look similar to the tables in MS Excel or MS Word.
  Whenever, you create a new database in MS Access, firstly, you have to create a table in that database. You can also relate a specific table to other tables, and easily define the primary key in that table.
• Relationships
  Relationships are the links or connections, which are formed between the one or more tables in the database. There exist following four types of relationships:
  1. One-to-One Relationship
  2. One-to-Many Relationship
  3. Many-to-One Relationship
  4. Many-to-Many Relationship
• Queries
  Queries are the commands, which are used to retrieve the data or information from the database. It also allows you to insert the information in the MS Access database.
• Forms
  It is an object or a component, which helps the users for entering the data in the table of any database by an interface. Any user can easily display the data of the database.
• Reports
  When the users inserted the data in the database, then they can easily view their information in an organized manner by running the reports. Unlike forms, the reports cannot be edited.
• Macros
  Macros are used for performing the repetitive tasks on reports and forms in the MS Access database. It also allows the user for adding functionalities to forms, controls, and reports.
• Modules
  Modules are used to perform the automating routine operations and user-defined functions which are written in VBA. Any user can easily use these modules from anywhere in the MS Access database.

Difference between MS Excel and MS Access

Both Access and Excel software applications are the products of Microsoft used for data analysis. But there are some differences which are given in the following table:

Launch the Microsoft Access

You can easily launch the MS-Access application in your Windows 10 system by following the given steps:

Step 1: Click on the Search box in the toolbar.

Step 2: After clicking, type the MS Access. Then, it will show you the icon of MS Access with the installed version of Microsoft office in your system. Select and click the icon.

Step 3: After clicking, an application window of MS Access will appear on your computer screen. The following screenshot displays the window of MS Access 2007.

Create a Database

In this section, you will learn how to create a database in the MS Access 2007 application software.

When you open the MS Access 2007, the following screen will display on your computer screen.

On this screen, there are various online database templates such as contacts, Events, Faculty, Students, Tasks. You can easily select the database template according to your needs. You can also choose the Blank Database option, which does not contain any existing data or objects.

In this section, we select the Blank Database option for storing the data. After clicking the option, the following screen will display which gives the default name of the file.

If you are satisfied with the default file name, click on the Create button, otherwise change the name of a file, and then click on Create Button.

We give the name of a file as Employee as shown in the above screenshot and then click the Create button. After clicking, the following screen will display, which shows that the new database is created with the given name in the MS Access:

Create a Table

In this section, you will learn how to create a table in any database in the MS Access 2007 software. For storing any data in the database, the creation of a table is the main aim.

The table is a collection of rows and columns (fields). In a table, every row is referred to as a record. Before the creation of the tables, you have to determine all the tables that are to be created.

The table can be easily created in the MS Access database by the following two ways:

1. Using Datasheet View
2. Using the Design View

1. Using Datasheet View

When you create a new database, then the MS Access automatically creates a new table for you in the datasheet view as shown in the following screenshot:

When you work in an existing database, and you have to create a new table in the datasheet view, then follow the given steps:

Step 1: Select the Create tab in the toolbar of MS Access.

Step 2: Then, click on the Table, in the Tables group.

Step 3: After clicking, a default table is opened in the datasheet view as shown in the following screen:

When the table has been opened in the datasheet view, then you have to add or enter the name of the fields in the table.

For adding the fields in a table, you have to follow the given steps:

Step 1: Double-click on the column header or Add New Field.

Step 2: Now, type the name of the field. When you entered the name, Access software sets a data type to that field.

You can also set the data type to the field in the Data type option of the Datasheet tab as described in the following screenshot:

There are various data types in that option. You can select an option according to what type of value field stores.

When all the fields of a table are entered and assigned by a data type, then you have to save the table by the following steps:

Step 1: Right-click on the name of the table as shown in the following screenshot:

Step 2: Now, click the Save option in the drop-down list. After clicking, a Save As dialog box will appear on your computer screen.

Step 3: Now, you have to insert/type/enter the name of the table. After entering, click on the OK button. We enter the Marks as the table name.

Now, the table is successfully created and saved in the MS Access database.

2. Using the Design View

When you create a new database, then the MS Access 2007 creates a new table in the datasheet view. For converting the table into the design view, you have to follow the given steps:

Step 1: Right-click on the name of the table as shown in the following screenshot:

Step 2: Select and click the Design View option from the list.

Step 3: After clicking, the table is displayed on the screen in the design view same as shown in the following screenshot.

When you work in an existing database, and you have to create a new table in design view, then follow the given steps:

Step 1: In the toolbar, click on the Create tab as shown in the following screenshot.

Step 2: Now, you have to click on the Table Design button or option in the Tables group.

Step 3: After clicking, you will see the following screen on your computer:

When the table is opened in the design view, then you will see the three columns. The first column is Field Name, which allows a user to enter the name of a field.
The second column referred to as a Data Type, which is also defined by the user.

The third column is Description, which is optional. In this column, a user provides a short description of each field.

Now, you have to add or enter the name of fields and assign the data type from the provided list. For adding the fields in the design view of the table, you have to follow the given steps:

Step 1: Firstly, you have to type the name of a field in the first column. Then, press the Tab button.

For example, we type the Student RollNo in the field name as shown in the following screenshot.

Step 2: Then, you have to choose or select the data type for the field from the drop-down list. Again, you have to press the Tab button. We select the Text data type.

Step 3: After pressing the Tab button, Access moves to another column, which is optional. Now, you have to press the Tab button again for entering the next field name.

Step 3: You have to repeat all the above three steps until all the fields are entered in the table. When all the fields are entered in the table, and then click on the Save button on Quick Access Toolbar.

Step 4: Now, you have to type the name of a table, and click on the OK button

Then, your table is successfully created in the design view of the MS Access database.

Adding/Deleting the Record to/from a Table

Once the table has been successfully created in the MS Access database, and the properties are properly defined, the next step is to add the data or information in the tables.

You can easily add, delete and edit the data in the tables in the datasheet view.

Following steps are used to insert the new record in the table:

Step 1: Open the table in datasheet view, in which you have to insert the data.

Step 2: Click on the Asterisk (*) sign, which is presented in the lower-left corner of a table as shown in the following screenshot:

Step 3: Now, add the data into the fields of "New Record" Row. In this row, you can easily move to the next cell using the Tab button.

When you completely inserted the data in each field of a new row, then press Enter key. After pressing Enter, Access automatically saves the new record.

Following steps are used to delete the existing record in the table:

Step 1: Open the table in datasheet view, from which you have to delete the record.

Step 2: Select the record to be deleted from the table. Then, Right-click on the selected record as shown in the following screenshot.

Step 3: Then, select and click the Delete Record option.

Step 4: After clicking, a confirmation box will appear on the screen, which confirms from you that "are you sure you want to delete these records". If yes, then click on Yes button, otherwise No.

Step 4: After clicking, the record is successfully deleted from the table.

Advantages of MS-Access:

• The main advantage of MS Access is that, it is easy to understand.
• It is the most popular because it is one of the best desktop databases.
• The graphical user interface of this system also provides easy functionality.
• Any user can easily import the data or information in the MS Access database from different sources.
• It works easily with various programming languages which actually works on the Windows operating system.

Disadvantages of MS-Access

• The main disadvantage of MS access is that, it is limited in size. It is used for only single departments and small enterprises.
• This software application is not available over the internet, but it can be run on local systems after downloading from the internet.
• It only supports 255 concurrent users.