Monthly Newsletter and much more

5 Star Support - Free Computer Help and Technical Support

 

5 Star Support Home
Computer Help Forums
Computer Tutorials
Tips, Tricks & Tweaks
Troubleshooting FAQ
 
Google

Microsoft Access Tips

Report of Table Relationships in Access 2002 Change Toolbars Control Width
Back Up Before Compacting and Repairing Convert Your Format for Better Performance
Create new fields from Access's Datasheet view Opening Access objects with disposable toolbar icons
Arranging VBA Windows Copy Access data to new records quickly
Useful Keyboard Shortcuts Rearranging Buttons on a Toolbar
Quick Selections Query Types
Missing typos in Access tables Automatically Compact On Close
View related fields in an Access query easily Simplify grouping on dates in Access reports
View or Refresh Links

Change the default language setting

Changing Access Find Option Access Keyboard Shortcuts
 

Use the information below at your own risk.  See "Terms of Use" on our home page

Report of Table Relationships in Access 2002

[Access 2002]

When building a database, the design and relationships of your tables is very important. Access 2002 will help you with this by generating a graphical representation of table relationships in your database. Here's how:

  1. Go to the Tools menu, select Relationships.
  2. Go to the File menu, select Print Relationships.

A report is generated that displays the relationships as they appear in the Relationships window.

  1. If you would like, you can Save or Print the report and then close the report window.
  2. Close the Relationships window.

[Top]

Change Toolbars Control Width

[Access 2000]

Have you ever tried squeezing another button on to your toolbar wishing for a larger Access container window? Well, you can't enlarge the window, but you can control the size of the buttons, text boxes and pull-down menu's. Here's how:

  1. Right click any toolbar and select Customize.
  2. In the resulting menu, click the text box that you would like to resize.
  3. Move your mouse pointer over the text boxes right vertical border until your pointer changes to a left-right pointing arrow.
  4. Left click and drag the text box to the desired size.
  5. Click the Close button.

[Top]

Back Up Before Compacting and Repairing

[Access 2003]

In most cases, the Access database will identify damaged files and automatically give you the compact and repair option. But, if an Access file is behaving very unpredictably, it may be necessary for you to perform a Compact and Repair of your database manually. It actually isn't a bad idea to perform a compact and repair of your database on a regular basis to help prevent file corruption. Here's how:
  1. Make a backup of your database prior to a compact and repair.
  2. Go to Tools>> Database>> Utilities.
  3. Select Compact and Repair Database.

Note: If everything goes well, you can delete your backup copy if you need to save disk space.

[Top]

Convert Your Format for Better Performance

[Access 2002]

By default, Access automatically saves your database files in Access 2000 format. This format is designed so that your database files can be shared with other Access 2000 uses. But, if you have no plans of ever sharing your files, then you can gain better performance (especially the larger databases) by converting to the Access 2002 format. Access 2002 will create a separate database file in the new format and leave your original database file intact. Here's how to convert to Access 2002 format:
  1. On the Tools menu, select Database Utilities.
  2. Select Convert Database.
  3. Then click Access 2002 File Format.

[Top]

Create new fields from Access's Datasheet view

[Access 2000]

If you need to create a table field on the fly while working in Datasheet view, here is a very quick method that should save you time:
  1. Right-click on the column heading of the column you want immediately to the right of your new field.
  2. Select Insert Column from the shortcut menu. Access creates a field with a name resembling "Field1" that you can then immediately start entering data in.

Note: You can also use the shortcut menu to rename or delete the newly created field. You will more than likely want to go to Design view at some point to set the field's properties. But, depending on the reason for your creating the field, you may find that creating it in the Datasheet view is more efficient for your immediate needs.

[Top]

Opening Access objects with disposable toolbar icons

[Access 2000]

If you find yourself constantly switching back and forth between the same set of database objects (i.e.: tables and forms), just simply create a toolbar button that will open the object for you. Here's how to create the disposable button:

Drag the object from the Database window up to an existing toolbar. Access will create a button with the appropriate icon. This won't work for modules, but you can use this technique for tables, forms, reports, queries, and macros. If you have multiple buttons for the same type of object, Access displays a ToolTip when you hover your mouse pointer over the button, indicating what the button will open. When you've finished working on the database, you will need to remove the buttons to avoid confusion when there are other files open. To remove a button, simply hold down the "Alt" key and drag the button off of the toolbar.

[Top]

Arranging VBA Windows

[Access 2000]

