History of Microsoft Access

Microsoft Access is the database program that is included with Microsoft’s Office suite. While not as intuitive and user-friendly as most other Office programs, Access offers powerful features for creating databases and extracting data from them. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.

Microsoft Access stores data in its own format based on the Access Jet Database Engine. It can also import or link directly to data stored in other applications and databases.

Software developers, data architects and power users can use Microsoft Access to develop application software. Like other Microsoft Office applications, Access is supported by Visual Basic for Applications (VBA), an object-based programming language that can reference a variety of objects including DAO (Data Access Objects), ActiveX Data Objects, and many other ActiveX components. Visual objects used in forms and reports expose their methods and properties in the VBA programming environment, and VBA code modules may declare and call Windows operating system operations.

II Basic part

2.1. History of Microsoft Access

Project Omega.

Microsoft’s first attempt to sell a relational database product was during the mid 1980s, when Microsoft obtained the license to sell R:Base. In the late 1980s Microsoft developed its own solution codenamed Omega. It was confirmed in 1988 that a database product for Windows and OS/2 was in development. It was going to include the “EB” Embedded Basic language, which was going to be the language for writing macros in all Microsoft applications, but the unification of macro languages did not happen until the introduction of Visual Basic for Applications (VBA). Omega was also expected to provide a front end to the Microsoft SQL Server. The application was very resource-hungry, and there were reports that it was working slowly on the 386 processors that were available at the time. It was scheduled to be released in the 1st quarter of 1990, but in 1989 the development of the product was reset and it was rescheduled to be delivered no sooner than in January 1991. Parts of the project were later used for other Microsoft projects: Cirrus (codename for Access) and Thunder (codename for Visual Basic, where the Embedded Basic engine was used). After Access’s premiere, the Omega project was demonstrated in 1992 to several journalists and included features that were not available in Access.

Project Cirrus.

After the Omega project was scrapped, some of its developers were assigned to the Cirrus project (most were assigned to the team which created Visual Basic). Its goal was to create a competitor for applications like Paradox or dBase that would work on Windows. After Microsoft acquired FoxPro, there were rumors that the Microsoft project might get replaced with it, but the company decided to develop them in parallel. It was assumed that the project would make use of Extensible Storage Engine (Jet Blue) but, in the end, only support for Microsoft Jet Database Engine (Jet Red) was provided. The project used some of the code from both the Omega project and a pre-release version of Visual Basic. In July 1992, betas of Cirrus shipped to developers and the name Access became the official name of the product.

1992: Microsoft released Access version 1.0 on November 13, 1992, and an Access 1.1 release in May 1993 to improve compatibility with other Microsoft products and to include the Access Basic programming language.

1994: Microsoft specified the minimum hardware requirements for Access v2.0 as: Microsoft Windows v3.1 with 4 MB of RAM required, 6 MB RAM recommended; 8 MB of available hard disk space required, 14 MB hard disk space recommended. The product shipped on seven 1.44 MB diskettes. The manual shows a 1994 copyright date.

With Office 95, Microsoft Access 7.0 (a.k.a. “Access 95”) became part of the Microsoft Office Professional Suite, joining Microsoft Excel, Word, and PowerPoint and transitioning from Access Basic to VBA. Since then, Microsoft has released new versions of Microsoft Access with each release of Microsoft Office. This includes Access 97 (version 8.0), Access 2000 (version 9.0), Access 2002 (version 10.0), Access 2003 (version 11.5), Access 2007 (version 12.0), Access 2010 (version 14.0), and Access 2013 (version 15.0).

Versions 3.0 and 3.5 of Microsoft Jet database engine (used by Access 7.0 and the later-released Access 97 respectively) had a critical issue which made these versions of Access unusable on a computer with more than 1 GB of memory.^[18] While Microsoft fixed this problem for Jet 3.5/Access 97 post-release, it never fixed the issue with Jet 3.0/Access 95.

