Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.

wasn't this an earlier question? I responded that I wasn't able to do this :(
but I'm sure that there's a way to do it.
I even had the function inside an SP and that raised exactly the same error.



"michdenis" wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.

Hi Patrick,

| wasn't this an earlier question? I responded that I wasn't able to do this :(
| but I'm sure that there's a way to do it.

Personally, it's the first time i ask this question.

Someone of this group gave this exemple... and it works too

"SELECT DISTINCT Sum([MyField]) AS SumofMyField FROM...

But how about a user fonction ? And more generally speaking
what functions are available in ADO for a Query in an Excel environment ?

Thanks.



"michdenis" wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.

I'snt this going to be slow? It means every time you modify the cell a new
connection has to be established. Would it be better to perform a query
returning all the fields, then lookup the value you need in the query results.

"michdenis" wrote:

Hi Patrick,

| wasn't this an earlier question? I responded that I wasn't able to do this :(
| but I'm sure that there's a way to do it.

Personally, it's the first time i ask this question.

Someone of this group gave this exemple... and it works too

"SELECT DISTINCT Sum([MyField]) AS SumofMyField FROM...

But how about a user fonction ? And more generally speaking
what functions are available in ADO for a Query in an Excel environment ?

Thanks.



"michdenis" wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.

Hi Joel,

You comment about the time required to perform the task...

First of all, can we used a user function ? Any example to show ?
Do you know why is possible to do this in Access and not in Excel ?
How about a web site, a tutorial discusssing about this subject ? Any suggestion ?

Thanks for your participation.




"Joel" a écrit dans le message de groupe de discussion :
...
I'snt this going to be slow? It means every time you modify the cell a new
connection has to be established. Would it be better to perform a query
returning all the fields, then lookup the value you need in the query results.

"michdenis" wrote:

Hi Patrick,

| wasn't this an earlier question? I responded that I wasn't able to do this :(
| but I'm sure that there's a way to do it.

Personally, it's the first time i ask this question.

Someone of this group gave this exemple... and it works too

"SELECT DISTINCT Sum([MyField]) AS SumofMyField FROM...

But how about a user fonction ? And more generally speaking
what functions are available in ADO for a Query in an Excel environment ?

Thanks.



"michdenis" wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.

You ca perform this function in excel. Patrick had code a I didn't want to
generate something Patrick already did.

When doing the function in Access you are using the current database which
already has the connection so the response time is very quick. To go out a
get information from another file will consume time.

A UDF function will only get called if a cell that has a dependency to the
UDF gets changed. So if you are only calling this UDF infrequently then it
is acceptable to to put the query into a UDF. If you have hundreds of these
queries then it will take a long time to open the workbook because opening
the workbook will update all the queries.

Each time the function is called a connection will be made to the database.
The connection is similar to opening the file that it has to transverse the
file system to find the file and look inside the file without opening the
file. Opening a file usually requires moving the data in the file into
memory on your PC. A connection doesn't have to move the data onto the PC.

1) Reading the data from access is the quickest
2) A connection from excel is slower than getting data directly from an
Access macro
3) Opening the database from excel is the slowest method.

"michdenis" wrote:

Hi Joel,

You comment about the time required to perform the task...

First of all, can we used a user function ? Any example to show ?
Do you know why is possible to do this in Access and not in Excel ?
How about a web site, a tutorial discusssing about this subject ? Any suggestion ?

Thanks for your participation.




"Joel" a écrit dans le message de groupe de discussion :
...
I'snt this going to be slow? It means every time you modify the cell a new
connection has to be established. Would it be better to perform a query
returning all the fields, then lookup the value you need in the query results.

"michdenis" wrote:

Hi Patrick,

| wasn't this an earlier question? I responded that I wasn't able to do this :(
| but I'm sure that there's a way to do it.

Personally, it's the first time i ask this question.

Someone of this group gave this exemple... and it works too

"SELECT DISTINCT Sum([MyField]) AS SumofMyField FROM...

But how about a user fonction ? And more generally speaking
what functions are available in ADO for a Query in an Excel environment ?

Thanks.



"michdenis" wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !

Any suggestion about a web site discussing of this subject is welcome.

Thanks for your time and your collaboration.

On Tue, 29 Sep 2009 08:44:06 -0400, "michdenis"
wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !


No you can't do that. It is the database engine (like Jet or ACE if you're
using Access) that determines what functions are available. Jet has a
library of functions that it understands. It knows what LEFT is, but will
not know what your custom function is.

You need to bring the data in as it is, then apply your custom function to
it. If you have a function to reverse the text, you would use

SELECT MyField FROM...
sVariable = ReverseText(rs.Fields(0).Value)

rather than

SELECT ReverseText(MyField) FROM...
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Hi Dick,

I knew i could do this :

SELECT MyField FROM...
sVariable = ReverseText(rs.Fields(0).Value)

But because some functions like "left(), right(), Sum(), Format()
and others can be used directly in a query i wondered if i could
used a UDF. Now, i know.

Thank you.


"Dick Kusleika" a écrit dans le message de groupe de discussion :
...
On Tue, 29 Sep 2009 08:44:06 -0400, "michdenis"
wrote:

Hi,

Is it possible to employ a user fonction in a query with ADO in Excel ?

If yes, is it possible to get a few examples which syntax should i used ?

For example, this one works :
SELECT Left(MyField,2) From .... But i can't integrate a personal fonction !


No you can't do that. It is the database engine (like Jet or ACE if you're
using Access) that determines what functions are available. Jet has a
library of functions that it understands. It knows what LEFT is, but will
not know what your custom function is.

You need to bring the data in as it is, then apply your custom function to
it. If you have a function to reverse the text, you would use

SELECT MyField FROM...
sVariable = ReverseText(rs.Fields(0).Value)

rather than

SELECT ReverseText(MyField) FROM...
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com