Nor sure what the problem is:

If you want recordsets (DQL subset of SQL) only, use recordset objects only:

set RS1 = CreateObject("ADODB.Recordset")
RS1.Open "SELECT * FROM MYTABLE", "MYConnectionString"
RS1.Open "SELECT * FROM MYTABLE2", "MYConnectionString"

i.e you do not need a connection object to open a recordset, just a
connection string.

IF you want to use a connection object:

SET CN = CreteObject("ADODB.Connection")
CN.Open "MyconnectionString")

SET RS = CreteObject("ADODB.RecordSet")
RS1.Open "your SQL statement", CN
RS2.Open "your SQL statement", CN

With the latter approach, you have 2 objects to manage.