Top tips for Access: Working with data - Access

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

Top tips for Access: Working with data

Applies to: Microsoft Office Access 2003

Applies to
Microsoft Office Access 2003 Microsoft Access 2000 and 2002

The following tips will help you work with your data. You may want to check in again later—we'll be adding more tips as we go.

Sharing data

If another user has a table open or is viewing data in queries, forms, or reports based on the table, you can't make any changes to the table's design. Access informs you that it is read-only.
If objects are dependent on one another, update all of them at the same time so that other users don't open inconsistent versions of the objects. For example, if you want to add fields to a form, make sure to add the fields to the underlying query before you update the form.
If you change a query that another user has open, that user must close and reopen the changed object to use the latest version. With Microsoft Visual Basic for Applications (VBA) modules, other users can't run the updated Function or Sub procedures until they close and reopen the Access database.
To send only a portion of a datasheet, open the datasheet and select the portion you want. Then, on the File menu, point to Send To, and click Mail Recipient (as Attachment). In the Send dialog box, click the file format you want, and click OK.

Indexes

Although a unique index will help locate information, using a primary key or unique constraint will give you the best performance.
Most databases prevent you from saving a table with a newly created unique index when there are duplicate key values in the existing data. Your database may also prevent the addition of new data that would create duplicate key values in the table. For example, if you create a unique index on the employee’s last name (lname) in the employee table, then no two employees can share the same last name.
Defining a primary key for a table in a database diagram automatically creates a primary key index that is a specific type of unique index. This index requires each value in the primary key to be unique. It also permits fast access to data when you use the primary key index in queries.
If an index is not clustered, the physical order of the rows in the table does not match the logical order of the key values. A clustered index usually provides faster access to data than does a nonclustered index.

Return to top

Data access pages

In a data access page, to quickly create one control that is based on a field, double-click that field in the field list.
Data access pages load faster in Page view and Internet Explorer if you use bound span controls.
To quickly open a data access page on a Web server, in the File name box type the address for the page you want to open, and then press ENTER. For example, you might type http://sales/february/Page1.htm to open a page from the sales department. If a Web folder at http://sales/february doesn't already exist, it will be created automatically when you type an address in the File name box.
When you click Promote, the selected control is moved to the next higher group level. To copy the field instead of moving it, press and hold the CTRL key when you click Promote on the toolbar.
When you save a data access page, all supporting files — such as bullets, background textures, and graphics — are, by default, organized in a supporting folder. If you move or copy your page to another location, you must also move the supporting folder so that you maintain all links to your page.
To set text direction directly in HTML, click HTML Source on the View menu and modify the <HTML> element to <HTML DIR=RTL> for a right-to-left page direction or <HTML DIR=LTR> for a left-to-right page direction.

Queries

Instead of dragging, add fields by double-clicking the name in the field list or selecting a field directly from the list box in the Field row on the grid.
You can add a table or query to a query by dragging the table or query name from the Database window to the upper part of query Design view.
Use queries to copy the column's data from the original column to the new column. Use the Query Designer to run an update query to add the data to the table to which you copied the columns.
To cause Access to temporarily ignore an action, enter False as a condition. Temporarily ignoring an action can be helpful when you are trying to find problems in a macro.
Retrieve only the data you need. Design your queries to limit the number of records that you retrieve, and select only the fields you need, so that Access can transfer as little data as possible over the network.
To get help on a keyword in SQL view, position your cursor on a keyword and press F1.
To delete several records that you don’t need anymore, create a delete query.
Save a frequently-applied filter as a query, and apply the query as a filter when you need it.

Return to top

Creating and working with databases and objects

Use conditional formatting to prevent users from making changes to a control when a condition is true. Specify the criterion, and in the format section, click the Enabled button (the rightmost button in the Conditional Formatting dialog box) once.
To use the same color for another control, click Line/Border Color (you don't have to click the arrow next to the button and pick the color again from the palette). To set different colors for several controls at once, click the arrow next to Line/Border Color, click the horizontal bar at the top of the color palette, and drag it to another location. This way, you don't need to click Line/Border Color each time you want to apply a color.
To use the same border width for another control, click the Line/Border Width button (you don't have to click the arrow next to the button and pick the width again from the list).
To use the same color for another control or section, click Fill/Back Color (you don't have to click the arrow next to the button and pick the color again from the palette). To set different colors for several controls at once, click the arrow next to Fill/Back Color , click the horizontal bar at the top of the color palette, and drag it to another location. This way, you don't need to click Fill/Back Color each time you want to apply a color.
Bypass the switchboard and the Autoexec macro by holding down SHIFT while you open the database.

Archiving data

Can't bear the thought of deleting obsolete records? Instead, flag them with a Yes/No field. Then, use the value in that field to determine whether to show records with a Yes value in your queries, forms, reports, and so on.

Viewing data

You can create a self-join when you insert or modify a subdatasheet. For example, suppose you want to see the employees who report to each manager as a subdatasheet of the Employees table. Open the Employees table, click Subdatasheet on the Insert menu, click the Tables tab, and click the Employees table. Then click ReportsTo in the Link Child Fields box, and click EmployeeID in the Link Master Fields box.

Importing and Linking Data or Objects

You can use relative paths to store pictures. For example, if your file is located at \\ServerName\ShareName\ and the pictures are stored in the folder \\ServerName\ShareName\Pictures, you don't have to specify the entire path for a picture, PictureFileName.gif, that you will display in a form or report. You only need to store the portion of the path that is different relative to the location of the current Access file—Pictures\PictureFileName.
If you want to use relative paths to store the locations of pictures to be displayed in data access pages, those paths must be relative to the location of the data access page, not the location of the Access database or Access project.