Access Web Apps

Access Web Apps to be retired

It was announced that Access Services is going to be retired. Details of the announcement may be found here: Updating the Access Services in SharePoint Roadmap. Additional timeline information along with possible methods for exporting your data out of an Access web app may be found here: Access web apps no longer supported. While these articles…

Why does Excel have a problem when using code in Access?

When you use code to refer to Excel (and other programs as well) you need to be careful not to use any code that does not refer back to the instantiated application object. If you do so then you will find that Access instantiates another Excel (or other program) object which is not visible to…

#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…

Use TryGet pattern for transient values

A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:

Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:

This will work…

“Too Few Parameters” error fix

Symptoms Whenever you run a SQL statement you get a “Too Few Parameters, expected X.” Cause Most common cause is misspelling or missing fields in the SQL statement. Resolution Check the SQL statement. If necessary, copy and paste to an Access query and test in the query to get it to highlight which part of…

MODULE LEVEL VARIABLES

Module level variables

It is common to create a big wall of declaration in a module such as this:

While the code will compile and work fine, the discoverability and naming of the module level variables does become difficult and unwieldy. To help cut down on this ambiguity, it is recommended to create a private user-defined type…

Self-documenting code

Strong preference should be given to writing code that is self-documenting. A common approach is to write a lot of comments, but the problem with comments are twofold: It actually can harm the readability because it adds to the overall length of code to read and understand. It must be correct or it can end…

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…

Access IIf() function to SQL Server equivalent

What is the SQL Server equivalent to Access IIf() function? Access SQL IIf([ResultValue]>=1, [Result Value], Null) SQL Server CASE WHEN [Result Value]>=1 THEN [Result Value] ELSE NULL END

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…