Fix Error Type:Microsoft JET Database Engine (0x80040E37)

bagni99,

One way of checking before you execute your query try to check it using response.write something like this...

response.write sqlstatement
response.end

This way you can seee the generated statement and you can spot the error.

HTH...

Happy programming...

- Make sure that you are checking the code in the folder in which the website is configured in IIS.

- Ensure that you are accessing the code from the correct server and the correct website / virtual directory .

- Try restarting IIS / Reloading the application from IIS Console.

- Try browsing to the webpage from the IIS console (you can view the list of web pages on the IIS console and you can rightclick on them sand select browse to view the files in IE.)

- Go to Line 253 and do a response.write on the query and see the query  being executed.

- Try executing the page by passing a value along the querystring :
eg: ObserverMisc_Querylist.asp?xyz=123456

These are the basic sanity checks that could be done to see if the correct code is being executed.

Let me know if none of the above checks doesnt help resolve your issue...

Hope this helps..
Cheers!!

No joy,  I am no expert on code so I am probably failing to use your suggestions properly.  
Everything seems to be in order. The files are in the right places. I just don't get it.  I can't find any reference to the 'ObserverMiscellaneous' table anywhere in the code but keep getting the error. I have included the page code below.  (Please excuse the formatting as cutting and pasting changes it).

I am getting desperate now.

<%@ LCID = 3081  %>
<% If Session("staffarea_status") <> "login" Then Response.Redirect "login.asp" %>
<%

' User levels
Const ewAllowAdd = 1
Const ewAllowDelete = 2
Const ewAllowEdit = 4
Const ewAllowView = 8
Const ewAllowList = 8
Const ewAllowSearch = 8
Const ewAllowAdmin = 16
Dim ew_SecTable(42)
ew_SecTable(0) = 0
ew_SecTable(1) = 15
ew_SecTable(2) = 0
ew_SecTable(3) = 0
ew_SecTable(4) = 0
ew_SecTable(5) = 0
ew_SecTable(6) = 0
ew_SecTable(7) = 0
ew_SecTable(8) = 0
ew_SecTable(9) = 0
ew_SecTable(10) = 0
ew_SecTable(11) = 0
ew_SecTable(12) = 0
ew_SecTable(13) = 0
ew_SecTable(14) = 0
ew_SecTable(15) = 0
ew_SecTable(16) = 0
ew_SecTable(17) = 0
ew_SecTable(18) = 0
ew_SecTable(19) = 0
ew_SecTable(20) = 0
ew_SecTable(21) = 0
ew_SecTable(22) = 0
ew_SecTable(23) = 12
ew_SecTable(24) = 12
ew_SecTable(25) = 0
ew_SecTable(26) = 12
ew_SecTable(27) = 0
ew_SecTable(28) = 0
ew_SecTable(29) = 0
ew_SecTable(30) = 0
ew_SecTable(31) = 0
ew_SecTable(32) = 0
ew_SecTable(33) = 0
ew_SecTable(34) = 0
ew_SecTable(35) = 0
ew_SecTable(36) = 0
ew_SecTable(37) = 0
ew_SecTable(38) = 0
ew_SecTable(39) = 0
ew_SecTable(40) = 0
ew_SecTable(41) = 0
ew_SecTable(42) = 0

' Get Current Table Security
ewCurSec = 0 ' Initialise
ewCurIdx = Session("staffarea_status_UserLevel")
If IsNumeric(ewCurIdx) Then
    If ewCurIdx = -1 Then ' System Administrator
        ewCurSec = 31
    ElseIf ewCurIdx > 0 and ewCurIdx <= 42 + 1 Then
        ewCurSec = ew_SecTable(ewCurIdx-1)
    End If
End If
%>
<% If Session("staffarea_status_UserID") = "" And Session("staffarea_status_UserLevel") <> -1 Then Response.Redirect "login.asp" %>
<%
Response.expires = 0
Response.expiresabsolute = Now() - 1
Response.addHeader "pragma", "no-cache"
Response.addHeader "cache-control", "private"
Response.CacheControl = "no-cache"

%>
<!--#include file="db3.asp"-->
<!--#include file="aspmkrfn.asp"-->
<%
displayRecs = 20
recRange = 10
%>
<%
dbwhere = ""
masterdetailwhere = ""
searchwhere = ""
a_search = ""
b_search = ""
whereClause = ""
%>
<%

