Hi guys (/girls/etc),
A friend asked me this question last night and I'm just looking for confirmation of the answer I gave him...
He's porting an Access app's front-end to ASP. The database is staying in Access, and he knows he's got to rewrite the forms and reports in ASP/HTML, but he figured he could just copy-and-paste the VBA (along with the embedded SQL) into ASP pages. However, he's run into a problem because the SQL contains calls to Access VBA functions. E.g. SELECT FormatName(FirstName, MiddleInitials, LastName), FormatAddress(AddressLine1, AddressLine2, AddressLine3) FROM MyTable;, where FormatName and FormatAddress are VBA functions in an Access module. This works fine when the SQL is being called from within an Access module, but when it's passed to the JET engine by IIS it obviously doesn't know anything about FormatName or FormatAddress, and so fails.
Now, as far as I know, there's no way to call a VBA function in an Access module from within an SQL query called from ASP (or anywhere outside of Access itself, in fact). Am I right?
My advice was to remove the function calls from the embedded SQL (but still return the fields that are passed as parameters to the functions), copy-and-paste the functions into ASP, and rework the ASP/VBA code to call the appropriate functions once you've returned the recordset, but I just wanted to check that there's no way of getting the JET engine to call these functions itself...
Thanks, in anticipation, gkrogers
A friend asked me this question last night and I'm just looking for confirmation of the answer I gave him...
He's porting an Access app's front-end to ASP. The database is staying in Access, and he knows he's got to rewrite the forms and reports in ASP/HTML, but he figured he could just copy-and-paste the VBA (along with the embedded SQL) into ASP pages. However, he's run into a problem because the SQL contains calls to Access VBA functions. E.g. SELECT FormatName(FirstName, MiddleInitials, LastName), FormatAddress(AddressLine1, AddressLine2, AddressLine3) FROM MyTable;, where FormatName and FormatAddress are VBA functions in an Access module. This works fine when the SQL is being called from within an Access module, but when it's passed to the JET engine by IIS it obviously doesn't know anything about FormatName or FormatAddress, and so fails.
Now, as far as I know, there's no way to call a VBA function in an Access module from within an SQL query called from ASP (or anywhere outside of Access itself, in fact). Am I right?
My advice was to remove the function calls from the embedded SQL (but still return the fields that are passed as parameters to the functions), copy-and-paste the functions into ASP, and rework the ASP/VBA code to call the appropriate functions once you've returned the recordset, but I just wanted to check that there's no way of getting the JET engine to call these functions itself...
Thanks, in anticipation, gkrogers
