Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access on the web (2002)

    I'm using Access 02 as a web database on a win2000 server.

    I am using nested queries. First, qryDoItAll collects all the data and calculates some ratios using my VBA function Ratio(num,den) which embeds a check for 0 in the denominator.

    Then qryPickOne selects a column from qryDoItAll, and does a few more calculations.

    This all works fine on my desktop.

    When try to run qryPickOne in the same database installed in my web, the Ratio function is unknown? How so?, it knows it on my desktop?

    Ok, in an attempt to solve the problem, i insert some ASP code defining Ratio, and making it available in the page that access the database. Still, Ratio is unknown. What is going on here? And how do i make this work?

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Hi Peter,

    I'm pretty sure I've used custom functions in a query with Access/ASP before. Assuming everything is set up properly, this *should* work...

    Can you post the SQL from the query in question and the ASP code from the page in question (including the function - if it's not too complicated or sensitive)?

    In the meantime, I'll do some checking to make sure that my earlier statement was correct...

    Thanks,

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    HI Mark ~ thanks for the response ...

    I am going to try to put together a small test case to help me debug, and perhaps demonstrates the situation. I am hypothesizing that perhaps the function name 'Ratio' has something to do with it ??? Altho that is not a certainty, since there are other custom functions in the mix too ~ maybe Access is just quitting at the first custom function it meets???

    Will report back soon.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    The attached zip is a small db that exhibits the same undesirable behavior.

    tA, tD, and tP are the 'experimental data' tables. tGuides contains acceptable calculated value ranges.
    qSA selects data from tA according to the value of tD.DID.
    qSP selects data from tP according to the value of tD.DID.
    qJoinAandP joins qSA and qSP on the same Year, Month, and DID.
    qAllADsFields performs the calculations on qJoinAandP.
    qFinalTest selects a single field from qAllADsFields and compares it with tGuides.
    Functions that perform the calculations are in the module 'generic_funcs'.

    I import customfunctions.mdb to FrontPage 02 and assign a new database connection.

    Then I create a new .asp, and Insert | Database | Results. When I select 'qFinalTest' as the record source, I get a db connection error. The details say I have an 'undefined function 'MyTotal' in expression.

    ????

    Anyway, the NAME of the function is not the problem that i had hypothesized earlier...
    Attached Files Attached Files

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Hi Peter,

    After looking into it, I discovered that I was mistaken in my previous post. The JET OLEDB provider can only read Access Tables and Queries - NOT Forms, Reports, Macros, or Modules.

    Therefore, the best thing to do in your case will be to include the raw data in your web query and perform the calculation with ASP.

    For example, you could declare your custom function in the ASP page (within brackets, of course (<% %>)). Then include the data used to feed the function in the record source for the page. Finally, call the function from where you want to see the result (presumably in a table):<pre><table>
    <tr>
    <td>Field Names Row</td>

    </tr>
    <% Do Until rst.EOF
    Response.Write("<tr><td>" & rst("YourFields") & "</td")
    'Here's the line in question:
    '------------------------------------
    Response.Write("<td>" & MyFunction(rst("ArgumentField1"), _
    rst("ArgumentField2")) & "</td>")
    Response.Write("</tr>")
    rst.MoveNext
    Loop %>
    </table></pre>

    Hopefully that will give you an idea of how to make it work.

    Please post back if that doesn't do the trick for you!

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Thanks Mark ~

    It appears that Frontpage doesnt recognize asp code inserted in a page, either, so it looks like I need to actually program in asp asd youve shown, rather than have Office do the work for me <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Mark ~ if i can trouble you again...

    Where did you get the info as to what JET OLEDB can handle? and, Is there anything else that CAN handle the modules? Like, can I "upsize" to the next rung on MS's ladder (SQL Server)?

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Hi Peter, the JET OLEDB is used in ASP in conjunction with the ADO objects. ADO does not currently support the use of Access Modules.

    SQL Server allows you to do some pretty heavy stuff with Stored Procedures. However, unless you already have the hardware, software, and a working knowledge of T-SQL, it's almost easier to look for other alternatives...(please keep reading)

    I have realized another obvious (and perhaps easy) solution for your problem - You can build the function into the SQL of the query (assuming the arguments come from fields in the recordset(s) you're working with).

    Although you can't use "custom" funcitons with JET/ADO, you CAN use built-in functions. That's where my earlier mistake came from. You could use something like IIF() in your case to pull this off:<pre>SELECT qAllADsFields.Year, qAllADsFields.Month, qAllADsFields.Quotient,
    tGuides.ParameterName, tGuides.MinValue, tGuides.MaxValue,
    IIF([Quotient] < [MinValue],-1,IIF([Quotient]>[MaxValue],1,0)) AS Status
    FROM tGuides, qAllADsFields
    WHERE (((tGuides.ParameterName)="Quotient"))
    ORDER BY qAllADsFields.Year DESC , qAllADsFields.Month DESC;</pre>

    I haven't tested this, but it *SHOULD* work....

    HTH

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Hey that's a good idea! <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    Altho 'perhaps easy' I doubt is an accurate assessment <img src=/S/grin.gif border=0 alt=grin width=15 height=15> If I pull the guts of the functions out into IIF statements, this thing is going to start looking like Excel.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access on the web (2002)

    SQL Server doesn't deal with any Access objects, only its own.
    Charlotte

  11. #11
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Figures.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access on the web (2002)

    To amplify Charlotte's comment - SQL server can be made to most of the arithmetic things that Access can do, but the logic part is lots harder. The reason is that you are limited to T-SQL instead of VB/VBA. I think the better answer is the one suggested by Mark - you can do lots of stuff with the IIF logic in queries, especially if you nest one atop another.
    Wendell

  13. #13
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Right! and the good thing about it is that i can test it in Access right next to the function code.

    Anyone have an idea as to how & where to document nested IIFs so when i come back to it in 6 months it is not opaque?

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access on the web (2002)

    Just include the equivalent logic written in standard VB(A) code in your documentation...

Posting Permissions

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