Collection must be a global object available from any procedure so as to reference its elements. In which case can manipulate it from anywhere to add and remove elements.
[a little OT]
Database.Recordsets works only with the Database instance that was used to create the recordsets.
Code:
Private Sub TestDatabaseRecordsets()
Dim dbA As DAO.Database
Dim dbB As DAO.Database
Dim rs(1 To 3) As DAO.Recordset
Dim i As Long
Set dbA = CurrentDb
Set dbB = CurrentDb ' new instance!
Set rs(1) = dbA.OpenRecordset("select * from TestTable1")
Set rs(2) = dbA.OpenRecordset("select * from TestTable2")
Set rs(3) = dbB.OpenRecordset("select * from TestTable1 where 1=0")
Debug.Print "dbA", dbA.Recordsets.Count, dbA.Recordsets(0).Name, dbA.Recordsets("select * from TestTable2").Name
' dbA.Recordsets("select * from TestTable2") ... call by name as key value
Debug.Print "dbB", dbB.Recordsets.Count, dbB.Recordsets(0).Name
Debug.Print "CurrentDb", CurrentDb.Recordsets.Count
Debug.Print "DBEngine", DBEngine(0)(0).Recordsets.Count
For i = 1 To 3
rs(i).Close
Next
Debug.Print "db(rs closed)", dbA.Recordsets.Count, dbB.Recordsets.Count
End Sub
output:
Code:
dbA 2 select * from TestTable1 select * from TestTable2
dbB 1 select * from TestTable1 where 1=0
CurrentDb 0
DBEngine 0
db(rs closed) 0 0
[back to topic]
I suspect that using an array instead of the many Recordset variables might be a suitable variant for the OP.
replace:
Code:
Public rs_Char1 As Recordset
Public rs_Char2 As Recordset
...
Public rs_Char12 As Recordset
with
Code:
Pubic rs_CharArray(1 to 12) as DAO.Recordset
(I find the public declaration questionable.)
But without knowing the intended use, I'm not going to keep guessing.
Riddle for 'Char' in rs_Char1 - rs_Char12: What could be a character recordset?
I'll take a look at this.
My initial hope was to manage the recordsets before being Set by reference from a string.
Apparently that aspect isn't possible.
But still, the post-Set recordset actions can be managed by use of the Collection or Array options.