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…
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…
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…
#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…
“Truthy” and “Falsy” Values
VBA is quite very loose in what it deems “truthy” and “falsy”. For example, this procedure can produce surprising result: If Me.MyBitField.Value = True Then ‘It is true Else ‘It is false End If At the first glance, it seems expected that if the bit field is set to 1, it would succeed. But in…
Access DateValue() function to SQL Server equivalent
What is the SQL Server equivalent to Access DateValue() function? Access SQL DateValue([Event Timestamp]) SQL Server Equivalent cast([Event Timestamp] as date) convert(date, [Event Timestamp])
Access UPDATE query to SQL Server
How do I convert an Access UPDATE query to SQL Server? Access SQL UPDATE Cars INNER JOIN UpdateList ON Cars.Parts = UpdateList.Parts SET Cars.Price = [Updatelist].[price] SQL Server UPDATE Cars SET Cars.Price = [Updatelist].[price] FROM Cars INNER JOIN UpdateList ON Cars.Parts = UpdateList.Parts
Access NZ() function to SQL Server equivalent
What is the SQL Server equivalent to Access NZ() function? Access SQL NZ([Event Timestamp]) SQL Server Equivalent COALESCE([Event Timestamp],0) ISNULL([Event Timestamp],0) * do not confuse this with the Access isnull() function.