Microsoft Access Cheat Sheet: Tips and Shortcuts

Microsoft Access is a database management system that allows you to create and process data in a database, which has rapidly become the database of choice for Windows based database development. If you are self taught and have not had any Microsoft Access training you are probably unaware of the many shortcuts available within Access. This article aims to provide some useful tips and shortcuts to help you when using Access.

Tips
Here are a few simple tips to make using Access easier:

Importing a Microsoft Word table
To import a Microsoft Word table into Access follow the steps described below:

1. In Word select the table you wish to import, click on the Table menu, select Convert and click Table to Text
   
2. Copy your selected table into a new document and save it as a text file
   
3. In Access use the Get External Data command to import the contents of your newly created text file
   

Use wildcards in the search text box
You can use wildcards such as question mark (?) and asterisk (*) in the search text box. The question mark will match any single character, for example, s?t will find "set" and "sat". The asterisk will match any number of characters, for example, s*d will find "sad", "started" and "standard".

Display current date and time in a column of any width
The function =NOW() will return the current date and time, but when entered in a column which is not wide enough to display the returned value the cell displays ###. You could get round this by widening the column but this is not always convenient, the column may already be at its maximum width to be able to see all the columns on one screen or it may force you to change your print settings. The solution is to use the TEXT function to convert the value returned by the NOW function to text. Text, as opposed to a number, will stretch into the next column. There are two arguments in the TEXT function. The first is the value to convert to text, the second is the format to show it in. Custom number codes in quotation marks should be used to specify the format. For example, TEXT(NOW(),"ddd dd/mm/yy hh:mm") will return the current date and time in the format MON 09/10/06 11:38.

Archiving data
If you can't bear the thought of deleting obsolete records you can instead flag them with a Yes/No field. You can then use the value in this field to determine whether to show records. For example, you could only display records with a Yes value in your queries, reports, forms and so on.

Automatically compact and repair
To ensure optimum performance you should compact and repair your Microsoft Access files regularly. In the Tools menu select Database Utilities and then click on Compact and Repair Database. You can simplify the process and get Access to do this for you automatically by completing the following steps:

1. Open the Access database or project you want Access to compact automatically
   
2. On the Tools menu select Options
   
3. Click on the General tab
   
4. Select the Compact on Close check box
   

Shortcuts
It is true that the keyboard is faster than the mouse for a number of commonly used tasks because you don't have to go through the point, select and click sequence. Here is a list of speedy keyboard shortcuts that you can use to save you time when working with your Access databases.

Moving records
Add a new record CTRL++
Cut current line and copy to clipboard CTRL+Y
Delete current record CTRL+-
Insert new line CTRL+ENTER

Moving through database
Move to beginning of multiple line field CTRL+HOME
Move to end of multiple line field CTRL+END
Move to first field in first record (Navigation mode) CTRL+HOME
Move to last field in last record (Navigation mode) CTRL+END
Move to page number/record number box F5
Next window CTRL+F6

Find and replace
Find CTRL+F
Find next SHIFT+F4
Find previous SHIFT+F3
Goto CTRL+G
Replace CTRL+H

Misc
Close CTRL+W
Copy CTRL+C
Cut CTRL+X
Edit/navigation mode (toggle) F2
File/Save As F12
Save current record SHIFT+ENTER

More tips
For some more tips when using Microsoft Access, why not visit the hints and tips page.