Ever wish you had an Access expert at your shoulder while you work, always ready with great tips and tricks for working with your data? If so, this article is for you. And if you like these tips, you may want to check in again later—we'll be adding more tips as we go.

Startup and settings

• Click Startup on the Tools menu to customize how your application looks and acts when it is opened.
• When you delete a built-in menu, the menu is still in the Customize dialog box. However, when you delete a custom menu, it is permanently deleted. To remove a custom menu and save it for later use, create a storage toolbar for storing unused menus, move the menu to this storage toolbar, and then hide the storage toolbar.
• Hold down the ALT key to drag a toolbar or menu item without displaying the Customize dialog box.
• Add a custom toolbar button to run your favorite macro. Simply drag the macro from the Database window onto the toolbar of your choice, and right-click the newly-created button to customize it.
• Customize your toolbox with ActiveX controls by dragging them from the Commands tab in the Customize dialog box onto the toolbox.
• Double-click an ActiveX control in Design view to show its properties.
• Bypass the switchboard and the Autoexec macro by holding down SHIFT while you open the database.
• To quickly display the Properties dialog box, press Alt+Enter.
• To close the Properties dialog box, press Alt+F4.
• Click Startup on the Tools menu to customize how your application looks and acts when it is opened.

Managing files

• You can use wildcards in the Search text box. Type a question mark (?) to match any single character, or type an asterisk (*) to match any number of characters. For example, s?t finds "sat" and "set"; s*d finds "sad" and "started."
• To open an Access data file on a network share you've recently opened by using a UNC address, click the arrow next to the File name box within the Open dialog box, and click the UNC connection.
• To open one of the last several databases you had open, click the file name at the bottom of the File menu. Access opens the database with the same option settings it had the last time you opened it.
• If the list of recently used files isn't displayed at the bottom of the File menu, click Options on the Tools menu, click the General tab, and then select the Recently used file list check box.
• To view a list of shortcuts to databases that you have opened previously:
  • In Access 2003, click My Recent Documents on the left side of the Open dialog box.
  • In Access 2002 and Access 2000, click History on the left side of the Open dialog box.
• Access 2002 and Access 2000 only: To quickly locate a file or folder in the Favorites folder, click Favorites on the left side of the Open dialog box. To open a favorite file or folder, click the shortcut and then click Open.
• To sort records on partial field values, use the Advanced Filter/Sort dialog box.
• To get help on a property in a property sheet, click the property, and then press F1.

Objects

• To rename an object in the Database window, press F2.
• To cycle through the objects in the Objects bar in the Database window, press CTRL+TAB.

Tables

• Right-click in the Database window and click Relationships to see how your tables are related.
• To import a Microsoft Word table into Access, in Word: Convert the table to text (click Table, click Convert, and click Table to Text), copy the selection to a new document, and save it as a text file. Then, in Access, use the Get External Data command to import the contents of the text file.
• Ensure that joined fields between tables are of the same data type and size.
• Copy a URL from your browser and paste it into a Hyperlink field to navigate to it from Access.
• Drag a selection from Microsoft Excel, Word, or PowerPoint while holding the CTRL key, and drop it in a Hyperlink field to create a hyperlink that opens the document and places you at the point of the selection.
• To bulk update data in a table, create an update query.
• To copy a field's value from the previous record to the same field in the current record, press CTRL+’ (apostrophe).
• Use a field's InputMask property to enter and store dates in a consistent format in design view.
• To display dates in a consistent format, set the field’s Format property in Table Design view.
• To bulk insert records into a table, create an append query.
• To add serial numbers to the records in a table, use an autonumber field.
• Right-click anywhere in the Database window and click Relationships to see how your tables are related.

Printing

• You can quickly print from a shortcut by dragging the icon to a printer icon on the desktop.

Design

• To display the properties of the current object or control in Design view, press F4.
• To open the list of fields in a form, report or data access page Design view, press F8.

Return to top

Wizards

• Drag a table or query from the Database window to a form or report in Design view to create a subform or subreport displayed as a datasheet. The SubForm or Subreport Wizard will start automatically.
• Using the Database Splitter Wizard, you can split a database into two files: one for the tables and one for the user interface. Point to Database Utilities on the Tools menu and click Database Splitter.
• To look up values from other tables, use a lookup field. To create a lookup field, choose Lookup Wizard as the data type in the field.
• The Combo Box Wizard lets you build a combo box that makes your form move to the record you choose.
• Start the Database Wizard by creating a new database that is based on a template. Click New, and in the New File pane, under Templates, click On my computer. On the Databases tab, click a template, and click OK to start the wizard.
• Use the Find Duplicates Query Wizard to find records with duplicate field values in a table or query.

Security and privacy

• Help protect your digital signatures from being invalidated by preventing users from changing your code.
• Your digital signature says only that you guarantee that the project has not been tampered with since you signed it. It does not prove that you wrote the project.
• Locking your VBA project doesn't prevent another user from replacing the digital signature with another signature.
• Corporate administrators might re-sign templates and add-ins so that they can control exactly what users may run on their computers.
• If you create an add-in that adds code to a VBA project, your code should determine if the project is digitally signed and notify the user of the consequences of modifying a signed project before continuing.
• When digitally signing macros, consider obtaining a timestamp so that others can verify your signature even after the certificate used for the signature has expired.

Programmability

• To stop a macro while it's running and then single step through it, press CTRL+BREAK.
• You can use VBA to locate or move between records by using a Recordset object and the Seek method or any of the Move or Find methods.
• Use the Object Browser to paste method or property syntax, including arguments, into your VBA code. The Object Browser pastes standard or named arguments that you can specify in any order.

Performance

• Don't use updatable result sets (dynasets) if you're not retrieving many records, are not updating the data, and don't need to see changes made by other users.
• To prevent result sets from being updated in a form, set the form's RecordSetType property to Snapshot.
• Snapshots of your data are faster to open and scroll through than updatable result sets (dynasets).
• If you need to retrieve a large number of records, a dynaset is faster and more efficient than a snapshot. For example, moving to the end of a snapshot requires the entire result set to be downloaded to the local computer, but with a dynaset, only the last screen of data is downloaded to the local computer. In addition, the fastest way to add new records to a table, form, or query is to click Data Entry on the Records menu. (Data Entry isn't available if the RecordsetType property is set to Snapshot.)
• If you will reuse the most recent data from the server while the application is running, it's faster to retrieve a single large chunk of data (many rows) and store it in a cache than it is to retrieve many individual rows. Access forms and datasheets automatically use a cache. If you are retrieving data by using a Recordset object created in VBA, use the CacheSize property to specify how many records to retrieve at one time into local memory.
• For bulk update queries against ODBC data sources, optimize performance on the server by setting the FailOnError property to Yes.
• Avoid using queries that cause processing to be done on a local client computer.
• To improve performance of a form or datasheet in PivotTable view, base the view only on the data that you want to pivot. A form or datasheet based on a large recordset can slow down significantly in PivotTable view. If you are not interested in seeing the entire recordset, you can create a query that includes just the fields and records you want, and then build a PivotTable view of the query.
• When designing or using a PivotTable view, displaying only the aggregate values and keeping details hidden improves the view's performance. Show details only for the items you are interested in, and hide them when you are finished.