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])
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…
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: 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…
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…
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…
Use properties instead of constant or magic numbers
A common programming mistake is to hard-code literals that corresponds to some property. That is particularly true with certain controls like tab controls & pages. The tab control exposes a Value property that indicates the current tab and the pages contains a PageIndex property that indicates its position in the tabs of the control. Thus,…
Microsoft Access 2003 Language Reference [Access 2003 VBA Language Reference]
This reference contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Access. The reference contains the following sections: What’s New: Provides a list of new members by object and in alphabetical order. Concepts: Provides important concepts for developing custom Access solutions. Reference: Provides reference materials for the Access…
Using Microsoft Access Data in a Mail Merge
Suppose you have an Access Database containing the names and addresses of your customers. Every now and again you may want or need to send a standard letter to each customer who has opted in to such mailings. However, you are aware that these letters are going to look so much better, and work so…
Hiding the Navigation Pane with VBA
Once you have created an Access Database Application to be used by other people, it may well be important for you to prevent users from gaining access to any of its design features. By this I mean, you may not want users to modify your tables, forms and queries etc in design view (inadvertently or…
IS NULL: Understanding and Using the Null Value in Microsoft Access
In Microsoft Access, a null value is a value that is not assigned or unknown. When working with databases, it’s important to understand how to use and identify null values in order to ensure accurate and efficient data management. The IS NULL operator is one of the most useful tools for working with null values…