Stand: 07.01.2004 

Copyright by Cramsession
MOUS Access 2000

Creating a database

What is Access?

Access is a product from Microsoft that allows you to create or manipulate Databases. (Database is explained below) The Best way to become familiar with this product is to of course have as much hands on experience with it as possible. If you are not familiar with the product, please visit these Links for detailed information on the Access Product.

Note: These links come from Microsoft’s Technet Web Site and are Highly Informative and I recommend that you look at them:


MS Access 2000 Overview

Access 2000 Product Enhancements

Access 2000 Tips and Tricks

What is a database?


bulletA database is a collection of data that is associated with a topic
bulletA database is an organized collection of related information

Note: Product Overview from Microsoft


Types of Databases

The two main types of databases are Flat and Relational:
bulletFlat databases systems are jammed together into one single table.
bulletRelational databases are more complex and hence are good at cutting down on storage space or duplicated material – the database is split into multiple tables and each table holds a portion of the total data.
bulletTo read a full article on Technet about Relational Database design: Click here


Opening a New Database

Why would you want to make a database?

You would want to make a database for Consolidated Storage that is easy to get to, work on, secure and edit. A database is a collection of your information, so it makes sense to have it all in one place where you can work on it. Now that we know “why” we would make a database, lets learn how to make one with Microsoft’s Access product (part of the Office 2000 Premium Suite).


To first open Access 2000, double click on this icon found on the MS Office toolbar, the Desktop or in the Start menu.


When you open Access 2000, you are confronted with options:

The Options you see below are to make a brand new database (from A series of templates) or to open a pre-existing one from somewhere on your machine or on the network.



You can select “Access database wizards, pages and Projects” to go through Wizards or you can simply open a Blank or existing database.

The Wizards option will walk you through the customization of your database. For testing purposes, you should be familiar with both. Let’s make one manually:

Choose to open a Blank database and name it Cramsession.mdb.

Note: The standard extension for Access databases is mdb. (*.mdb)

MDB stands for Microsoft Database.


Table Creation

Open a Preexisting table or create a new one

If you want to create a table, you would need to open a new database or a pre-existing one.

Open the Cramsession.mdb we just made:


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Once you open the database, you can create a table three ways.

You can create a table using:
bulletA Wizard
bulletDesign View
bulletBy entering data

As seen below, you can select one of the above options to create a table. Always use the Wizard if you are unsure what exactly it is you want to do. Make sure you know how to do both for exam purposes.


If your browser doesn't support inline frames click HERE to view the full-sized graphic.

Wizard View


The Wizard view is of course the easiest way to go. Let’s look below and see how it is used:

Simply follow the directions and the prompts. You can see that, by choosing sample fields, you can then enter them into your new table. Remember, a table is where you are going to begin to enter your data to create content for your new database (in this case, Cramsession.mdb).


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Note: The table is the easiest way to go for beginners but for the MOUS test, you may need to be familiar with what is most efficient for you. The MOUS test is timed so the quickest, most efficient way is the way you want to work. There are multiple ways to do things.

Design View


Design view is a little more difficult, but has the same basic functionality. Make sure you know both. On the bottom of the dialog box below is a little pane that will help you along. Pay attention to what you need to do, as well as use help and the office assistant iyou are completely lost. The Access help files are pretty informative too.

If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Make Sure you practice how to create and modify tables.


Use the Office Assistant

Although it may seem annoying at times, the Office Assistant can actually be helpful when you are in a jam. Nothing will be more helpful to you than hands on knowledge and maybe TechNet, but when you’re in a tough spot and unsure how to proceed, use the assistant.

So what should you do if you do not see the assistant?

How to find the assistant:

The Office assistant is a tool that helps you navigate through tasks or get help for whatever you ask of it. To get the Assistant to show up (if it is hiding) you can go to:



Once the Assistant is out, you can find options by double clicking on the assistant itself. (You can hide the assistant or select options.)

Some of the options you can use are to change the assistant itself into some other animated character. You can also choose to hide the assistant, and you can really customize what the assistant is willing to help you with and in how much depth.



Note: Know how to put a query into the assistant to get additional help on a topic.


Select an object using the Objects Bar

The objects toolbar will give you the options to edit Tables, Queries, Forms, Reports, Pages, Macros, and Modules. This tool bar is very similar to the one you will see in Outlook. This is where you can get to Tables, Queries, Forms, Reports, Pages, Macros, and Modules. Use this toolbar to navigate through options in Access. Each one is described below:



Note: Be comfortable navigating with this toolbar.


Print database objects

Please note that printing in Access is not an easy task. Each view mentioned above has a different way to print. You actually have to enter each view and then the option to print becomes enabled. Make sure you know how to get in to each view:



Go to the Objects toolbar => Tables => Open the Table. Once you have opened it, in either view (design too), the print option will become enabled.



Go to the Objects toolbar => Forms => Open a form => Print.




Same as above.

Go to the Objects toolbar => Reports => Open a Report => Print.



Go to the Objects toolbar => Queries.

You can print a query with a right click menu:



Print Database Relationships

NOTE: To print relationships you need to be within relationships:



See also Relationship Creation within this Cramsession.


Set primary keys

Visit TechNet’s How to Create Keys

It is good practice to assign one key to be the primary key used in the database.

What are Primary keys used for?

They are used is used to link the different records in the tables together.

Setting a Primary Key can be done by going into the appropriate field and right clicking on it. It will give you the Primary Key option menu.


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Next, set the field as the Primary Key after you selected it.



You will see the key show up in the left-hand side of the field.

You can also click the PRIMARY KEY button on the toolbar.


Modify field properties


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


You can modify Field Properties on the bottom of the table. You can also right click the appropriate field to produce a Properties menu that will allow you to change table properties:



