Access might be a desktop database, but it can still be difficult to manage. These tips will help you work more effectively with less effort.
Most of us lack serious Access development skills, so creating and maintaining an Access database can be tricky. In fact, you might prefer handing the project off to an expert. If you can't hire someone else, working faster and smarter in Access is essential. In this article, I'll show you 10 simple ways to boost your Access productivity.
These tips work in the Ribbon versions; most will work in earlier versions, including menu versions. There's no demonstration file because you won't need one.
Note: This article is also available in the free PDF 50 time-saving tips to speed your work in Microsoft Office.
1: Access data
If you have existing data in a Word or Excel document, it's easy to get that data into an Access table. The easiest way is to copy and paste. Simply copy the data in the source document, open your Access database, and press Ctrl+V to paste the data into a new table. If you copy from Excel, Access uses the sheet name for the new table's name. If copying from a Word table, Access prompts you for a table name and offers more options, as shown in Figure A. When you're copying raw content (not a table) from Word, create the table first and paste the data directly into it.
It's easy to paste data into Access from Excel and Word.
Copying data from Access to Excel or Word is just as easy; simply select the records or the entire table and copy as you normally would.
2: Create a task
When exporting data from Access, you can create an Outlook task. Let's look at a simple example for doing so while exporting data to an Excel sheet:
- Select the (Access) table or records you want to export.
- Click the External Data tab and click Excel in the Export group.
- In the resulting dialog (Figure B), click the Export Data With Formatting And Layout option and click OK.
- In the next pane, check the Save Export Steps; doing so displays new options.
- Check the Create Outlook Task option (Figure C) and click Save Export.
Identify the export destination.
Create an Outlook task for the saved export.
When Outlook opens the new Task window, set a recurrence or reminder, and then click Save & Close. You'll find the new task in your Outlook Tasks list (Figure D).
With the new task in Outlook, you won't forget a scheduled export task.
3: Perform a datasheet sort
If you're lucky, you have a custom database that satisfies all your sorting and filtering needs with a quick click. The rest of us can rely on datasheet sorting options. However, use these options with caution--working directly with the raw data is never the best route, but it's quick!
To access sorting options, open the table in Datasheet View and click the Home tab. You have several flexible tools in the Sort & Filter group. Click inside any field and then click Ascending or Descending. To sort multiple fields, hold down the Shift key and click the fields to add them to the selection. As you can see in Figure E, a few clicks sorts first by vendor, then by invoice amount, and then by invoice date. Click Remove Sort to return the data to its original order at any time.
Sort by multiple fields.
Use Selection to filter data based on the data. Simply select a value and then choose an option from the Selection dropdown list (Figure F).
This will find all the invoices that are $1,000 or more.
SEE: Build your Excel skills with these 10 power tips (TechRepublic PDF)
4: Investigate dependencies
A relational database comprises many related objects. Before you start modifying things, review dependencies so you don't accidentally affect an existing object--usually this happens when you change a field's datatype or an object's name, or even delete an object that's part of a dependency. You'll save time spent fixing things after the fact by checking dependencies before you make changes.
To see an object's dependency, select that object in the Navigation pane, click the Database Tools tab, and then click Object Dependencies in the Relationships group. Using the Object Dependencies pane, you can quickly see all relationships with the selected object. For example, the Vendor table shown in Figure G depends on the VendorLookup table. This relationship should be considered if you modify either table.
A quick glance at existing dependencies can save you a lot of trouble.
5: Add multiple fields to the design grid
To add fields to the query design grid, you probably double-click, drag, or choose from the grid's dropdown to add fields one at a time. But there are several ways to add more than one field at the same time:
- Hold down the Shift key and click the first and last fields you want to add and then drag the contiguous block of fields to the grid.
- Hold down the Ctrl key and click fields to create a noncontiguous block of fields you can drag to the grid.
- Double-click the table's title bar to automatically select all the fields and drag them to the grid (Figure H).
Double-click the title bar to select all the fields in the table.
6: Use Filter By Form
A customized database can be time-consuming and expensive to build and maintain, so take advantage of built-in features, such as Filter By Form. With the form open, choose Filter By Form from the Advanced dropdown in the Sort & Filter group. Access will display a blank form resembling the original. If any of the controls contains a search string (from a previous task), delete it. Select filtering values from the control dropdowns and click Toggle Filter (in the Sort & Filter group. As you can see in Figure I, the navigation toolbar at the bottom of the form tells you how many records match your filter. Use the toolbar to browse through them. To remove the filter, click Toggle Filter.
Review the matching records.
Other filtering options are available by right-clicking the control. With easy-to-implement tools such as this, you'll have a database up and running quickly. You can add customized filtering forms later.
7: Set these time-saving properties
To create a more user-friendly interface, I recommend setting the following two properties for all forms, unless you have a specific reason not to:
- AutoCenter set to Yes opens the form in the center of the screen regardless of where you last left it. This setting works in newer versions after setting the default Document Window Options property to Overlapping Windows. (Look in the Current Database options in the Application Options section.)
- Resize set to Yes opens your form to its designed size and not the size last used.
Both settings reduce the time needed to readjust the form's position and size when those two qualities matter, and they often do.
SEE: How to import Access web app data into an Excel workbook for reporting (TechRepublic)
8: Let Access help
Access provides two utilities that can help you avoid problems and improve performance:
- Analyze Performance lists potential problems and offers suggestions for correcting them. You can analyze everything or narrow the list to specific objects. This tool is particularly helpful when modifying an existing database.
- Analyze Table helps you normalize your data; a properly normalized database performs better and is easier to modify and maintain.
You'll find these tools on the Database Tools tab in the Analyze group. Run both tools before building forms and reports on new tables to avoid time spent repairing mishaps.
9: Use templates
Considering how complex an Access database can be, finding the right template might seem impossible. However, it's quicker to tweak a template than to start from scratch. When you launch Access, it displays the backstage, where you can open an existing database, create a new database, or search for existing templates. It won't take long to look at what's available. Click one of the tiles shown in Figure J, click a link to see even more, or enter a search string.
Save time by starting with a template.
10: Customize the Navigation Pane
By default, the Navigation Pane groups objects, but it's flexible enough to allow for a bit of customization. During the development stages, this option can save you a bit of time by allowing you to:
- Group objects instead of creating a temporary switchboard form.
- Hide objects.
- Group objects you use more frequently than others.
The process is simple: Right-click the Navigation Pane and choose Navigation Options. In the resulting dialog, add categories, add groups to those categories (Figure K), and then return to Access. In the Navigation Pane, drag objects from the Unassigned Objects group to your custom groups.
Custom groups can streamline the Navigation Pane.
To hide everything but the custom groups, open the Navigation Options pane, choose the custom category, and then uncheck the Unassigned Objects option. The result is a Navigation Page with fewer objects to distract users.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- Microsoft Office Specialist Certification Training (TechRepublic Academy)
- Microsoft broadens Office 365 through the Windows Store test program (ZDNet)
- How to use a conditional format to highlight subtotal rows in Excel (TechRepublic)
- Two creative ways to use Word watermarks (TechRepublic)