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.

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| 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.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




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.

Now I'm confused!!!

i want to call a user defined fonction in a query


???
This sounds like you want to perform addtional filtering. Instead of
performing a query why not open a recordset and perform your functions to the
recordset.


"michdenis" wrote:

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| 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.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




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.

Back to my first message :
this example works :

SELECT Left(MyField,2) From...

Suppose you want to change Left() for Replace() in this query
Is it possible ? You see a big difference between both ?
And it's not even a UDF...




"Joel" a écrit dans le message de groupe de discussion :
...
Now I'm confused!!!

i want to call a user defined fonction in a query


???
This sounds like you want to perform addtional filtering. Instead of
performing a query why not open a recordset and perform your functions to the
recordset.


"michdenis" wrote:

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| 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.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




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

The command text portion of a query is the SQL. The SQL is simply a string
(String Query Language) Below I set a variable SQL to yoour query string.

SQL = "SELECT Left(MyField,2) From..."

Then you can make a variable FUNC

FUNC = "Left"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."


or
FUNC = "Replace"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."



"michdenis" wrote:

Back to my first message :
this example works :

SELECT Left(MyField,2) From...

Suppose you want to change Left() for Replace() in this query
Is it possible ? You see a big difference between both ?
And it's not even a UDF...




"Joel" a écrit dans le message de groupe de discussion :
...
Now I'm confused!!!

i want to call a user defined fonction in a query


???
This sounds like you want to perform addtional filtering. Instead of
performing a query why not open a recordset and perform your functions to the
recordset.


"michdenis" wrote:

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| 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.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




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.

i have tried numerous syntax and i was unable to get a
result if i use "replace" as the function in my query.

I always get the same message saying ... this function
is not known...

Thanks for your collaboration.


"Joel" a écrit dans le message de groupe de discussion :
...
The command text portion of a query is the SQL. The SQL is simply a string
(String Query Language) Below I set a variable SQL to yoour query string.

SQL = "SELECT Left(MyField,2) From..."

Then you can make a variable FUNC

FUNC = "Left"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."


or
FUNC = "Replace"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."



"michdenis" wrote:

Back to my first message :
this example works :

SELECT Left(MyField,2) From...

Suppose you want to change Left() for Replace() in this query
Is it possible ? You see a big difference between both ?
And it's not even a UDF...




"Joel" a écrit dans le message de groupe de discussion
:
...
Now I'm confused!!!

i want to call a user defined fonction in a query


???
This sounds like you want to perform addtional filtering. Instead of
performing a query why not open a recordset and perform your functions to the
recordset.


"michdenis" wrote:

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| 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.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




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.

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

You need to use a "Where" phrase with like

SELECT Myfield from .....
WHERE (Myfield Like 'ab*')


whre ab is the 1st two character

"michdenis" wrote:

i have tried numerous syntax and i was unable to get a
result if i use "replace" as the function in my query.

I always get the same message saying ... this function
is not known...

Thanks for your collaboration.


"Joel" a écrit dans le message de groupe de discussion :
...
The command text portion of a query is the SQL. The SQL is simply a string
(String Query Language) Below I set a variable SQL to yoour query string.

SQL = "SELECT Left(MyField,2) From..."

Then you can make a variable FUNC

FUNC = "Left"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."


or
FUNC = "Replace"
SQL = "SELECT " & FUNC" & "(MyField,2) From..."



"michdenis" wrote:

Back to my first message :
this example works :

SELECT Left(MyField,2) From...

Suppose you want to change Left() for Replace() in this query
Is it possible ? You see a big difference between both ?
And it's not even a UDF...




"Joel" a écrit dans le message de groupe de discussion
:
...
Now I'm confused!!!

i want to call a user defined fonction in a query


???
This sounds like you want to perform addtional filtering. Instead of
performing a query why not open a recordset and perform your functions to the
recordset.


"michdenis" wrote:

Thanks for your explanation and your time.

but i did not totally understand !

'-----------------
A UDF function will only get called if a cell that has a
| dependency to the UDF gets changed.
***OK

| So if you are only calling this UDF infrequently then it
| is acceptable to to put the query into a UDF.
*** i want to call a user defined fonction in a query
not put the query into a UDF
if we can't, it was my question ...

| 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.
'-----------------
**** Ok, i was using a sheet in the same workbook for conducting my test.
When a connexion is established to a workbook, this connexion may
and can support as many queries as required to the same database till
the connexion is closed. isn't it ?

You did a helpful intervention. Thank.




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.