It is very common for us to embed some kind of logic based on a lookup table or in some cases, a column of a table. Because we have application logic connected to it, the code are fragile and subject to changes as the requirements develops. We want to avoid this situation where we might…
Normalize all constraints’ & indices’ names
Constraints that are created by SSMA or by user via SSMS tend to have ugly names. Worse, auto-generated names are not stable across backups. Meaning if you restore a new database based on a copy of another database, the constraint names will change. That create huge problems during migrations because scripts that references constraint will…
Access CDate() function to SQL Server equivalent
What is the SQL Server equivalent to Access CDate() function? Access SQL CDate([Event Timestamp]) SQL Server Equivalent cast([Event Timestamp] as datetime) convert(datetime, [Event Timestamp])
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…
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
Access 2007: Access Developer Extensions
The Microsoft Office Access 2007 Developer Extensions make it easy to deploy and manage solutions built using Microsoft Access. The Access 2007 Developer Extensions provide packaging and deployment tools and licensing and distribution agreements to make it easier for developers to bring solutions to market. Whether you are working in a small business or a…
How to Display a Form Automatically when your Application Opens
This is a quick tip on how to automatically display a form when the user opens your Access Application. In addition to improving User Friendliness, your database design will also appear much more professional. The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening. This will…
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…
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:
|
1 2 3 |
For Each ctl In Me.Controls Debug.Print ctl.ControlSource 'A potential error Next |
Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:
|
1 2 3 4 5 |
For Each ctl In Me.Controls On Error Resume Next Debug.Print ctl.ControlSource 'A potential error On Error GoTo 0 Next |
This will work…
Fixes or workarounds for recent issues in Access
Access crashes and slow performance Error: “Automatic configuration of the current version of Microsoft Access has failed.” occurs when you try to start Microsoft Access after updating to Version 1802 [FIXED] ISSUE You may get the following error when start Access after updating to Monthly Channel Version 1802 (Build 9029.2167 or higher): “Automatic configuration of…