' Get search criteria for advance search
x_WinchesOrHours = Request.QueryString("x_WinchesOrHours")
z_WinchesOrHours = Request.QueryString("z_WinchesOrHours")
arrfieldopr = Split(z_WinchesOrHours,",")
If x_WinchesOrHours <> "" Then
      x_WinchesOrHours = Replace(x_WinchesOrHours,"'","''")
      x_WinchesOrHours = Replace(x_WinchesOrHours,"[","[[]")
      a_search = a_search & "[WinchesOrHours] " ' Add field
      a_search = a_search      & arrfieldopr(0) & " " ' Add operator
      If UBound(arrfieldopr) >= 1 Then
            a_search = a_search & arrfieldopr(1) ' Add search prefix
      End If
      a_search = a_search & x_WinchesOrHours ' Add input parameter
      If UBound(arrfieldopr) >=2 Then
            a_search = a_search & arrfieldopr(2) ' Add search suffix
      End If
      a_search = a_search       & " AND "
End If
x_Hours = Request.QueryString("x_Hours")
z_Hours = Request.QueryString("z_Hours")
arrfieldopr = Split(z_Hours,",")
If x_Hours <> "" Then
      x_Hours = Replace(x_Hours,"'","''")
      x_Hours = Replace(x_Hours,"[","[[]")
      a_search = a_search & "[Hours] " ' Add field
      a_search = a_search      & arrfieldopr(0) & " " ' Add operator
      If UBound(arrfieldopr) >= 1 Then
            a_search = a_search & arrfieldopr(1) ' Add search prefix
      End If
      a_search = a_search & x_Hours ' Add input parameter
      If UBound(arrfieldopr) >=2 Then
            a_search = a_search & arrfieldopr(2) ' Add search suffix
      End If
      a_search = a_search       & " AND "
End If
y_Hours = Request.QueryString("y_Hours")
If y_Hours <> "" And UBound(arrfieldopr) >=5 Then
      a_search = a_search & "[Hours] " & arrfieldopr(3) & " " & arrfieldopr(4) & y_Hours & arrfieldopr(5) & " AND "
End If
If Len(a_search) > 4 Then
      a_search = Mid(a_search, 1, Len(a_search)-4)
End If
%>
<%

' Build search criteria
If a_search <> "" Then
      searchwhere = a_search ' Advanced search
ElseIf b_search <> "" Then
      searchwhere = b_search ' Basic search
End If

' Save search criteria
If searchwhere <> "" Then
      Session("ObserverMisc_Query_searchwhere") = searchwhere

      ' Reset start record counter (new search)
      startRec = 1
      Session("ObserverMisc_Query_REC") = startRec
Else
      searchwhere = Session("ObserverMisc_Query_searchwhere")
End If
%>
<%

' Get clear search cmd
If Request.QueryString("cmd").Count > 0 Then
      cmd = Request.QueryString("cmd")
      If UCase(cmd) = "RESET" Then

            ' Reset search criteria
            searchwhere = ""
            Session("ObserverMisc_Query_searchwhere") = searchwhere
  ElseIf UCase(cmd) = "RESETALL" Then

            ' Reset search criteria
            searchwhere = ""
            Session("ObserverMisc_Query_searchwhere") = searchwhere
      End If

      ' Reset start record counter (reset command)
      startRec = 1
      Session("ObserverMisc_Query_REC") = startRec
End If

' Build dbwhere
If masterdetailwhere <> "" Then
      dbwhere = dbwhere & "(" & masterdetailwhere & ") AND "
End If
If searchwhere <> "" Then
      dbwhere = dbwhere & "(" & searchwhere & ") AND "
End If
If Len(dbwhere) > 5 Then
      dbwhere = Mid(dbwhere, 1, Len(dbwhere)-5) ' Trim rightmost AND
End If
%>
<%

' Load Default Order
DefaultOrder = "WinchesOrHours"
DefaultOrderType = "ASC"

' No Default Filter
DefaultFilter = ""

' Check for an Order parameter
OrderBy = ""
If Request.QueryString("order").Count > 0 Then
      OrderBy = Request.QueryString("order")

      ' Check if an ASC/DESC toggle is required
      If Session("ObserverMisc_Query_OB") = OrderBy Then
            If Session("ObserverMisc_Query_OT") = "ASC" Then
                  Session("ObserverMisc_Query_OT") = "DESC"
            Else
                  Session("ObserverMisc_Query_OT") = "ASC"
            End if
      Else
            Session("ObserverMisc_Query_OT") = "ASC"
      End If
      Session("ObserverMisc_Query_OB") = OrderBy
      Session("ObserverMisc_Query_REC") = 1
