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
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…
“Too Few Parameters” error fix
Symptoms Whenever you run a SQL statement you get a “Too Few Parameters, expected X.” Cause Most common cause is misspelling or missing fields in the SQL statement. Resolution Check the SQL statement. If necessary, copy and paste to an Access query and test in the query to get it to highlight which part of…
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
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…
Access 2003 Technical Articles
In this section of the MSDN Library, you’ll find technical articles that demonstrate how to build and deploy complex Access 2003 runtime-based solutions, automate Access from another application; use the new XML features in Access 2003, and even how you can animate Access 2003. You can use the TOC to your left to navigate through…
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…
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…
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…
Access 2010: Unrecognized Database Format
I’ve been seeing more and more people having problems with the Unrecognized Database Format when creating or modifying an Access 2007 (accdb) format database in Access 2010 and then trying to open it again in Access 2007. The issue appears to be that Access 2010 does not have its own file format. When you add a…