Performance VIII - hasty hardware

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Make the most of the cache ■

Use cache memory wherever possible when using external SQL data sources. Access forms and reports have automatic caching mechanisms. When using recordsets in your code, try using the CacheStart, CacheEnd and FillCache methods to maximize cache effectiveness.

Access Help topic: FillCache method

Increase RAM ■■

Increase the amount of RAM on your computer. The minimum acceptable amount Access 2000 is 64MB, although 128MB is preferable and 256MB is better yet. The basic message here is: the more RAM, the better.

Avoid NTFS compression ■

If you are using NTFS compression, move your database to a non-compressed drive. NTFS adds a significant amount of overhead during disk operations, and Access applications are very disk-intensive.

Get a faster processor ■■

Okay, so it's stating the obvious but the faster your PC's processor the better. As little as 100MHz can give fair performance for 95/97; I'm currently running 2000 with 2.3GHz. Like RAM, the message here is: the more the merrier!

Keep enough local disk space free ■

Access requires a fair amount of disk space to operate, especially with large databases. Operations such as running large action queries, adding lots of data, importing data, and compiling and saving module code can use a lot of additional space on a temporary basis. Additionally, transactions, and compacting the database can use a lot of disk space. As a minimum, try to keep ten times the size of your largest database available in free local storage.

Optimize that disk ■

Keep your disk in clean and tidy by deleting surplus files and emptying your Recycle Bin. Look at Disk Defragmenter and Disk Cleanup within Windows, or investigate clean-up tools like the always excellent (and snappily titled) freeware Crap Cleaner.

Virtual Memory ■

Although the Windows default virtual memory settings are okay for most systems, you may want to consider changing the settings if you don't have a lot of space on the disk being used for virtual memory, or if you have a local drive that is faster than the one being used by default for virtual memory. Exercise caution and change in small increments though.

Strip your wallpaper ■

If your PC is really struggling, you may find the tiniest performance hike by removing desktop wallpaper, or switching to a small tiled bitmap.

Avoid disk compression software ■

Don't use disk compression software on the drive where you have installed Access, or where your database file(s) are located. Disk compression has a big negative impact on db performance.

Disable Office startup programs ■

I can't guarantee this applies to all versions, but in my experience Office installs one or more startup programs in your Start Menu Startup folder (e.g. Office Fast Start and Office Find Fast indexing utilities). Consider removing these, as they use memory. Find Fast runs continuously, so is especially damagaing to performance.

Don't journal ■

If you have Outlook installed (not Outlook Express), disable journalling unless it is absolutely essential to you - otherwise Outlook watches out for every time you open an Access database, and logs it, inevitably slowing things down. To disable this annoying function, select Tools, Options, Preferences, Journal options from the Outlook menu.

Don't spring too many leaks ■

I don't care what our friends at Redmond might say, almost all Windows applications "leak" memory. This is due to data structures, variables, and other memory consumers that are not correctly released by the application. By closing and reopening Access now and again, you allow Windows to reclaim any leaked memory.

Install Windows locally ■

Your OS should be installed on a local fixed drive on your computer. Because of the number of components involved in running Windows, a network installation causes relatively worse performance, especially for resource-hungry database app's.

Keep databases local too ■

Where possible, keep your databases on a local drive instead of on a network drive - 9 times out of 10, local disk access is faster than network access.

And install Access locally too ■

Most applications designed for PCs are faster when run from local drives than from a networked location, and Access is no exception.

Close other applications ■

Okay, it's an obvious one but you can free up memory by closing applications that you aren't using.

Review running Services ■

A whole host of services are running all the time in the background of your Windows OS - stop any you don't need (but set them to start automatically when needed) to free up system resources, but do so with caution and understanding - it's worth taking a look at this basic but comprehensive services guide before you change anything.

Performance VII - vigorous VBA

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Compile an MDE ■■■

If possible, make an MDE file out of your database; an MDE file is compiled, so your code runs faster. Additionally, since no source code is stored in the MDE file, the database loads faster and uses less memory. Later versions of Access even have a wizard to help you make this split (Tools, Database Utilities, Make MDE file).

Access Help topic: About MDE files

Achieve the Compiled state ■■

Sounds Zen, but isn't. Module code is saved in two states in your database: source and compiled. The source state consists of the contents of your actual modules, with full text including white space, procedure and variable names, and comments. The compiled state is the executable version of your code; comments and white space are removed, and a stream of executable instructions produced. And the source state is sloooower than the compiled. This is because when you run a procedure, VBA checks to see if the parent module is compiled. If it is, the code simply runs. If it is not compiled, VBA compiles it by loading the code into memory, performing a syntax check, and compiling it into an executable format. If all these operations succeed, it can then run the code... hence the slow-down. So clearly you want your database to keep all code in a compiled state, so it runs as fast as possible. To do this, from a VBA code editor window click the Debug menu and select either Compile and Save All Modules or Compile Project, depending on your version of Access.

Access Help topic: Compile

And avoid Decompilation ■■

So how do you avoid loss of the compiled state? Modifying any code or code-bearing objects (e.g. forms, reports, controls) causes decompilation. But don't worry too much, your database doesn't need to be compiled in development - it only really requires the performance benefits of the compiled state when it is actually deployed to users. Therefore, when you are ready to deliver your database for testing or live use, put it into the compiled state using the steps outlined above.

ActiveX controls need References ■

If you're using an ActiveX control, your database should have a Reference to the ActiveX (OCX) file. This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: when you insert an ActiveX control into a form or report, Access automatically creates a Reference for that control, but you need to do this manually if using an external control in raw VBA (Tools, References).

Be explicit ■

Always explicitly declare variables - ensure this by always putting Option Explicit phrase at the top of each module.

Access Help topic: Option Explicit Statement

Choose the right types ■

Use the most efficient variable type possible when declaring variables. For example, don't use a Long Integer when a Byte will do. Avoid Variant types whenever possible because they can be inefficient and slow.

Access Help topic: Data Type Summary

Bind early ■

Avoid using the Object data type. Instead use the specific data type for the object you're working with. This allows for early binding, which can be substantially faster in many cases.

Assign things to variables ■■

If you are going to refer to a property, control or some other object more than once, assign it to an object variable. The performance hike will vary depending on how slow the statements you're replacing with the variable are, and how many statements are being replaced.

Access Help topics: Dim, Set

Use the Me keyword ■

Use the Me keyword instead of Form!FormName to refer to a form in that form's module.

Avoid the Immediate If ■

The IIf (Immediate If) statement can be slow as it does not employ "short-circuit" evaluation, i.e. both sides of the expression are always evaluated.

Access Help topic: IIf Function

Use string versions of functions where you can ■

Use the string version of functions (e.g. Left$()) when working with strings, and the non-string version (e.g. Left()) when working with variants.

Calculate with Integers and Longs ■

Instead of Doubles, use the Integer or Long data types whenever possible in your mathematical calculations.

Use dynamic arrays ■

Instead of fixed arrays, use dynamic arrays with the Erase and ReDim statements - this makes better use of your available memory.

Clean up after yourself ■

Eliminate unused procedures, variables and other code constructs. These elements use memory unnecessarily, and slow down the execution of your other code.

Stop painting ■■

Turn off screen painting during repetitive operations that update the screen, as this can have a big impact on performance - this is done by setting the Application.Echo property to false. The size of performance gain you get will depend on the type of video card in your PC. Don't forget to turn screen painting back on when your code has finished though!

Access Help topic: Echo Method

Don't go to infinity, or beyond ■

In other words, avoid infinite recursion. It sounds obvious, but if you have code that can call itself without having some type of short-circuit mechanism, you'll run into nasty "Out of Stack Space" errors.

Organise your code logically... ■

Organize and structure you modules to take advantage of the fact that when a procedure is loaded from a module, the entire module is loaded into memory. By placing related procedures in the same module, you can reduce the number of loads VBA has to make.

