Function and SQL

[Jean Philippe]

Hi, I've just created a little function in the module of Access to
remplace some caracteres. So when I used it directly in Access no
problem, but when I want to use the same request from asp with SQL I
get an error who is saying that Undefined function 'remplaceAccents'
in expression.

See below the SQL string and the function

SELECT remplaceAccents([Liste_TAB.Prenom]) AS Prenom,
remplaceAccents([Liste_TAB.Nom]) AS Nom
FROM Liste_TAB;
Function remplaceAccents(strTrie)
Dim strIllegal, strlegal
' Liste des caracteres illégaux
**************************************************
*****
strIllegal = Array("'", "é", "Á", "Â", "Ã", "Ä", "Å", "Æ", "Ç", "È",
"É", "Ê", "Ë", "Ì", "Í", "Î", "Ï", "Ñ", "Ò", "Ó", "Ô", "Õ", "Ö", "Ù",
"Ú", "Û", "Ü", "Ý", "à", "á", "â", "ã", "ä", "å", "æ", "ç", "è", "é",
"ê", "ë", "ì", "í", "î", "ï", "ð", "ñ", "ò", "ó", "ô", "õ", "ö", "ù",
"ú", "û", "ü", "ý", "ÿ")
' Liste des caracteres que nous voulons remplacer
*************************************
strlegal = Array("''", "e", "A", "A", "A", "A", "A", "ae", "C", "e",
"e", "e", "e", "i", "i", "i", "i", "N", "O", "O", "O", "O", "O", "U",
"U", "U", "U", "Y", "a", "a", "a", "a", "a", "a", "ae", "c", "e", "e",
"e", "e", "i", "i", "i", "i", "o", "n", "o", "o", "o", "o", "o", "u",
"u", "u", "u", "y", "y")

For i = 0 To UBound(strIllegal)
strTrie = Replace(strTrie, strIllegal(i), strlegal(i))
Next

remplaceAccents = strTrie
End Function

Hope some will have the answer.
Thanks
JP

[Steve Jorgensen]

The only functions you can call from JET SQL when not running from within
Access are those built-in to SQL, and those in th VBA library. You may not
use user defined functions or functions defined in other libraries such as
the Access library.

I'm not certain if your application here, but if data entry always happens
through Access or VB, you could consider adding a column to store the
converted values as they are entered, and calling the function to generate
the data for this column before saving any new record or record changes.

On 3 Oct 2003 06:07:38 -0700, in**@itssystem.com (Jean Philippe) wrote:

Hi, I've just created a little function in the module of Access to
remplace some caracteres. So when I used it directly in Access no
problem, but when I want to use the same request from asp with SQL I
get an error who is saying that Undefined function 'remplaceAccents'
in expression.

See below the SQL string and the function

SELECT remplaceAccents([Liste_TAB.Prenom]) AS Prenom,
remplaceAccents([Liste_TAB.Nom]) AS Nom
FROM Liste_TAB;
Function remplaceAccents(strTrie)
Dim strIllegal, strlegal
' Liste des caracteres illégaux
************************************************* *
*****
strIllegal = Array("'", "é", "Á", "Â", "Ã", "Ä", "Å", "Æ", "Ç", "È",
"É", "Ê", "Ë", "Ì", "Í", "Î", "Ï", "Ñ", "Ò", "Ó", "Ô", "Õ", "Ö", "Ù",
"Ú", "Û", "Ü", "Ý", "à", "á", "â", "ã", "ä", "å", "æ", "ç", "è", "é",
"ê", "ë", "ì", "í", "î", "ï", "ð", "ñ", "ò", "ó", "ô", "õ", "ö", "ù",
"ú", "û", "ü", "ý", "ÿ")
' Liste des caracteres que nous voulons remplacer
*************************************
strlegal = Array("''", "e", "A", "A", "A", "A", "A", "ae", "C", "e",
"e", "e", "e", "i", "i", "i", "i", "N", "O", "O", "O", "O", "O", "U",
"U", "U", "U", "Y", "a", "a", "a", "a", "a", "a", "ae", "c", "e", "e",
"e", "e", "i", "i", "i", "i", "o", "n", "o", "o", "o", "o", "o", "u",
"u", "u", "u", "y", "y")

For i = 0 To UBound(strIllegal)
strTrie = Replace(strTrie, strIllegal(i), strlegal(i))
Next

remplaceAccents = strTrie
End Function

Hope some will have the answer.
Thanks
JP

[Ross Presser]

in**@itssystem.com (Jean Philippe) wrote in news:ef**************************@posting.google.c om:

Hi, I've just created a little function in the module of Access to
remplace some caracteres. So when I used it directly in Access no
problem, but when I want to use the same request from asp with SQL I
get an error who is saying that Undefined function 'remplaceAccents'
in expression.

When you connect to a database from ASP using ADO, you will not be able to use ANY user defined access
functions, period. Access may not even be installed on the web server; how do you expect it to execute
the VBA to evaluate your function?

--
Ross Presser -- rpresser AT imtek DOT com
"... VB is essentially the modern equivalent of vulgar Latin in 13th Centurary Europe. Understand it, and
you can travel to places you never heard of and still understand some people." -- Alex K. Angelopoulos

[Pieter Linden]

I could be remembering wrong, but I would have sworn you could create
a function in ASP that allowed you to do a replace *before* submitting
any variables to the database... I thought the Replace function was
available in ADO or whatever...

[Ross Presser]

pi********@hotmail.com (Pieter Linden) wrote in
news:bf**************************@posting.google.c om:

I could be remembering wrong, but I would have sworn you could create
a function in ASP that allowed you to do a replace *before* submitting
any variables to the database... I thought the Replace function was
available in ADO or whatever...

The VBScript function Replace() is available to your ASP code. There is
also a limited set of functions, REPLACE() among them, available from
within the SQL statement itself (they are provided by the OLE DB provider
being used, either the "Microsoft OLE DB Provider for ODBC" or the
"Microsoft OLE DB Provider for Microsoft Jet", depending on the
connection string you use). But you cannot write a complex user defined
function in your ASP code and call it from the SQL statement. Nor can
you put that user defined function in the MDB, in Access, and call that
from the SQL statement.

SELECT tbl.ID, tbl.Name, replace(tbl.Name, 'Mr.', 'Mister') FROM tbl
is legal.

SELECT tbl.ID, tbl.Name FROM tbl WHERE tbl.Name = GetNameFromRegistry()
(where GetNameFromRegistry() is an ASP UDF or a VBA UDF) is not.
For the list of functions provided when you use the OLE DB Provider for
ODBC, see the ODBC Programmer's Reference Appendix E: Scalar Functions.

http://msdn.microsoft.com/library/en...odappepr_3.asp

I couldn't find an authoritative reference for the list of functions
provided by the OLE DB Provider for Microsoft Jet, but I suspect it is
very similar.