Access on the web (2002)

Topic

New Reply

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?

Reply | Quote

Replies

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,

Reply | Quote

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.

Reply | Quote

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…

Reply | Quote

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):

<% Do Until rst.EOF
         Response.Write("")
         Response.Write("")
         rst.MoveNext
      Loop %>

  

    
Field Names Row

" & rst("YourFields") & "</td")
'Here's the line in question:
'------------------------------------
         Response.Write("
" & MyFunction(rst("ArgumentField1"), _
             rst("ArgumentField2")) & "

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

Reply | Quote

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

Reply | Quote

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)?

Reply | Quote

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:

SELECT qAllADsFields.Year, qAllADsFields.Month, qAllADsFields.Quotient, 
tGuides.ParameterName, tGuides.MinValue, tGuides.MaxValue, 
IIF([Quotient] [MaxValue],1,0)) AS Status
FROM tGuides, qAllADsFields
WHERE (((tGuides.ParameterName)="Quotient"))
ORDER BY qAllADsFields.Year DESC , qAllADsFields.Month DESC;

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

HTH

Reply | Quote

Hey that’s a good idea!

Altho ‘perhaps easy’ I doubt is an accurate assessment If I pull the guts of the functions out into IIF statements, this thing is going to start looking like Excel.

Reply | Quote

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

Reply | Quote

Figures.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote