Microsoft Access

bout Microsoft Access

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's 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.

ACCESS VERSUS EXCEL

You may be wondering what the benefits of using Access are compared with using an Excel spreadsheet. Well, it really depends on what you want to do with the data that you're storing and how much data you intend to store.

Excel may be fine if you've only got a small amount of data, and if you don't have many attributes against each piece of data. It may be fine if you don't have much in the way of relational data across multiple worksheets. Once you start storing many attributes against each piece of data, and perhaps you find yourself repeating information across multiple worksheets, then it's time to start using Access (or another database system if you prefer).

Another important reason for using Access over Excel is, if you need to generate a lot of queries and reports. Access is much better suited for doing this compared to Excel.

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've 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 .mdeextension.

Microsoft Access - Create a Database

Creating a database in Microsoft Access is relatively straightforward. This lesson demonstrates how to create a database.

1. 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":
   

2.   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'll just use a blank database here:

1. 
2. Choose a location to save the database:
   

Your New Database

Once you've 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.

Microsoft Access - 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 from a previous lesson, we are going to create a table called Individual. This table will have 4 columns: IndivdidualId, FirstName, LastName, andDateCreated.

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):
   

DATASHEET VIEW

You have now created a database table called "Individual". You can now switch between "Design View" and "Datasheet View" as you wish. Datasheet View allows you to see any data that has been entered into the table.

1. From the "View" dropdown icon, click "Datasheet View" (this changes the view from Design View, to Datasheet View):
   
2. You should now see something like this:
   

We have just created a database table. This table doesn't have any data yet, but we have created the columns that are required before any data is entered

Microsoft Access - 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's 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. We'll cover this in the next lesson.

Microsoft Access - Create a Form

As mentioned in the previous lesson, 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.

• To jump to the next record, click the  button.
• To jump to the last record, click the  button.
• To jump past the last record so that it's blank and ready for a new record to be entered, click the  button.

Microsoft Access - 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's say we want to add the IndividualId 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:
   

RE-ARRANGING THE FIELDS

You might have noticed that the label for our fields appear exactly as the name of the field. This is OK, but we have no space between multiple words (i.e. "FirstName" appears instead of "First Name"). You can fix this by simply clicking in the label area and re-wording the label.

Also, while we're changing the form around, let's move the IndividualId field to the top of the form. To move the fields around, click on the edge of the field and hold your mouse down. When you see the cursor turn into a hand, reposition the field to the new location, then release the mouse.

You can re-size a field by clicking the edge and dragging once your cursor turns in to a two-headed arrow.

PROPERTIES

For any element that you select, you can view/change its properties. To view the properties panel, select "Properties" from the "View" menu.

The following screenshot shows that we are viewing the properties for the IndividualId text box. The "Data" tab is selected, which shows us the source of the data to populate the text box. The "Formatting" tab enables us to change the way the field is displayed.

VIEWING THE RESULT

Of course, you can always switch back to Form View at any time to see what your form looks like. You can see by this example that I changed the property of the labels so that their font weight is bold:

Microsoft Access - 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 "querying" the database for some data that matches a given criteria.

For example, you might like to see a list of all individuals whose first name is "Homer". Or you might like to see a list of all users who have registered with your database over a given time period. To do this, you need to do a query.

Here's how 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":
   

THE RESULT

You should now see something like this:

At this stage, you may be thinking that there's not much difference between the results of our query and our table. We can still see all records from our table. The only difference is that we can only see 2 fields instead of all 4 fields.

Microsoft Access - Modify a Query

In the previous lesson, we created a query using the "Simple Query Wizard". In this lesson, we will modify that query using "Design View".

1. From Datasheet View, switch to Design View by clicking on the Design View icon (from the tool bar): 
   (If you can't see this icon, select "Design View" from the "View" menu). You should now see something like this:
   
   The top panel shows us the tables that are used in this query (we only use one table in our query). The bottom panel is where we specify what to do with each field in the table. We can nominate to display a field or not display it. We can sort the results by any of these fields. We can also provide criteria to be used in filtering the results. We'll do this in the next step.
2. In the "Criteria" cell for the "FirstName", add the word "Homer". While you're there, add the IndividualId field so that it will also be displayed in our results. Do this by selecting "IndividualId" from the dropdown menu on the third column. Also, ensure that the "Show" checkbox is checked:
   

THE RESULT

Now when you switch back to Datasheet View, you should now see something like this:

So you can see that, once we get a lot more data into our database, queries can be very useful. Imagine if there were 100 individuals in our database called "Homer". You could also add criteria into the "Or" field. For example, you might want to search for individuals called either "Homer" or "Barney".

You may need to practice this to get a good understanding of what it can do. Switch back and forth between Design View and Datasheet View as many times as you like. Change the criteria, add some fields, show fields, don't show them...

Once you become familiar with queries, you'll realise the true benefits of database management systems.

Microsoft Access - 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 everytime 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.
Here's how you can go about creating 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're doing here is ensuring that the form doesn't 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).
   

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's 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's 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.

Microsoft Access Versus SQL Server

As we near the end of this tutorial, you may be thinking "Microsoft Access seems to have some great features, why would I want to use any other database package?". In particular, if you've heard about SQL Server, you may be wondering what the difference is between Microsoft Access and SQL Server.
Now that you've learned how to use MS Access, there are some very good reasons why you wouldn't use it in some cases. Here's a rundown on Microsoft Access versus SQL Server.

MS ACCESS

Access is more suited for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server. You'll generally only find SQL Server on developers' computers/servers or on production server machines.
Another reason you might use Access instead of SQL Server is money. You might already have installed Access as part of the Microsoft Office suite. Purchasing SQL Server would be an extra expense that may not be necessary - depending on your situation. SQL Server can also be quite expensive.

MS SQL SERVER

SQL Server is a more robust database management system. SQL Server was designed to have many hundreds, or even thousands of users accessing it at any point in time. Microsoft Access on the other hand, doesn't handle this type of load very well.
This makes SQL Server perfectly suited for database driven websites. You should never use Access for a database driven website - unless it has a very small amount of traffic (like you and a few of your friends). Even then, you may find yourself getting errors due to multiple users trying to access the database at the same time.
SQL Server also contains some advanced database administration tools that enable organisations to schedule tasks, receive alerts, optimize databases, configure security accounts/roles, transfer data between other disparate sources, and much more.

SO WHICH ONE - MICROSOFT ACCESS OR SQL SERVER?

You need to make the decision. It may already be made for you. If you've just finished this whole tutorial, then there's a good chance you already have MS Access. This probably means that you've got good reason to use Access.
If you do think you need to upgrade to SQL Server, don't panic - it's not a scary as you might think! And you didn't just waste your time learning Access. You will find that SQL Server is similar in many ways to Access. Now that you know your way around Access, you should be able to relate to SQL Server much better.
In any case, I recommend learning about SQL Server and SQL (Structured Query Language), especially if you're interested in a career in developing database driven websites. This will provide you with a better understanding of database systems in general and how everything fits together.

http://naijicomputerelearning.blogspot.com/p/a.html