Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    13

    Unanswered: calling Access Report from ASP

    Hi guys,
    I am trying to use ASP (vbscript) to bring out Access genereated reports dynamically. The query for the report works fine since the report runs perfectly in Access.
    query looks somethin like this..
    Code:
     sSQL = " SELECT weekendingEmp.WeekEnding, " & _
    	" weekendingEmp.EmployeeID, " & _
    	" Employees.EmployeeName, " & _
    	" Sum(Hours.Hours) AS SumOfHours, " & _
    	" Employees.BaseWeekHours, " & _
    	" Employees.Current " & _
    	" FROM (weekendingEmp LEFT JOIN Hours ON (weekendingEmp.EmployeeID = Hours.EmployeeID) " & _
    	" AND (weekendingEmp.WeekEnding = Hours.WeekEnding)) " & _
    	" INNER JOIN Employees ON weekendingEmp.EmployeeID = Employees.EmployeeID " & _
    	" GROUP BY weekendingEmp.WeekEnding, " & _
    	" weekendingEmp.EmployeeID, " & _
    	" Employees.EmployeeName, " & _
    	" Employees.BaseWeekHours, " & _
    	" Employees.Current "& _
    	" HAVING ((..some condition..) AND (Sum(Hours.Hours)<[BaseWeekHours]) AND ((Employees.Current) = True)) " & _
    	" ORDER BY weekendingEmp.WeekEnding DESC; "
    Interesting thing is, if i take out (Employees.Current = True) statement, i can actually see the report on my asp page..
    but if i keep that statment, i get: "HTTP 404 - File not found" error.. i want to keep that statement because the report is only interested in employees who are currently employeed..
    anybody know wut's wrong?
    any help will be greatly appreciated..
    thanks
    Last edited by SuNG; 04-02-04 at 16:08.

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Try replacing true with On or 1. Access has some fields that don't map very well to standard data types using ado/oledb/odbc

  3. #3
    Join Date
    Apr 2004
    Posts
    13
    hey thanks for the fast reply.. but that doesn't work either..

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I can't help but wonder if the problem lies somewhere else. Do you want to include the entire page script??

  5. #5
    Join Date
    Apr 2004
    Posts
    13
    sure.. i hope this can help..

    Code:
     Dim sRpt, sPath, sXML, sXSL, sRedirectTo
      Dim sMDB, sAppName, objAcc, objQry, sQry, sSQL
      Dim dteStart, dteEnd, sFormat, iFrmt, sMsg
       
      Const fmtASP = 4
      Const fmtHTM = 0
      Const acExpRpt = 3
      Const acUTF8 = 0
      
      ' Set the name of the Access XP report and fmt file
      sRpt = Request("Report")
      ' If missing, run Report
      If sRpt = "" Then sRpt = "Report"
    
      sFormat = Request("Format")
      If sFormat = "" Then sFormat = "asp"
    
      ' Assume that Access XP database file is in the same 
      ' web folder as the ASP page.  Determine the path 
      ' and use it to set the XML and XSL file names.
      sMDB = Server.MapPath("/test/Time_Track2002.mdb")
      Response.Write "Database: '" & sMDB & "'"
      ' Response.End
      sPath = Left(sMDB, Len(sMDB) - Len("Time_Track2002.mdb"))
      sXML = sPath & sRpt & ".xml"
      sXSL = sPath & sRpt & ".xsl"
        
      ' Create Access XP Application Object and open it.
      sAppName = "Access.Application.10"
      Set objAcc = Server.CreateObject(sAppName)
      objAcc.OpenCurrentDatabase sMDB
    
     sSQL = " SELECT weekendingEmp.WeekEnding, " & _
    	" weekendingEmp.EmployeeID, " & _
    	" Employees.EmployeeName, " & _
    	" Sum(Hours.Hours) AS SumOfHours, " & _
    	" Employees.BaseWeekHours, " & _
    	" Employees.Current " & _
    	" FROM (weekendingEmp LEFT JOIN Hours ON (weekendingEmp.EmployeeID = Hours.EmployeeID) " & _
    	" AND (weekendingEmp.WeekEnding = Hours.WeekEnding)) " & _
    	" INNER JOIN Employees ON weekendingEmp.EmployeeID = Employees.EmployeeID " & _
    	" GROUP BY weekendingEmp.WeekEnding, " & _
    	" weekendingEmp.EmployeeID, " & _
    	" Employees.EmployeeName, " & _
    	" Employees.BaseWeekHours, " & _
    	" Employees.Current "& _
    	" HAVING (((weekendingEmp.WeekEnding)>DateAdd(""ww"",-30,Now())) AND ((Sum(Hours.Hours))<[BaseWeekHours] Or (Sum(Hours.Hours)) Is Null) AND ((Employees.Current) = On)) " & _
    	" ORDER BY weekendingEmp.WeekEnding DESC; " 
             
      sQry = "TimesheetQryIntranet"
      
      Set objQry = objAcc.DBEngine(0)(0).QueryDefs(sQry)
      objQry.SQL = sSQL
      Set objQry = Nothing
      
        sRedirectTo = sRpt & ".asp"
        iFrmt = fmtASP
    
      objAcc.ExportXML acExpRpt,sRpt,sXML,,sXSL,,,iFrmt
      
      objAcc.CloseCurrentDatabase
      objAcc.Quit
      Set objAcc = Nothing
    
      Response.Redirect sRedirectTo

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    so have you checked sRedirectTo to ensure you are always redirecting to the right location??

  7. #7
    Join Date
    Apr 2004
    Posts
    13
    yea... it works fine..
    since if i just take that "Employees.Current = True" Statement out, everything works perfectly..

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Having a bit more detailed read of the code I can see what you are doing now and it makes a bit more sense...

    Basically your 404 is because there are no results returned to export and thus no page is created....

    I'm not sure why it is not returning any data when you remove that where clause though. All I could suggest is possibly changing it to 'True' or true or some other variation.

    You might want to make another post with a different title and seem is you get a better response... something like checking true/false columns in Access.

  9. #9
    Join Date
    Apr 2004
    Posts
    13
    coo.. thanks for ur help

  10. #10
    Join Date
    Apr 2004
    Posts
    13
    Hey sup,

    i figured out wut the problem was... u see.. it didn't like "Current" field name because "Current" is actually a key name and is a function...
    so after putting square brackets.. like: [Current] solved the problem..

  11. #11
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    errghhhh,... yeah, that would make sense.... glad to hear your found the problem.

Posting Permissions

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