I have an Access database containing three tables: Projects, Exercises, 
POCs. I used the Access wizard to create a join between Projects and POCs.
 
I am using the following to declare, open a connection / record set. Then 
I use the sql string from the wizard that I assing to a variable and I 
open the connection:
 
' Connection/Recordset module retrives records from Projects table
Set MyDB = Server.CreateObject("ADODB.Connection")
Set MyRS = Server.CreateObject("ADODB.Recordset")
 
Dim dbPath
dbPath = Server.MapPath("../Databases/Project_Exercise_042602.mdb")
MyDB.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
 
' Build our query based on the input.
 MySQL = "SELECT Projects.Name, Projects.Description, Projects.Time_Line, 
Projects.Time_Frame, " _
       & "Projects.Participants, Projects.Spike, Projects.POR, 
Projects.StatusReviewName, " _
       & "Projects.StatusReviewFile, Projects.DesignReviewName, 
Projects.DesignReviewFile, " _
       & "Projects.QuadChartName, Projects.QuadChartFile, POSs.Titile, 
POSs.Fname, POSs.Lname, " _
       & "POSs.Dept, POSs.Organization, POSs.Phone, POSs.UC_Email, 
POSs.C_Email, POSs.POCFlag, " _
       & "Projects.ID " _
       & "FROM Projects LEFT JOIN POSs ON Projects.POC_Id = POSs.POC_Id " _
       & "WHERE (((Projects.StatusReviewName)<>'False')) OR 
(((Projects.StatusReviewFile)<>'False')) " _
       & "OR (((Projects.DesignReviewName)<>'False')) OR 
(((Projects.DesignReviewFile)<>'False')) OR " _
       & "(((Projects.QuadChartName)<>'False')) OR 
(((Projects.QuadChartFile) Is Not Null));"
 
MyRS.CursorLocation = 3
MyRS.Open MySQL, MyDB
 
Next I repeat the previous code but for the Exercise table except that 
the 'where' clause contains the condition, value of the record id from the 
above record set:
' Connection/Recordset module retrives records from Exercises table
Set MyDB1 = Server.CreateObject("ADODB.Connection")
Set MyRS1 = Server.CreateObject("ADODB.Recordset")
 
Dim dbPath1
dbPath1 = Server.MapPath("../Databases/Project_Exercise_042602.mdb")
MyDB1.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath1
 
' Build our query based on the input.
 MySQL1 = "SELECT Exercises.Event, Exercises.ID, Exercises.Project_Id " _
        & "FROM Exercises " _
     & "WHERE Exercises.Project_Id = " & MyRS("ID") & ";"
 
MyRS1.CursorLocation = 3
MyRS1.Open MySQL1, MyDB1

 
Loop cycles through the recordset, finds the first record displays the 
record Name. Then it reads the next piece of vb code and cycles through 
the first record of the second record set and if the Exercises 
name "Event" is present it will display is as many times as there are 
corresponding Events present, associated through a foreign key in the 
table.
 
<%
For intRecord = 1 To MyRS.RecordCount 
    MyRexCount=MyRexCount+1
        
%>
      <tr valign=top bgcolor=White>     
          <td class=SearchParam><font class=Data width="70"><%=MyRS
("Name")%></font></td>
         <td><font class=Data width="100">
 
<% 
        If MyRS1("Event") <> "" OR NOT ISNULL(MyRS1("Event")) Then
            For intRecord1 = 1 To MyRS1.RecordCount 
                Response.Write MyRS1("Event") & "<br><br>"
                MyRS1.MoveNext
                if MyRS1.EOF then 
                    Exit For 
                End if
            Next 
        Else
            Response.Write "&nbsp;"  'Blank space
        End If %>
     
     </font></td>
          <td><font class=Data width="100"><%= MyRS("Time_Line") %
></font></td>
          <td><font class=Data width="100"><%= MyRS("Time_Frame") %
></font></td>
          <td><font class=Data width="100"><%= MyRS("Description" ) %
></font></td>
          <td><font class=Data width="60"><%= MyRS("Participants") %
></font></td>
          <td><font class=Data width="70"><%= MyRS("Spike") %></font></td>
          <td><font class=Data width="70"><%= MyRS("POR") %></font></td>
    </tr><%
    MyRS.MoveNext
    if MyRS.EOF then 
         Exit For 
    End if
Next
 
 
 
 
It works the first time around but on the second cycle I get a error as 
follows:
The line 163 is the line location of the second 'for' loop.

error '80020009' 
Exception occurred. 

/exercise_database/Projects/PrBriefs.asp, line 163 

 

Is the error occuring due to the multiple recordsets or connections. The 
logic seem fine, I mean, its quite rudimentary, yet it crashes.

Can you help in any way? 
Thank you.

Eric

This sounds like an insane way of doing things.

Databases work best when you use data in sets, not in cursors.
Return just the one dataset (eg with a join), and cut out all the nesting
looping constructs...

To be honest though I can't see what the problem is - I don't see what the
relationship is between the recordsets/tables that you have, nor can I
fathom what the point is of all this code.

However, I *suspect* that you're creating recordset's inside some kind of
loop - don't! Return just the one recordset using a JOIN between the tables.