The VBA IDE (Visual Basic Editor) has three windows:

  1. The Properties window
  2. The Project Explorer window
  3. The Immediate window

To cut down on the amount of clutter you have when writing code in the Immediate window, you can close the Project Explorer and the properties windows. By making some resizing adjustments, you will increase the amount of code you can view on screen.

If you need to access either of the other two windows after they have been closed, you can use the two keyboard shortcuts:

Press Ctrl-R to display the Project Explorer.
Press F4 to display the Properties window.

[Top]

Copy Access data to new records quickly

[Access 2000]

Using the Paste Append feature, you can quickly create new records that will copy existing information from other records. As an example of how this feature works:

  1. Open a table in Datasheet view.
     
  2. While holding down the Shift key, select adjacent fields with the data you would like to copy.

Hint: You can also select fields from adjacent records.

  1. When you've finished, press CTRL C to copy the data.
     
  2. Select Edit/Paste Append and when Access asks for confirmation, click Yes.

You will now have an appropriate number of new records in the table that contains the information you have copied.

[Top]

Useful Keyboard Shortcuts

[All Versions]

The Plus symbol (+) in the Command column does not get pressed. As an example, the first command shown would be:

Press CTRL and while holding CTRL, press ;

Command Description
CTRL + ; Insert today's date
CTRL + : Insert the current time
CTRL + Enter Insert a carriage return in a memo or text field
CTRL + ' Insert the data from the same field in the previous record
ESC Undo the changes you have made to the current field
ESC ESC (press ESC twice) Undo the changes you have made to the current record
F11 Display the database window
CTRL + N Open a new database
CTRL + O Open an existing database
ALT + F11 Switch between the Visual Basic Editor and the previous active window
CTRL + F Find and replace
CTRL + C Copy
CTRL + P Paste
CTRL + Z Undo
CTRL + S Save
CTRL + P Print

[Top]

Rearranging Buttons on a Toolbar

If you are unhappy with how buttons are placed on a toolbar, go ahead and move them. Here's how:

While holding the ALT key, drag the button from one position to another. Be careful not to drag the button completely off the toolbar or Access will automatically delete it. If that happens, don't worry a quick keyboard command will bring it back: CTRL - Z or you can choose Undo Delete from the Edit menu.

If you would like to copy a button, just hold down the ALT-CRTL buttons while dragging the button from one toolbar to another.

[Top]

Quick Selections

[All Versions]

Select one record (highlighting it) in Datasheet view, then you can:

1. Select an entire row by pressing Shift + Spacebar.
2. Select the entire datasheet, press Ctrl + Shift + Spacebar.
3. Select and entire column, press Ctrl + Spacebar

[Top]

Query Types

[Access 2003]

Microsoft Access supports many types of queries. Select queries are by far the most common queries. Select queries are used for viewing and are a data source for forms, reports, controls, and other queries. The other queries create or change data and are known collectively as Action queries. Below, is a short description of the major categories of queries:
  • Select Queries:
    Retrieves data for display using SQL SELECT statement.
     
  • Make-Table Queries:
    Much like the Select queries except the data is placed in a new table.
     
  • Append Values Queries:
    Creates a new row and inserts literal values into specified columns.
     
  • Append Queries:
    Creates new rows by copying existing rows from one table into another, or into the same table as new rows.
     
  • Update Queries:
    Used to modify data in the records. Can be used to delete values from individual data columns.
     
  • Delete Queries:
    Removes one or more rows from a table.

[Top]

Missing typos in Access tables

[Access 2000]

After spell checking a table, you could come across a wrong spelled word that Access didn't catch. By default, Access will purposely skip certain words that are all in upper case or that have numbers in them. You can change the way that Access spell check so that it will include all words. Here's how:
  1. Run the spell checker on a data selection that will cause the Spelling dialog box to be displayed.
  2. Select the Options button and clear the Words In UPPERCASE and Words With Numbers check boxes.
  3. Click OK and then Cancel.

[Top]

Automatically Compact On Close

[Access 2000]

Regularly compacting an Access database allows it to run more efficiently by reducing the file size and defragmenting the data. However, most users forget to do this on a regular basis. So, by using the Compact On Close feature, you can forget about having to do this and let it do it on its own. Here's how:
  1. Select Tools/Options from the menu bar
  2. Click on the General tab
  3. Select the Compact On Close check box
  4. Click OK

Access will automatically compact and repair the file if the database will be reduced by at least 256 kilobytes when you close it.

