What is Microsoft Access
This page hopes to explain to you 'What is Microsoft Access' in simple terms.
Microsoft Access has been around for some time, yet people often still ask me what is Microsoft Access and what does it do? Microsoft Access is a part of the Microsoft Office Suite. It does not come with all versions of Microsoft Office, so if you specifically want Microsoft Access make sure the office suite you are purchasing has it.Microsoft Access has the look and feel of other Microsoft Office products, including its layout and navigational aspects. That is where the similarity ends. Microsoft® Access is a database and, more specifically, a relational database. This will be explained in more detail later.
Access has an .mdb extension by default, whereas Microsoft® Word has the .doc extension. Although this has changed in Access 2007 where the extension is now an accdb extension. Early versions of Access cannot read accdb extensions but Microsoft Access 2007 can read and change earlier versions of Access. The above is a bried overview of what is Microsoft Access. Now lets look at it in a bit more detail.What is Microsoft Access made up of?
The Microsoft® Access Database is made up of 7 major components:
The following gives a quick overview of each component.
The tables are the backbone and the storage container of the data entered into the database. If the tables are not set up correctly, with the correct relationships, then the database may be slow, give you the wrong results or not react the way you expect. So, take a bit of time when setting up your tables.Queries, forms, etc. are usually based on a table.
The tables that contain data look a bit like a table in Microsoft® Word or a Microsoft® Excel Spreadsheet, when opened. They have columns and rows as does a table in Microsoft® Word and an Excel worksheet. Each of the columns will have a field name at the top and each of the rows will represent a record.
As an example:
Relationships are the bonds you build between the tables. They join tables that have associated elements. To do this there is a field in each table, which is linked to each other, and have the same values.
Are the means of manipulating the data to display in a form or a report. Queries can sort, calculate, group, filter, join tables, update data, delete data, etc. Their power is immense. The Microsoft® Access database query language is SQL (Structured Query Language). The need to know SQL is not required in the early stages of learning Access. Microsoft® Access writes the SQL for you, after you tell it what you want, in the Design view of the queries window.
Forms are the primary interface through which the users of the database enter data. The person who enters the data will interact with forms regularly. The programmer can set the forms to show only the data required. By using queries, properties, macros and VBA (Visual Basic for Applications), the ability to add, edit and delete data can also be set. Forms can be set up and developed to reflect the use they will be required for.
Reports are the results of the manipulation of the data you have entered into the database. Unlike forms, they cannot be edited. Reports are intended to be used to output data to another device or application, i.e. printer, fax, Microsoft® Word or Microsoft® Excel.
Macros are an automatic way for Access to carry out a series of actions for the database. Access gives you a selection of actions that are carried out in the order you enter. Macros can open forms; run queries, change values of a field, run other Macros, etc. the list is almost endless.
Modules are the basis of the programming language that supports Microsoft® Access, The module window is where you can write and store Visual Basic for Applications (VBA). Advanced users of Microsoft® Access tend to use VBA instead of Macros. If you would like to learn VBA, I have a simple step by step lessons.
Click here to find out more
All of the above components are persistent; this means that changes are saved when you move from one component to another, not when the database is closed, as in a Microsoft® Word Document.
I hope the above give you a bit of an idea of what is Microsoft Access and what it includes.
The total size of a database file (.MDB) is limited only by the storage capacity of your PC (Microsoft® quote the maximum database size of 2 Gigabyte (2000 Megabytes)). These figures are for pre 2007 versions of Microsoft Access.
Very few realistic limitations exist, though here are some parameters:
|Maximum table size||1 Gb|
|No. if fields in a record or table||255|
|No. of indexes in a table or a record||32|
|Ni. of fields in an index||10|
|No. of tables in a query||32|
|Maximum size of a form or report||22"|
|Characters in a memo field||64,000|
|MDB size||2 Gb|
|No. of characters in object names||64|
What is Microsoft Access as compared to a Relational Database
Now that you understand a bit of what is Microsoft Access. it is time to explain what a relational database is. The relational database was invented by E. F. Codd at IBM in 1970.The power of a relational database is the ability to bring a lot of information together quickly. I am not going into too much technical detail of what a relational database but hope to explain it in simple terms, so it is possibly not 100% technically accurate. To me these are the rules of a relational database:
- No duplicate data (except linked fields - explained shortly)
- Information is broken into categories
- Data is broken down to the smallest useable bit. For example a persons name would be broken down into 4 sperate sections title, first name, middle name and last name.
- Each record has a unique identifier, this distinguishes a particular record from any other record
To explain this I will give an example below:
For the purpose of this example, we will be looking at a Library data base from a relational database viewpoint. For a Library database we would want to collect the following information:
- Information about the books;
- Information about the borrowers;
- Information about when a book was borrowed and by whom.
You may be tempted to include all this information within the one table. Once you start entering data the following occurs:
Entering data this way requires multiple entries (and duplicate data in some of the columns)
This can lead to the following problems:
- Data entry errors (see bolded items);
- The user having to enter the same information over and over;
- The database would grow very big, very quickly, causing it to run slower.
Therefore, to meet the rules of a relational database we would first break this one large table into smaller tables of like information (categories). As an example:
- Table One (tblBook) would contain information about the books;
- Table Two (tblBorrower) would contain information about the borrowers; and
- Table Three (tblLoan) would contain information about the borrowing of a book.
The next step is to list all the facts you think are required for each of these tables underneath them.
I have included the examples below for each of the tables for the library database. The primary key and foreign key fields will be explained a bit later.
The fields have been broken down in to their smallest logical part. As an example, a person’s name has been broken down to:
- First name;
The information is only recorded once, i.e. we do not include all the information about the customer in the tblLoan table.
1) Explain how you can import data from other sources into Access database?
To import data into Access data-base
- In the main menu, click on EXTERNAL DATA, in the Import and Link group
- Choose the type of the file you want to import
- To know which type of file can be import or link Access database, follow the instruction in the Get External Data wizard
2) Explain what is the size limitation for an Access database?
The Access 2.0 database has the size limitation of 1 gigabyte.
3) Between different data’s what are the types of relationship can be formed?
The table contains your data can build three types of relationship
- One-to-one relationships: A single data in one table is linked with a single data in another table.
- One-to-many relationship: A single data in a table is linked to several data’s in another table
- Many-to-many relationship: Where several data’s in one table is related to several other data’s in another table
4) Explain how you can build a relationship between the different databases in Access?
When you establish a relationship between two data, the first table for your data is referred as the primary key while the new field in another data is called a foreign key.
5) Name some of the file extension for MS Access?
Some of the file extension for MS Access are
- Access database.accdb
- Access project.adp
- Access project.mdw
- Access blank project template.adn
- Access workgroup.mdw
- Protected access database.accde
6) Explain what do you mean by queries?
Queries are the medium of manipulating the data to display in the form or report. Queries can join tables, update data, delete data, sort, calculate, filter, etc.
7) Explain what joins are and how you can open the database in Access?
Joins determines the relationship between tables in Access by stating the details of the relationship between two tables. You can create joins on the database tools tab or by opening a query in design view.
8) Mention how you can create a simple query in Access 2013?
- Open your database
- Go to -> create tab
- Click on the query wizard icon
- Select a query type
- From the pull-down menu choose the appropriate table
- Select the field you want in the query results
- To add information from additional tables, repeat steps 5 and 6
- Click on next
- Choose the type of results that you would like to produce
- Give your query a title
- Click on finish
9) Explain how you can export data in Excel format?
- From the toolbar click on the External Data tab
- Click Excel on the Export group
- It will open the export excel spreadsheet dialog box
- Specify the destination of the file name and format
- When you click OK, it will export the data to Excel spreadsheet
10) Explain how you can create a table using MAOL (Microsoft Access Object Library)?
To create a table using MAOL, you have to pronounce a variable of type object and then initialize it with the CreateTableDef() method of the recent database object. This technique takes argument as the name of the table.
11) Explain how you can create a form in Microsoft Access 2013?
Forms allow to insert data across multiple tables. There are different methods for creating forms but with Form Wizard method, you can always modify the form later using Design View. To create a form
- From the main menu, click on CREATE and then choose the FORM option
- It will create a new form in Layout view, based on the fields in the Customer table
- Scroll the drop down arrow under the View icon and select Form View. This how the user will see the form.
- On the form, you will see fields like CustomerId, First Name, Last Name and Date Created
- After filling of all details in the form, you can see the newly created record with data in the table
- Once you completed filling the field in the form, confirm that you click on the save button on the top of the ribbon
12) In MS Access 2013 what are the different ways you can enter data?
Different methods you can enter data in MS Access are
- Datasheet View
- SQL View
- Import from External Data (XML, Data Services, HTML etc.)
13) Explain the use of WHERE clause in MS Access SQL?
- In the WHERE clause of a SELECT statement, you use query criteria. For example, you want to find the address of a particular customer, but you only remember his last name that is Harrison. Instead of searching all the fields in the table, you use where clause to retrieve the address of the customer.
WHERE [LASTNAME] = ‘Harrison’
- You cannot link or join fields that have dissimilar data types. To link data from two different data sources, depending on values in fields that have different data types; you will create a WHERE clause that uses one field as a criteria for the other field, by using LIKE keyword.
14) Explain how we can do multi-join query in MS Access?
In Access, we can only join two tables, in order to join more tables you require to group the first join together by using parentheses, as if it was a newly derived table. After that, you can join another table to that group.
( ( Table 1
LEFT JOIN Table 2…
LEFT JOIN Table3…
LEFT JOIN Table4…
15) Explain how MS Access is better than MS SQL Server?
- With a small number of users, MS Access is easy to handle and suited for desktop use
- Easy compatibility and sharing compare to MS SQL Server
- SQL server is, usually, used by developers and in a bigger organization
- MS Access is cheaper than MS SQL
- MS Access requires less database administration
16) What things to be taken in consideration for lookup fields?
Look up fields: Ensure that you relate tables, links, etc. if you want to show look up values and not look up ids. For the display of lookup values, related table should be linked to the destination field.