Run Query w/Function from Excel

[Kev]

I hate to post a repeat of a previous post but, the reply
I received didn't quite solve my problem.
I have query in Access with a function that I wrote as
one of the fields. My problem is that when I try to run
the query from Excel using ADODB.Command I get the
following error: "Run-time error '-2147217900(80040e14)':
Undefined function 'Function Name' in expression.." This
query is a parameter query so, I need to be able to pass
the parameters to the it. This also happens when using
the "NZ" function in a Access query.
I can rule out a need for a reference being set to the
ADO Interface or Access Object Model.

Thanks in advance,

Kev

 

[Guest]

This may be silly question but why don't you just run the query from excel using a recordset or execute statment?

 

[Rich P]

Instead of using the ADO command object, just use straight
text and for Nz, just write your own function in Excel

Dim ..., RS As ADODB.RecordSet
....
strSql = Select fld1, fld2, yourNz(fld3) from tbl1"
RS.Open strSql, conn, , adOpenDynamic, adLockPessimistic
Sheet1.Range("A1").CopyFromRecordset
....

Rich P

 

[onedaywhen]

This didn't work for me. I wrote my own public function called yourNZ
in a standard module in Excel but when I tried to use it in a query I
got an error: "Undefined function 'yourNZ' in expression."

--