INTRODUCTION TO MICROSOFT ACCESS | DON STEVE BLOG
March 29, 2024

Microsoft Access is a Relational Database Management System (RDBMS), designed primarily for home or small business usage.

Access is known as a desktop database system because it is functions are intended to be run from a single computer. This is in contrast to a server database application (such as SQL Server), where it is intended to be installed on a server, then accessed remotely from multiple client machines.

Microsoft (or MS) Access is a software package that you install just like any other software package, and is bundled as part of the Microsoft Office suite.

 

Microsoft Access Database File Extension

When you create (and save) a database in Microsoft Access, the database is saved with a .mdb extension. This is the file extension you will use the most, when developing Access databases. Once you have established your database, you also have the option of saving it as an MDE file, which gives you some benefits over the MDB file. An MDE file uses a .mde extension.

 

Microsoft Access – Create a Database

Creating a database in Microsoft Access is relatively straightforward.

You may notice that, whenever you start Microsoft Access, you see the following screen.

In this case, you can simply select “Blank Database”

 

If you already have Access open, you can go to the “File” menu and click on “New Database”:

 

1) Choose “Blank Database”. (Skip this step if you already chose “Blank Database” at step one). You also have the ability to choose from a template, but we will just use a blank database here:

 

2) Choose a location to save the database:

 

Your New Database

Once you have completed the above tasks, you should see a blank database, like this:

 

We know this database is blank because it doesn’t have any tables. If it did, you would see these tables in the middle pane of the table tab. Now that we have our blank database, we can start adding some tables.

 

Create a TableĀ 

With database management systems, you need to create your tables before you can enter data. Microsoft Access makes creating tables a breeze.

Using our blank database, we are going to create a table called Individual. This table will have 4 columns: Individualld, FirstName, LastName, and DateCreated.

 

Design View

“Design View” enables you to create your database tables and specify the columns and their data types etc. The following steps demonstrate how to switch to Design View and create a table.

1) Ensuring you have your blank database open and you’re on the following screen (with the “Tables” tab selected), click “New”.

 

2) Select “Design View” and click “OK”:

 

3) Fill out the details in the “Field Name” column and the “Data Type” column. Here, we are creating the column names and specifying the type of data that can be entered into them. Restricting the data type for each column is very important and helps maintain data integrity. For example, it can prevent us from accidentally entering an email address into a field for storing the current date.

Other options for each column include restricting the length of data (under “Field Size”), setting a default value (this is used if someone leaves the field blank), specifying whether the data is required, and more. Leave these options with their default value for now.

 

4) Click the “Save” icon, enter the table name (“Individual”), and click “OK”:

 

5) When prompted to set a primary key, click “Yes”. (A primary key ensures that the data in this column is unique – no two values can be the same. This is important for when you need to select or reference data from this column):

 

Adding Data

In Microsoft Access, there are two primary methods of adding data to your database. Strictly speaking, there is a third method (using SQL), but we’ll save that for later.

Here is an explanation of the two main methods.

Direct entry: You can type directly into the table while it’s in Data Sheet view. Initially, this may seem like the quickest and easiest method, but it’s not suitable if you have lots of data, and/or if non-technical users need to enter data.

 

Form

You can set up a form, so that non-technical users can enter data into the form. Once they submit the form, the data is automatically inserted into our table. The form could insert data into multiple tables too – saving you from having to open up each table to manually insert the data.

Access provides a form wizard, which steps you through the process to building a form.

 

Create a Form

Forms provide non-technical users the ability to add data to your database.

Here are the steps involved in creating a form in Access.

1) Ensuring the “Forms” tab is selected, click “New”.

2) Select “Form Wizard” and click “OK”. (For now, we’ll use the “Form Wizard” to create a form. Once you become comfortable with creating forms, you can choose another option if you prefer).

 

3) Select the fields that you need on your form and click “Next”. In this case, we only need the user to enter first name and last name, so we choose those two fields:

 

