Insert info from one table into another table ASP & Access

Ok, I have found various posts about inserting data from one table to another but still cannot figure out my coding issues. I have two tables in one database. tblinfo & tblresources. I have an ASP search that goes through the resources table and displays any matches when queried. When the matches are displayed I have a loop that displays [update Delete insert into HTML] buttons. Update and Delete work great, my issue is when clicking [insert into HTML] I want to take the data from whichever match I select using its ID_no and insert it into tblresources. I keep getting errors that read, "Operation is not allowed when the object is open. " I am not sure if I need 2 recordsets or only one. The code below is with 2 recordsets, I dont know if I can even do that :confused:

Any help or wisdom is greatly appreciated.

<%

'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim rsNewComments
Dim strSQL 'Holds the SQL query for the database
Dim lngRecordNo 'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ID"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("html_resources.mdb")

'Set an active connection to the Connection object using DSN connection
adoCon.Open "DSN=Addcomments"

'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

strSQL = "INSERT INTO tblresources (Organization, Description, url) SELECT Institution, description, url FROM info WHERE id_no=" &

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
rsAddComments.AddNew

'Add a new record to the recordset
rsAddComments.Fields("Organization") = rsNewComments.Fields("Institution")
rsAddComments.Fields("Description") = rsNewComments.Fields("description")
rsAddComments.Fields("url") = rsNewComments.Fields("url")

'Write the updated recordset to the database
rsAddComments.Update

'Reset server objects
'adocon.Close
Set adoCon = Nothing
rsAddComments.Close
Set rsAddComments = Nothing



'Redirect to the Terms.asp page
Response.Redirect "Thankyou2.html"
%>

Well, you're telling your connection object to open one connection string, a DSN-less one, then you're telling it to open a DSN.

Connection objects can only open one connection at a time. They can be DSN's or DSN-less, but you can't tell it to open two of them. That's where the error you quoted is probably coming from.

The "two recordset approach" - I see only one?

When I click my link to initiate the ASP page I get this error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/ATP_TEST/resources/add_project.asp, line 38

Oddly enough I went to check the database and found that even though I am receving this error the information is getting inserted into the database the way I need it to be. I know this error means that the database is missing the item I am trying to refer to, but I have checked and triple checked my spelling and syntax with regards to the table and data. Any ideas?

This is the code that does work but gives me the afore mentioned error:

<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added to the database
Dim strSQL 'Holds the SQL query for the database
Dim lngRecordNo 'Holds the record number to be updated

'Read in the record number to be updated
lngRecordNo = CLng(Request.QueryString("ID"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("html_resources.mdb")

'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

'strSQL = INSERT INTO tblresources SELECT * FROM info WHERE id_no= & lngRecordNo

strSQL = "INSERT INTO tblresources (institution, description, url) SELECT institution, description, url FROM info WHERE id_no=" &

lngRecordNo

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
rsAddComments.Open strSQL, adoCon

'Tell the recordset we are adding a new record to it
'rsAddComments.Update

'Add a new record to the recordset
rsAddComments.Fields("institution") = rsAddComments.Fields("institution")
rsAddComments.Fields("description") = rsAddComments.Fields("description")
rsAddComments.Fields("url") = rsAddComments.Fields("url")

'Write the updated recordset to the database
rsAddComments.Update

'Reset server objects
'adocon.Close
Set adoCon = Nothing
rsAddComments.Close
Set rsAddComments = Nothing

'Redirect to the Terms.asp page
Response.Redirect "Thankyou2.html"
%>