2007.11.26 10:03 PM

Why No Nz() Outside of Access?

Microsoft Access wires up its one DAO.DBEngine instance, its OLEDB-based ADO CurrentProject.Connection instance (starting with Access 2000), and its OLEDB-based ADO CurrentProject.AccessConnection instance (starting with Access 2002) with a reference to its Access.Application instance, as well to its opened and referenced VBA projects, thus making their properties, methods, and (for VBA projects) public functions available for use within SQL queries executed via these database vectors. This makes it possible to execute queries like the following from within Access, whether via the Query maintenance feature or in code using DAO via DBEngine or CurrentDb(), or ADO via CurrentProject.Connection or CurrentProject.AccessConnection:

select
  Version AS AccessVersion, 
  CBool(Visible) AS IsVisible, 
  CurrentUser() AS UserName, 
  CLng(DCount('1','MSysObjects','Type = 1')) AS NumberOfTables, 
  Eval('Date()') AS EvalDate, 
  GetOption('Default Font Name') AS DatasheetFontName, 
  Nz(Null,'was null') AS NzTest, 
  CLng(Forms.Count) AS NumberOfOpenForms;

In Access 2003 with one test form open, this query returns the following results:

Step out of Access, though, and try to execute a query like this using DAO or OLEDB via ADO or ADO.NET (with your own DBEngine and/or Connection instances) and you'll encounter an error - usually a 3085 "Application-defined or object-defined error" in VBA, or maybe a -2147217900 "Undefined function 'xxx' in expression" in .NET. There is no way to overcome this, short of newing up a full instance of Access via COM automation and executing your queries using the resulting Access.Application object's DBEngine property or CurrentDb() method (for DAO), or its CurrentProject property's Connection or AccessConnection properties (for ADO).

The only non-intrinsic SQL functions available in JET database queries outside of Access are those found in the VBA library, whose objects are exposed for use via the underlying JET database engine (with consideration for certain sandbox restrictions). You can perform some crazy-complex calculations in your queries using functions like VBA's IPmt() and PPmt(), and you can evaluate conditional logic using VBA's IIf(), but you cannot Nz() a column to overcome a Null value, because Nz() is a method of the Access.Application object.

So what are the alternatives to Nz()? Well, you could combine VBA's IIf() and IsNull() to do the same thing:

select
  IIf(IsNull(SomePossiblyNullField), '', SomePossiblyNullField)
from
  SomeTable

Or, if you're dealing with potentially Null string values, you can take advantage of the non-conforming JET SQL syntax's explicit concatenation operator (&), which it shares with VBA, and pound an empty string onto your value. The following query will produce the same result as the previous one:

select
  SomePossiblyNullField & ''
from
  SomeTable

I say non-conforming because in most cases combining Null with anything via an operator results in a Null; however, the concatenation operator works differently, treating Nulls like empty strings. Mathematical operators, like +, -, *, etc., do conform with standard tri-value Null logic in that their application to any Null value results in a Null expression, including the unfortunate case where + is applied to strings, which JET's SQL syntax and VBA still allow for historical reasons.

One more point regarding things you cannot do outside of Access. The ADO Connection exposed by the Access.Application object's CurrentProject property's AccessConnection property mentioned earlier is a multi-provider connection that relies on the new (and specially adapted) Microsoft.Access.OLEDB.10.0 OLEDB provider to support updateable bound-forms and other Access-specific activities (it relies on the Microsoft.JET.OLEDB.4.0 OLEDB provider for data access when targeting JET MDB databases; I assume other database-specific OLEDB providers when targeting other database types). As far as I can tell, it is not possible to directly instantiate an ADO Connection object using the Microsoft.Access.OLEDB.10.0 OLEDB provider outside of Access. This may be related to its being wired-up on initialization with a handle to the Access.Application object and VBA projects.


Comments

Just been having this exact problem trying to manipulate Access data via ADO in a VB6 program (ie "nz" being unrecognised). Very useful tip about Iif(Isnull(...... - many thanks.

Clive Wales | 2008.02.06 08:10 AM

Thanks! You're a lifesaver! :-)

Peter | 2008.07.25 08:37 AM

Thanks! Had same prob here - saved me time and so saved some of my life indeed!

THANK YOU!

Harald | 2008.09.24 06:54 AM


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00e54f8e8c078833

Listed below are links to weblogs that reference Why No Nz() Outside of Access?: