p2p.wrox.com: Archives - access

access_asp thread: ERROR: Cannot open any more tables

Message #1 by "Evan Kezsbom" <ekez99@y...> on Wed, 19 Feb 2003 01:46:37

Hello - 

I'm recieving the following error:

Microsoft OLE DB Provider for ODBC Drivers error '8007000e' 
[Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables. 

Though I'm looping through a fair amount of data, I'm closing and killing 
the recordset object each time...?

Any ideas?

Thanks in advance.

Code snippet:

------------------------------------------

<%
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 Underwriter.*" & _
		 " FROM Client, Loan, Underwriter" & _ 
		 " WHERE Loan.UndID = "  & UndID & _ 
		 " AND Loan.UndID = Underwriter.UndID" & _
		 " AND Loan.CliID = " & CliID & _
		 " AND Client.CliID = " & CliIDFile & _
		 " AND Underwriter.CliID = Loan.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
	LoanTypeCountAdvanceZ = rs1.RecordCount
Else
	LoanTypeCountAdvanceZ = 0
End If
rs1.close
set rs1 = nothing

sql = "SELECT Distinct LoanException.LExID" & _
	 " FROM Loan INNER JOIN LoanException ON Loan.LoanID = 
LoanException.LoanID" & _
	 " WHERE LoanException.LoanID = Loan.LoanID" & _ 
	 " AND LoanException.LExEmpID = "  & UndID & _ 
	 " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _ 
	 " AND LoanException.LExType = '"&"Minor"&"'"  & _
	 " AND Loan.DisplayFlg = " & TRUE & _ 
	 " 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
	AdvanceMoMinorCountZ = rs1.RecordCount
Else 
	AdvanceMoMinorCountZ = 0
End If

If (AdvanceMoMinorCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then	
	AdvanceMoMinorAvgZ = AdvanceMoMinorCountZ/LoanTypeCountAdvanceZ
	AdvanceMoMinorAvgZ = round(AdvanceMoMinorAvgZ,2)
Else
	AdvanceMoMinorAvgZ = 0
End If 	
rs1.close
set rs1 = nothing

set rs1 = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct LoanException.LExID" & _
	 " FROM Loan INNER JOIN LoanException ON Loan.LoanID = 
LoanException.LoanID" & _
	 " WHERE LoanException.LoanID = Loan.LoanID" & _ 
	 " AND LoanException.LExEmpID = "  & UndID & _ 
	 " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _ 
	 " AND LoanException.LExType = '"&"Important"&"'"  & _
	 " AND Loan.DisplayFlg = " & TRUE & _ 
	 " 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
	AdvanceMoImportantCountZ = rs1.RecordCount
Else 
	AdvanceMoImportantCountZ = 0
End If	
If (AdvanceMoImportantCountZ > 0)  AND (LoanTypeCountAdvanceZ > 0) Then
	AdvanceMoImportantAvgZ = 
AdvanceMoImportantCountZ/LoanTypeCountAdvanceZ
	AdvanceMoImportantAvgZ = round(AdvanceMoImportantAvgZ,2)
Else
	AdvanceMoImportantAvgZ = 0
End If	
rs1.close
set rs1 = nothing	
	
	
set rs1 = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Distinct LoanException.LExID" & _
	 " FROM Loan INNER JOIN LoanException ON Loan.LoanID = 
LoanException.LoanID" & _
	 " WHERE LoanException.LoanID = Loan.LoanID" & _ 
	 " AND LoanException.LExEmpID = "  & UndID & _ 
		 " AND LoanException.LExEmpType = '"& "Underwriter" & "'" 
& _ 
	 " AND LoanException.LExType = '"&"Serious"&"'"  & _
	 " AND Loan.DisplayFlg = " & TRUE & _ 
	 " 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
	AdvanceMoSeriousCountZ = rs1.RecordCount
Else 
	AdvanceMoSeriousCountZ = 0
End If	
If (AdvanceMoSeriousCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
	AdvanceMoSeriousAvgZ = AdvanceMoSeriousCountZ/LoanTypeCountAdvanceZ
	AdvanceMoSeriousAvgZ = round(AdvanceMoSeriousAvgZ,2)
Else
	AdvanceMoSeriousAvgZ = 0
End If	
rs1.close
set rs1 = nothing

%>
<td valign="top" class="news" align="left">
	<%= AdvanceMoMinorAvgZ %><br>
	<%= AdvanceMoImportantAvgZ %><br>
	<%= AdvanceMoSeriousAvgZ %>
</td>
<%


'If viewByMoValNext >= 13 Then
	'viewByMoValNext = 1
'Else
	viewByMoValNext = viewByMoValNext - 1
'End If
MoCount = MoCount + 1


loop
%>

------------------------------------------

Message #2 by "Ken Schaefer" <ken@a...> on Wed, 19 Feb 2003 13:21:15 +1100

In your code where you have:

<%
rs1.Open sql,conn,1,3
%>

what is "conn"? (I've asked this before but you never answer)

Second, your SQL is *insane*. You are selecting *all* these records and all
these fields just to get a recordcount. That's crazy.

You should do something like:

strSQL = _
    "SELECT a.LoanCloseDteMo, COUNT(*) AS NumRecs " & _
    " FROM Client, Loan AS a, Underwriter" & _
    " WHERE a.UndID = "  & UndID & _
    " AND a.UndID = Underwriter.UndID" & _
    " AND a.CliID = " & CliID & _
    " AND Client.CliID = " & CliIDFile & _
    " AND Underwriter.CliID = Loan.CliID" & _
    " AND a.DisplayFlg = " & TRUE & _
    " AND a.LoanCloseDtYr = "&viewByYrVal
    " GROUP BY a.LoanCloseDtMo" & _
    " ORDER BY a.LoadCloseDtMo ASC"

Set objRS = objConn.Execute strSQL
If not objRS.EOF then
    arrResults = objRS.GetRows
End If
objRS.Close
Set objRS = Nothing
%>

which would return a single recordset which would look like this:

LoanCloseDteMo    NumRecs
    1                            12
    2                              5
    3                              6
    4                              2

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Evan Kezsbom" <ekez99@y...>
Subject: [access_asp] ERROR: Cannot open any more tables


: I'm recieving the following error:
:
: Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
: [Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables.
:
: Though I'm looping through a fair amount of data, I'm closing and killing
: the recordset object each time...?
:
: Any ideas?
:
: Thanks in advance.
:
: Code snippet:
:
: ------------------------------------------
:
: <%
: 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 Underwriter.*" & _
: " FROM Client, Loan, Underwriter" & _
: " WHERE Loan.UndID = "  & UndID & _
: " AND Loan.UndID = Underwriter.UndID" & _
: " AND Loan.CliID = " & CliID & _
: " AND Client.CliID = " & CliIDFile & _
: " AND Underwriter.CliID = Loan.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
: LoanTypeCountAdvanceZ = rs1.RecordCount
: Else
: LoanTypeCountAdvanceZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
: sql = "SELECT Distinct LoanException.LExID" & _
: " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: LoanException.LoanID" & _
: " WHERE LoanException.LoanID = Loan.LoanID" & _
: " AND LoanException.LExEmpID = "  & UndID & _
: " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _
: " AND LoanException.LExType = '"&"Minor"&"'"  & _
: " AND Loan.DisplayFlg = " & TRUE & _
: " 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
: AdvanceMoMinorCountZ = rs1.RecordCount
: Else
: AdvanceMoMinorCountZ = 0
: End If
:
: If (AdvanceMoMinorCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
: AdvanceMoMinorAvgZ = AdvanceMoMinorCountZ/LoanTypeCountAdvanceZ
: AdvanceMoMinorAvgZ = round(AdvanceMoMinorAvgZ,2)
: Else
: AdvanceMoMinorAvgZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
: set rs1 = Server.CreateObject("ADODB.RecordSet")
: sql = "SELECT Distinct LoanException.LExID" & _
: " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: LoanException.LoanID" & _
: " WHERE LoanException.LoanID = Loan.LoanID" & _
: " AND LoanException.LExEmpID = "  & UndID & _
: " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _
: " AND LoanException.LExType = '"&"Important"&"'"  & _
: " AND Loan.DisplayFlg = " & TRUE & _
: " 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
: AdvanceMoImportantCountZ = rs1.RecordCount
: Else
: AdvanceMoImportantCountZ = 0
: End If
: If (AdvanceMoImportantCountZ > 0)  AND (LoanTypeCountAdvanceZ > 0) Then
: AdvanceMoImportantAvgZ 
: AdvanceMoImportantCountZ/LoanTypeCountAdvanceZ
: AdvanceMoImportantAvgZ = round(AdvanceMoImportantAvgZ,2)
: Else
: AdvanceMoImportantAvgZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
:
: set rs1 = Server.CreateObject("ADODB.RecordSet")
: sql = "SELECT Distinct LoanException.LExID" & _
: " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: LoanException.LoanID" & _
: " WHERE LoanException.LoanID = Loan.LoanID" & _
: " AND LoanException.LExEmpID = "  & UndID & _
: " AND LoanException.LExEmpType = '"& "Underwriter" & "'"
: & _
: " AND LoanException.LExType = '"&"Serious"&"'"  & _
: " AND Loan.DisplayFlg = " & TRUE & _
: " 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
: AdvanceMoSeriousCountZ = rs1.RecordCount
: Else
: AdvanceMoSeriousCountZ = 0
: End If
: If (AdvanceMoSeriousCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
: AdvanceMoSeriousAvgZ = AdvanceMoSeriousCountZ/LoanTypeCountAdvanceZ
: AdvanceMoSeriousAvgZ = round(AdvanceMoSeriousAvgZ,2)
: Else
: AdvanceMoSeriousAvgZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
: %>
: <td valign="top" class="news" align="left">
: <%= AdvanceMoMinorAvgZ %><br>
: <%= AdvanceMoImportantAvgZ %><br>
: <%= AdvanceMoSeriousAvgZ %>
: </td>
: <%
:
:
: 'If viewByMoValNext >= 13 Then
: 'viewByMoValNext = 1
: 'Else
: viewByMoValNext = viewByMoValNext - 1
: 'End If
: MoCount = MoCount + 1
:
:
: loop
: %>
:
: ------------------------------------------

Message #3 by "Evan Kezsbom" <ekez99@y...> on Wed, 19 Feb 2003 06:03:34

Hi Ken - 

Sorry about that...
conn is refrencing the following:

set conn = Server.CreateObject("ADODB.Connection")
conn.open Application("ConnString")

I open the connection at the top of the page and close is at the bottom...

conn.Close
Set conn = Nothing


....this is probably a poor set-up as well (?)

Thank you for your help



> In your code where you have:

<%
rs1.Open sql,conn,1,3
%>

what is "conn"? (I've asked this before but you never answer)

Second, your SQL is *insane*. You are selecting *all* these records and all
these fields just to get a recordcount. That's crazy.

You should do something like:

strSQL = _
    "SELECT a.LoanCloseDteMo, COUNT(*) AS NumRecs " & _
    " FROM Client, Loan AS a, Underwriter" & _
    " WHERE a.UndID = "  & UndID & _
    " AND a.UndID = Underwriter.UndID" & _
    " AND a.CliID = " & CliID & _
    " AND Client.CliID = " & CliIDFile & _
    " AND Underwriter.CliID = Loan.CliID" & _
    " AND a.DisplayFlg = " & TRUE & _
    " AND a.LoanCloseDtYr = "&viewByYrVal
    " GROUP BY a.LoanCloseDtMo" & _
    " ORDER BY a.LoadCloseDtMo ASC"

Set objRS = objConn.Execute strSQL
If not objRS.EOF then
    arrResults = objRS.GetRows
End If
objRS.Close
Set objRS = Nothing
%>

which would return a single recordset which would look like this:

LoanCloseDteMo    NumRecs
    1                            12
    2                              5
    3                              6
    4                              2

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Evan Kezsbom" <ekez99@y...>
Subject: [access_asp] ERROR: Cannot open any more tables


: I'm recieving the following error:
:
: Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
: [Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables.
:
: Though I'm looping through a fair amount of data, I'm closing and killing
: the recordset object each time...?
:
: Any ideas?
:
: Thanks in advance.
:
: Code snippet:
:
: ------------------------------------------
:
: <%
: 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 Underwriter.*" & _
: " FROM Client, Loan, Underwriter" & _
: " WHERE Loan.UndID = "  & UndID & _
: " AND Loan.UndID = Underwriter.UndID" & _
: " AND Loan.CliID = " & CliID & _
: " AND Client.CliID = " & CliIDFile & _
: " AND Underwriter.CliID = Loan.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
: LoanTypeCountAdvanceZ = rs1.RecordCount
: Else
: LoanTypeCountAdvanceZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
: sql = "SELECT Distinct LoanException.LExID" & _
: " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: LoanException.LoanID" & _
: " WHERE LoanException.LoanID = Loan.LoanID" & _
: " AND LoanException.LExEmpID = "  & UndID & _
: " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _
: " AND LoanException.LExType = '"&"Minor"&"'"  & _
: " AND Loan.DisplayFlg = " & TRUE & _
: " 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
: AdvanceMoMinorCountZ = rs1.RecordCount
: Else
: AdvanceMoMinorCountZ = 0
: End If
:
: If (AdvanceMoMinorCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
: AdvanceMoMinorAvgZ = AdvanceMoMinorCountZ/LoanTypeCountAdvanceZ
: AdvanceMoMinorAvgZ = round(AdvanceMoMinorAvgZ,2)
: Else
: AdvanceMoMinorAvgZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
: set rs1 = Server.CreateObject("ADODB.RecordSet")
: sql = "SELECT Distinct LoanException.LExID" & _
: " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: LoanException.LoanID" & _
: " WHERE LoanException.LoanID = Loan.LoanID" & _
: " AND LoanException.LExEmpID = "  & UndID & _
: " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _
: " AND LoanException.LExType = '"&"Important"&"'"  & _
: " AND Loan.DisplayFlg = " & TRUE & _
: " 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
: AdvanceMoImportantCountZ = rs1.RecordCount
: Else
: AdvanceMoImportantCountZ = 0
: End If
: If (AdvanceMoImportantCountZ > 0)  AND (LoanTypeCountAdvanceZ > 0) Then
: AdvanceMoImportantAvgZ 
: AdvanceMoImportantCountZ/LoanTypeCountAdvanceZ
: AdvanceMoImportantAvgZ = round(AdvanceMoImportantAvgZ,2)
: Else
: AdvanceMoImportantAvgZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
:
: set rs1 = Server.CreateObject("ADODB.RecordSet")
: sql = "SELECT Distinct LoanException.LExID" & _
: " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: LoanException.LoanID" & _
: " WHERE LoanException.LoanID = Loan.LoanID" & _
: " AND LoanException.LExEmpID = "  & UndID & _
: " AND LoanException.LExEmpType = '"& "Underwriter" & "'"
: & _
: " AND LoanException.LExType = '"&"Serious"&"'"  & _
: " AND Loan.DisplayFlg = " & TRUE & _
: " 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
: AdvanceMoSeriousCountZ = rs1.RecordCount
: Else
: AdvanceMoSeriousCountZ = 0
: End If
: If (AdvanceMoSeriousCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
: AdvanceMoSeriousAvgZ = AdvanceMoSeriousCountZ/LoanTypeCountAdvanceZ
: AdvanceMoSeriousAvgZ = round(AdvanceMoSeriousAvgZ,2)
: Else
: AdvanceMoSeriousAvgZ = 0
: End If
: rs1.close
: set rs1 = nothing
:
: %>
: <td valign="top" class="news" align="left">
: <%= AdvanceMoMinorAvgZ %><br>
: <%= AdvanceMoImportantAvgZ %><br>
: <%= AdvanceMoSeriousAvgZ %>
: </td>
: <%
:
:
: 'If viewByMoValNext >= 13 Then
: 'viewByMoValNext = 1
: 'Else
: viewByMoValNext = viewByMoValNext - 1
: 'End If
: MoCount = MoCount + 1
:
:
: loop
: %>
:
: ------------------------------------------

Message #4 by "Ken Schaefer" <ken@a...> on Wed, 19 Feb 2003 17:44:54 +1100

That's OK - that's the "correct" way to do it. If conn was a connection
string, then you'd also get the error you are experiencing (because of the
implicitly creating connection objects)

Did you try altering your SQL statements to reduce the amount of recordsets
and data you need to bring back?

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Evan Kezsbom" <ekez99@y...>
Subject: [access_asp] Re: ERROR: Cannot open any more tables


: Sorry about that...
: conn is refrencing the following:
:
: set conn = Server.CreateObject("ADODB.Connection")
: conn.open Application("ConnString")
:
: I open the connection at the top of the page and close is at the bottom...
:
: conn.Close
: Set conn = Nothing
:
:
: ....this is probably a poor set-up as well (?)
:
: Thank you for your help
:
:
:
: > In your code where you have:
:
: <%
: rs1.Open sql,conn,1,3
: %>
:
: what is "conn"? (I've asked this before but you never answer)
:
: Second, your SQL is *insane*. You are selecting *all* these records and
all
: these fields just to get a recordcount. That's crazy.
:
: You should do something like:
:
: strSQL = _
:     "SELECT a.LoanCloseDteMo, COUNT(*) AS NumRecs " & _
:     " FROM Client, Loan AS a, Underwriter" & _
:     " WHERE a.UndID = "  & UndID & _
:     " AND a.UndID = Underwriter.UndID" & _
:     " AND a.CliID = " & CliID & _
:     " AND Client.CliID = " & CliIDFile & _
:     " AND Underwriter.CliID = Loan.CliID" & _
:     " AND a.DisplayFlg = " & TRUE & _
:     " AND a.LoanCloseDtYr = "&viewByYrVal
:     " GROUP BY a.LoanCloseDtMo" & _
:     " ORDER BY a.LoadCloseDtMo ASC"
:
: Set objRS = objConn.Execute strSQL
: If not objRS.EOF then
:     arrResults = objRS.GetRows
: End If
: objRS.Close
: Set objRS = Nothing
: %>
:
: which would return a single recordset which would look like this:
:
: LoanCloseDteMo    NumRecs
:     1                            12
:     2                              5
:     3                              6
:     4                              2
:
: Cheers
: Ken
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: "Evan Kezsbom" <ekez99@y...>
: Subject: [access_asp] ERROR: Cannot open any more tables
:
:
: : I'm recieving the following error:
: :
: : Microsoft OLE DB Provider for ODBC Drivers error '8007000e'
: : [Microsoft][ODBC Microsoft Access Driver] Cannot open any more tables.
: :
: : Though I'm looping through a fair amount of data, I'm closing and
killing
: : the recordset object each time...?
: :
: : Any ideas?
: :
: : Thanks in advance.
: :
: : Code snippet:
: :
: : ------------------------------------------
: :
: : <%
: : 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 Underwriter.*" & _
: : " FROM Client, Loan, Underwriter" & _
: : " WHERE Loan.UndID = "  & UndID & _
: : " AND Loan.UndID = Underwriter.UndID" & _
: : " AND Loan.CliID = " & CliID & _
: : " AND Client.CliID = " & CliIDFile & _
: : " AND Underwriter.CliID = Loan.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
: : LoanTypeCountAdvanceZ = rs1.RecordCount
: : Else
: : LoanTypeCountAdvanceZ = 0
: : End If
: : rs1.close
: : set rs1 = nothing
: :
: : sql = "SELECT Distinct LoanException.LExID" & _
: : " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: : LoanException.LoanID" & _
: : " WHERE LoanException.LoanID = Loan.LoanID" & _
: : " AND LoanException.LExEmpID = "  & UndID & _
: : " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _
: : " AND LoanException.LExType = '"&"Minor"&"'"  & _
: : " AND Loan.DisplayFlg = " & TRUE & _
: : " 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
: : AdvanceMoMinorCountZ = rs1.RecordCount
: : Else
: : AdvanceMoMinorCountZ = 0
: : End If
: :
: : If (AdvanceMoMinorCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
: : AdvanceMoMinorAvgZ = AdvanceMoMinorCountZ/LoanTypeCountAdvanceZ
: : AdvanceMoMinorAvgZ = round(AdvanceMoMinorAvgZ,2)
: : Else
: : AdvanceMoMinorAvgZ = 0
: : End If
: : rs1.close
: : set rs1 = nothing
: :
: : set rs1 = Server.CreateObject("ADODB.RecordSet")
: : sql = "SELECT Distinct LoanException.LExID" & _
: : " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: : LoanException.LoanID" & _
: : " WHERE LoanException.LoanID = Loan.LoanID" & _
: : " AND LoanException.LExEmpID = "  & UndID & _
: : " AND LoanException.LExEmpType = '"& "Underwriter" & "'" & _
: : " AND LoanException.LExType = '"&"Important"&"'"  & _
: : " AND Loan.DisplayFlg = " & TRUE & _
: : " 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
: : AdvanceMoImportantCountZ = rs1.RecordCount
: : Else
: : AdvanceMoImportantCountZ = 0
: : End If
: : If (AdvanceMoImportantCountZ > 0)  AND (LoanTypeCountAdvanceZ > 0) Then
: : AdvanceMoImportantAvgZ 
: : AdvanceMoImportantCountZ/LoanTypeCountAdvanceZ
: : AdvanceMoImportantAvgZ = round(AdvanceMoImportantAvgZ,2)
: : Else
: : AdvanceMoImportantAvgZ = 0
: : End If
: : rs1.close
: : set rs1 = nothing
: :
: :
: : set rs1 = Server.CreateObject("ADODB.RecordSet")
: : sql = "SELECT Distinct LoanException.LExID" & _
: : " FROM Loan INNER JOIN LoanException ON Loan.LoanID 
: : LoanException.LoanID" & _
: : " WHERE LoanException.LoanID = Loan.LoanID" & _
: : " AND LoanException.LExEmpID = "  & UndID & _
: : " AND LoanException.LExEmpType = '"& "Underwriter" & "'"
: : & _
: : " AND LoanException.LExType = '"&"Serious"&"'"  & _
: : " AND Loan.DisplayFlg = " & TRUE & _
: : " 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
: : AdvanceMoSeriousCountZ = rs1.RecordCount
: : Else
: : AdvanceMoSeriousCountZ = 0
: : End If
: : If (AdvanceMoSeriousCountZ > 0) AND (LoanTypeCountAdvanceZ > 0) Then
: : AdvanceMoSeriousAvgZ = AdvanceMoSeriousCountZ/LoanTypeCountAdvanceZ
: : AdvanceMoSeriousAvgZ = round(AdvanceMoSeriousAvgZ,2)
: : Else
: : AdvanceMoSeriousAvgZ = 0
: : End If
: : rs1.close
: : set rs1 = nothing
: :
: : %>
: : <td valign="top" class="news" align="left">
: : <%= AdvanceMoMinorAvgZ %><br>
: : <%= AdvanceMoImportantAvgZ %><br>
: : <%= AdvanceMoSeriousAvgZ %>
: : </td>
: : <%
: :
: :
: : 'If viewByMoValNext >= 13 Then
: : 'viewByMoValNext = 1
: : 'Else
: : viewByMoValNext = viewByMoValNext - 1
: : 'End If
: : MoCount = MoCount + 1
: :
: :
: : loop
: : %>
: :
: : ------------------------------------------
:

Return to Index

P2P Archives