OleDbException on Access DB Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I get this error where calling a query in an Access DB with the Nz function
contained within it:

System.Data.OleDb.OleDbException : Undefined function 'Nz' in expression.

I don't believe this should happen. How can I fix it.
 
Hi,

I believe NZ is a Access-specific function, which is not recognized by Jet
provider as a valid one. There are some functions (like VBA one), which
could be used in Access itself, but could not pass syntax verification when
they called from outside using Jet. What you could do is to store your query
in an Access database and call this query as a stored procedure from your
application. It should eliminate this syntax-related issue.
 
Are you sure this works.

I have my query already stored in the database.

Perhaps you can send me working sample code which demonstrates what you mean.
 
Hi,

I believe (I could be wrong) it should work because in this case provider
does not know what is inside of the stored query. Code should be like

Dim loConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Path\MyDatabase.mdb;User ID=Admin;Password=" )

Dim loCMD As OleDbCommand = New OleDbCommand("MyQueryNameHere", nwindConn)
loCMD.CommandType = CommandType.StoredProcedure

loConn.Open()

Dim myReader As OleDbDataReader = loCMD.ExecuteReader()
 
Nope that doesn't work.

I have replaced Nz with an IIf(IsNull... expression.

That works. Interesting that some functions work and others don't. Do you
have some information as to way this is the case.
 
It is just because some Access-specific functions and some VBA functions are
not supported by Jet provider. I remember it was an article about it but I
cannot find it anymore. If I do, I will post it here
 
¤ Nope that doesn't work.
¤
¤ I have replaced Nz with an IIf(IsNull... expression.
¤
¤ That works. Interesting that some functions work and others don't. Do you
¤ have some information as to way this is the case.
¤

The following may help:

How to configure Jet 4.0 to prevent unsafe functions from running in Access 2000 and Access 2002
http://support.microsoft.com/default.aspx?scid=kb;en-us;239482

The Nz function is not available via sandbox mode.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top