Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2002
    Posts
    14

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

    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

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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)
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  4. #4
    Join Date
    Mar 2002
    Posts
    14

    I copied une function

    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.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Can you post the Code that's in the Module or the SQL you're having trouble with?
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Mar 2002
    Posts
    14
    <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

  7. #7
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  8. #8
    Join Date
    Mar 2002
    Posts
    14
    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.

  9. #9
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  10. #10
    Join Date
    Mar 2002
    Posts
    14
    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.

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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:

    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
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •