IS NULL: Understanding and Using the Null Value in Microsoft Access

In Microsoft Access, a null value is a value that is not assigned or unknown. When working with databases, it’s important to understand how to use and identify null values in order to ensure accurate and efficient data management. The IS NULL operator is one of the most useful tools for working with null values…

ID vs Code

There are cases where a table’s primary key is referred to often in the code. In fact, it might have some special meanings in code. It is highly encouraged to avoid the use of IDENTITY or AutoNumber (or any auto-incrementing schema) for those keys because it is also usually desirable to create a VBA Enum…

Using Microsoft Access Data in a Mail Merge

Suppose you have an Access Database containing the names and addresses of your customers. Every now and again you may want or need to send a standard letter to each customer who has opted in to such mailings. However, you are aware that these letters are going to look so much better, and work so…

Hardware Recommendations For Running Access

Hardware matters. Invest in a faster PC and your Microsoft Access databases will run faster. Be alert for sellers packaging fast processors with minimal memory and a slow hard drive to keep prices low. Look at all aspects of the purchase when database speed is a priority. A faster hard drive is worth the investment…

Minimizing stateful code

First we need to define what a state is. A state means that the object has variable(s) that may cause it to behave differently, depending on the content of the variables. As an example, the Forms collection is stateful:

This is legal code and will compile but we can’t be assured that this will…

Unrecognized Database Format

Access 2010: Unrecognized Database Format

I’ve been seeing more and more people having problems with the Unrecognized Database Format when creating or modifying an Access 2007 (accdb) format database in Access 2010 and then trying to open it again in Access 2007. The issue appears to be that Access 2010 does not have its own file format. When you add a…

Prefer compile time errors over run time errors

In VBA, there are several features that allow us to do things that may not be checked at the compile time. In particular, it is possible to write several late-bound expressions which means that it can potentially contain run-time errors. One common misconception about late-binding is that it’s a matter of adding a reference and…

Truthy and Falsy Values

“Truthy” and “Falsy” Values

VBA is quite very loose in what it deems “truthy” and “falsy”. For example, this procedure can produce surprising result:

At the first glance, it seems expected that if the bit field is set to 1, it would succeed. But in fact, it wouldn’t because True is equivalent to -1, not 1. Unless we…

#LateBind Constant

In standard code, we make use of #LateBind conditional compilation constant, which is typically defined via project’s Conditional Compilation Argument in the dialog: NOTE: The dialog can be found via Tools → <project name> Properties, which is located below the Options menu item. The menu item caption will be different as it includes the project…

Enable and Disable a Form Control using VBA

I was working on a design for an Order Management Database, and one of the tasks I dealt with involved dynamically Enabling or Disabling one of the form’s Command Buttons’ using VBA. This gave me the idea for the present Access tip. The command button was located on a Customer Details form which had an Orders…