Undefine Function "FunctionName" in expression

[Guest]

Hello everybody,
I would like to inform you that this question is not for novices...
I have an Access query that uses a VBA public user-defined function (reside
in an module) to calculate one query's field. When I directly run the query
everything is ok. BUT..when the query is run from outside the ACCESS (for
example through VB6 ADO code to result a recordset: rs.open "query") then the
jet engine issues an error message: "Undefined Function 'FunctionName' in
expression". It seeems that jet engine doesn't execute the function if the
query is run outside the Access. I have searched the web and I found some
information but we will analyze them after your comments.

 

[Stuart McCall]

Hello everybody,
I would like to inform you that this question is not for novices...
I have an Access query that uses a VBA public user-defined function
(reside
in an module) to calculate one query's field. When I directly run the
query
everything is ok. BUT..when the query is run from outside the ACCESS (for
example through VB6 ADO code to result a recordset: rs.open "query") then
the
jet engine issues an error message: "Undefined Function 'FunctionName' in
expression". It seeems that jet engine doesn't execute the function if the
query is run outside the Access. I have searched the web and I found some
information but we will analyze them after your comments.

The Jet engine knows nothing about VBA. The Access UI ties them together
closely, but as you have found, outside of the Access environment, no VBA
code is visible to Jet. Sorry to be the bringer of bad news.

 

[Marshall Barton]

I would like to inform you that this question is not for novices...
I have an Access query that uses a VBA public user-defined function (reside
in an module) to calculate one query's field. When I directly run the query
everything is ok. BUT..when the query is run from outside the ACCESS (for
example through VB6 ADO code to result a recordset: rs.open "query") then the
jet engine issues an error message: "Undefined Function 'FunctionName' in
expression". It seeems that jet engine doesn't execute the function if the
query is run outside the Access. I have searched the web and I found some
information but we will analyze them after your comments.

Jet and Access are separate entitie, so you do not have an
Access query, you have a Jet query.

When you use Jet outside of Access, then Access and your VBA
module are not available.

 

[Guest]

Ok you are right. But what did Microsoft mean when it said that the new Jet
4.0 SP8 would run both build-in functions AND user-defined functions?

 

[George Nicholson]

In my experience:

If you need query results that involve a user defined function while in an
external program (say, Excel), you need to open an instance of Access via
Automation, run the query and grab the results.

 

[George Nicholson]

for clarity, I probably should have said:
"...you need to create an instance of Access via Automation, open the
mdb within Access, run the query and grab the results."

 

[Marshall Barton]

I don't know. Maybe they're talking about the ability to
invoke some SQL Server UDF thingies.

 

[Guest]

Yes, this is a solution but a very slow solution and not so elegant..
I give the Microsoft URL (http://support.microsoft.com/kb/294698) in witch I
read the:
"Jet 4.0 Service Pack 8 now permits the execution of user-defined functions"
for your comments
Thanks any way...

 

[George Nicholson]

You are taking that sentence out of context.

"How you can implement Sandbox mode is extended in Jet 4.0 Service Pack 8 to
be more compatible with Access databases. Previous implementations of
Sandbox mode were too restrictive for most Access applications. Starting
with Jet 4.0 Service Pack 8, the enhanced Sandbox mode continues to block
unsafe Visual Basic for Applications functions, but Jet 4.0 Service Pack 8
now permits the execution of user-defined functions."

"..now permits execution of user-defined functions." isn't a newly
introduced feature, its a Sandbox mode bug fix.