Else
      OrderBy = Session("ObserverMisc_Query_OB")
      If OrderBy = "" Then
            OrderBy = DefaultOrder
            Session("ObserverMisc_Query_OB") = OrderBy
            Session("ObserverMisc_Query_OT") = DefaultOrderType
      End If
End If

' Open connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open xDb_Conn_Str

' Build SQL
strsql = "SELECT * FROM [ObserverMisc Query]"
If DefaultFilter <> "" Then
      whereClause = whereClause & "(" & DefaultFilter & ") AND "
End If
If dbwhere <> "" Then
      whereClause = whereClause & "(" & dbwhere & ") AND "
End If
If (ewCurSec And ewAllowList) <> ewAllowList Then
      whereClause = whereClause & "(0=1) AND "
End If
If Session("staffarea_status_UserLevel") <> -1 Then ' Non system admin
whereClause = whereClause & "([Name] = '" & Session("staffarea_status_UserID") & "') AND "
End If
If Right(whereClause, 5)=" AND " Then whereClause = Left(whereClause, Len(whereClause)-5)
If whereClause <> "" Then
      strsql = strsql & " WHERE " & whereClause
End If
If OrderBy <> "" Then
      strsql = strsql & " ORDER BY [" & OrderBy & "] " & Session("ObserverMisc_Query_OT")
End If      

'Response.Write strsql
Set rs = Server.CreateObject("ADODB.Recordset")
rs.cursorlocation = 3
rs.Open strsql, conn, 1, 2
totalRecs = rs.RecordCount

' Check for a START parameter
If Request.QueryString("start").Count > 0 Then
      startRec = Request.QueryString("start")
      Session("ObserverMisc_Query_REC") = startRec
ElseIf Request.QueryString("pageno").Count > 0 Then
      pageno = Request.QueryString("pageno")
      If IsNumeric(pageno) Then
            startRec = (pageno-1)*displayRecs+1
            If startRec <= 0 Then
                  startRec = 1
            ElseIf startRec >= ((totalRecs-1)\displayRecs)*displayRecs+1 Then
                  startRec = ((totalRecs-1)\displayRecs)*displayRecs+1
            End If
            Session("ObserverMisc_Query_REC") = startRec
      Else
            startRec = Session("ObserverMisc_Query_REC")
            If Not IsNumeric(startRec) Or startRec = "" Then                  
                  startRec = 1 ' Reset start record counter
                  Session("ObserverMisc_Query_REC") = startRec
            End If
      End If
Else
      startRec = Session("ObserverMisc_Query_REC")
      If Not IsNumeric(startRec) Or startRec = "" Then            
            startRec = 1 'Reset start record counter
            Session("ObserverMisc_Query_REC") = startRec
      End If
End If
%>
<!--#include file="header.asp"-->
  <p><span class="aspmaker"><font size="+1">Miscellaneous </font></span></p>
<form action="ObserverMisc_Querylist.asp">
<table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td><span class="aspmaker">
            &nbsp;&nbsp;<a href="ObserverMisc_Querylist.asp?cmd=reset">Show all</a>
            &nbsp;&nbsp;<a href="ObserverMisc_Querysrch.asp">Advanced Search</a>
            &nbsp;&nbsp;<a href="ObserverMisc_QuerylistPrint.asp?key=<%=Server.URLEncode(x_idkeynum)%>">Printer Friendly</a>
            </span></td>
      </tr>
</table>
</form>
<form method="post">
<table width="100%" border="0" cellspacing="1" cellpadding="1" bgcolor="#B4B4B4">
      <tr bgcolor="#d9d9d9">
            <td><span class="aspmaker" style="color: #000000;">
<a href="ObserverMisc_Querylist.asp?order=<%= Server.URLEncode("Name") %>" style="color: #000000;">Name&nbsp;<% If OrderBy = "Name" Then %><span class="ewTableOrderIndicator"><% If Session("ObserverMisc_Query_OT") = "ASC" Then %>5<% ElseIf Session("ObserverMisc_Query_OT") = "DESC" Then %>6<% End If %></span><% End If %></a>
            </span></td>
            <td><span class="aspmaker" style="color: #000000;">
