Bob Barrows said:
aa said:
I have beem recommended to use the Nz() function in the ORDER BY part
of an Access2000 stored query.
The query runs correcly from within Access2000
Yet when ran from ASP, it causes an error:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'Nz' in expression.
Where should I define this function?
There are quite a few Access/VBA functions that can only be used when Access
itself is running. Nz() is one of them. See here for the list of functions
that can be used from external applications (VB, ASP, etc):
http://support.microsoft.com/default.aspx?scid=kb;en-us;294698&Product=acc
Note that the article shows you how to allow the functions to be used, but
this is not recommended from a security standpoint.
Instead of Nz, you can use IIF:
iif(isnull([fieldname], <default value>, [fieldname])
I just noticed an update to this article: Jet SP7 will allow user-defined
functions to be used! If I was still using Access in my applications, I
would be elated.
It sounded too good to be true, so I had to go see for myself. Here's
what I did:
1. Installed Jet 4.0 SP8 (4.0.8015.0)
2. Created the following module:
[modHelloWorld]
Option Compare Database
Public Function HelloWorld()
HelloWorld = "Hello World!"
End Function
3. Created the following query:
[qryHelloWorld]
SELECT HelloWorld() AS retVal;
4. Ran the following ASP code:
<%
Dim cn,rs
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "<Your DSNLess OLEDB Connection String Here>"
cn.qryHelloWorld rs
Response.Write rs(0)
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
%>
No joy. I still get:
Microsoft JET Database Engine (0x80040E14)
Undefined function 'HelloWorld' in expression.
Note, I also changed my registry settings to completely disable
sandboxing per the referenced article. Same results.
Here's my environment:
Windows 2000 Professional (sp3)
Internet Information Server (5.0)
Active Server Pages (3.0)
VBScript (5.6)
MS Jet (04.00.0000)
Microsoft Data Access Components (2.80)
Microsoft OLE DB Provider for Jet (04.00.8015)
OLE DB (02.10)
Has anyone else had success using UDFs with Jet 4.0 sp7 or above?
-Chris Hohmann
