Microsoft Access 2000

Microsoft Access 2000

Compress and Clean Up

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 and Repair Database.

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?

Microsoft Access 2000 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 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.

Lose the 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.

Duplicating a Database

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

Looking up Data

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.

Making Subforms

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.

Easier Code Viewing

Make your Visual Basic for Applications code easier to read. Open a module, select Tools/Options, pick different colors for different kinds of code (keywords, comments and so forth).

Changing Styles

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.

Cycle Property

Use the 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.

Gathering Info

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.

Changing Buttons

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

Viewing Code

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

Changing Control Types

Change controls on a form from one control to another by right-clicking on the control and selecting Change To. 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.