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

[hercule2]

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 !

 

[RichieNJim]

I do not believe there is a way to use ADO on a recordset who uses a custom function.

Workarounds...

(1) Re-write the function in VBScript and process your SQL server side in your ASP page.

(2) Create an Access.Application on the server, open the database with that object, then call a macro that runs your query, or call openquery. I would use code here:

http://www.4guysfromrolla.com/webtech/042600-1.shtml

to start with.

(3) COM+. Create your function in VB, and another function that returns a recordset which uses your function.

Good luck!

 

[hercule2]

With your solution 2, 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.

 

[RichieNJim]

Why export to HTML? Another approach may be to use your openquery, like you have, but change that query to a &quot;make table query&quot; and then use ADO on the result set?

 

[hercule2]

yes, could you help me with an example ?

Set appAccess = CreateObject(&quot;Access.Application&quot
appAccess.OpenCurrentDatabase &quot;base.mdb&quot;, False
Set db = appAccess.DBEngine.Workspaces(0).Databases(0)
db.querydefs.Delete (&quot;Dynamic_Query&quot
Set QD = db.CreateQueryDef(&quot;Dynamic_Query&quot;, sql)
appAccess.DoCmd.OpenQuery &quot;Dynamic_Query&quot;

'appAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;Dynamic_Query&quot;, &quot;C:\temp\Test.xls&quot;, True

//**** Here your code ***///

appAccess.CloseCurrentDatabase
Set db = Nothing
Set appAccess = Nothing

 

[RichieNJim]

Something like this, let me know how you make out.
--Richie

Dim appAccess
Dim db
Dim qd
Dim rs
Dim c

Set appAccess = CreateObject(&quot;Access.Application&quot
appAccess.OpenCurrentDatabase &quot;base.mdb&quot;, False

Set db = appAccess.DBEngine.Workspaces(0).Databases(0)
db.querydefs.Delete (&quot;Dynamic_Query&quot

Set c = Server.CreateObject(&quot;ADODB.Connection&quot
c.ConnectionString = &quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot; & &quot;DBQ=c:\base.mdb&quot;
c.Open

sql = &quot;SELECT Now() AS fld1 INTO tbl_Test;&quot;
Set qd = db.CreateQueryDef(&quot;Dynamic_Query&quot;, sql)
appAccess.DoCmd.OpenQuery &quot;Dynamic_Query&quot;

'appAccess.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;Dynamic_Query&quot;, &quot;C:\temp\Test.xls&quot;, True

//**** Here your code ***///

Set rs = Server.CreateObject(&quot;ADODB.RecordSet&quot
rs.Open &quot;select * from tbl_Test;&quot;, c
'other code.
rs.Close
c.Close

appAccess.CloseCurrentDatabase

Set db = Nothing
Set rs = Nothing
Set c = Nothing
Set appAccess = Nothing