<a href="ObserverMisc_Querylist.asp?order=<%= Server.URLEncode("WinchesOrHours") %>" style="color: #000000;">Winches or Hours&nbsp;<% If OrderBy = "WinchesOrHours" Then %><span class="ewTableOrderIndicator"><% If Session("ObserverMisc_Query_OT") = "ASC" Then %>5<% ElseIf Session("ObserverMisc_Query_OT") = "DESC" Then %>6<% End If %></span><% End If %></a>
            </span></td>
            <td><span class="aspmaker" style="color: #000000;">
<a href="ObserverMisc_Querylist.asp?order=<%= Server.URLEncode("Hours") %>" style="color: #000000;">Total Hours&nbsp;<% If OrderBy = "Hours" Then %><span class="ewTableOrderIndicator"><% If Session("ObserverMisc_Query_OT") = "ASC" Then %>5<% ElseIf Session("ObserverMisc_Query_OT") = "DESC" Then %>6<% End If %></span><% End If %></a>
            </span></td>
            <td><span class="aspmaker" style="color: #000000;">
<a href="ObserverMisc_Querylist.asp?order=<%= Server.URLEncode("LastUpdated") %>" style="color: #000000;">Last Updated&nbsp;<% If OrderBy = "LastUpdated" Then %><span class="ewTableOrderIndicator"><% If Session("ObserverMisc_Query_OT") = "ASC" Then %>5<% ElseIf Session("ObserverMisc_Query_OT") = "DESC" Then %>6<% End If %></span><% End If %></a>
            </span></td>
<% If (ewCurSec And ewAllowEdit) = ewAllowEdit Then %>
<td>&nbsp;</td>
<% End If %>
<% If (ewCurSec And ewAllowDelete) = ewAllowDelete Then %>
<td>&nbsp;</td>
<% End If %>
</tr>
<%

' Avoid starting record > total records
If CLng(startRec) > CLng(totalRecs) Then
      startRec = totalRecs
End If

' Set the last record to display
stopRec = startRec + displayRecs - 1

' Move to first record directly for performance reason
recCount = startRec - 1
If Not rs.Eof Then
      rs.MoveFirst
      rs.Move startRec - 1
End If
recActual = 0
Do While (Not rs.Eof) And (recCount < stopRec)
      recCount = recCount + 1
      If CLng(recCount) >= CLng(startRec) Then
            recActual = recActual + 1 %>
<%

      ' Set row color
      bgcolor = "#EBEBEB"
%>
<%      

      ' Display alternate color for rows
      If recCount Mod 2 <> 0 Then
            bgcolor = "#ebebeb"
      End If
%>
<%

      ' Load Key for record
      key = rs("ObserverMiscID")
      x_ObserverMiscID = rs("ObserverMiscID")
      x_Name = rs("Name")
      x_WinchesOrHours = rs("WinchesOrHours")
      x_Hours = rs("Hours")
      x_Notes = rs("Notes")
      x_LastUpdated = rs("LastUpdated")
%>
      <tr bgcolor="<%= bgcolor %>">
            <td><span class="aspmaker"><% Response.Write x_Name %></span>&nbsp;</td>
            <td><span class="aspmaker"><% Response.Write x_WinchesOrHours %></span>&nbsp;</td>
            <td><span class="aspmaker"><% Response.Write x_Hours %></span>&nbsp;</td>
            <td><span class="aspmaker"><% If IsDate(x_LastUpdated) Then Response.Write EW_FormatDateTime(x_LastUpdated,0) Else Response.Write x_LastUpdated End If %></span>&nbsp;</td>
<% If (ewCurSec And ewAllowEdit) = ewAllowEdit Then %>
<td><span class="aspmaker"><a href="<% key = rs("ObserverMiscID") : If Not IsNull(key) Then Response.Write "ObserverMisc_Queryedit.asp?key=" & Server.URLEncode(key) Else Response.Write "javascript:alert('Invalid Record! Key is null');" End If %>"><img src='images/edit.gif' alt='Edit' width='16' height='16' border='0'></a></span></td>
<% End If %>
<% If (ewCurSec And ewAllowDelete) = ewAllowDelete Then %>
<td><span class="aspmaker"><input type="checkbox" name="key" value="<%= key %>" class="aspmaker"><img src='images/delete.gif' alt='Delete' width='16' height='16' border='0'></span></td>
<% End If %>
      </tr>
<%
      End If
      rs.MoveNext
