Use a string to refer to a defined recordset object

[Bilbo_Baggins_Esq]

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.

I use "Public" so yeah, got that covered.
Thanks.

 

[Bilbo_Baggins_Esq]

[a little OT]
Database.Recordsets works only with the Database instance that was used to create the recordsets.

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:

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:

Public rs_Char1 As Recordset
Public rs_Char2 As Recordset
...
Public rs_Char12 As Recordset

with

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?

Thanks!

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.

Thanks again!

 

[Bilbo_Baggins_Esq]

As an FYI, to the MVPs and Mods, the responses to this ask have been very informative and helpful.

Though I apparently can't do 100% of what I'd have liked to do, the insights offered are still very helpful and accomplish 90% of my goal.

My suggestion would be that if someone seeks this in the future (seemingly rare anyway) this thread might be a useful resource to reference.

 

[Josef P.]

My initial hope was to manage the recordsets before being Set by reference from a string.

Can you show this on a symbolic code (doesn't have to work).

Apparently that aspect isn't possible.

Never say never. Whether there are other options depends on your plan, which seems to be top secret.