...and have fewer modules ■

Organize code into fewer procedures and modules. VBA allocates and manages memory for every procedure, so by rationalising several short, similar procedures into one, you can have faster code. remember though that this may make your code less manageable.

Use Bookmarks ■

Use bookmarks to move among records instead of using the FindNext method: it's faster becuase it's a direct move rather than a series of sequential reads.

Access Help topic: Bookmark property

Seek and ye shall Find ■

Whenever possible, use Seek instead of Find. It's faster as it uses indexes more efficiently.

Access Help topic: Seek method

If you must use FindRecord and FindNext... ■

...do so on indexed fields, to maximise the speed and efficiency of these operations.

Access Help topic: Find methods

Think carefully about Transactions in code ■

There shouldn't be any need to wrap update code in a BeginTrans...CommitTrans pair, as Jet has been optimized to do this since Access 95. Unless you have a series of updates that you need to commit, or potentially rollback, as a single transaction there should be no need to use BeginTrans...CommitTrans.

Access Help topic: Transactions

Constant-ly Variable ■

If you're using data that isn't going to change, put it in a constant instead of a variable. This allows VBA to compile the value into the constant when the underlying module is compiled, making the execution of that code faster.

Code isn't always better ■

Don't write code if a query would be better. Access is, after all, a database product and can handle 90% of all the data operations you will ever need... so why write recordset code for an operation that Jet can handle (and intelligently optimize) for you?

Reduce OLE references ■■

Every time you reference a VB object, method or property, you initiate one or more calls the OLE's Idispatch interface... and each call takes time. As such, it follows that by reducing the number of calls you can make your code run faster. These calls can be minimised, or optimised at least, by:

• Using object variables instead of directly referring to objects
• Using the With statement and For Each construct to minimize object references
• Ensuring references are outside loops
• Referring to a member of collection by its index number, rather than its name.

Declare String data carefully ■

Stack and heap memory is allocated differently according to the type of strings you create. By understanding some basic principles, you can handle strings more efficiently. Use the following guidelines to minimise stack memory usage:

• Local fixed-length strings less than or equal to 64 characters use 2 bytes for each character in the string. They don't use heap memory.
• Local fixed-length strings longer than 64 characters use 4 bytes of stack memory for a pointer to the variable in heap memory and 2 bytes of heap memory for each character in the string.
• Local variable-length strings use 4 bytes of stack memory for a pointer to the variable in heap memory, and a variable amount of heap memory according to the length of the string.

So... if your code used a large number of fixed-length strings of 64 characters or less, you can reduce stack usage by changing the strings to local variable-length strings or making them static fixed-length strings.

Close your database sometimes! ■

VBA dynamically loads code into memory as needed on a module level. If a function is called or a variable is used, the entire module containing that function or variable is loaded into memory. As you use your application, you keep loading code into memory... but VBA doesn't support dynamic unloading of these modules. Because of this, RAM will begin to be used up. For best performance then, periodically close your db to unload the modules. Note: you don't have to close Access itself, just the database itself, unless you have library database code loaded, in which case you should bail out of Access too. Oh yeah, you should always close your db after a Compile All Modules command, becuase this, by definition, loads all of your code into memory!

Remove Comments From Non-MDE Databases ■

If you are not deploying your database application as an MDE file, consider removing white space and comments from your delivered code. This will result in less space used, and offer a marginal performance increases. Of course, keep a development copy of your database that retains the white space and comments.

Access Help topic: modules, optimizing code

ActiveX Controls Should Have References ■

If you are using an ActiveX control, your database should have a Reference to the ActiveX (OCX) file. This allows you to use early binding to bind variables to the control's objects, making it load and run faster. In most cases, this is handled for you: when you insert an ActiveX control into a form or report, Access automatically creates a Reference for that control.

Don't Use Expressions To Determine Loop Boundaries ■

If use loop constructs in your VBA code, such as For...Next, Do...While, etc. don't force VBA to evaluate the boundaries of the loop each time. For example, instead of saying:

For intCounter=0 To Forms.Count - 1
...
Next intCounter

use this:

intCount=Forms.Count - 1
For intCounter=0 To intCount
...
Next intCounter

In the second example, VBA only has to determine the value of Forms.Count once. In the first, example, the value needs to be determined for each iteration of the loop.

The Big Bang (!) ■

Use bang (!) instead of the dot (.) operation to refer to collection members - ! is faster.

Integer division ■

If you're dividing one number by another, and both are integers, use integer division. In other words, int1 \ int2 is faster than int1 / int2. This is becuase Access doesn't have to work out what type of numbers are involved in the calculation before performing it.

Use Snapshots When Appropriate ■

Don't open Dynaset type recordset object on SQL database tables unless you need to add or edit records, or need to see the changes made by other users. Instead, consider using Snapshot recordsets which can be faster to scroll through. Of course, Snapshot recordsets can take longer to open since they require a full read of the source data.

Access Help topic: OpenRecordset Method

Use Dynasets for Large Record Sets ■

If you need to retrieve a large number of records, use a Dynaset instead of a Snapshot. Snapshot type recordsets must load all records from the data source before becoming available, whereas Dynasets are available as soon as the first 20 or so records are retrieved. Also, when using a Snapshot against large ODBC data sources, you run the risk of running out of disk space on your local computer. This is because all data is downloaded into RAM until the amount of RAM is exhausted. Then, the database engine creates a temporary database to store the contents of the snapshot. In a nutshell, when you open a snapshot, you need at least as much disk space as the largest table you are opening.

Access Help topic: OpenRecordset method

Use FailOnError For Bulk Updates ■

If you are using bulk update queries, optimize performance on the remote server by setting the FailOnError property of the Querydef object, or query to Yes.

Access Help topic: FailOnError property

Use ODBCDirect ■

ODBCDirect gives you almost direct access to server data through ODBC. In many cases, it is a faster and more flexible way to hit server data than that traditional Jet/Linked table technique. This feature, available in Access 97, can make client/server applications much faster.

Access Help topic: ODBC, ODBCDirect

Make It Look Faster ■

If you have exhausted all other performance optimization techniques, consider making your application "look" faster. Do this by displaying status messages and progress meters as your application loads forms, runs queries, and performs any other operation that may take a bit of time. While this doesn't make your application run faster, it appears to run faster.

Access Help topic: optimizations, performance

Minimize Record Navigation ■

Avoid record navigation wherever possible on linked tables. Only use the PageUp and PageDown movements, and the Move last movements when absolutely necessary.

Access Help topic: linked tables, optimizing performance

Release Locks ASAP ■

To improve multi-user concurrency, assume that other users will be trying to edit data in the same linked tables that you are using. In other words, keep records locked only as long as is necessary.

Access Help topic: linked tables, optimizing performance

Keep Static Data Local ■

Keep static data, such as lookup tables, on the local machine. Update the local tables as necessary from the server. For example, a lookup table containing the two-letter abbreviations for American states is not likely to change anytime soon. When such a table is used in a data entry application, it is a huge performance bottleneck to retrieve that data from the server everytime it is needed. Instead, copy that table to your application's local database.

Performance VI - mighty macros

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Convert! ■■

Convert macros to Visual Basic for Applications (VBA) code. 99 times out of 100, VBA code runs faster than macros (conversion advice).

Performance V - rapid reports

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Avoid overlapping controls ■

It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Think carefully about graphics sparingly ■

Use bitmap and other graphic objects sparingly (or not at all, if you ask me) as they can take more time to load and display than other controls. If you must use graphics, use the Image control instead of unbound object frames to display bitmaps. The Image Control is a faster and more efficient control type for graphic images. Also, if you have to use graphics use bitmaps with the lowest possible color depth. Consider black and white bitmaps where appropriate.

