Is it possible to execute VBA code from external app via ADO like ASP?

[hercule2]

hercule2 (Programmer) Nov 30, 2003
Hi, I'm french,

Using Access 2000 I have written a small VBA routine inside of standard module. I have also written a query that calls this routine to modify one of the columns in a table, ie. to strip punctuation characters from the column, eg.

select name, MyFunc([name])
from myTable
where name = "myname"

MyFunc (in fact function replace) is defined in Module1 as a function that currently returns its argument (will put in the stripping details later!), eg.


Public Function MyFunc(ByVal s As String) As String
MyFunc = s
End Function

After i do a compil.

This all works fine if I run the query within the Access environment.

However, if I try to execute the query from ADO (within a web page ASP or a VB app), I get an error saying:


[Microsoft][ODBC Microsoft Access Driver]undefined function 'MyFunc' in expression

If I modify my query to use a built in function, such as Lcase$(), it works fine (because the query structure is fine).

I have also tried this without going through ODBC (using OLEDB from Visual Basic) without success.

So, is it actually possible to call a user-defined function from outside of the Access environment, and if so, how????

Any suggestions (solutions with ASP and access for exemple) are appreciated !

 

[Funka]

would it work for you if you called your function on the results, after they've been returned from access, like so:

' get the data
strSql = "SELECT name FROM myTable"
Set rs = conn.Execute(strSql)

' display the results
Response.Write "value " & rs(0) & " is " & myFunc(rs(0))


' define the function -- whatever function you want
Function myFunc(str)
    myFunc = str
End Function

SQL Server allows user-defined functions, it's been a while since i've used access but i don't believe you can in the context you're seeking...

good luck!
-f!

 

[hercule2]

I can't use what you said because my function will be in the order by, but after work i do this from web ASP page:

"Requete6" is an already existing request in database.

<%
Set appAccess = CreateObject(&quot;Access.Application&quot
appAccess.OpenCurrentDatabase &quot;base.mdb&quot;, False
Set db = appAccess.DBEngine.Workspaces(0).Databases(0)
Set QryDef = db.QueryDefs(&quot;Requête6&quot
QryDef.sql = &quot;Select TOP 5 MyFunc([field]) from table&quot;
appAccess.DoCmd.OpenQuery &quot;Requête6&quot;, acViewNormal, acReadOnly
appAccess.DoCmd.TransferText acExportHTML, , &quot;Requête6&quot;, &quot;c:\temp\x.html&quot;, False
QryDef.Close
Set QryDef = Nothing
%>


So, i can (under ACCESS 2000) by this unique way use this function MyFunc() but the result
is not easy to exploit from the file c:\temp\x.html.

Is it possible after &quot;DoCmd.OpenQuery&quot; to obtain the recordset like that ?

<%
Set m_Conn = server.CreateObject(&quot;ADODB.Connection&quot
m_Conn.Open &quot;eq14CVs&quot;, &quot;&quot;, &quot;&quot;
SQL = &quot;Select TOP 5 [field] as toto from table&quot; ' MyFunc is not permit here
Set rs = m_Conn.Execute(SQL)
Do
If (rs.EOF) Then Exit Do
response.write rs(&quot;toto&quot & &quot;<br>&quot;
rs.MoveNext
Loop
%>

I can't change my base in sqlserver.