HiHiHi,

I have an Access 2002 Database (also several Access 2000 dbs) that has
several Public finctions in a modue. Several of the access queries
call the functions. When I execute the queries in Access, everything
works. When I call the queries via ADO, the queries break.

An example of an error is:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function
'BuildKeyPath' in expression.

BuildKeyPath is a public function in a module called 'RegSpt'

Here is part of the query:
SELECT blah,blah,blah ..., BuildKeyPath([regID)] As SubKeyPath, blah
blah blah ...;

I have tried several methods of calling the query via ADO as well as
variations of Recordset.Open and variations of CommandObject.Execute.
I have also tried all of the CommandTypes that are available.

The questions I have are:
1- Can I call a public function through ADO? If so, how?
2- Is it possible to execute/open a query through ADO that executes
public functions? If so, how?

I checked various websites and the MSDN site(s) to no avail.

TIA

-{*filter*}-
*****
I never >try< anything
I just do it.

Wanna Try Me?
*****

Hi,
Within the Access environment, you can run queries that
reference functions. Once you step outside of that
environment, ADO knows nothing of your functions, it just
sends the SQL to Jet, bypassing what I believe is called the
'expression service'. So, in a nutshell, you can't use queries that
refer to functions.

HTH
Dan Artuso, MVP

27 Nov 2002 07:18:31 -0500, managed to coherently babble:

Hola,

Thanx for answering.

ATTN: MICROSOFT. IF SOMEONE WORKING FOR MICROSOFT IS READING THIS, THE
COMPANY NEEDS TO FIX THIS IN EITHER JET 5, THE NEXT ADO UPDATE OR
WHATEVER TECHNOLOGY COULD SOLVE THIS PROBLEM.

This is surprising me and frustrating me. Something like this is why I
stopped using Access several years ago. It wasn't until Access 2000
with it's 'Access Database Projects' that I started looking at Access
as a useful tool again.

ok, enough whining.

The query already exists exists in the database (as if it were a
stored procedure). The external ADO function isn't referencing the
function directly, only the 'precompiled' query living in the database
is.

It defies logic  that a query stored in the database cannot access a
custom function stored in the same database when the query is called
from an external application using ADO.

The functions in the access database perform calculations on the data.
This architecture was chosen to prevent each different application
(some ASP apps, some custom apps) from having to perform the same
calculations on the data. Also to insure that the calculations could
be updated in one location.

So. I see my options as the following:
1- Use DAO (bleh). I don't like this because it's possible that 100 or
more users could use an application which accesses the database
simultaneously. DAO would require a separate instance of the database
to be created for each user.

2- Move the calculations to the applications. This means more work for
me. :(

3- Move the database to MSSQL server (or some other DBMS) and create
extended stored proceudres to manipulate the data.

Any other ideas?

Thanx for your patience.

-{*filter*}-

3- Move the database to MSSQL or the MSDE and create extended stored
procedures.

?Hi,
?Within the Access environment, you can run queries that
?reference functions. Once you step outside of that
?environment, ADO knows nothing of your functions, it just
?sends the SQL to Jet, bypassing what I believe is called the
?'expression service'. So, in a nutshell, you can't use queries that
?refer to functions.

*****
I never >try< anything
I just do it.

Wanna Try Me?
*****

Cobblers. ADO is about data, not about GUIs and user functions. ADO code
can be called in a variety of environments, including VB, Excel or
Powerpoint, VBS, Perl, ASP etc. etc. Its job is simply to send and fetch
data from tables.

It has long been an anomalous, if welcome, facility for DAO users in Access
that we have been able to call Access code from SQL -- but this is the
exception, not the rule. There is no "fix" here, any more than Excel's
table functions are missing in Word and need to be fixed.

So what? Oh I see, I thought you meant 100 times Dim db As Database...
Microsoft says you can open a mdb with 255 concurrent users, but it starts
to get hairy after a dozen or so. See (3) below. Using a split architecture
helps, but I don't know if I'd use Jet for that many people.

And reduced performance. You may be able to carry out the functions within
SQL anyway using the builtin functions: check the help.

And you were worried about the extra work in (2)??! Probably the best idea
though, for future proofing and because you are pushing Jet with 100
concurrent users. I hope you are doing frequent backups!

Tim F