Monday 4 December 2017

Global variables

Having been an Access guru until 2004 and then not really doing anything with it since, it has been joyous recently to get drawn back into the world of Microsoft's desktop database. There have been a lot of changes since 2004 though, mostly for the best. Even the changes that are not so good (the demise of user-level security, for example) I can understand - Microsoft want to push developers who need user-level permissions towards other platforms, like SQL Server.

But I digress. Doing Access development again has been fun, and I found myself thinking about global variables on the way into work this morning. What are they, and where should you put them?

Well, a global (or environment) variable is a variable with global scope, meaning that it is visible and accessible throughout the program. And in Access, there are two places you can put them:

  1. Create a module called something like basGlobal and add the following:
  2. Option Explicit
    
    Public strMsg As String 'Adds a global string variable called strMsg, accessible everywhere because it's public
    Public Const lngSomeNumber As Long = 994 'Adds a numeric global constant called lngSomeNumber
  3. Create a table and store it there. Create a table called USysMetadata (and watch how prefixing a table with USys ["user-system"] hides it from default users) with two columns, MetaName and MetaValue. Store the name of the global variable in MetaName and the value in MetaValue.

Easy, right? But which approach to use, when? Especially when both have their advantages. The module approach is better for performance, especially if your database is deployed compiled (MDE or ACCDE). Retrieving variable values from USysMetadata might typically involve a DLookup("MetaValue", "USysMetadata", "MetaName='YourVariableNameHere'") or some SQL, neither or which is so fast. But on the flip side, if you need to add more global variables, well, you can't do that in a multi-user environment so easily for the basGlobal approach, since you need exclusive access to make design changes. By contrast, to add more using USysMetadata, just add a new row to the table. Couldn't be easier, and exclusive access is not required.

The other difference here is that USysMetadata allows variable values to be retained after you've logged out - basGlobal doesn't allow this.

So, some general rules (whilst noting that there are always exceptions to such rules):

  • For global constants (i.e. not variables) use basGlobal for performance.
  • For session variables (i.e. that don't need to be retained between log-ins) use basGlobal for performance.
  • For true system variables (i.e. that can be set and read by all users and/or that need to be retained across sessions) use USysMetadata for flexibility.

It's nice to be back, offering up my Access two penn'orth, even if no-one reads it.

Wednesday 11 November 2015

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.

Tuesday 10 November 2015

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.

Monday 9 November 2015

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).

Friday 6 November 2015

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.

Thursday 5 November 2015

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

Wednesday 4 November 2015

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.

Tuesday 3 November 2015

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

Monday 2 November 2015

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.

Friday 30 October 2015

Polls

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