Run Query w/Function from Excel
 

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

Run Query w/Function from Excel
 

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

Run Query w/Function from Excel
 

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

-----Original Message-----
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

.

Run Query w/Function from Excel
 

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."

--

"Rich P" wrote in message ...
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

-----Original Message-----
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

.