Access 2010 Training

Microsoft Access is a popular database program that is part of the Microsoft Office suite of products. This LIS training document deals with Access2010, the latest version of Access.

Goals

Upon completion of this 1-on-1 training, you will be able to:

Enhancements in Access 2010

  • File Menu Returns: The Windows button of Office 2007 has been replaced with an updated File menu, which, when clicked, takes you to the new Backstage View. See URL for details.
  • Updated Ribbon: It is now possible to customize the Ribbon to suit your personal needs and preferences.
  • Data Type Gallery: The Add Field button of Access 2010 has been replaced with the “Data Type gallery”. This allows you to define data types as collections of fields (such as an Address having a Street, a Town, etc.) and then add an entire such collection with one click.
  • Themes: You can now use the same desktop themes as in Word and Excel to customize the colors and styles of your database. This replaces the AutoFormat feature of Access 2007.
  • Data Macros: Developers can now use data macros to attach logic to record/table events (similar to an SQL trigger). This can be used for a wide variety of purposes, from keeping a log of record changes to automatically sending emails.
  • Sharepoint Integration: Access 2010 is integrated with Sharepoint, so you can upload your databases to Sharepoint and then publish them to the web.
  • Navigation forms: Professional-looking navigation forms for your reports are now easy to create via drag-and-drop, and you don’t have to worry about much of the code or logic behind them.
  • Templates: You can save frequently used Access objects or fields as Application Parts templates to use across your databases. You can also upload useful templates to the web so that others can use them, and you can download templates that others have posted.

For additional information on Office 2010, please visit Lynda.com. If you are interested in access to Lynda.com please contact the Technology Help Desk at helpdesk@luther.edu or 563-387-1000.

Training

Before you Begin

Access databases are wonderful for storing, viewing, and retrieving information. In order to make your database easier for you to use now and in the future, spend a little time up front thinking about what you want to store and how you want to display the information. In this example, we are going to create a simple database to store contact information. It will contain first name, last name, street, city, state, zip, phone number, and email address for each contact person. We are splitting first name from last name so that we can sort by either first or last name and can easily use just the first name (or just the last name) whenever we desire.

Create a Database

1. First, open Microsoft Access by clicking Start > All Programs > Applications > Microsoft Office Access 2010.

Note: Access may be located in a different place in your Start menu.

When you open Access, you will see the screen below from which you can either create a new database or open an existing database.

Access 2010 - 1

2. Let’s create a new database without using a template. Click on the Blank Database icon in the top middle of the screen. Notice that the right hand side of the screen changes and is now asking for a File Name. A default file name, “Database2.accdb” in this case, is entered for you. Change that to “Contacts.accdb” since we will be creating a database to store names and addresses of people. Once your screen appears as shown below, click the Create button.

Access 2010 - 2

3. Your database has now been created and is ready for use. Access expects that the first thing you will want to do with your database is create a table, and so it displays a window that is waiting for the table to be defined. See the screen below.

Access 2010 - 3

Create a Table

Now that we have a database, the next step is to create a table to hold the contact information. Since we have already decided on the fields we want in the database, we can begin entering them, one by one, into Access.

1. ID Field – Access enters an ID field for you. Each table in Access should have what is called a “primary key”, or a field that is unique for each record. This helps the database to perform better and prevents duplicate records. In our case, we could have two contacts with the same first name, or the same last name, or the same value in any of the fields we are using so we don’t have a unique field. We’ll use the default ID field that Access provides.

2. Other Fields – Enter the field names by clicking on the box that reads Click to Add, then selecting Text, entering the field name and then pressing the Enter key. Use an uppercase letter for the first letter in each word part and no separation between parts. For example, use “FirstName” rather than “first name”. If you really want the separation, use an underscore (_) rather than a space because it will make life easier for you down the road. The fields to enter are: FirstName, LastName, Street, City, State, Zip, Phone, and Email. To change the text, you can right click on the downward arrow and select Rename, or you can double click on the field. When you are finished, your screen will appear as shown below:

Access 2010 - 4

