Can I call (run) MS Access Module from ASP?

Shurik · #1 (**permalink**) 04-26-04, 15:14

I have a huge pre-coded module in MS Access and right now i am trying to run it through ASP. Is it possible, and if it is, how do I do that? I couldn't find any info on it on google.
Thanks a lot in advance,
Shurik

myle · #2 (**permalink**) 04-26-04, 22:18

Bad News is no

but you could copy the code into a inc file and inc it in you ASP

but will have to take out the

Dim ThisValue as String -> Dim ThisValue

Sub ChangeThis(ThisText as String) -> Sub ChangeThis(ThisText)

rokslide · #3 (**permalink**) 04-27-04, 01:08

In theory couldn't you do it by installing access on the server and then using the access object to programaticly open the db file??

It's not a good solution though and I would suggest that myle's is the best....

Shurik · #4 (**permalink**) 04-27-04, 09:35

Thank you for your reply!
I tried the copy one of my functions into ASP page, and it works just fine if I call it directly from the page itself. However, if it is in the SQL statement, it still gives me error message -- undefined function. I do not know how to work around it. I copied the whole SQL from Acces it doesn't look pretty, but at least I wish it worked. Do you have any idea why it would give me this error, and what to do about it.

Seppuku · #5 (**permalink**) 04-27-04, 13:48

Can you post the Code that's in the Module or the SQL you're having trouble with?

Shurik · #6 (**permalink**) 04-27-04, 15:29

<code>
<%@ Language=VBScript%>
<%
Function accrual(date1, ADate, idate)

...bla-bla to calculate accrued date and the function gives a date (works just fine if I call from asp page.

End Function

%>
<html>

<head>
<title>Welcome to Midas</title>

...bla-bla until SQL which I copied from ACCESS -- it is ugly, I know
...COnnection....

Set myRS=Server.CreateObject("ADODB.Recordset")
MakeQuery="SELECT HOLD_BUY_BEG.LastOfT_COMMIT,"_

... bla-bla ...

&"accrual('"&Beginning&"',[S_MATDT],[S_ISSDT]) AS [Recent Accr] "_
&"IIf([LastOfT_STLDT]>'"&Ending&"','"&Beginning&"',[Recent Accr])"_

... bla-bla....

MyRS.open MakeQuery, MyConn, 0,4

</code>

The sql is suppsed to take a data from the form on the page itslef that submits to itself.
ending, beginning, portname are inputs from the user

I did not want to tire anybody with lenghty code, but if you feel like you need the whole sql statement, or page code, I can submit it too.

Thanks a lot in advance

Seppuku · #7 (**permalink**) 04-27-04, 16:23

Ok.. I see what you're trying to do. You want to execute the ASP function accrual from the SQL statement. The problem is that ASP functions cannot be called from SQL. You must prepare the statement completely before it is sent to the DB server. The SQL statement itself is passed as a string to the DB server which executes the statement and returns you a recordset. Once that string is executing on the DB server, it has no way to communicate back to the ASP except to return a recordset (or output values of a stored procedure).

I can't properly evaluate how to handle this, because I don't know what accrual does. If it's relatively simple, SQL may have inline functions you can use in your statement.

An example is the Now function in ASP/VB. You can call this from ASP and get the current date and time. Or you can use the GETDATE() function in SQL to do the same.

In ASP it would look like:
"SELECT * FROM myTable WHERE TimeStamp <= '" & Now & "'"

In SQL it would look like:
"SELECT * FROM myTable WHERE TimeStamp <= GETDATE()"

Unfortunately, you cannot do:
"SELECT * FROM myTable WHERE TimeStamp <= Now"

Because SQL evaluates "Now" as part of the statement, and is not recognized as a function SQL knows of (because it cannot communicate with your ASP during the execution of the statement).

You may have to go so far as to re-write the ASP into SQL and put it in a Stored Procedure. It really depends upon the complexity of accrual.

Shurik · #8 (**permalink**) 04-27-04, 19:18

Thanks for reply. The accrual function and other functions that I use are not that straight forward -- for example, accrued determines the recent accrual date for bonds given its issue date, maturity date, beginning and ending month of calculation. So I do not think I can write it in sql.
How can I use procedures?
Also, another thing I was thinking of (I haven't done it yet), is to get the data and run my functions with those data, and then insert in into the the table that I have just created with null values under accrued date. however, right now I am having a great problem with my make-table query from Access and its sql is not executing in ASP.
What do you think?
I guess I need to learn more about procedures.

Seppuku · #9 (**permalink**) 04-27-04, 19:29

Well.. stored procedures may or may not be the solution... honestly, I can't remember if Access supports SP.

In the end, you may not be able to do it in one call to the DB. It's quite common to query the DB for a general set of data, then use your ASP to do the logic. Keep in mind, the larger the recordset returned, the slower it will take to process, but sometimes this is the only way to get the solution, especially with the limited toolset provided in Access.

Shurik · #10 (**permalink**) 04-27-04, 21:17

I read an article where I learned how to send a parameter to queries. I am thinking about running make-table query and then get data from new table (I need it to be table later on) , and then insert the data after running asp function. However, I have no idea about the syntax I need to use -- it is pretty simple for select query:
strQuery = "query_name '"&param1&"', '"&param2&"', etc "

however, in order to run make-table query I need to put the whole sql. But I don't know how to send the parameters to the query. I tried
... BIG SQL...(HOLDINGS_BEG.T_CUSIP = [BEG VAR ACCR].V_CUSIP) HAVING &portname
or something like that. I think I should use HAVING but after that I am unable to find the right syntax -- if it is at all possible.
Thanks a lot again.

Seppuku · #11 (**permalink**) 04-28-04, 11:36

I'm sorry, I'm a bit confused by your question. Unfortunately, I don't know the MS Access keywords as well as I know SQL Server, but it's possible that HAVING may not be supported. Here is the definition of the HAVING clause:

Quote:

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. It is usually used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause
--
In some instances, you might want to exclude individual rows from groups (using a WHERE clause) before applying a condition to groups as a whole (using a HAVING clause).

A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause.

Example:

Code:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
   ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(price) > 10

Internet Services

Web Development

Digital Marketing

Consumer Tech