Secondly, make sure you are disposing of all your objects when you're doing:

objRS.Close
Set objRS = Nothing

objConn.Close
Set objConn = nothing

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "eric mkuska" <risho@c...>
Subject: [access_asp] Exception Occurred


: I have an Access database containing three tables: Projects, Exercises,
: POCs. I used the Access wizard to create a join between Projects and POCs.
:
: I am using the following to declare, open a connection / record set. Then
: I use the sql string from the wizard that I assing to a variable and I
: open the connection:
:
: ' Connection/Recordset module retrives records from Projects table
: Set MyDB = Server.CreateObject("ADODB.Connection")
: Set MyRS = Server.CreateObject("ADODB.Recordset")
:
: Dim dbPath
: dbPath = Server.MapPath("../Databases/Project_Exercise_042602.mdb")
: MyDB.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
:
: ' Build our query based on the input.
:  MySQL = "SELECT Projects.Name, Projects.Description, Projects.Time_Line,
: Projects.Time_Frame, " _
:        & "Projects.Participants, Projects.Spike, Projects.POR,
: Projects.StatusReviewName, " _
:        & "Projects.StatusReviewFile, Projects.DesignReviewName,
: Projects.DesignReviewFile, " _
:        & "Projects.QuadChartName, Projects.QuadChartFile, POSs.Titile,
: POSs.Fname, POSs.Lname, " _
:        & "POSs.Dept, POSs.Organization, POSs.Phone, POSs.UC_Email,
: POSs.C_Email, POSs.POCFlag, " _
:        & "Projects.ID " _
:        & "FROM Projects LEFT JOIN POSs ON Projects.POC_Id = POSs.POC_Id "
_
:        & "WHERE (((Projects.StatusReviewName)<>'False')) OR
: (((Projects.StatusReviewFile)<>'False')) " _
:        & "OR (((Projects.DesignReviewName)<>'False')) OR
: (((Projects.DesignReviewFile)<>'False')) OR " _
:        & "(((Projects.QuadChartName)<>'False')) OR
: (((Projects.QuadChartFile) Is Not Null));"
:
: MyRS.CursorLocation = 3
: MyRS.Open MySQL, MyDB
:
: Next I repeat the previous code but for the Exercise table except that
: the 'where' clause contains the condition, value of the record id from the
: above record set:
: ' Connection/Recordset module retrives records from Exercises table
: Set MyDB1 = Server.CreateObject("ADODB.Connection")
: Set MyRS1 = Server.CreateObject("ADODB.Recordset")
:
: Dim dbPath1
: dbPath1 = Server.MapPath("../Databases/Project_Exercise_042602.mdb")
: MyDB1.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath1
:
: ' Build our query based on the input.
:  MySQL1 = "SELECT Exercises.Event, Exercises.ID, Exercises.Project_Id " _
:         & "FROM Exercises " _
:      & "WHERE Exercises.Project_Id = " & MyRS("ID") & ";"
:
: MyRS1.CursorLocation = 3
: MyRS1.Open MySQL1, MyDB1
:
:
: Loop cycles through the recordset, finds the first record displays the
: record Name. Then it reads the next piece of vb code and cycles through
: the first record of the second record set and if the Exercises
: name "Event" is present it will display is as many times as there are
: corresponding Events present, associated through a foreign key in the
: table.
:
: <%
: For intRecord = 1 To MyRS.RecordCount
:     MyRexCount=MyRexCount+1
:
: %>
:       <tr valign=top bgcolor=White>
:           <td class=SearchParam><font class=Data width="70"><%=MyRS
: ("Name")%></font></td>
:          <td><font class=Data width="100">
:
: <%
:         If MyRS1("Event") <> "" OR NOT ISNULL(MyRS1("Event")) Then
:             For intRecord1 = 1 To MyRS1.RecordCount
:                 Response.Write MyRS1("Event") & "<br><br>"
:                 MyRS1.MoveNext
:                 if MyRS1.EOF then
:                     Exit For
:                 End if
:             Next
:         Else
:             Response.Write "&nbsp;"  'Blank space
:         End If %>
:
:      </font></td>
:           <td><font class=Data width="100"><%= MyRS("Time_Line") %
: ></font></td>
:           <td><font class=Data width="100"><%= MyRS("Time_Frame") %
: ></font></td>
:           <td><font class=Data width="100"><%= MyRS("Description" ) %
: ></font></td>
:           <td><font class=Data width="60"><%= MyRS("Participants") %
: ></font></td>
:           <td><font class=Data width="70"><%= MyRS("Spike") %></font></td>
:           <td><font class=Data width="70"><%= MyRS("POR") %></font></td>
:     </tr><%
:     MyRS.MoveNext
:     if MyRS.EOF then
:          Exit For
:     End if
: Next
:
:
:
:
: It works the first time around but on the second cycle I get a error as
: follows:
: The line 163 is the line location of the second 'for' loop.
:
: error '80020009'
: Exception occurred.
:
: /exercise_database/Projects/PrBriefs.asp, line 163
:
:
:
: Is the error occuring due to the multiple recordsets or connections. The
: logic seem fine, I mean, its quite rudimentary, yet it crashes.
:
: Can you help in any way?
: Thank you.
:
: Eric