To get this dialog box, right click the field and select properties from the menu.


Modify tables using Design View

When Modifying Tables you should use the design view, since it will be a lot easier than using the datasheet view. With the Design view, you are in full control and disaster is less likely to occur to your database.

Open the table in Design View: You can use the following Icon:



Alternatively, you can use the toolbar icon:


Then, the table will open. Now, you can now modify appropriate fields.


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Other options are to right click on the table you want to work with (as shown above) and then select Design View from the options stated earlier. You can Highlight the row you would like to insert a new field into and select:

Insert => Rows



Now you can add the Field name to the new row.

Remember, this is the safest way to edit: always use Design view if you can.

Note: Within Field Properties (On the bottom left of the open dialog box) there are options for help. This includes being aware of the 64 Character limitations and using the F1 option for “help”.


Use the Lookup Wizard

To access the Wizard go to the table you are working on => Insert => Lookup Field.



Once selected, you can create a lookup column:


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Use the Input Mask wizard

What is the Input Mask Wizard used for?

An Input mask is something you use to filter certain data into a field. If they are then paired with validations, you have more protection for your fields in your table. This protects against having wrong information in your database.

Think of the Mask as a simple series of characters that will tell Access what kind of data it should expect in that field.

Lets see how to make one:

Open the table in Design View.



Select what you want to work with and go to the bottom of the view when you will see the Input Mask area (shown above surrounded by red rectangular box).



Note: Practice with this feature.


Create a form with the Form Wizard

To create a form with the Form Wizard go to the Objects menu => Forms.



Next the Wizard will appear: Start adding what you need.

If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Select the Layout and Finish.


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Use the Office Clipboard

What exactly is the clipboard?

The clipboard is a section of memory that holds “saved” information. The clipboard will be used mostly when you cut and paste or copy and paste.

This is how you view what is currently in the Clipboard:

To access the Clipboard, go to View => Toolbars => Clipboard.



On selected you can view the Clipboard contents:



Delete records from a table

To delete a record, hit the Del key.

Alternatively, you can go through the menus: Edit => Delete.



Find a record

To Find a record, use CTRL + F.

Alternatively, you can go through the menus: Edit => Find.



Sort records

To Sort: Go to Records => Sort => {Ascending} { Descending}.


Apply and remove filters

Know how to Filter By Form or By Selection.


How to set up a Filter

Open a Table => Records => Filter => then select an option.


If your browser doesn't support inline frames click HERE to view the full-sized graphic.


Filtering by Form will allow you to have the options below:



Specify criteria in a query

Go to the Objects Menu => Queries.



You can edit the Query => Specify Criteria.



You can also use the “Expression Builder” – this can be found by right clicking the Criteria, and selecting “Build”.



Establish relationships

To establish relationships, go to Tools => Relationships.



Once you select Relationships, you can add tables and queries.


Enforce referential integrity

To enforce referential integrity you need to be in relationship view (from above). Then you need to go to Relationships => Edit Relationship.



You then need to modify and add data => then select the Enforce Referential Integrity option (show below, surrounded by red rectangular box).


Create a report with the Report Wizard

To create a report with the Report Wizard, go to the Objects menu => Reports.



Now, select Create report using Wizard (very similar to the Forms Wizard).



Once you select what you want, click Next.



Plan the Layout of your report:



The next few options allow you to title and view your report.


Preview and print a report

When viewing the report, simply select the Print Preview option located on a toolbar or within the File menu:



Your report can be viewed, previewed a few ways, and printed.


Use the Control Toolbox to add controls

How do you add controls?

You can use the toolbox, but you have to first activate it:

You can find the toolbox by right clicking the area below the Title bar to produce the menu you see below. The title bar is marked in Red and the Toolbox option is “Checked” off which means it is activated.



If you do not see it there, you need to select “customize”, and when you do, you will see the next dialog box:



Now you can select the Toolbox option seen above. Once you select it, you can view the new Toolbar seen below:



When you click on the little arrow on the far right of the tool bar:



the following drop down box is selected (as seen below) and you can now add and remove controls to the toolbar.

Toolbox buttons used to create controls:



These can be added by using the toolbar for the Toolbox.


Import data to a new table

You can import quite simply by right-clicking the Objects men and choosing Import:



Or you can go File menu => Get External Data => Import.



Save a table, query, and form as a Web page

Why would you want to save something as a web page?

Saving something as a web page will create a HTML (Hypertext Markup Language) document that will allow you to post it on the web with greater ease, or have the form within a web-enabled database structure. I have seen entire databases (like Cisco’s CiscoWorks RME Resource Manager Essentials) in complete HTML format.

How do you set it up?

Go to the Objects menu => Select Pages, and then select “Create a data access page in Design View”.



When you create and save the document, it will be in HTML.

When you are practicing, open the new document and view the results. Make sure you know how to create one for the MOUS exam.


Add Hyperlinks

Why add Hyperlinks and what are they?

Currently, everything is going to the web. Web-enabling a database is becoming a very common occurrence. Note that adding a hyperlink is only redirecting you to another location. If you click on the Hyperlinks within the “Links” section, you can understand that when you click on the Link called “” it will automatically take you to the MOUS web site.

How do you add a hyperlink?

To add a hyperlink to your table, go to Design View and select the line you want to add a hyperlink to. In the Data Type area drop down box, select Hyperlink.



Make sure you save the table.



Compact and Repair a database

You must understand that a database of any kind will always need some kind of maintenance. I have seen many databases become corrupt by simple day-to-day, normal use. Once an inconsistency occurs or you have a problem, you need to run repairs on the database.

Within Access, there are simple tools that will do the repairs for you. Make sure you know where to find them:

Within the table go to Tools => Database Utilities.



Selecting Compact and Repair Database will do the compaction and the repairs automatically.