Undefine Function "FunctionName" in expression

nonlinear

unread,

Nov 7, 2007, 4:19:03 PM11/7/07

to

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

unread,

Nov 7, 2007, 4:45:32 PM11/7/07

to

"nonlinear" <nonl...@discussions.microsoft.com> wrote in message
news:7FFD46F0-CFC9-44AC...@microsoft.com...

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

unread,

Nov 7, 2007, 5:10:37 PM11/7/07

to

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.

--
Marsh
MVP [MS Access]

nonlinear

unread,

Nov 8, 2007, 4:53:03 AM11/8/07

to

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

unread,

Nov 8, 2007, 12:35:57 PM11/8/07

to

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.

--
HTH,
George

"nonlinear" <nonl...@discussions.microsoft.com> wrote in message
news:7FFD46F0-CFC9-44AC...@microsoft.com...

George Nicholson

unread,

Nov 8, 2007, 12:44:40 PM11/8/07

to

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

--
HTH,
George

"George Nicholson" <Georg...@Junkmsn.com> wrote in message
news:%23TE3U3i...@TK2MSFTNGP04.phx.gbl...

Marshall Barton

unread,

Nov 8, 2007, 1:45:18 PM11/8/07

to

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

--
Marsh
MVP [MS Access]

nonlinear

unread,

Nov 9, 2007, 2:32:00 AM11/9/07

to

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

unread,

Nov 9, 2007, 12:32:26 PM11/9/07

to

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.

--
HTH,
George

"nonlinear" <nonl...@discussions.microsoft.com> wrote in message

news:0410141B-0540-4F95...@microsoft.com...