Access Help topic: Add a picture or object

Don't sort report queries ■

Don't base reports on queries with an ORDER BY clause. Reports use their Sorting and Grouping settings to sort and group, so the sort order of the underlying record set is ignored - it just slows the underlying query down.

Avoid expressions and functions ■

Try to avoid reports that sort or group on expressions or functions - this can be very sloooow.

Move expressions ■

Reports require at least one query execution per section. Because of this, your report may run faster if you take calculated fields (expressions) out of the underlying query and move it into the report.

Index appropriately ■■

Consider indexing any fields that are used for sorting/grouping or linking to subreports. Also conder indexing any suberport fields that are used for criteria. Of course, also bear in mind that remember that over-indexing can cause performance bottlenecks when editing, adding and deleting data.

Access Help topic: Indexed property

Minimize the report's dataset ■■

Base reports and subreports on queries instead of tables. By using a query, you can restrict the number of fields returned to the absolute minimum number, making data retrieval faster.

Avoid domain aggregate functions ■

Try to avoid using domain aggregate functions (e.g. DCount, DLookup) in a report's recordsource property. These are generally slow and will cause the report to open and display pages.

Use HasData property ■

Consider using the report's NoData event or HasData property to identify empty reports. You can then display a "sorry, no data" message and close the report.

Access Help topics: HasData property, NoData event

Avoid unnecessary property assignments ■

Only set properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance, so make sure you're not setting any properties that don't need to be set.

Avoid the unnecessary ■

If a subreport is based on the same query as its parent report, or the query is similar, consider removing the sub report and placing its data in the main report. While not always feasible, this can speed up the overall report.

Simplify ■

Minimize the number of controls on your report. Loading controls is the biggest performance hit on report load times.

Performance IV - fast forms

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Move startup code onto your startup form... ■

If you use a Startup form instead on an AutoExec macro, place the VBA code needed for that form in the form's module instead of a standard module. Since Access has to load your Startup form, it will automatically load that form's module, which is generally faster than loading a standard module. This technique gives your application the appearance that it is loading faster.

...but also minimize startup form code ■

Minimize the amount of code in your startup form. You may want to defer certain operations, such as opening data access objects in code, or checking objects to a later time. Again, this gives the impression that your application is loading faster.

Avoid using ActiveX controls on startup form ■

ActiveX controls can take longer to load than other controls and will subsequently slow down the load time of your application.

Avoid overlapping controls ■

It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Think carefully about graphics sparingly ■

Use bitmap and other graphic objects sparingly (or not at all, if you ask me) as they can take more time to load and display than other controls. If you must use graphics, use the Image control instead of unbound object frames to display bitmaps. The Image Control is a faster and more efficient control type for graphic images. Also, if you have to use graphics use bitmaps with the lowest possible color depth. Consider black and white bitmaps where appropriate.

Access Help topic: Add a picture or object

Close unused forms ■

Close forms that aren't being used. Every form that is open consumes memory that could be used by other parts of your applications.

Open forms hidden ■

Consider opening your application's most commonly used forms when your application starts. Set their Visible properties to False, and then make the Visible as needed. This front-loads some performance hits to the application load event, making forms load faster when needed.

Use the DataEntry property of a form ■

If a form's record source contains a large number of records, and the form is primarily used to add new records, set the DataEntry property of the form to Yes. This precludes Access from having to retrieve existing records when the form loads.

Access Help topic: DataEntry property

Don't sort a form's recordset ■

Avoid sorting records in a form's underlying record source unless a particular presentation order is absolutely necessary for the form. This will make the form load faster.

Base forms on queries and minimize fields returned ■

Consider basing forms and subforms on queries rather than tables. By doing this, you can use the query to restrict the number of fields returned, making the form load faster.

Use lightweight forms ■■

Consider replacing VBA in a form's module with calls to standard modules, or with hyperlink objects. Then set the form's HasModule property to False. This turns the form into a 'lightweight' form, making it load faster.

Access Help topic: HasModule property

Index fields used to link subforms to a form ■

Try indexing all fields in the subform that are linked to the main form. Also index all fields in the subform that are used for criteria.

Set editing properties on subforms ■

Set the subform's AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform aren't going to be edited. Or set the RecordsetType property of the subform to Snapshot.

Access Help topics: AllowEdits property, AllowAdditions property, AllowDeletions property, RecordsetType property

Reduce the number of fields in listbox and combobox rowsources ■

In the RowSource property of listbox and combobox controls, include only the fields that are necessary.

Index listbox and combobox fields ■

Index both the first field displayed in a combobox or listbox, and the bound field.

Set AutoExpand on comboboxes to No ■

Set the AutoExpand property of comboboxes to No if you don't need the "fill in as you type" feature.

Access Help topic: AutoExpand property

The first displayed field of an AutoExpand combobox should be text ■

In a combobox that has the AutoExpand property set to Yes, the first displayed field should be of type Text rather than Number data type. In order to find matches, Access needs to convert numeric values to text. If the data type is Text, this conversion can be skipped.

Optimize bound comboboxes ■

If the bound field in a lookup combobox is not the displayed field, don't use expressions for the bound field or the displayed field, don't use restrictions (the WHERE clause) in the rowsource, and use single-table row sources wherever possible.

Ensure linked data for combobox and listbox controls is local ■

If a the data that fills a listbox or combobox does not change often, and that data comes from a linked table, consider moving that data's table into the database. This can be a huge performance boost, especially if the linked table is located on a network drive.

Minimize the number of controls ■

Minimize the number of controls on your form. Loading controls is one of the biggest performance hits when loading a form.

Group controls on multiple pages ■

Consider grouping controls on multiple pages. When the form loads, prepare only the controls on the form's first page. Defer operations on other page's controls, such as setting the record source until the user moves to that page. This makes the form load faster.

Close forms that contain unbound OLE objects ■

Close forms that contain unbound OLE Objects when they are not in use. When you activate unbound OLE objects, the memory used in that operation is not released until the form is closed.

Convert subforms to listbox or combobox controls ■

Where possible, convert subforms to listbox or combobox controls. It is far quicker to load a control than it is to load an additional form as a subform.

Move form code to a standard module ■

You can reduce a form's load time by moving its code from the form module to a standard module. When the form loads, the form's module doesn't need to be loaded. Of course, the standard module needs to be loaded at some point, but once a standard module is loaded, it stays in memory until you close the database.

Access Help topic: About modules

Avoid unnecessary property assignments ■

Set only the properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance. Review your form's startup code to ensure that you are not setting any form or control properties that don't need to be set.

Use the Requery method instead of the Requery action ■

Use the Requery method instead of the Requery action. The method is significantly faster than the action.

Access Help topic: Requery method

Give visual feedback ■

Give the user some visual feedback during long operations. Consider using status meters to display a task's progress. At a minimum, use the Hourglass cursor along with a status message. This won't make your application any faster, but it be perceived to be doing something - this counters perceived slowness!

Use native tab controls ■

If you have databases from old versions of Access that used OLE custom controls for tabbed dividers, replace them with the native tab control found in Access 97 and above - it loads and runs faster.

Access Help topic: Tab control

Keep forms lightweight with hyperlinks ■

Hyperlinks in label controls make it easy to open another Access object. Instead of placing command buttons on your forms to do common operations, investigate the possibility of using a label control with Hyperlink properties. This approach eliminates the need for a command button, and its associated event code.

Access Help topic: About hyperlinks

Split forms into multiple pages ■

Consider using multi-page forms, separated by the page-break character. This allows you to present only the controls needed, and can reduce form-load time. For example, if your form has 10 combobox controls that take along time to fill, split the form into multiple pages using the PageBreak control. Then, pick the 5 combobox controls the user is most likely to use and place them on the first page. Place the remaining controls on the second page. Load time for the form should be substantially reduced, especially if the queries filling those combo box controls are complex.

