OK... I'm an old dog that's having trouble with some new tricks.

I have a completely functioning MSACCESS Application that I'm rewriting and updating with ASP (script)(for general web access) using MSACCESS2002. MANY if not MOST of the defined queries in the app use one or more user defined functions that are included in Module called CommonFunctions.

I've already transfered all the tables, queries, and modules to MSA2002. I've written many of the general ASP screens, and am now getting into the nitty-gritty stuff. From ASP, I cannot seem to be able to call any of the queries (nor can I use EXECUTE) without getting an error that says the functions are undefined, even though they do exist in the database. I can run the queries fine from ACCESS, but they go down in flames from ASP.

I've done considerable research, and am beginning to believe that I cannot reference any module defined functions outside of the ACCESS environment. Does anyone know of any way to:

SELECT * FROM xtable WHERE functionx() (where functionx() is defined in an ACCESS module?

-or-

EXECUTE sp_query1 (where sp_query1 references functionx() as an argument or condition)

I don't think I want to use data access pages since nearly all of my user base probably is NOT using an XP office stuff.

ANY help or comments would be greatly appreciated.

Tim

I've run into the same exact situation myself. Just about the only way to solve the problem is to recreate all of the custom functions you created in Access by making them server-side functions using VBScript embedded in your ASPs.

As far as why you can't call any modules directly through ASP...that's because when you create a connection object through whatever means you are using (OLE DB, DSN, "DSN-less"), it is ultimately communicating with the database through ODBC and not using any of the Access class objects. Your connection is hitting the Jet database engine directly, and any custom objects other than tables are not visible or accessable to the connection object.

Now, in theory, you can instantiate a MS Access object using VBScript in your ASP code, but this causes performance issues on the webserver. This is because you would actually create an instance of the Access Application object on the webserver's memory every time you reference the object in an ASP. If the object isn't released from memory properly, then you will have severe memory issues on the webserver after a while. Some commercial ASP hosting sites won't even allow you to instantiate a MS Office Application object in the webserver's memory (believe me, I've tried) B^) If you are doing it internally for a business, then you may have this option available to you if the network admins allow it...

Just as a general approach for this problem, what I do is create an include file, say CustomFunctions.inc. Then I put all of the procedures I made in Access modules there, do a little rewriting when needed since VBScript is looser than VBA, and then include the custom functions file at the top of each ASP document. That way, I can reference any procedures I need at run-time. You can call any procedures in ASP just like you can in VB or VBA, it's just that the functions and subs you make aren't compiled, they are interpreted just like the rest of you ASP code.

If you need any conversion suggestions, just ask. Good luck!

Kael