|
 |
access_asp thread: HELP! >> ODBC Microsoft Access Driver] Cannot open any more tables
Message #1 by "Evan Kezsbom" <ekez99@y...> on Wed, 22 Jan 2003 22:55:17
|
|
Hi -
I am encountering on the following error:
-------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
[Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables.
ReportView.asp, line 2265
-------------------------------------------------------
I have never seen this error before - anywhere??? I've looked through my
code and believe all objects and variables are being opened and closed
appropriately.
Any suggestions, insight et would be GREATLy appreciated, as this is an
extreemly urgent matter.
Thank you in advance!
CODE:
<%'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''%>
<%End If%>
<%If reportType = "LoanDesc" Then
'''''''''''''''''''''''''''''''''''
viewByYrVal = request.querystring("viewByYrVal")
If request.querystring("viewByYrVal") = "" then
viewByYrVal = request.form("viewByYrVal")
End if
'''''''''''''''''''''''''''''''''''
%>
<br>
<table border="0" cellspacing="0" cellpadding="0" align="center"
width="730">
<tr>
<td valign="top" align="left">
<table border="0" cellspacing="0" cellpadding="0"
align="left" width="730">
<tr>
<td valign="top">
<table border="1" bordercolor="#000066"
cellspacing="0" cellpadding="0" align="left" width="730">
<tr>
<td valign="top">
<table border="0"
cellspacing="0" cellpadding="3" align="left" width="730">
<%
'FIND NUMBER OF
LTYPES (CountTotalLType)
'SET THE FIRST
LTYPEID (LTypeIDStart) FOR THE FOLLOWING LOOP TO BEGIN ON - NOTE ORDER BY
ASC
set rs11 =
Server.CreateObject("ADODB.RecordSet")
sql = "SELECT
Distinct LoanType.*" & _
" FROM
LoanType" & _
" ORDER
BY LoanType.LTypeID ASC"
'response.write sql
rs11.Open
sql,conn,1,3
If not rs11.EOF
Then
LTypeIDStart = rs11
("LTypeID")
CountTotalLType =
rs11.RecordCount
End If
CounterX = 1
'START LOOP ON
TOTAL COUNT
For i = 1 to
CountTotalLType step 1
'SET LTYPE FOR FISRT GO-AROUND
If
LTypeID = "" Then
LTypeID = LTypeIDStart
End If
set rs = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct LoanType.*" & _
" FROM LoanType" & _
" WHERE LoanType.LTypeID <>" & LTypeID
'response.write sql
rs.Open sql,conn,1,3
If
not rs.EOF Then
'CREATE RECORD SETS FOR EACH LTYPE
'
3 SETS PER TYPE (minor/important/serious)
Do
while not rs.eof
If
Counter = CountTotalLType Then
Exit Do
End If
LTypeID = rs("LTypeID")
LType = rs("LType")
' FIRST GET TOTAL
set rs1 = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct Loan.*" & _
" FROM Loan, LoanException, LoanType" & _
" WHERE LoanException.LoanID =
Loan.LoanID" & _
" AND Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID = " & CliID & _
" AND Loan.DisplayFlg = " & TRUE & _
" AND Loan.LoanCloseDtMo = "&viewByMoVal&
_
" AND Loan.LoanCloseDtYr = "&viewByYrVal
'response.write sql
rs1.Open sql,conn,1,3
If not rs1.EOF Then
LoanTypeCount = rs1.RecordCount
End If
rs1.close
set rs1 = nothing
'MINOR
set rs1 = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct LoanException.LExID" & _
" FROM Loan, LoanException, LoanType" & _
" WHERE LoanException.LoanID =
Loan.LoanID" & _
" AND Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID = " & CliID & _
" AND Loan.DisplayFlg = " & TRUE & _
" AND LoanException.LExType
= '"&"Minor"&"'" & _
" AND Loan.LoanCloseDtMo = "&viewByMoVal&
_
" AND Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br>" & sql
rs1.Open sql,conn,1,3
If not rs1.EOF Then
LexTypeCount_Minor = rs1.RecordCount
Else
LexTypeCount_Minor = 0
End If
If LexTypeCount_Minor > 0 then
LexTypeAvg_Minor = LexTypeCount_Minor/LoanTypeCount
LexTypeAvg_Minor = round(LexTypeAvg_Minor,2)
Else
LexTypeAvg_Minor = 0
LexTypeCount_Minor = 0
End IF
rs1.close
set rs1 = nothing
'IMPORTANT
set rs1 = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct LoanException.LExID" & _
" FROM Loan, LoanException, LoanType" & _
" WHERE LoanException.LoanID =
Loan.LoanID" & _
" AND Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID = " & CliID & _
" AND Loan.DisplayFlg = " & TRUE & _
" AND LoanException.LExType
= '"&"Important"&"'" & _
" AND Loan.LoanCloseDtMo = "&viewByMoVal&
_
" AND Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br>" & sql
rs1.Open sql,conn,1,3
If not rs1.EOF Then
LexTypeCount_Important = rs1.RecordCount
Else
LexTypeCount_Important = 0
End If
If LexTypeCount_Important > 0 then
LexTypeAvg_Important =
LexTypeCount_Important/LoanTypeCount
LexTypeAvg_Important = round
(LexTypeAvg_Important,2)
Else
LexTypeAvg_Important = 0
LexTypeCount_Important = 0
End IF
set rs1 = nothing
'SERIOUS
set rs1 = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct LoanException.LExID" & _
" FROM Loan, LoanException, LoanType" & _
" WHERE LoanException.LoanID =
Loan.LoanID" & _
" AND Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID = " & CliID & _
" AND Loan.DisplayFlg = " & TRUE & _
" AND LoanException.LExType
= '"&"Serious"&"'" & _
" AND Loan.LoanCloseDtMo = "&viewByMoVal&
_
" AND Loan.LoanCloseDtYr = "&viewByYrVal
'response.write sql
rs1.Open sql,conn,1,3
If not rs1.EOF Then
LexTypeCount_Serious = rs1.RecordCount
Else
LexTypeCount_Serious = 0
End If
If LexTypeCount_Serious > 0 then
LexTypeAvg_Serious =
LexTypeCount_Serious/LoanTypeCount
LexTypeAvg_Serious = round(LexTypeAvg_Serious,2)
Else
LexTypeAvg_Serious = 0
LexTypeCount_Serious = 0
End IF
set rs1 = nothing
%>
<tr>
<td valign="top" class="subtitle"
style="color: #ffffff;" align="left" bgcolor=<%=BG%>><b><%= UCASE(LType) %
></b></td>
</tr>
<tr>
<td valign="top" class="newsred">
<table border="0" cellspacing="0"
cellpadding="3" align="left" width="">
<tr>
<td valign="top"
class="newsred" align="left">
<b><u>Files:</u></b>
</td>
<td valign="top"
class="newsred" align="left">
<b><u>Type:</u></b>
</td>
<td valign="top"
class="newsred" align="left">
<b><u>Total:</u></b>
</td>
<td valign="top"
class="newsred" align="left">
<b><u>Avg:</u></b>
</td>
<td valign="top"
class="newsred" align="left">
</td>
<%
MoCount = 1
viewByMoValNext =
viewByMoVal - 1
Do while MoCount < 12
'///////////////////////////
If viewByMoValNext = 0 Then
viewByMoValNext =
12
viewByYrVal =
viewByYrVal - 1
End If
'///////////////////////////
set rs1 =
Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct
LoanException.LExID" & _
" FROM Loan,
LoanException, LoanType" & _
" WHERE
LoanException.LoanID = Loan.LoanID" & _
" AND
Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID
= " & CliID & _
" AND
Loan.DisplayFlg = " & TRUE & _
" AND
LoanException.LExType = '"&"Serious"&"'" & _
" AND
Loan.LoanCloseDtMo = "&viewByMoValNext & _
" AND
Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br><br>"&sql&"<br><br>"
rs1.Open sql,conn,1,3
Select case viewByMoValNext
Case "1"
MoNm
= "Jan '"&RIGHT(viewByYrVal,2)
Case "2"
MoNm
= "Feb '"&RIGHT(viewByYrVal,2)
Case "3"
MoNm
= "Mar '"&RIGHT(viewByYrVal,2)
Case "4"
MoNm
= "Apr '"&RIGHT(viewByYrVal,2)
Case "5"
MoNm
= "May '"&RIGHT(viewByYrVal,2)
Case "6"
MoNm
= "Jun '"&RIGHT(viewByYrVal,2)
Case "7"
MoNm
= "Jul '"&RIGHT(viewByYrVal,2)
Case "8"
MoNm
= "Aug '"&RIGHT(viewByYrVal,2)
Case "9"
MoNm
= "Sep '"&RIGHT(viewByYrVal,2)
Case "10"
MoNm
= "Oct '"&RIGHT(viewByYrVal,2)
Case "11"
MoNm
= "Nov '"&RIGHT(viewByYrVal,2)
Case "12"
MoNm
= "Dec '"&RIGHT(viewByYrVal,2)
End Select
%>
<td valign="top"
class="newsred" align="left">
<b><u><a
href="https://www9.ssldomain.com/premierqc/ReportView.asp?
step=2&reportType=<%=reportType%>&CliID=<%=CliID%>&viewByMoVal=<%=
viewByMoValNext %>&viewByYrVal=<%= viewByYrVal %>&viewByFlg=month"><%=
MoNm %></a></u></b>
</td>
<%
'If viewByMoValNext = 12
Then
'viewByMoValNext =
1
'Else
viewByMoValNext =
viewByMoValNext - 1
'End If
MoCount = MoCount + 1
loop
rs1.close
set rs1 = nothing
%>
</tr>
<tr>
<td valign="top"
class="news" align="left">
<%
set rs1 =
Server.CreateObject("ADODB.RecordSet")
sql = "SELECT
LoanType.*" & _
" FROM
Client, Loan, LoanType" & _
" WHERE
Loan.LTypeID = " & LTypeID & _
" AND
Loan.LTypeID = LoanType.LTypeID" & _
" AND
Loan.CliID = " & CliID & _
" AND
Client.CliID = " & CliIDFile & _
" AND
LoanType.LTypeID = Loan.LTypeID" & _
" AND
Loan.DisplayFlg = " & TRUE & _
" AND
Loan.LoanCloseDtMo = "&BlockMo& _
" AND
Loan.LoanCloseDtYr = "&BlockYr
'response.write "<br><br>" & sql
rs1.Open
sql,conn,1,3
If not rs1.EOF Then
LTypeFilesCnt = rs1.RecordCount
Else
LTypeFilesCnt = 0
End If %>
<%= LTypeFilesCnt %
>
<%
If
(LexTypeCount_Minor > 0) AND (LTypeFilesCnt > 0) then
LexTypeAvg_Minor =
LexTypeCount_Minor/LTypeFilesCnt
LexTypeAvg_Minor =
round(LexTypeAvg_Minor,2)
Else
LexTypeAvg_Minor = 0
LexTypeCount_Minor = 0
End IF
If
(LexTypeCount_Important > 0) AND (LTypeFilesCnt > 0) then
LexTypeAvg_Important = LexTypeCount_Important/LTypeFilesCnt
LexTypeAvg_Important = round(LexTypeAvg_Important,2)
Else
LexTypeAvg_Important = 0
LexTypeCount_Important = 0
End IF
If
(LexTypeCount_Serious > 0) AND (LTypeFilesCnt > 0) then
LexTypeAvg_Serious
= LexTypeCount_Serious/LTypeFilesCnt
LexTypeAvg_Serious
= round(LexTypeAvg_Serious,2)
Else
LexTypeAvg_Serious = 0
LexTypeCount_Serious = 0
End IF
%>
</td>
<td valign="top"
class="news" align="left">
Minor<br>
Important<br>
Serious
</td>
<td valign="top"
class="news" align="left">
<%=
LexTypeCount_Minor %><br>
<%=
LexTypeCount_Important %><br>
<%=
LexTypeCount_Serious %>
</td>
<td valign="top"
class="news" align="left">
<%=
LexTypeAvg_Minor %><br>
<%=
LexTypeAvg_Important %><br>
<%=
LexTypeAvg_Serious %>
</td>
<td valign="top"
class="newsred" align="left">
<img
src="images/black.gif" width="1" height="40" border="0" alt="">
</td>
<%
MoCount = 1
'///////////////////////////
viewByYrVal =
request.querystring("viewByYrVal")
If request.querystring
("viewByYrVal") = "" then
viewByYrVal =
request.form("viewByYrVal")
End if
'///////////////////////////
viewByMoValNext =
viewByMoVal - 1
Do while MoCount < 12
'///////////////////////////
If viewByMoValNext = 0 Then
viewByMoValNext =
12
viewByYrVal =
viewByYrVal - 1
End If
'///////////////////////////
set rs1 =
Server.CreateObject("ADODB.RecordSet")
sql = "SELECT
LoanType.*" & _
" FROM
Client, Loan, LoanType" & _
" WHERE
Loan.LTypeID = " & LTypeID & _
" AND
Loan.LTypeID = LoanType.LTypeID" & _
" AND
Loan.CliID = " & CliID & _
" AND
Client.CliID = " & CliIDFile & _
" AND
LoanType.LTypeID = Loan.LTypeID" & _
" AND
Loan.DisplayFlg = " & TRUE & _
" AND
Loan.LoanCloseDtMo = "&viewByMoValNext& _
" AND
Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br><br>" & sql
'set rs1 =
Server.CreateObject("ADODB.RecordSet")
'sql = "SELECT Distinct
Loan.*" & _
'" FROM Loan,
LoanException, LoanType" & _
'" WHERE
LoanException.LoanID = Loan.LoanID" & _
'" AND
Loan.LTypeID = " & LTypeID & _
'" AND Loan.CliID
= " & CliID & _
'" AND
Loan.DisplayFlg = " & TRUE & _
'" AND
Loan.LoanCloseDtMo = "&viewByMoValNext& _
'" AND
Loan.LoanCloseDtYr = "&viewByYrVal
'response.write
sql
rs1.Open sql,conn,1,3
If not rs1.EOF Then
LoanTypeCountAdvance = rs1.RecordCount
Else
LoanTypeCountAdvance = 0
End If
rs1.close
set rs1 = nothing
sql = "SELECT Distinct
LoanException.LExID" & _
" FROM Loan,
LoanException, LoanType" & _
" WHERE
LoanException.LoanID = Loan.LoanID" & _
" AND
Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID
= " & CliID & _
" AND
Loan.DisplayFlg = " & TRUE & _
" AND
LoanException.LExType = '"&"Minor"&"'" & _
" AND
Loan.LoanCloseDtMo = "&viewByMoValNext & _
" AND
Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br><br>"&sql&"<br><br>"
rs1.Open sql,conn,1,3
If not rs1.EOF Then
AdvanceMoMinorCount = rs1.RecordCount
Else
AdvanceMoMinorCount = 0
End If
If (AdvanceMoMinorCount >
0) AND (LoanTypeCountAdvance > 0) Then
AdvanceMoMinorAvg
= AdvanceMoMinorCount/LoanTypeCountAdvance
AdvanceMoMinorAvg
= round(AdvanceMoMinorAvg,2)
Else
AdvanceMoMinorAvg
= 0
End If
set rs1 =
Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct
LoanException.LExID" & _
" FROM Loan,
LoanException, LoanType" & _
" WHERE
LoanException.LoanID = Loan.LoanID" & _
" AND
Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID
= " & CliID & _
" AND
Loan.DisplayFlg = " & TRUE & _
" AND
LoanException.LExType = '"&"Important"&"'" & _
" AND
Loan.LoanCloseDtMo = "&viewByMoValNext & _
" AND
Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br><br>"&sql&"<br><br>"
rs1.Open sql,conn,1,3
If not rs1.EOF Then
AdvanceMoImportantCount = rs1.RecordCount
Else
AdvanceMoImportantCount = 0
End If
If
(AdvanceMoImportantCount > 0) AND (LoanTypeCountAdvance > 0) Then
AdvanceMoImportantAvg =
AdvanceMoImportantCount/LoanTypeCountAdvance
AdvanceMoImportantAvg = round(AdvanceMoImportantAvg,2)
Else
AdvanceMoImportantAvg = 0
End If
set rs1 =
Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct
LoanException.LExID" & _
" FROM Loan,
LoanException, LoanType" & _
" WHERE
LoanException.LoanID = Loan.LoanID" & _
" AND
Loan.LTypeID = " & LTypeID & _
" AND Loan.CliID
= " & CliID & _
" AND
Loan.DisplayFlg = " & TRUE & _
" AND
LoanException.LExType = '"&"Serious"&"'" & _
" AND
Loan.LoanCloseDtMo = "&viewByMoValNext & _
" AND
Loan.LoanCloseDtYr = "&viewByYrVal
'response.write "<br><br>"&sql&"<br><br>"
rs1.Open sql,conn,1,3
If not rs1.EOF Then
AdvanceMoSeriousCount = rs1.RecordCount
Else
AdvanceMoSeriousCount = 0
End If
If (AdvanceMoSeriousCount
> 0) AND (LoanTypeCountAdvance > 0) Then
AdvanceMoSeriousAvg = AdvanceMoSeriousCount/LoanTypeCountAdvance
AdvanceMoSeriousAvg = round(AdvanceMoSeriousAvg,2)
Else
AdvanceMoSeriousAvg = 0
End If
%>
<td valign="top"
class="news" align="left">
<%=
AdvanceMoMinorAvg %><br>
<%=
AdvanceMoImportantAvg %><br>
<%=
AdvanceMoSeriousAvg %>
</td>
<%
viewByMoValNext =
viewByMoValNext - 1
MoCount = MoCount + 1
loop
rs1.close
set rs1 = nothing
%>
</tr>
</table>
</td>
</tr>
<%
Counter = Counter + 1
'///////////////////////////
viewByYrVal = request.querystring("viewByYrVal")
If request.querystring("viewByYrVal") = "" then
viewByYrVal = request.form("viewByYrVal")
End if
'///////////////////////////
rs.movenext
loop
End If
rs.close
set rs = nothing
CounterX =
CounterX + 1
Next
rs11.close
set rs11 =
nothing
%>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
<br>
<%
End If
Message #2 by "Ken Schaefer" <ken@a...> on Thu, 23 Jan 2003 13:36:12 +1100
|
|
a) What is "conn"? Is it a connection object or a connection string?
b) Try not to open recordsets inside loops - that's where I think your
problem probably is. Can't you use a JOIN and return one (large) recordset
instead?
c) a 2265 line ASP page is insane. Consider classes, or at the very least
routines to cut down on the number of lines. It'll lead to more robust code
as well.
d) Next time, please mark line 2265 - I pasted your code into Word, and it
is 25 (!!) pages long. I'm not going to wade through all the code trying to
find what might be a possible error.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Evan Kezsbom" <ekez99@y...>
Subject: [access_asp] HELP! >> ODBC Microsoft Access Driver] Cannot open any
more tables
: I am encountering on the following error:
:
: -------------------------------------------------------
: Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
: [Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables.
:
: ReportView.asp, line 2265
: -------------------------------------------------------
:
: I have never seen this error before - anywhere??? I've looked through my
: code and believe all objects and variables are being opened and closed
: appropriately.
:
: Any suggestions, insight et would be GREATLy appreciated, as this is an
: extreemly urgent matter.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<snipped for your viewing sanity>
|
|
|
View Cart
