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])
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…
Using Custom Functions in Calculated Controls
Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database. Today I am going to explain how custom functions can be…
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
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: Forms(“SomeForm”).Visible = False This is legal code and will compile but we can’t be assured that…
Module level variables
It is common to create a big wall of declaration in a module such as this: Private strCompanyName As String Private dteOrderDate As Date Private lngOrderID As Long Private objEmail As Object Private objOutlook As Object While the code will compile and work fine, the discoverability and naming of the module level variables does become…
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…
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…