Access Help topic: Pagebreak control

Choose control types wisely ■

Different control types have different performance "weights" associated with them. When choosing controls for a form, keep the following relative weights in mind:

• Rectangle: weight of 1
• Line: 1
• Page Break: 1
• Label: 3
• Command Button: 5
• Option Button: 5
• Toggle Button: 5
• Checkbox: 5
• Tab Page: 5
• Textbox: 10
• Option Group: 10
• Image: 20
• Tab Control: 25
• Listbox: 40
• Combobox: 40
• Custom Control (OCX): 45
• SubForm: 50
• Object Frame: 50
• Bound Object Frame: 50

Performance III - quick queries

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Subdatasheet Name ■■■

The default setting for the Subdatasheet Name property of a table is [Auto]. Set it to [None] for all your tables that don't have or need sub-datasheets.

Access Help topic: SubdatasheetName Property

Minimize traffic ■■

Minimize the amount of data returned by your queries. Do this by structuring then to return only the fields and records needed. Reduce the number of fields returned, and put constraints on the query by using the WHERE clause. The exception to this is when doing a simple count of records, when SELECT COUNT(*) is fastest (since it doesn't have to check for Null values in a specified field).

Don't force local query processing ■

Don't use query constructs that cause processing to be done by Access on the local computer. The following query operations force the Jet database engine to perform local data processing:

• Join operations between table that are linked to different data source (i.e. a join between a SQL table and a linked Access table).
• Join operations based on query that uses the DISTINCT keyword, or a query that contains a GROUP BY clause.
• Outer joins that contain syntax that is not directly supported by the remote database server.
• The LIKE operator used with Text or Memo fields.
• Multi-level grouping and totalling operations.
• GROUP BY clauses that are based on a query with the DISTINCT keyword, or the GROUP BY clause.
• Crosstab queries that have more than one aggregate, or that have field, row, or column headings that contain aggregates, or that have an ORDER BY clause.
• User-defined functions, or functions that are not supported by the remote server.
• Complex combinations of INNER JOIN, LEFT JOIN, or RIGHT JOIN operations in nested queries.

Indexing... again ■■

Try indexing any fields in the query that are used to set criteria, and those that you use for sorting. Also, index the fields on both sides of a join (although if you create a relationship between joined fields, the index is automatically created). As mentioned elsewhere on this page, it is possible to over-index, so tread carefully!

Access Help topic: View or edit indexes

... and use Primary Key indexes whenever possible ■

Because Primary Key indexes do not allow null values, they can be faster than other indexes.

Access Help topic: About primary keys

And for non-indexed fields... ■

Try to avoid the use of the BETWEEN, AND, IN and = operators on non-indexed fields.

Only join with compatible types ■

This sounds like dating advice, but try to ensure fields that are joined in a query have the same, or compatible, data types - your queries will thank you for it, and so will your users. For reference, an AutoNumber field is a Long Integer type.

Avoid the Immediate If ■

The IIf (Immediate If) statement can be slow as it does not employ "short-circuit" evaluation, i.e. both sides of the expression are always evaluated.

Access Help topic: IIf Function

Avoid too many Functions ■

Avoid calculated fields, or fields that use expressions in subqueries - slowness is an inevitable by-product.

Use Domain Aggregate functions sparingly ■■

Things like DMax and DLookup are generally slow, so avoid them where possible. Unless desperate, try not to use them to access data from a table that is not already in your query.

Fixed column headings in crosstabs ■

Wherever possible, use fixed column headings in your crosstab queries.

If in doubt, compile! ■■

When your db is compacted its data statistics are updated. When you then run a query, these updated statistics are compiled in the query's execution plan. This sequence of events results in the fastest possible query. Before you deploy your application, compact the database, then force each query to be recompiled (which in turn causes it to use the latest statistics) - do this by opening it in design view, saving it, and then running it.

Rush-more! ■

Jet uses Rushmore query optimization where possible. Rushmore can be applied to queries run on native Access data, and on certain linked table types. To ensure that Rushmore optimizations are used whenever possible, create indexes on all fields that are used to restrict a query's output. Queries that don't contain indexes on fields used in their restriction clauses cannot benefit from Rushmore.

If you have 1:n joins... ■

...in a query with a restriction, try moving the restriction to the other side of the join. If the restriction is on the many side, try moving it to the one side. Compare performance for both versions, and choose the fastest (obviously).

The Sub-Way: sub-queries instead of joins ■

If you have a slow query with a join, try replacing the join with a sub query. In some cases, this may run faster.

Performance II - top tables

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Subdatasheet Name ■■■

The default setting for the Subdatasheet Name property of a table is [Auto]. Set it to [None] for all your tables that don't have or need sub-datasheets.

Access Help topic: SubdatasheetName Property

Persist links to ODBC tables ■■

If you are going to be accessing a SQL database table, link the table permanently. This makes opening that table much faster in subsequent attempts. This is because linked tables cache a lot of information about the source table in your database, making the retrieval of the same structural information unnecessary after the linked table is created. A good way to achieve this is to create a linked table with one small field, then create a small form with a single control that is bound to this form. When your database opens, open the form hidden and keep it open all the time.

Access Help topic: linked tables

Normalize data ■■■

Correctly normalize your table structure to remove redundancies and repeated data. You can use the built-in Table Analyzer (Tools, Analyze, Table) to help you split your tables into related tables if you're having problems. A better approach is to understand the theory...

Access Help topic: Table Analyzer

But occasionally you might need to Denormalize ■

Although you should normally normalize (!) your data for best performance, you may want to experiment with denormalizing some of your data if you have frequently run queries whose joins could benefit from such data restructuring.

Don't forget your keys! ■■■

In my view, every table should have a primary key; this allows your db to quickly access specific records. Also, you can't create secondary indexes in a table until you've defined a primary key.

Access Help topic: About primary keys

Choose the right types ■■

By choosing the most efficient data type for each field, you can decrease both the disk space used to store data, and the time it takes to retrieve, manipulate, and write data. The general guideline is to choose the smallest data type possible to store a particular type of data. In other words, if you have a numeric field that is always between 1 and 100 set it as a Byte data type, not the default Long Integer - a Byte is 1 byte in size, whilst a Long Integer is 4 bytes.

Access Help topic: FieldSize property

Enjoy beautiful relationships ■■

Relationships allow joins between tables to work faster. Also, relationships that implement referential integrity allow updates and deletions to be cascaded without having to write code to do this. Bonus!

Access Help topic: Relationships

Indexing - get the balance right ■■

If you search on a field, or use it in a join, index that field for big performance gains. Remember though that every index you create adds to the time it takes the database engine to update, delete and add records, so experiment to make sure you get the right balance. A couple of pointers: firstly, don't apply indexes to fields that contain lots of identical data (e.g. Yes/No field) as this is almost always bad news performance-wise. Also, try to keep indexes as simple as you can - when creating a multi-field index, use only as many fields as are absolutely necessary.

Access Help topic: View or edit indexes

DataEntry mode ■

If you are only going to be adding records, use the Data Entry command on the Records menu. This is more efficient for adding records because existing records are not read from the database.

Access Help topic: Data Entry mode

Performance I - amazing Access

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Don't AutoCorrect ■■■

In Tools, Options, General, uncheck "Track name AutoCorrect info". You'll find huge improvements in the time it takes your database to do virtually anything. Note that this function (which cascades changes you make to object names) can be useful in a development environment, but should be turned off in a production environment.

Access help topic: AutoCorrect options

Compact and repair ■■■

Compact and/or repair your database regularly (in Access 2000 and above, one action does both). Compacting your database reclaims unused space and makes nearly everything faster. Also, compact anytime you import/delete objects in your database, or compile and save VBA code. If you find this an onerous chore, investigate admin scheduling tools like the Handy Access Launcher. And don't forget to compact your MDW files sometimes too!

Access help topics: Compact, Repair

Analyze this! ■■

Access has its own performance analyzing capabilities (Tools, Analyze, Performance). Select All Objects Types, check Select All and hit OK - Access will recommend optimization techniques for anything and everything in your database, which might give you some ideas. Be wary of implementing too many indexes though - Access is forever recommending them and you might have good reasons not to put too many in.

Access Help topic: Performance Analyzer

Power up the Jet engine ■■

There are many Registry settings you can play with to optimize the Jet database engine, including these:

• Threads: the number of operating system threads available to the Jet database engine.
• MaxBufferSize: the data buffer used by Jet.
• UserCommitSync: tunes the performance of explicit transactions.
• ImplicitCommitSync: tunes the performance of implicit transactions.
• FlushTransactionTimeout: tunes the performance of asynchronous write operations.
• ExclusiveAsyncDelay: tunes the performance of asynchronous mode writes to exclusively opened databases.
• SharedAsyncDelay: tunes the performance of asynchronous mode writes to databases opened for shared access.
• PageTimeout: the delay for checking other user's changes to the database.
• LockDelay: how long Jet waits between retries on locked pages in a shared database.
• MaxLocksPerFile: the maximum number of locks can be placed on a database.
• RecycleLVs: determines how memo, OLE and hyperlink data pages are recycled.

Now I don't pretend to know the best settings for all of these, and if you're unsure you're advised to leave the defaults alone (this is good advice for any changes you might ever make to any part of the Registry). But if you do want to experiment, these keys can be found in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0 (for Jet 4) and can be edited either with RegEdit or, if you're familiar with good old DAO, the DAO SetOption command in VBA (or SaveSetting in later versions of Access). Don't forget to back-up the Registry before you start experimenting.

Access Help topics: Registry, SetOption method, SaveSetting method

Do the splits! ■■■

You can improve performance (and simplify maintenance) by splitting your database in two. The "application" front-end (FE) of your database holds all objects except tables, and is linked to the "data" back-end (BE) that contains the actual tables. This can give particular gains in a multi-user, networked environment, as the FE can be installed on each user's workstation with only the BE on a server. Later versions of Access even have a wizard to help you make this split (Tools, Database Utilities, Database Splitter).

Access Help topic: Splitting an Access database

Keep your Options open! ■■

Avoid lock conflicts by experimenting with the Refresh Interval, Update Retry Interval, Number of Update Retries and ODBC Refresh Interval Settings - you'll find these in Tools, Options.

Be Exclusive ■

If you are opening a database that no other users need to use, open it in Exclusive mode. To do this, check the Exclusive checkbox in the Open Database dialog.

Polls

In case these past polls are of interest to somebody, somewhere, here are four survey's worth of results...

Standards

Why should you bother with standards? Maybe a couple of illsutrations are in order: consider a finance database that has a table called "Account", a query called "Accounts", a form called called "Account" and a report called "Accounts". Pretty confusing already, isn't it? Now try referring to these items in code, and you'll see what a nightmare naming can be. Alternatively, think of a form you've created without changing any of the control names - in six months time are you going to be able to easily tell TextBox8 from TextBox9, or ComboBox13 from ComboBox31?

Wouldn't it be a whole lot easier to adopt a naming convention that enables you to know what something is just from its name? Well, there are numerous naming conventions out there, the most popular of which, from an Access perspective at least, is the Leszynski/Reddick VBA naming conventions, a copy of which can be found in Word format here. You'll find the code samples on this site loosely conform to these standards. Like many developers, I use the subset of these standards that works for me, and I expect you'll do the same.

Anyway, regardless of whether you buy into the Leszynski/Reddick conventions or define your own, here are some general pointers on standards:

• even if you define your own, use some sort of standard and stick to it - you'll thank me for this later. It's more important to work to a standard - any standard - than to adhere slavishly to a particular standard.
• document your conventions so that others may follow your work. If you use an existing convention, reference it in your application's documentation.
• don't use spaces in object names - concatenate words instead, and use capitalisation to make things clear (e.g. use "CustomerDetails" instead of "Customer details"). apply your convention as you go along - backtracking over your database to rename all objects is no fun, and mistakes are easily made, even with third-party "search-and-replace" tools.

Adding an "ALL" option to a combo-box

We're all familiar with using a combo-box (also known as a drop-down list) to select data from a pre-defined or data-driven list of values. It's also quite common to use a combo-box to select a data value which can then be used in some context, for example selecting a value which is then used to filter a form or report. When you're using a combo-box in this way, it can often be good to add an “ALL” option at the top of the list, to show that you want everything... but how do you do this?

Fortunately it's a piece of cake, but there are different methods depending on what the row-source of the combo-box is. Broadly speaking, there are four generic types of combo-box row-source:

1. The Value List - the simplest row-source, with data values typed in and separated by semi-colons, so a row-source of North;South;East;West will give the drop-down list four simple values.
2. The Simple Bind - if a combo is on a form that's bound to a table, then the row-source of that combo can just be a table field name; as long as that field has a lookup set up at a table level, that lookup will cascade to the form and populate the combo-box drop-down list.
3. The Simple SELECT - a combo-box can have a simple SQL statement as the row-source, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID; would produce a drop-down list of Region names in alphabetical order.
4. The Complex SELECT - often a combo-box will use a SQL statement that returns more than one field, typically a numeric ID field and one or more narrative text fields. The ID field can then be given a column width of zero in the combo-box, allowing the user to see and highlight an intuitive and meaningful text value but to actually be selecting a numeric ID value. Such a row-source might look something like SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so we're happy with the four basic types of combo-box row-source as they might appear on a form control. Before we consider how to change each type to add an "ALL" option, let me first explain the general principle of what we're going to do. We're starting out with a combo-box from which we can select a value and then use that value in some way. For example, you might have a button on your form that opens a report but filters the data therein based on the value selected in your combo-box - in this case, the OnClick event of that button might include some VBA along the lines of DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion where Me.cboRegion represents the value of the combo-box. But to add an “ALL” option, we're going to have to change our thinking because what we're actually going to be adding is a “*” wildcard character option. So, in our subsequent use of the combo-box value, instead of using an Equals operator (=) we're going to have to think about using a LIKE operator. In other words, the previous example will have to change to something like DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE " & Me.cboRegion

That's easy enough, isn't it? The important thing to remember is this works because the SQL WHERE constraint of “LIKE '*'” returns everything that isn't Null.

Okay, enough theory - here are some examples of how to change each of the four combo-box row-source types to add that “ALL” option.

Row-source	Without “ALL”	With “ALL”
Value List	North;South;East;West	*;ALL;North;North;South;South;East;East;West;West
Simple Bind	The cascaded table field's lookup, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;	A UNION statement, e.g. SELECT "*" AS RegionID, "ALL" AS RegionName, 0 AS SortOrder FROM tblRegion UNION SELECT RegionID, RegionName, 1 FROM tblRegion ORDER BY SortOrder, RegionName;
Simple SELECT	A SELECT statement with one output, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID;
Complex SELECT	A SELECT statement with more than one output, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so you probably have a few questions now, such as:

Q. Why does my combo-box now show an asterisk or a number, instead of my nice intuitive narrative text?

A. Unless you tell it otherwise, the combo-box will still display the same number of columns, with the same widths, as before you made the changes outlined above. To restore the nice formatting, you need to do all the following:

• increase the combo-box's Column Count property - in the example above, the Column Count property would be 3 (2 for the Value List example), as the SQL statement returns three fields;
• add a column width of 0cm to the start of the Column Widths property (so the */ID field is hidden) - in the example above the Column Widths property would be 0cm;2.54cm;0cm (0cm;2.54cm for the Value List example) as you wouldn't want to see the third column either;
• ensure that the Bound Column property is set to 1.

Q. What's that “SortOrder” value all about?

A. You want your “ALL” to appear at the top of your drop-down list, right? But what happens when sorting alphabetically doesn't put “ALL” at the top? For example, you might have a list of countries, in which you'd find that Albania came above “ALL” in your combo-box. By adding a simple numeric flag called SortOrder, this can be avoided: SortOrder has a value of 0 for your “ALL” option and 1 for everything else. SortOrder can then be used as the first item in your row-source SQL statement's ORDER BY clause, to ensure that “ALL” comes first - subsequent items can then be added to the ORDER BY clause to sort the remaining data items however you like.

Q. Hows does it work again?

A. Time to revisit the example. Let's say you have a combo-box called cboRegion on a form; previously it had a simple SELECT style row-source. You want to use this control to restrict the data included in a report - the report is opened by clicking a button on the same form. Previously, the button's OnClick event would have contained something like this:

DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion 

If you'd selected a RegionID of 1 from your combo-box, this would have parsed as

DoCmd.OpenReport "rptSalesman", , , "RegionID = 1" 

Now you have something like:

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '" & Me.cboRegion & "'" 

If you select “ALL” from your combo-box, this will now parse as

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '*'" 

And, as we learnt earlier, "LIKE '*'” returns everything, and so gives you your “ALL” option. Hooray!

Q. I still don't get it - can't I download an example database?

A. Yes you can (I'm good to you, aren't I?). There's no difference in the code at all but, due to popular demand, I've created Access 97, 2000 and 2002 versions of a database to illustrate the example we've talked about. They have two tables (tblRegion and tblSalesman), one form (misleadingly entitled frmSalesman) and one report (rptSalesman). Take a look at the properties of cboRegion and the OnClick event of cmdRpt in frmSalesman - you can surely work the rest out for yourself!

Free download samples: Access 2002 and above | Access 2000 | Access 97 - you'll need unzipping software for these downloads, such as WinZip or 7-Zip.

Creating a “save record?” prompt

By default, any changes made to a bound record in a form are automatically saved whenever you move away from that record - this could be by moving to another record, by creating a new record or by closing the form. This is generally fine, but what if an unplanned change has been made in error? Wouldn't it be nice to get a “record changed - save changes?” type prompt when you move away from a record?

Fortunately this is easily achieved through use of the form's BeforeUpdate event - this is triggered by saving changes to a bound record (that's why it called “before update”, after all), so it's triggered when you move away from a modified record. Of course, it's also triggered if you do an explicit Save operation, but that's okay, it then just gives you a chance to confirm that you want to save, which is generally a good thing too. Anyway, here's the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel + vbQuestion)
    Case vbYes 'Go-ahead and save
        'Don't have to do anything
    Case vbNo 'Undo all changes, close without saving
        Me.Undo
    Case Else 'Cancel change, continue editing
        Cancel = True
    End Select