The native Access database format (the Jet MDB Database) has also evolved over the years. Formats include Access 1.0, 1.1, 2.0, 7.0, 97, 2000, 2002, and 2007. The most significant transition was from the Access 97 to the Access 2000 format; which is not backward compatible with earlier versions of Access. As of 2011 all newer versions of Access support the Access 2000 format. New features were added to the Access 2002 format which can be used by Access 2002, 2003, 2007, and 2010.

Microsoft Access was the first mass-market database program for Windows. With Microsoft’s purchase of FoxPro in 1992 and the incorporation of Fox’s Rushmore query optimization routines into Access, Microsoft Access quickly became the dominant database for Windows – effectively eliminating the competition which failed to transition from the MS-DOS world.

Access’s initial codename was Cirrus; the forms engine was called Ruby. This was before Visual Basic. Bill Gates saw the prototypes and decided that the BASIC language component should be co-developed as a separate expandable application, a project called Thunder. The two projects were developed separately.

2.2. Access File Extensions

When you save a database in Microsoft Access, it is saved with a .accdb extension. This is the file extension you will use the most when developing Access databases. Older Access databases used an .mdb extension or sometimes a .mde extension, but in 2007, Microsoft started to phase these out in favor of the .accdb extension. If you’re unable to open an older database, you may need to convert to .accdb first using an older version of Access.

 2.3. Elements of a Microsoft Access Database

Box Number 1

The box that is numbered as 1 is nothing but the “Navigation Panel” which list all the six objects that  are used in MS Access. Those six objects are as follows:

1. Tables
2. Queries
3. Forms
4. Reports
5. Macros
6. Modules

Table

Table is an object that is used to define and store data. When you create a new table, Access asks you to define fields which is also known as column headings.

• Each field must have a unique name, and data type.
• Tables contain fields or columns that store different kinds of data, such as a name or an address, and records or rows that collect all the information about a particular instance of the subject, such as all the information about a customer or employee etc.
• You can define a primary key, one or more fields that have a unique value for each record, and one or more indexes on each table to help retrieve your data more quickly.

Query

An object that provides a custom view of data from one or more tables. Queries are a way of searching for and compiling data from one or more tables.

• Running a query is like asking a detailed question of your database.
• When you build a query in Access, you are defining specific search conditions to find exactly the data you want.
• In Access, you can use the graphical query by example facility or you can write Structured Query Language (SQL) statements to create your queries.
• You can define queries to Select, Update, Insert, or Delete data.
• You can also define queries that create new tables from data in one or more existing tables.

Form

Form is an object in a desktop database designed primarily for data input or display or for control of application execution. You use forms to customize the presentation of data that your application extracts from queries or tables.

• Forms are used for entering, modifying, and viewing records.
• The reason forms are used so often is that they are an easy way to guide people toward entering data correctly.
• When you enter information into a form in Access, the data goes exactly where the database designer wants it to go in one or more related tables.

Report

Report is an object in desktop databases designed for formatting, calculating, printing, and summarizing selected data.

• You can view a report on your screen before you print it.
• If forms are for input purposes, then reports are for output.
• Anything you plan to print deserves a report, whether it is a list of names and addresses, a financial summary for a period, or a set of mailing labels.
• Reports are useful because they allow you to present components of your database in an easy-to-read format.
• You can even customize a report’s appearance to make it visually appealing.
• Access offers you the ability to create a report from any table or query.

Macro

This object is a structured definition of one or more actions that you want Access to perform in response to a defined event. An Access Macro is a script for doing some job. For example, to create a button which opens a report, you could use a macro which will fire OpenReport action.

• You can include simple conditions in macros to specify when one or more actions in the macro should be performed or skipped.
• You can use macros to open and execute queries, to open tables, or to print or view reports.
• You can also run other macros or Visual Basic procedures from within a macro.
• Data macros can be attached directly to table events such as inserting new records, editing existing records, or deleting records.
• Data macros in web apps can also be stand-alone objects that can be called from other data macros or macro objects.

Module

Module is an object in desktop databases containing custom procedures that you code using Visual Basic. Modules provide a more discrete flow of actions and allow you to trap errors.