Loop
%>
</table>
<% If ((ewCurSec And ewAllowDelete) = ewAllowDelete) Then %>
<% If recActual > 0 Then %>
<p><input type="button" name="btndelete" value="DELETE SELECTED" onClick="this.form.action='ObserverMisc_Querydelete.asp';this.form.submit();"></p>
<% End If %>
<% End If %>
</form>
<%

' Close recordset and connection
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
<table border="0" cellspacing="0" cellpadding="10"><tr><td>
<%
If totalRecs > 0 Then
      rsEof = (totalRecs < (startRec + displayRecs))
      PrevStart = startRec - displayRecs
      If PrevStart < 1 Then PrevStart = 1
      NextStart = startRec + displayRecs
      If NextStart > totalRecs Then NextStart = startRec
      LastStart = ((totalRecs-1)\displayRecs)*displayRecs+1
      %>
<form>      
      <table border="0" cellspacing="0" cellpadding="0"><tr><td><span class="aspmaker">Page</span>&nbsp;</td>
<!--first page button-->
      <% If CLng(startRec)=1 Then %>
      <td><img src="images/firstdisab.gif" alt="First" width="20" height="15" border="0"></td>
      <% Else %>
      <td><a href="ObserverMisc_Querylist.asp?start=1"><img src="images/first.gif" alt="First" width="20" height="15" border="0"></a></td>
      <% End If %>
<!--previous page button-->
      <% If CLng(PrevStart) = CLng(startRec) Then %>
      <td><img src="images/prevdisab.gif" alt="Previous" width="20" height="15" border="0"></td>
      <% Else %>
      <td><a href="ObserverMisc_Querylist.asp?start=<%=PrevStart%>"><img src="images/prev.gif" alt="Previous" width="20" height="15" border="0"></a></td>
      <% End If %>
<!--current page number-->
      <td><input type="text" name="pageno" value="<%=(startRec-1)\displayRecs+1%>" size="4"></td>
<!--next page button-->
      <% If CLng(NextStart) = CLng(startRec) Then %>
      <td><img src="images/nextdisab.gif" alt="Next" width="20" height="15" border="0"></td>
      <% Else %>
      <td><a href="ObserverMisc_Querylist.asp?start=<%=NextStart%>"><img src="images/next.gif" alt="Next" width="20" height="15" border="0"></a></td>
      <% End If %>
<!--last page button-->
      <% If CLng(LastStart) = CLng(startRec) Then %>
      <td><img src="images/lastdisab.gif" alt="Last" width="20" height="15" border="0"></td>
      <% Else %>
      <td><a href="ObserverMisc_Querylist.asp?start=<%=LastStart%>"><img src="images/last.gif" alt="Last" width="20" height="15" border="0"></a></td>
      <% End If %>
<% If (ewCurSec And ewAllowAdd) = ewAllowAdd Then %>
      <td><a href="ObserverMisc_Queryadd.asp"><img src="images/addnew.gif" alt="Add new" width="20" height="15" border="0"></a></td>
<% End If %>
      <td><span class="aspmaker">&nbsp;of <%=(totalRecs-1)\displayRecs+1%></span></td>
      </td></tr></table>      
</form>      
      <% If CLng(startRec) > CLng(totalRecs) Then startRec = totalRecs
      stopRec = startRec + displayRecs - 1
      recCount = totalRecs - 1
      If rsEOF Then recCount = totalRecs
      If stopRec > recCount Then stopRec = recCount %>
      <span class="aspmaker">Records <%= startRec %> to <%= stopRec %> of <%= totalRecs %></span>
<% Else %>
      <% If (ewCurSec And ewAllowList) = ewAllowList Then %>
      <span class="aspmaker">No records found</span>
      <% Else %>
      <span class="aspmaker">No Permission</span>
      <% End If %>
<p>
<% If (ewCurSec And ewAllowAdd) = ewAllowAdd Then %>
<a href="ObserverMisc_Queryadd.asp"><img src="images/addnew.gif" alt="Add new" width="20" height="15" border="0"></a>
<% End If %>
</p>
<% End If %>
</td></tr></table>
<!--#include file="footer.asp"-->

Hi

which line is line 253?

Scott

Yes Scott!  That's it.  The query in the Access database was refering to the 'ObserverMiscellaneous' table (which no longer existed).  I over looked it because the table name in the design view of the query was shortened to ObserverMisc..  

Problem solved.
Thank you
Bagni :)

good stuff, glad your sorted.

and thanks for the grade!

Cheers

Scott