End Sub

Changing the default printer

It's a common question - how do you control what printer is used in your application? Especially if you don't want to use the default printer?

Access 2000 introduced the Application object which, amongst many other things, has a Printer property that can be easily used to set and change the current default printer. Here's how you might use it:

Dim prt As Printer 'Define a printer variable
Set prt = Application.Printer 'Set the variable to the current default printer
Application.Printer = Application.Printers("\\YourPrintServer\YourPrinter") 'Change the default printer
'Do whatever printing activities you want here
Application.Printer = prt 'Don't forget to set back to the user's original default printer

Personally, I find this most useful for printing things to my PDF queue which, needless to say, isn't my default printer. Speaking of which, if you're looking for a free PDF print queue tool, let me save you looking as I've tried dozens. The best, in my view, are Cute PDF and Bullzip - not only are they properly free (no nags or adverts), they’re very easy to use and produce smaller PDFs than lots of comparable products.

Control Access options at start-up

From Access 2000 onwards, default behaviour is to have multiple windows open for multiple database components, i.e. if you open a database and then open a form and a report you'll have three Access windows open on your taskbar. Now I don't like this - it's messy and it spoils the illusion of your nicely packaged Access database looking like a standalone application. What you need is a way of enforcing the "one window" constraint.

Later versions of Access also have a function that enables object name changes to be tracked. This is great in a development environment but really should be turned off when your database is deployed for live use - you'll get huge performance gains by doing this. Of course, you can set these options before the database is deployed, but unless you have tightly secured your database what's to stop an inquisitive user turning it back on? What you need is a way of enforcing these options.