• Everything that can be done in a macro can also be done in a module, but you don’t get the macro interface that prompts you what is needed for each action.
• Modules are far more powerful, and are essential if you plan to write code for a multi-user environment, because macros cannot include error handling.
• Modules can be standalone objects containing functions that can be called from anywhere in your application, or they can be directly associated with a form or a report to respond to events on the associated form or report.

Box Number 2

This area is the called as the work area wherein you will see the details of the objects that is being clicked. For e.g. in the image above, the work area shows the details of the table named Customer.

Box Number 3

By now you might have realised that this is nothing but the Ribbon which is a central location for all the tools & functions that will be required during the course of using the database application.

2.4. How to use Microsoft Access

• Create a Blank Database

Microsoft Access 2016 allows you to create either a blank database, or one that’s based on a template. A template is like a pre-built database that you can use as a starter for your own database.

• Select a Blank Database

You can create a database from two different places:

1. From the Access Welcome screen

Click Blank desktop database from the Access “welcome” screen (the screen that is displayed when you first open Access).

2. From the File –> New menu

Alternatively, if you already have another Access database open, you can go to the File menu at the top-left corner of Access, then click New to bring up the same options. Click Blank desktop database to create a new database.

• Name the Database

Name the database at the prompt.

• Your New Database!

Your new database has now been created.

           Customize the Blank Table

Access 2016 creates a table automatically when you create a blank database. You can customize this table, then create more using the Create tab.

·      Select a Data Type for the New Field

Right-click Click to Add and select Short Text from the combo box.

This combo box lists the data types you can assign to a field. Access requires that each field is assigned a data type.

In this case, we’re telling Access to only allow Short Text in this field. If a user tries to enter a different data type, they will receive an error.

·      Name the Field

• Once you’ve selected a data type, the field header will be highlighted with the text Field1 so that you can provide a name for the field.
• Enter ArtistNameas the name of the field.

·      Rename the First Field

• The first field in our table is currently called ID. We’ll rename it.
• Right-click on the IDfield header, and select Rename Field. The field will be highlighted for you to rename it.
• Enter ArtistId.

·      Save the Table

Right-click on Table1 to save the table.

            Create a New Table

Now we’ll create a new table. This one will contain information on the albums that the artists release, so we’ll call it Albums.

• Create a New Table

Ensuring the Create tab is selected on the Ribbon, click Table. This will add a blank table called Table1 to the database.

• Set up the Fields

Now add 4 fields and rename the first. Set them up as follows:

• Create a new Table in Design View

Design View can make it easier to set up a table. It provides a different view of the table to Datasheet View, and is typically used to configure the table.

You can use Design View to create a tables, set up data types for each field, specify default values, specify how data should be entered and displayed, and more.

So now that we’ve established that Design View and Datasheet View are just two ways of looking at the same table, let’s go ahead and create a table in Design View.

• Create the Table

Click Table Design from the Ribbon (ensuring that the Ribbon is on the Create tab).

A blank table called Table1 will be displayed in Design View.

• Add Fields to the Table

In the first cell under Field Name add a field called GenreId. Select Autonumber as its data type.

Under that field, create a new one called Genre and select Short Text for its data type.

• Change the Genre Field Properties
• Ensuring the Genrefield is selected (by clicking on it), look at the bottom Field Properties
• Double-click somewhere in the Requiredrow, so that the value now reads Yes.
• Now, double-click in the Allow Zero Lengthfield so that it changes to No.
• We have just made the Genrefield a required field — all records must have a value in this field, and it can’t just contain a blank string.
• Save the Table
• Right-click on the Table1tab and select Save from the drop down list.
• Call the table Genresand click OK.

• Create relationships between tables

Access allows you to create relationships between tables so that you can query related data from multiple tables.

In relational database terms, a relationship is a situation where multiple tables can contain related data that is linked by a common field.

A relationship consists of a parent table and a child table. The child table references the parent table by having a field that matches a field in the parent table. The child’s field is referred to as a foreign key. The parent’s field is the primary key.