Note: This setting needs to be activated on a file-by-file basis.

[Top]

View related fields in an Access query easily

[Access 2000]

When you are designing a query and you would like to temporaily view fields from the underlying tables beyond the ones you want shown in the final result. Do do this, most users drag the relevant fields to the design grid and then delete them when they're done. However, there is a much easier way to accomplish the same thing. Here's how:

1. Open the query in Design view.
2. If necessary, display the query's Properties sheet.
3. Set the Output All Fields property setting to Yes and run the query.

Data from all of the underlying tables fields will be displayed in addition to the fields you specified in the QBE grid. To change the query so that it only displays the fields specifically selected in the QBE grid, simply reset the Output All Fields property to No.

[Top]

Simplify grouping on dates in Access reports

[Access 2000]

When sorting and grouping your report data based on a Date/Time field, you can summarize your data in terms other than individual dates. As an example, you may want to group data by month, quarter or year. Fortunately, you won't need to use expressions to accomplish this. Here's how:
  1. In Design view and from the menu bar, select View | Sorting And Grouping.
  2. Select the appropriate field in the Field/Expression column of the Sorting And Grouping dialog box.
  3. Click in the Group On text box and choose one of the following items from the dropdown menu:

Hour
Qtr
Year
Month
Week
Day
Minute

  1. Close the Sorting And Grouping dialog box.

[Top]

View or Refresh Links

[Access 2003]

If you would like to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables. Here's how:
  1. Open the database that contains links to tables.
  2. On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
  3. Select the check box for the tables whose links you want to refresh.
  4. Click OK to refresh the links.

Microsoft Access will confirm a successful refresh or, if the table wasn't found, it will display the Select New Location of <table name> dialog box, then you can specify the table's new location.

If several selected tables have moved to the new location that you have specified, the Linked Table Manager will search that location for all selected tables, and it will update all links in one step.

[Top]

Change the default language setting

[Access 2003]

Access makes it possible to change your default settings in Microsoft Office to have them match the default settings of a different language. Here's how:

Windows XP:
Start menu>> All Programs>> Microsoft Office>> Microsoft Office Tools>> Microsoft Office 2003 Language Settings.

Windows 2000:
Start menu>> Programs>> Microsoft Office>> Microsoft Office Tools>> Microsoft Office 2003 Language Settings.

  1. Click the Enabled Languages tab.
  2. In the Choose the language that defines default behavior in Microsoft Office applications box.
  3. Select the language you want
  4. Click OK.

A message will appear describing the effects of the change. To continue, click Continue and lose customizations.

[Top]

Changing Access Find Option

The Find feature defaults to the selected field, which means you could end up searching just the active field, instead of the entire table. When using the Find option in Access, you can change the default search options to something that would be more to your own search preferences. Here's how:
  1. Select Tools>> Options from the menu bar.
  2. Click on the Edit>> Find tab.
  3. Select the option button you want from the Default Find and Replace Behavior panel.
  4. Fast Search is the default, and causes Access to search the current field for an exact match to your criteria.
  5. General Search searches all fields and matches any part of the field.
  6. Start Of Field Search searches the current field for matches between your search string and the beginning characters in the field.
  7. When you've decided on the proper search criteria, click OK.

Note: You may have to restart Access before the changes take effect.

[Top]

Access Keyboard Shortcuts

Below is a list of keyboard shortcuts for Microsoft Access. Keyboard shortcuts can often increase your productivity. You will need to experiment to find out which ones that you are comfortable using and would save you time.
 
Command Action
ALT + F11 Switches between the Visual Basic Editor and the previous active window
CTRL + C Copy
CTRL + F Find and replace
CTRL + N Opens a new database
CTRL + O Opens an existing database
CTRL + P Print
CTRL + S Save
CTRL + V Paste
CTRL + Z Undo
CTRL + ; Inserts current date
CTRL + : Inserts the current time
CTRL + Enter Inserts a carriage return in a memo or text field
CTRL + ' Inserts the data from the same field in the previous record
ESC Undo the changes you've made to the current field
ESC ESC (press ESC twice) Undo the changes you've made to the current record
F11 Displays the database window

[Top]

   Site Map  | About 5 Star Support  | Links | Comments
    Privacy Policy  | Terms of Use  | Newsletter Archive  | Awards
Usage of this site constitutes acceptance of our Terms of Use
Copyright 2000-2014  5 Star Support All rights reserved.