Fortunately, both these requirements can be met using VBA. You want this code to run at start-up, so I guess you could put it in the OnLoad event of a start-up form if you have one. Typically though, I prefer to write a function called fctnAutoExec and call this from an AutoExec macro using the RunCode action. This will then run at start-up by default. I often give fctnAutoExec a boolean parameter so that I can differentiate between it running at start-up or me running it by some other means and at some other time from within the database application. Anyway, fctnAutoExec might take the form:

Public fctnAutoExec(Optional booStartup As Boolean)
    'Ensure name change tracking options are turned off
    Application.SetOption "Log Name AutoCorrect Changes", False
    Application.SetOption "Perform Name AutoCorrect", False
    Application.SetOption "Track Name AutoCorrect Info", False
    'Ensure multiple windows in Taskbar are disabled
    Application.SetOption "ShowWindowsInTaskbar", False
    If booStarting Then
        'Do stuff that you want at actual start-up
        'i.e. things you might omit from ad-hoc runs.
        'E.g. write "logon" entry to user history table
    End If
End Function

So, to run the function from your AutoExec macro you'd just use the RunCode action with fctnAutoExec(True) as the parameter. To run the function on an ad-hoc basis from within your database, for example if you want to re-initialise settings but without doing any start-up only stuff, you'd just call the function as fctnAutoExec(False) or even just fctnAutoExec, since a Boolean data type defaults to False.

As a footnote, you should know that both the “track changes” and “show windows in taskbar” settings can be set manually before you deploy, using Tools, Options, General and Tools, Options, View respectively. This VBA does give you a nice way of ensuring your preferences are always applied though.

Faster recordset iterations

Looping through a DAO recordset is a pretty common requirement, isn't it? And in each loop, more often than not you'll want to do something to the value or values that you find in certain fields. Ever noticed how such an iterative process is not particularly fast? Especially if you have a big recordset...

Here's an example of a traditional iterative loop - each time through, the code assigns the name of a supplier to a string variable (in other words, this is about the simplest example I could think of):

Dim strSupplier As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
    strSupplier = rst("SupplierName")
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Pretty standard stuff, I'm sure you'll agree, and maybe even how you'd write the code yourself if asked. Bill Gates would undoubtedly approve. But it's slow! If your supplier table is complex and holds several thousand records this is going to be very tedious! And all we're doing is assigning a value to a string variable. What if we want to do something more complex? And maybe to/with more than one field value? We need a faster method... fortunately, help is at hand in the shape of the Field object. Compare the example above to the code that follows, which has exactly the same effect but can be 20+ times faster:

Dim strSupplier As String
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
Set fld = rst("SupplierName")
rst.MoveFirst
Do Until rst.EOF
    strSupplier = fld
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Neat, isn't it? Whether by accident or design, once the Field object variable has been defined as equating to a certain field in a Recordset object, it automatically updates itself for each record as you iterate through that recordset. And anything that can give you such a hike in speed (for simple DAO recordset operations - differences for ADO recordsets are less apparent) in an Access environment has got to be worth exploring, surely?

Running parametised queries from code

We've all been there sometimes - you've got a parametised query which works fine, but you want to call it and supply the parameters from code. The most likely reason for doing this is that you want to run the query in a batch job or just without requiring user input.