3. Save – Now that all of the fields have been entered, save the table by clicking the File tab and then selecting Save. You can also click on the Save icon on the quick toolbar. You will be prompted to enter a name for the table. The default name of “Table1” will be suggested, but let’s change it to “ContactInformation” so that we know what type of information is stored in the table. Use the same formatting rules as for fields. Click OK.

Access 2010 - 5

4. Design View – To view the table in design mode rather than datasheet mode which we are currently using, click on the View button in the top left corner or the same icon found in the very bottom right corner of the screen. Go ahead and click on the other icons found in the bottom right corner to see the PivotTable and PivotChart views as well.

Access 2010 - 6
p=. Access 2010 - 7

5. Datatypes – From design view you can see all of the fields more easily and can change the datatype as well. It’s best to change text fields to be of the appropriate datatype (e.g. Date/Time for dates) and to be only as big as needed (the default is 255 characters in length). For the purposes of this exercise, let’s leave them as text.

Access 2010 - 10

Enter Data into the Table

Before we create and print a report, let’s add some data to our database so that we have something interesting to view on the report. If you want, you can enter the same data that is shown following step 6 of this section.

1. Go to the Datasheet View by clicking on the icon.

2. Since the ID field is a number that is automatically incremented for us, we don’t want to enter anything into that field.

3. To prepare to enter a first name, tab to the next field, or place your cursor in the FirstName field and click.

4. Type a name into the FirstName field. Notice that when you type the first character, a few things happen:

1. A new blank record is created below the one you are typing into,

2. the asterisk Access 2010 - 11 is now on that line and the line on which you are typing has a pencil icon Access 2010 - 12 to indicate that editing is in progress,

3. and the ID field changes from Access 2010 - 13 to Access 2010 - 14 .

5. When you are done typing the first name, tab to the next field and enter the last name. When you tab off of the last field (Email), you are taken to a new row in which you can enter information for a second contact. Also notice that the pencil now moves to this row and that row one has a blank where it previously had the asterisk. This means the first record is now saved in the table. While the pencil is still on the row, you can undo the entry and it will not be saved in the table. This can be done by pressing the undo icon or pressing the ESC key.

6. Continue until you have a few records in the table. When you are finished, your screen will appear as shown below.

Access 2010 - 15

Create a Report

It’s fast and easy to create a simple report for your data using the Create tab.

Access 2010 - 16

1. To create a basic report of your information, click on the Report icon. You can also select Labels, Blank Report, or Report Wizard for additional reporting options, but for the purposes of this training, the basic report is fine and will provide you the following report as an additional tab next to your table.

Access 2010 - 17

2. You’ll notice that the information doesn’t all appear on the page and that the Format tab now displays on the ribbon.

Access 2010 - 18

3. To clean up the report to appear as shown below and fit on one page, do the following:

Access 2010 - 19

1. Under the Home tab, in the views section, click on the bottom half of the view icon, and select Layout View. Or right click on the ContactInformation text and select Layout View. This will enable you to edit the report.

2. Double click on the ContactInformation text and notice that you can insert a space between the words or change the report title however you like.

3. Click on the column header ID, right click, and select Delete Column to remove that information from displaying on the report.

4. Click on the FirstName column header, move your mouse to the right edge of the box that appears until it is a double-arrow, click and drag the box so it is narrower. Repeat this for the other fields so that the information fits on one page.

4. To save the report, click the File tab and then selecting Save. You can instead click on the Save icon on the quick toolbar. You will be prompted to enter a name for the report. The default name of “ContactInformation” will be suggested since that is the name of the table on which the report is based. That name is fine so click OK.

Access 2010 - 20

Print

You can print tables, reports, and other information from within Access.

  • To print a report, run the report to the screen by double clicking on the name of the report on the left side of the screen. Then click the File tab, select Print and click on print. You can also use the Ctrl+P keyboard shortcut.

Access 2010 - 21

  • To print a table, display the data on the screen as you would like it to be printed by double clicking on the name of the table on the left side of the screen and then selecting the appropriate view (Datasheet, PivotTable, or PivotChart). Then click the File tab and click Print. You can also use the Ctrl+P keyboard shortcut.

