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…
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…
Microsoft Access saves information under the following file formats: File Format Extension Access Project (2007, 2010) .adp Access Blank Project Template (2007, 2010) .adn Access Database (2007, 2010, 2013, 2016) .accdb Access Record-Locking Information (2007, 2010, 2013, 2016) laccdb Access Web App Reference .accdw Access Signed Packages .accdc Access Database, used for Add-ins .accda Access…
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…
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…
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 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…
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…
Drag and Drop In MS Access Doug was on a roll in January of 2004 when he wrote his article on drag and drop in MS Access. He tested it now using Access 2016 and it still works. This article shows you how to add drag-and-drop to your Access application using combinations of multi-value and…
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.