Aside from the obvious comment (if you know the values you want to use, why use your query at all? Why not just embed some SQL in your code?), if you really want to use your query and keep things simple, here's how you do it from code. In this example, you've got a query called qryFilterStaffByTitle which takes a parameter to restrict staff records to those of certain job titles. In the query design grid, the prompt used for the parameter is [Please enter the required job title here]. Here's the equivalent code for running the query and supplying the value of "Manager" to the job title parameter:

Dim dbs As Database, rst As Recordset, qdfParam As QueryDef
Set dbs = DBEngine(0)(0)
Set qdfParam = dbs.QueryDefs("qryFilterStaffByTitle")
qdfParam![Please enter the required job title here] = "Manager"
Set rst = qdfParam.OpenRecordset()

Creating a scrolling marquee field

Creating a marquee field (you know: a text box or label that scrolls, common on some websites) in an Access Data Page is easy, because there's a control for it in your toolbox. Not so when you're working with forms though. I was surprised to find a number of people searching this site for help on this subject, so here's an easy way of adding a scrolling marquee to your forms.

Let's do a simple example first - a Label control will act as a marquee for us. Here's what you do:

1. Create a form and place a Label control on it - name it lblMarquee and fill it with a nice long text string (for demonstration purposes, size the label so that you can't see all the text on screen when you view the form).
2. Set the form's TimerInterval property to 200 - this property is measured in milliseconds, so a value of 200 equates to one fifth of a second.
3. Create an event procedure for the form's OnTimer event that seems to scroll the label by simply taking off the first character of the string and appending it to the end, like so:

Private Sub Form_Timer()
    lblMarquee.Caption=Mid(lblMarquee.Caption, 2) & Left(lblMarquee.Caption, 1)
End Sub

4. Check out your form - you have a scrolling marquee field!

Okay, but that's a pretty basic example. It would be more useful if the marquee stopped scrolling when the mouse pointer hovered over it. This is a tiny bit more complicated... but still very easy. In fact, I bet I can show you how to do it in eight easy steps...

1. Start the same way as in the simple example: create a form and place a Label control on it called lblMarquee, then fill the label with a long text string.
2. This time though, leave the form's TimerInterval property set to 0.
3. Create an event procedure for the form's OnTimer event that seems to scroll the label - this uses exactly the same code fragment as the simple example, above.
4. Whilst you're in the VBA code editor window, declare a constant called intTimer with a value of 200 - place this declaration in the General Declarations section at the top of your code. This is so that if you subsequently want to change the speed of the scroll you only have to change the timer interval value in one place.
5. Create an event procedure for the Form's OnOpen event that sets the form's TimerInterval property to intTimer (to start the marquee scrolling).
6. Create an event procedure for the lblMarquee's OnMouseMove event that sets the form's TimerInterval property to 0 (to stop the marquee when the mouse hovers over it).
7. Create event procedures for the OnMouseMove events of the Form and its Detail section that set the form's TimerInterval property back to intTimer (these restart the marquee when the mouse moves away from the label).
8. Check out your form - you now have a scrolling marquee that pauses when the mouse hovers over it!

Here's the VBA for this form in full:

Private Const intTimer As Integer=200 'Use a constant for the timer interval

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval=intTimer 'Sets initial scrolling speed
End Sub

Private Sub Form_Timer()
    lblMarquee=Mid(lblMarquee.Caption, 2) & Left(lblMarquee.Caption, 1) 'Does the scrolling
End Sub

Private Sub lblMarquee_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=0 'A zero value stops the Timer event, and hence the scrolling
End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=intTimer 'Restarts the marquee
End Sub

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=intTimer 'Restarts the marquee
End Sub

Clearly this isn't very sophisticated but you get the idea of how this can be made to work. Try experimenting with values other than 200 to see what value gives the smoothest scrolling (you might find it varies depending on font face and size). Also, I believe this code could be adapted to make other control types scroll too (a text box being the obvious alternative).

Sending an e-mail from Access

For a long time now, there has been a very easy way to send e-mails from within Access - just use the DoCmd.SendObject command without specifying an object to send! This worked (note the past tense) fine if you just wanted to send basic e-mails using your default, MAPI-compliant mail software.

However, with the introduction of Access 2000, this solution became very buggy - not only would it not always work, sometimes it would fail without any indication which could be a bit of a problem if you were sending mails without editing them first. For full details of the spurious nature of this bug, check out this Microsoft KnowledgeBase article. And bless 'em, our friends at Redmond even went so far as to post a (pretty rudimentary) alternative piece of code, which completists can read here.

Game over - you don't need to read any further, right? Wrong! As I've said, the alternative code supplied by Microsoft is fairly basic but, to be fair, it did form my starting point for the following piece of code. Basically, my e-mail function, posted here for your delectation, uses Outlook to not only send an e-mail but to do so specifying all the following parameters:

• The sender - handy if you have more than one Service set up in Outlook
• The recipient(s) - addressee(s), CC and BCC
• The subject
• The message text
• Attachment path
• Voting options
• Urgency
• Edit before sending - true or false

The sender, voting, urgency and attachment path are all features not unsupported by the old SendObject method of e-mailing, so in many ways the Access 2000 bug has been beneficial.

To use this code, you must first set a reference to the Outlook Object Library (in Access 2000 you can do this from the Tools, References menu option in the Visual Basic Editor) - the file you're looking to reference is an OLB file, e.g. msoutl9.olb for Outlook 2000.

Here's an example of how to call this function. In this example, an e-mail will be sent to Jo Smith and Estelle Jones, CC'ing Bob Downes, with a short subject and title, and an attachment. The message will be sent with high priority, and Yes/No/Maybe voting buttons. Finally, we'll edit the message before it's sent.

fctnOutlook , "Jo Smith;Estelle Jones", "Bob Downes", , "My subject here", "My message here", "c:\temp\att.txt", "Yes;No;Maybe", 2, True

Note that multiple names in the same parameter are separated by semi-colons, as are the options in the voting buttons string.

Finally, you'll see that when you use this code you get prompted to confirm that it's okay for your program to send an e-mail using Outlook. This is a virus-protection method introduced by Microsoft (read their white paper on this for more info) to prevent script-kiddies writing malicious code to send self-propagating mails to everyone in your address book. Using straight Outlook this is largely unavoidable, so you'll have to get used to clicking the “Yes” button (unless you want to consider Outlook Redemption as a freeware solution to this inconvenience, although that means getting away from straight Outlook). At least you get an option to allow your program access to Outlook for up to 10 minutes, which can be handy if you're about to generate a whole load of e-mails. Anyway, here's the code for you to cut and paste.

Function fctnOutlook(Optional FromAddr, Optional Addr, Optional CC, Optional BCC, _
Optional Subject, Optional MessageText, Optional AttachmentPath, Optional Vote As String = vbNullString, _
Optional Urgency As Byte = 1, Optional EditMessage As Boolean = True)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
    If Not IsMissing(FromAddr) Then
        .SentOnBehalfOfName = FromAddr
    End If

    If Not IsMissing(Addr) Then
        Set objOutlookRecip = .Recipients.Add(Addr)
        objOutlookRecip.Type = olTo
    End If

    If Not IsMissing(CC) Then
        Set objOutlookRecip = .Recipients.Add(CC)
        objOutlookRecip.Type = olCC
    End If

    If Not IsMissing(BCC) Then
        Set objOutlookRecip = .Recipients.Add(BCC)
        objOutlookRecip.Type = olBCC
    End If

    If Not IsMissing(Subject) Then
        .Subject = Subject
    End If

    If Not IsMissing(MessageText) Then
        .Body = MessageText
    End If

    If Not IsMissing(AttachmentPath) Then
        'Check file exists before attaching!
        If Len(Dir(AttachmentPath)) > 0 Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        Else
            MsgBox "Attachment not found.", vbExclamation
        End If
    End If

    If IsNull(Vote) = False Then
        .VotingOptions = Vote
    End If

    Select Case Urgency
    Case 2
        .Importance = olImportanceHigh
    Case 0
        .Importance = olImportanceLow
    Case Else
        .Importance = olImportanceNormal
    End Select

    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

    If EditMessage Then
        .Display
    Else
        .Save
        .Send
    End If
End With

Set objOutlook = Nothing

End Function

Querying users and security groups

Unbelievably, it is possible to part with your hard-earned cash for commercial tools that, amongst other things, present a summary of user/group membership in an easy-to-interpret way. People can get away with it because these tools are much easier to use and interpret in this respect than Access's integrated view of the same information, which constrains you to looking at either all the members of one group or all the group memberships of one user. Even I, the most ardent of Access-philes, have to admit that sucks.

So what's the solution? Part with your hard-earned cash? Never! Instead, let's delve deeper into your system.mdw file. For the uninitiated, this is the workgroup information file that controls the security information for the database in question. There is a default workgroup information file that is used for all databases unless you specify otherwise via the /wrkgrp command line parameter. But I digress...

Take a look in the system.mdw file - I find my default version at C:\Program Files\Common Files\System\system.mdw (I'm using Access 2000, by the way). Equally, you can open up any copy of a system.mdw file that you might be using for other databases. Either way, you'll find a couple of tables in there, MSysAccounts and MSysGroups. If you can't see these: go to Tools, Options and check System Objects on the View tab. You'll also find some queries, notably MSysGroupMembers and MSysUserMemberships which are on the way to what we want but still constrain you to the one user/group at a time view mentioned earlier.

So where were we? Well, as the name suggests, the MSysAccounts and MSysGroups tables store info on the users and security groups in your workgroup information file, so we can write a neat crosstab query to provide a grid of which users belong to which groups, like so:

TRANSFORM Count(MSysGroups.GroupSID) AS CountOfGroupSID
SELECT MSysAccounts.Name
FROM (MSysAccounts INNER JOIN MSysGroups ON MSysAccounts.SID=MSysGroups.UserSID)
INNER JOIN MSysAccounts AS MSysAccounts_1 ON MSysGroups.GroupSID=MSysAccounts_1.SID
WHERE (((MSysAccounts.FGroup)=0) AND ((MSysAccounts_1.FGroup)<>0))
GROUP BY MSysAccounts.Name
PIVOT MSysAccounts_1.Name;

Go on, give it a try! It's pretty tidy, isn't it? One thing to note - you don't have to do this in the system.mdw - if you'd rather integrate this crosstab in your database application, simply create links to the two tables in the relevant system.mdw file (File, Get External Data, Link Tables...) and then you can create the query above in your database file.

Linking to Outlook folders

Considering how thoroughly it's explained in the Help file, it's surprising how often the question “how can I link to Outlook folders?” crops up. This is popular as, for example, it allows the user to query the contents of their Inbox, use data from their Contacts list, report on their Calendar, and so on. Fortunately for us, this is very easy to do - if you can link to a table, you can link to virtually any Outlook folder on your friendly neighbourhood Exchange Server. We'll go through how in just a second but first a note for all users of Access 97 (are there any left?) - you can do this too but only after you've installed a free add-on from Microsoft, available here.

Now that's out of the way, here's how you do the linking.

1. In Access click File, Get External Data, Link Tables.
2. In the resultant “Link” dialogue box, change the Files of type parameter to Exchange or Outlook (either will do fine). This launches the Link Exchange/Outlook Wizard.
3. Use the tree structure shown by the Wizard to highlight the Outlook folder you want to link to and click Next.
4. Specify a name for the linked table.
5. Untick the “store my MAPI profile” check-box (unless you are likely to be the only user of the linked table).
6. Click Finish.

And that's it, you then have a table in your database that links back to your Outlook folder, and can be queried just like any other table. Note that this doesn't work for Outlook Express.

Calculating the median of a field

Access comes loaded with mathematical functions for all kinds of things, and it's easy to determine the sum, average, maximum or minimum of a field in a table, using either a query or some VBA. But what about some more complicated functions? What if you want to, for example, know the median value of a field in a table? For those of you whose maths is a bit rusty, the median is the value below which 50% of the data falls - the physical midpoint in a range, if you will. Access does provide a MEDIAN() worksheet function but this only works on a series of numbers rather than table data. It's still useful for illustrating exactly what a median is though - check out these examples:

MEDIAN(1, 2, 3, 4, 5) equals 3

MEDIAN(1, 2, 3, 4, 5, 6) equals 3.5, the average of 3 and 4

So anyway, you want to work out the median of a table field. The following function takes two parameters, the name of the table and the name of the field, both as strings. Note that if you have spaces in either name (naughty you) you'll need to enclose them in square brackets before you pass them to the function. Anyway, the function just returns the median value.

Function fctnMedian(strTable As String, strField As String) As Variant
    Dim dbs As DAO.Database, rst As DAO.Recordset 'You must add a reference to the DAO Object Library too
    Dim booEven as Boolean
    Set dbs = DBEngine(0)(0)
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTable & " ORDER BY " & strField)
    If rst.EOF = False Then
        rst.MoveLast
        booEven = (rst.RecordCount Mod 2 = 0) 'Is there an even number of records in the recordset?
        rst.PercentPosition = 50 'Rounds down if there is an even number of records...
        fctnMedian = rst.Fields(strField)
        If booEven Then
            rst.MoveNext
            fctnMedian = (fctnMedian + rst.Fields(strField)) / 2 'Takes average of this & the next value up
        End If
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Converting macros to VBA

There will, inevitably, come a time when you get sick of macros - they're slow, unwieldy and clutter up your database. Who needs them (with the exception of AutoExec and AutoKeys, of course)? Most annoying of all though, macros just don't give you the flexibility you might want - what if you want to add custom error-handling, or perform some action beyond the dazzling array that the RunCommand macro action gives you?

It's time, then, to convert your crappy old macros to shiny VBA. Fortunately, this conversion couldn't be easier. Simply highlight the macro in question in the database window, then click Tools, Macro, Convert Macros to Visual Basic. You'll be prompted on-screen to confirm whether you want to add comments and error-handling. I'd recommend always saying yes to these questions; after all, you can always delete the comments later if they're not appropriate, and the default error-handling saves you a bit of typing when you come to write your own.

There are a couple of things to note though. Each macro you convert will be put in its own module - if you want to simplify things later on, you'll have to manually cut and paste the created VBA code into the module of you choice, then delete the surplus module. These created modules have names of the form “Converted Macro - Your macro name here”.

It's also worth remembering that the VBA this conversion process creates might not always be as slick as you might like. Consider the simplest of examples: a simple macro called mcrOpenMain which has one action, OpenForm, to open frmMain. Converting this in the manner described above produces the following VBA:

'------------------------------------------------------------
' mcrOpenMain
'
'------------------------------------------------------------
Function mcrOpenMain()
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain", acNormal, "", "", , acNormal

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Error$
    Resume mcrOpenMain_Exit

End Function

Now this isn't too horrendous, and certainly isn't as bad as the VBA that some of the form design wizards (notably the command button one) write. But it does add all the optional command parameters in and does ugly error handling - I like to see the error number too, for a start. My advice then is this: once you're happy that the VBA does what it's supposed to do, delete/edit any surplus comment lines, strip out any unnecessary command parameters and improve the error-handling loop... something like this:

Function mcrOpenMain()
' Converted from mcrOpenMain on 15-Sep-2005 by A. Developer
' Called from menu mnuMain
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain"

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume mcrOpenMain_Exit

End Function

Much nicer, wouldn't you say? Depending on how fussy you are, you might also want to consider renaming the created function so that it doesn't start with “mcr” - but if you do, don't forget to check and change any other references to your nice new function, otherwise they won't run.