• Open the Relationship Dialog
• Click Relationshipsfrom the Database Tools tab on the Ribbon.
• The Show Tablesdialog box should appear. If it doesn’t appear, click Show Tables.
• Select the Tables
• Select both the Artists and Albums tables from the list and click Add.
• Click Close to close the dialog box.
• Create the Relationship

Click and drag the Albums.ArtistId field over the Artists.ArtistId field and release. The Edit Relationships dialog box appears.

• Edit the Relationship

1. Check Enforce Referential Integrity and click Create.
2. A diagram now appears, representing the relationship.
3. Save the relationship by pressing Ctrl+ S, right-clicking on the tab and clicking Save, or clicking the X icon to close the relationship.

• Create a query with the Query Wizard or go straight to the Query Design option. How to use Query Design?

One of the main functions of a database is to provide the ability to query the data. The ability to create complex queries across multiple tables with various data types is one of the features that distinguishes a database management system from a simple spreadsheet application.

• Launch the Query Designer

1. Click Query Design from the Create tab on the Ribbon.
2. The Show Table dialog box will appear.
3. Select the Tables for the Query
4. Select both the Artists and Albums tables and click Add.
5. Click Close to close the dialog box.

• Design the Query

1. Select the options as per the screenshot.
2. Run the query by clicking the ! Run button on the Ribbon.

• View the Query Results

The query results are displayed in Datasheet View.

• Save the Query

You can save a query so that you can run it again later. Any time you run the query, it will return the current (up to date) data.

• Create a Form from a Table

Access 2016 provides many options that make it easy to create forms.

One thing that separates Access (and other desktop database systems) from most client/server database management systems (DBMSs), is the ability to create forms. Most client/server systems require you to use separate tools to build forms.

In Access 2016, you have the option of creating a blank form from scratch, using the Form Wizard, or having Access automatically create a form from a table or query

Create the Form

Select the table in the left menu that you want to use for the form (in our case, the Albums table) and click Form from the Create tab on the Ribbon.

Access immediately creates a form based on the selected table. The form displays all fields as label/field pairs. The label allows users to see which field is which.

The field is presented in an editable form element such as a text box, combo box, etc. The user can then edit the underlying data using the form elements.

Form Views

There are three different views for working with forms:

• Design View
• Layout View
• Form View

When you first create a form from a table, Access opens it in Layout View. You can customize the form however you like. You can switch between views as often as you like while customizing your form.

Switching views can be done by using the View button on the Ribbon, or the three icons at the bottom right of the Access window.

Design View

Design View provides a more detailed view of the form’s structure than Layout View.

Layout View

Layout View provides a more visual layout for editing the form. It bears a close resemblance to the actual form that the user will see.

Layout View allows you to edit the form properties while it has real data.

Form View

Form View is how the user will see the form. You can’t edit the form’s properties, but you can use the form just as a user would.

ІІІ Conclusion

Microsoft Access is a pseudo-relational database engine from Microsoft. It is part of the Microsoft Office suite of applications that also includes Word, Outlook and Excel, among others. Access is also available for purchase as a stand-alone product. Access uses the Jet Database Engine for data storage.

Access is used for both small and large database deployments. This is partly due to its easy-to-use graphical interface, as well as its interoperability with other applications and platforms such as Microsoft’s own SQL Server database engine and Visual Basic for Applications (VBA).

Access was first released as version 1.0 in November 1992. The software was fine while working with small databases but in larger files there was the danger of data corruption. With each release of Office, Microsoft has also included a newer version of Access, each of which includes new or updated features.

Access is an easy-to-use tool for creating business applications, from templates or from scratch. With its rich and intuitive design tools, Access can help you create appealing and highly functional applications in a minimal amount of time. Integrate data between Access and line-of-business apps using the Access connector library to generate aggregated visuals and insights in the familiar Access interface. Easily store data in SQL Server and Microsoft Azure SQL to enhance reliability, scalability, security, and long-term manageability.