8 handy tips for Microsoft Access 2010

Although perhaps not as widely used as Word or Excel, Access is, in some respects, the most powerful program in Microsoft Office. Access is a flexible and relatively simple way to create databases to store, manage and enter data. It even exceeds the capabilities of Excel. Access provides a simple yet robust way to manage your data – although "simple," of course, is a relative term. But these eight tips can make life for those of you still using Access 2010 simpler.

Access databases can range from a simple application like a village shop inventory tracker to an enterprise app that integrates with SQL servers and contains thousands of records. Access 2010 is a powerful program, and it introduced a number of new features, such as Web Browser Control, the Image Gallery, and several powerful developer enhancements. Access 2010 also features the Ribbon interface for quick access to commands and menu options. There's Backstage View as well, which gives users a centralised way to perform common database management tasks.

All these capabilities may make Access too high-end for the average IT tinkerer – but you don't have to be a programmer to design a robust database. Access includes a variety of templates to get you started in just a few clicks.

These tips can help, too. While they assume a basic level of knowledge, some of the tips are dead simple, and require little more than clicking a command on the ribbon. Some involve creating a couple lines of code. They all, however, are great ways to help you add some punch to your databases and get the most you can from Access. So, without further ado, let's move on to our eight tips...

Hide the ribbon

You've created a database application. You set a form to open when users access the database. You only want users to be able to view or enter data into the form, and not do anything else in the app. To restrict control to the app, or maybe simply for aesthetic reasons, you may want to hide the Ribbon when an app opens. Doing this requires adding a little bit of code. For example, to hide the ribbon on a form, right click on the form in "Design" view and the select "Form Properties." Click on the "Event" tab and go to "On Open." In the right column, look for a button with three dots; this opens up the Visual Basic Editor. Enter the following code in the VB window:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)

DoCmd.ShowToolbar "Ribbon", acToolbarNo

End Sub

Click the "Save" icon and then File > Close and Return to Microsoft Access.

Unhide the ribbon and toolbars

To unhide the ribbon on a form, as above, right click on the form in "Design" view and the select "Form Properties." Click on the "Event" tab and go to "On Open." In the right column, look for a button with three dots; this opens up the Visual Basic Editor. Enter the following code in the VB window:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)

DoCmd.ShowToolbar "Ribbon", acToolbarYes

End Sub

Click the "Save" icon and then File > Close and Return to Microsoft Access.

Hide the navigation panel

You may not only want to hide ribbons and toolbars, but also the navigation pane that lets you browse all database objects. To do so, go into the BackStage View of the database, and click on Options > Current Database. Under "Navigation," clear the checkmark next to "Display Navigation Pane."

Quickly share reports with non-Access users

Want to share a report with someone who does not have access to your application? It's easy to do in Access. From the Navigation Pane (shown above), right click on the report you want to share and click "Export." Select the file format you want to save in – Excel, PDF, HTML or another format.

What's great in Access 2010 is that if you have a report you want to send someone on a regular basis, you can save the export steps and quickly export the report without using the Export Wizard. You can also create an Outlook Task from within Access 2010 that will not only remind you when it's time to export the report, but will also create a Run Export button in Outlook to perform the Access export. Users are given the option to create this button whenever an export task is created.

It's easy to add an image into the Image Gallery and then share it across multiple reports or forms. To add an image to the gallery, go to the form or report you want the image to appear in and switch to Design view. Click on "Insert Image" from the Controls group in the Ribbon and browse your folders for the image. Then, select the image and then click on the form or report to add it. Doing so also adds the image to the Image Gallery as well as to the current form or report, so that it will be available for use across all reports and forms.

Use Smart Tags

Smart Tags are a great way to add additional information in a database that would usually require a lookup on the web. For instance, I can add a Smart Tag to my form populated with a list of company names. This tag will pull up the stock ticker symbol and other financial information about that company. Smart Tags can also be used to schedule meetings, add contacts to instant messaging, and enable a slew of other conveniences all from within an Access database. To add a Smart Tag, go into the Design View of the form, report, or data access page. Right click on the field you want to add the tag to and click on Properties. Under the Data tab on the Property Sheet, click on the button to the left of the Smart Tag row to bring up the Smart/Action tag window.

Use Web Browser Control to display web pages on a form

The Web Browser Control is a cool way to place web data onto forms in your database app. For instance, if you have a database with a list of products, you can add a control to get information on the products' vendors. Or you can create a link to a mapping site to associate with addresses. To add a Web Browser Control, open up a form in Design view. From the Controls grouping of icons on the Ribbon, select the Web Browser Control icon (as shown in the screenshot above). Click the area on the form where you want to add the control. In the "Insert Hyperlink" window enter the URL of the website you want the control to display. As an example, I created a link to Google Maps on a list of suppliers, to quickly look up their locations. There's actually quite a bit one can do with Web Browser Controls – including having the web content dynamically update based on data displayed in a form – but that involves expression building, which is beyond the scope of this article. Microsoft offers some solid additional information on working with Web Browser Controls here.

Completely delete a database

There are times when you may want to just delete a database and start from scratch (especially if you are new to Access). To delete a database, go to Windows Explorer and delete the Access file. By default, Access databases are stored in the Documents library in Windows and are saved as .accdb files. However, even after you've deleted the file, you may still see the database name show up in the Access file listing. To get rid of the listing you have to delete it from the registry. Type in "regedit" in the Search field in Windows 7 (or at the Start screen in Windows 8). Click open the following:

HKEY_CURRENT_USER→Software→Microsoft→Office→14→Access→File MRU

Delete the key that has the name of the database you want to remove from the Access file list (also called MRU).