4) Choose which layout you’d like your form to use. Leave this at the default (“Columnar”) and click “Next”:

5) Choose which style you’d like your form to use. Leave this at the default (“Clouds”) and click “Next”:

6) Choose a name for your form and click “Finish”:

 

Your form should now look something like this:

 

This is a very basic form and in most cases, you’d have a lot more fields on your form. You can see that the form is already presenting the first record in our Individual table.

 

Modify a Form

In the previous lesson, we used the “Form Wizard” to create a form. In this lesson, we’re going to modify the form.

At the end of that lesson, we were able to see our form in “Form View”. Form View is the way the form is intended to be viewed by the user. If we want to modify it, we need to switch to “Design View”.

 

Selecting “Design View”

To switch to Design View, select “Design View” from the “File” menu:

 

Enlarging the Form

To make the form taller, click on the “Form Footer” panel and drag it down. To make the form wider, click on the edge of the form and drag it across:

 

Adding a Field

Let us say we want to add the Individualld field to the form. To do this:

1) Select “Field List” from the “View” menu:

 

2) You should now see a “Field List” panel. From this panel, click and drag the field you want onto the form area. When you release your mouse, the field will be added to the form:

 

Create a Query

A “query” refers to the action of instructing the database to return some (or all) of the data in your database. In other words, you are 66querying” the database for some data that matches a given criteria.

 

To create a query in Microsoft Access:

1) Ensuring you have the “Query” tab open, click “New”.

2) Select “Simple Query Wizard” and click “OK”:

3) Choose the fields you’d like to be presented in the results of your query.

To choose 1 field at a time, select it and click the “>” arrow. To choose all fields click the “>>” arrows. For now, just select the “FirstName” and “LastName” fields, then click “Next”:

 

4) Choose a name for your query and click “Finish”:

 

Create a Macro

Microsoft Access provides the ability for you to create macros. A “macro” refers to a set of actions that can be run automatically, and on demand. You could make a macro run when a user presses a certain key, or you could make a macro run every time your database is opened.

For example, if say 90% of your database usage is entering data into the same form, you could create a macro that automatically opens that form every time the database is opened. That way, it saves you from having to click on the Forms tab, then double clicking the form you want to open.

 

How to create a macro:

1) Ensuring the “Macros” tab is selected, click “New”.

2) Use the dropdown menu to select your first action – choose “OpenForm”. In the bottom pane, select the form name you wish to open – choose “Individual Form”

 

3) Use the dropdown menu to select your second action – choose “GoToRecord”. In the bottom pane, choose the record you want to go to – choose “New”. What we are doing here is ensuring that the form does not open up a previous record – it goes straight to the end and has a blank record ready for you to enter a new record (this saves time and unnecessary clicks).

4) You are now ready to save your macro. To do this, click on the “Save” icon and enter a name for your macro. Make sure you name this macro “Autoexec”. By naming it Autoexec, we are instructing Access to run this macro everytime we open the database.

 

Convert Access Database to Excel

A very common question many people have when learning Access is “how do I convert an Access database to an Excel spreadsheet?”.

What they usually mean is, “how do I export the data from my Access database to an Excel spreadsheet”.

Often they want to do this because they need to distribute the data to someone else who may not have Microsoft Access. Also, in most cases, it’s better to send someone a simple spreadsheet containing exactly the data they require, than to send them the whole database and then say, “run the Individual query”.

In any case, it is very easy to export data from Access to Excel. You can either export the contents of a table, or the result of a query.

Here is how you go about doing this.

  1. Click on the table or query that contains the data you want to export.
  2. From the “File” menu, click “Save As/ Export”.
  3. Select “To an External File or Database”.
  4. Choose a location for your Excel spreadsheet (or double click on an existing one).

You now have an Excel spreadsheet containing the data from your query that you can email to anyone you like.

Leave a Reply

Your email address will not be published. Required fields are marked *