Access 2010 - 22

  • It is good practice to Print Preview any table, report, or other item first BEFORE sending it to the printer to assure it appears as you expect. Do this by clicking the File tab, Clicking on Print and then selecting Print Preview. Notice how the page numbering displays at the bottom of the page, where you want it, when doing the print preview.

Access 2010 - 23

Maintain your Database

There are two commands that you will want to run regularly to keep your database working well and backed up. Both of these commands are found under the File tab.

  • Compact and Repair Database – If your database is small, when you select this option you won’t see any window appear. However, the command is reducing the file size by getting rid of unused space within the database. It is also looking for potential errors and correcting them if found. It is recommended that you run this command at least once a month. It is best to run this command before backing up your database so that the back up is smaller. This command is located under the File tab, within the Info page.
  • Back Up Database – When you select this option, you are presented with a window that essentially is asking where you would like to keep a backup of your database. It is recommended that you back up to a network share and that you accept the default name that is provided by Access. Simply click Save & Publish under the File tab. Under File Types select Save Database As, then select Back Up Database, and click Save As. Notice that the date is appended to the database name which means a new backup will be created each time you run this command. You will want to remove old copies periodically.

Access 2010 - 24

Work with Previous Versions of Access

Access 2007 and Access 2010 use the same file format. However, there are enhancements in Access 2010 that are not available in Access 2007. For detailed information on the differences and the changes that can be made so an Access 2010 database can be used in Access 2007, see Backward Compatibility between Access 2010 and Access 2007.

If you need to share a database with someone who does not have Access 2007/2010, you will need to save your database in the older file format. To save your database as an Access 2000 database, click Save & Publish under the File tab. Under File Types select Save Database As, then select Access 2000 Database, and click Save As. A window will appear asking for the location and name you would like to use. If you aren’t certain whether you should copy the database or change the existing database to an Access 2000 database, contact either the person who you are working with or the Technology Help Desk.

Access 2010 - 25

Similarly, if you open a database that is from an older version of Access, such as Access 2000, you may receive a message. If you do, and you aren’t certain how you should respond, click Cancel and then contact either the person who created the older database or the Technology Help Desk.

Although there is an add-on for older versions of Word, Excel, and PowerPoint to allow users to open Office 2010 documents, there is no such add-on available for older versions of Access.

Customize the Ribbon

Access and other 2010 Office programs give you the option to customize the Ribbon in order to have quick access to commands you use most often, such as save, or cut. Now let’s say you want to have the Quick print command right under your eyes for easy access. To do this:

  1. Click on the File tab, then click Options, and select Customize Ribbon.
  2. In the box on the right labeled Customize the Ribbon assure Main Tabs is selected.
  3. The box below will now read Main Tabs. Select Home, then click on New Group at the bottom of the page.
  4. The title New Group (Custom) immediately pops under the tab Home. You can click on Rename and call it whatever you want.
  5. Next go to the box on the left, select Quick Print and click Add.
  6. Then click on OK at the bottom of the Access Options page. You now have a new group under your Home tab.

Access 2010 - 26

You can always go back later and delete your created tab by selecting it from the Customize the Ribbon box, and click on Remove. Note: your created groups or tabs will have (custom) next to them, although the word custom does not appear on the ribbon.

Additional Resources

Review the Help within Access 2010

Access 2010 has a built-in help system. Hit the F1 key, or click the question mark on the upper right part of the window to access it.

  • You can select a topic by clicking on the text, such as Getting started with Access 2010 .
  • You can also type a word or phrase into the box at the top of the window, click the Search button, and then select the topic of interest.

Review Online Microsoft Resources

Consult Lynda.com

Lynda.com is a provider of web-based software training videos and resources. If you are interested in access to Lynda.com please contact the Technology Help Desk at helpdesk@luther.edu or 563-387-1000.

Consult Reference Books

Many books are available for check-out at the Technology Help Desk, including, but not limited to, the following. Since Access 2007 and Access 2010 are very similar, Access 2007 books are still useful.

Review the Technology Help Desk web site

Since Access 2007 and Access 2010 are very similar, Access 2007 information is still useful.

Contact the Technology Help Desk