hi,



I have a query within Access that uses a public function created within a 

module.  I can run this query within access, and it returns the recordset 

appropriately.  When I try to run the same query from within an ASP page I 

get an error:



 



Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 



[Microsoft][ODBC Microsoft Access Driver] Undefined function 'SetCategory' 

in expression. 



 



I have tried building the query?s SQL within the ASP page and then 

executing the SQL statement.  I have also tried accessing the query as a 

stored procedure using the command object.  I have been unable to find any 

examples or documentation stating whether this is a limitation with ASP, 

or whether there is a specific syntax to use for a query when calling a 

public function that is stored within a module within Access from an ASP 

page.



The primary reason for the function is to set the value for a field that I 

would like to sort the recordset by.  It seems likely that I will need to 

run the query without the field, save the resulting recordset into an 

array, run through the array and apply the function to each row to 

determine the value of the new field, and then sort the array by that 

field.  It just seems that this is the least optimized way of performing 

this task.



Any help or suggestions on where to look for an answer would be greatly 

appreciated.  I have included sample code below.



Thank you,



Raymond Dalton

rdalton@c...



 



Sample SQL text for the query



SELECT tblHospitals.HospitalID, SetCategory([strRFPRFI],[strContExpire],

[lngGroupFK]) AS Category 



FROM tblHospitals LEFT JOIN tblStates ON tblHospitals.intStateFK = 

tblStates.intStateID 



WHERE (tblHospitals.intStateFK=4) ORDER BY SetCategory([strRFPRFI],

[strContExpire],[lngGroupFK])



 



Command object code



Set objCmd = Server.CreateObject("ADODB.Command")



objCmd.ActiveConnection = gobjConnection



objCmd.CommandText = "qryReport" ?the name of the report that is saved 

within access using the sample SQL text above



objCmd.CommandType = 4



objCmd.CommandTimeout = 60



set rs_List = objCmd.Execute



 



The alternative method of just opening up a straight recordset using the 

sample SQL text above stored within SQL_txt



    Set rs_List = Server.CreateObject("ADODB.Recordset")



    rs_List.Open SQL_txt, gobjConnection, 3, 3



 



Thanks again,



Raymond



 

You can't use user-defined functions in an SQL string -- but may use the

built-in functions in some places.



-- Z









----- Original Message -----

From: "Raymond Dalton" <rdalton@c...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, February 18, 2002 4:13 PM

Subject: [access_asp] Question concerning accessing Access module within ASP





> hi,

>

> I have a query within Access that uses a public function created within a

> module.  I can run this query within access, and it returns the recordset

> appropriately.  When I try to run the same query from within an ASP page I

> get an error:

>

>

>

> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

>

> [Microsoft][ODBC Microsoft Access Driver] Undefined function 'SetCategory'

> in expression.

>

>

>

> I have tried building the query's SQL within the ASP page and then

> executing the SQL statement.  I have also tried accessing the query as a

> stored procedure using the command object.  I have been unable to find any

> examples or documentation stating whether this is a limitation with ASP,

> or whether there is a specific syntax to use for a query when calling a

> public function that is stored within a module within Access from an ASP

> page.

>

> The primary reason for the function is to set the value for a field that I

> would like to sort the recordset by.  It seems likely that I will need to

> run the query without the field, save the resulting recordset into an

> array, run through the array and apply the function to each row to

> determine the value of the new field, and then sort the array by that

> field.  It just seems that this is the least optimized way of performing

> this task.

>

> Any help or suggestions on where to look for an answer would be greatly

> appreciated.  I have included sample code below.

>

> Thank you,

>

> Raymond Dalton

> rdalton@c...

>

>

>

> Sample SQL text for the query

>

> SELECT tblHospitals.HospitalID, SetCategory([strRFPRFI],[strContExpire],

> [lngGroupFK]) AS Category

>

> FROM tblHospitals LEFT JOIN tblStates ON tblHospitals.intStateFK 

> tblStates.intStateID

>

> WHERE (tblHospitals.intStateFK=4) ORDER BY SetCategory([strRFPRFI],

> [strContExpire],[lngGroupFK])

>

>

>

> Command object code

>

> Set objCmd = Server.CreateObject("ADODB.Command")

>

> objCmd.ActiveConnection = gobjConnection

>

> objCmd.CommandText = "qryReport" 'the name of the report that is saved

> within access using the sample SQL text above

>

> objCmd.CommandType = 4

>

> objCmd.CommandTimeout = 60

>

> set rs_List = objCmd.Execute

>

>

>

> The alternative method of just opening up a straight recordset using the

> sample SQL text above stored within SQL_txt

>

>     Set rs_List = Server.CreateObject("ADODB.Recordset")

>

>     rs_List.Open SQL_txt, gobjConnection, 3, 3

>

>

>

> Thanks again,

>

> Raymond

>

>

>




$subst('Email.Unsub').

>