2,001 Tips
Applications
Databases

Microsoft Access 7.0

Compress and Clean Up Often

Creating and deleting huge arrays of data will cause Access files to grow to unwieldy sizes. To compress an Access database, close all open databases and select Tools/Database Utilities/Compact Database. Choose the database you want to compress and provide a new filename for the compressed version.

Getting from A to B

Calling on a domain aggregate function from a function or subroutine will slow your database down to a crawl. For speed and efficiency, learn the appropriate database programming techniques and make sure you know how to use Data Access Objects in Access.

Queries, Anyone?

Microsoft Access 7.0 is fully 32-bit and designed to work with multiple CPUs under Windows NT 3.51 and 4.0. Try performing a query against a database of several thousand records on a dual-CPU machine running Access and compare the results with those obtained on a single-processor unit. You may find that performance increases significantly enough that you won't have to invest in a new database management system to properly exploit multiprocessor functions.

Get a Proper License

Customize your toolbox with OLE custom controls by dragging and dropping from the command well onto the toolbox. You should not do this unless you've properly licensed and registered the OCX you want to use. For example, you should not use an ActiveX control you've downloaded from the Internet unless it's installed correctly and licensed to run on your machine.

Check Out the Previews

Right-click while in Print Preview to get a grid for the number of pages to preview. It's more detailed than the list of pages available from the menu.

Lose that Data

To remove all instances of a piece of data from your current recordset, right-click on data in a Form/Datasheet and choose Filter Excluding Data.

Easy Duping

To duplicate a database object quickly without using Copy/Paste, drag and drop it within the Database window while pressing the Ctrl key.

Look It Up

If you've got fields in a table with redundant data, use the Table Analyzer to break out the data across two or more tables for better efficiency. The Table Analyzer works without damaging the original data table. It also generates a query that simulates the original unaltered table, so you can easily plug it into existing forms and reports.

Subforms Made Easy

Use the Subform/Subreport Wizard to create a subform displayed as a datasheet. To start the wizard, drag a table or query from the Database window to a form. Just verify the correct linking field and you've got a form with a subform displayed as a datasheet.

Pass the Crayons

Make your Visual Basic for Applications code easier to read-in Tools/Options/Module, pick different colors for different kinds of code (keywords, comments and so forth).

Get a Stylish View

Bring up any object in design view by pressing the Ctrl key and double-clicking in the Database window.

2 x 1 = More than 2

If response times are slowing down while you work on a networked Access database, use the new replication support in Access 7.0 to create a replicated database on another network share. This enables people to use both shares while ensuring that the databases are synchronized.

Go Cycling

Use the new Cycle property to determine what happens when you tab past the last field in a form. You control whether you advance to the next record or the next page, or return to the first control on the form.

Ties that Bind

Bind data directly to a control by clicking on the control, then dragging and dropping the desired field from the field list.

Info-Gathering

Keep the Debug window open after you close the Module window if you want to get "on-the-fly" answers to how particular variables are set, or to see the values of properties of given objects.

Presto! Change-O!

To edit the bitmap on a button, go into customize mode, right-click on the button and choose Edit Image.

See It All

View all the code in a module in one long continuous sheet rather than one sub at a time by selecting Tools/Options. Select the Module tab and check the "Full module view" box.

Take Control of Controls

Change controls on a form from one control to another by right-clicking on the control-you don't have to delete them and re-create them from scratch.

"Free" OCXes May Not Be Free

OCX software components installed by third-party programs for use in Access databases may require you to pay royalties or a licensing fee if you convert your Access database into an application. Check with manufacturers before getting yourself into hot water.

[ Go to 2,001 Tips Table of Contents ]