Use a string to refer to a defined recordset object (1 Viewer)

[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.

 

[dalski]

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

I'm trying to do the exact same thing. Was very clear in post-01 what you were after; dynamically creating a recordset, surprised at the confusion.

 

[MajP]

I'm trying to do the exact same thing. Was very clear in post-01 what you were after; dynamically creating a recordset, surprised at the confusion.

I am glad it was clear to someone because like everyone else on this thread I have no idea what the OP is asking and why they would feel a need to do this.

If for some bizarre reason I had to do it, I would use a dictionary instead of a collection because then you can check if the User defined name exists. With a collection you can return it by a named index, but you cannot check if that index exists.

Public MyRecordsets As New Dictionary
'need reference to microsoft scripting runtime
'This allow you to add, find, and determine if that index exists

Private Sub LoadMyRecordset()
  'Demo uses the employee tables with titles Mr. Ms. Dr.
  'rs_MS is all Misses
  'rs_MR is all Misters
  'rs_DR is all Doctors
  'in a dictionary, .add "Key", item
 
  'add recordsets to the dictionary and provided a unique "name" / index
  If Not MyRecordsets.Exists("RS_MS") Then
    MyRecordsets.Add "RS_MS", CurrentDb.OpenRecordset("Select * from Employees where TitleOfCourtesy = 'MS.'")
  End If
 
  If Not MyRecordsets.Exists("RS_MR") Then
    MyRecordsets.Add "RS_Mr", CurrentDb.OpenRecordset("Select * from Employees where TitleOfCourtesy = 'MR.'")
  End If
 
  If Not MyRecordsets.Exists("RS_DR") Then
   MyRecordsets.Add "RS_Dr", CurrentDb.OpenRecordset("Select * from Employees where TitleOfCourtesy = 'DR.'")
  End If

End Sub

'Return the recordset by its user defined named  index
'The name being printed is the actual recordset name which is the SQL of that recordset

Public Sub TestByIndex()
  Debug.Print MyRecordsets("RS_MS").Name
  Debug.Print MyRecordsets("RS_MR").Name
  Debug.Print MyRecordsets("RS_DR").Name
End Sub

 

[dalski]

like everyone else on this thread I have no idea what the OP is asking and why they would feel a need to do this.

Thanks Pete, not to hijack the thread & simplifying but in the interest of publicly shaming myself - I read it as being able to use a var to refer to a recordset - imagine you have several similar recordsets:
r1
r2
r3...

You want to save space in your code by avoiding huge blocks everywhere. So you geniusly loop & create these dynamically (declarations have to be declared statically obviously).

dim rs1 as DAO.Recordset
rs2...
    For i = 1 To 3
        strPlaceholder = "r" & i
        strSQLtxt = "SELECT...ForeignKey=" & i
        Set strPlaceholder = strSQLtxt
    Next i

Trouble is VBA thinks you're wanting to assign the strSQLtxt variable. But user wanted to initialize the rs; DYNAMICALLY; not having to hardcode each rs.

 

[The_Doc_Man]

I took the original question as looking for an analogous addressing method to recordset.Fields("fieldname") which would return a FieldDef object. OP wants to find something that (if it existed) would be like X.Recordsets("somename") that would return a Recordset object.

The barrier here is that the name of the recordset isn't necessarily the same as the name of the object variable that points to it, and we don't have anything like a modulename.Variables("name") or Me.Variables("name") construct for code to self-examine the names of variables in modules. (At least, I don't THINK we do.)

I'm not even sure that a recordset HAS a unique name in any formal sense. IF it is a DAO.Recordset, then its .Name property is kind of ugly. I got this from the MS Learn site:

"The Name property of a Recordset object opened by using an SQL statement is the first 256 characters of the SQL statement."

It doesn't say what it does when you open a recordset based on a named query or table, but what it does for SQL statements is enough to suggest that the name of a recordset doesn't have to be unique, since you can open TWO recordsets based on the same SQL string.

MajP's suggestion to use a Dictionary object to hold recordsets is probably the closest answer... though you still need to supply a key (name) for each object. Using the recordset object's variable name would probably suffice. However, you are still trying to cross a metadata barrier because of the syntax and semantic differences between a reference (name) and a literal value ("name").

This question appears in every compiled or pseudo-compiled language: Is the textual name of an object the same as the object itself? The answer is usually "NO" because there is usually no simple way to change a name that has compile-time meaning to something that has run-time meaning. The presence of VBA Colletions is exceptional in that regard in that it lets you pick object properties by quoted name rather than by in-line name.

This perilously approaches the domain of self-referential code. In general, self-referential code is extremely difficult to manage and it is possible to crash programs this way. Having the ability to reference a variable/object by its variable name using a text string to name it requires that you have access to the compilation symbol table. Which isn't normally the case for VBA.

Note that if you had a scripting language that was interpretive, you could get closer to a direct solution here. But VBA is at least pseudo-compiled and thus maintains a compile-time/run-time barrier.

 

[MajP]

Still no idea why that would be useful, but fully doable as previously demonstrated.
I dynamically create a recordset for each employee

Public MyRecordsets As New Dictionary
'need reference to microsoft scripting runtime
'This allow you to add and find a recordset with a user defined name

Private Sub LoadMyRecordset()
  Dim i As Integer
  For i = 1 To DCount("*", "Employees")
    MyRecordsets.Add "R" & i, CurrentDb.OpenRecordset("Select * from Employees where EmployeeID = " & i)
  Next i
End Sub

Now you can call the recordset by your user defined name as shown by the test

Public Sub TestByIndex()
  Dim key As Variant
  For Each key In MyRecordsets.Keys
    Debug.Print "Recordset Name/Index: (" & key & ")" & "      " & MyRecordsets(key).LastName & ", " & MyRecordsets(key).firstname
  Next key
End Sub

Recordset Name/Index: (R1)      Davolio, Nancy
Recordset Name/Index: (R2)      Fuller, Andrew
Recordset Name/Index: (R3)      Leverling, Janet
Recordset Name/Index: (R4)      Peacock, Margaret
Recordset Name/Index: (R5)      Buchanan, Steven
Recordset Name/Index: (R6)      Suyama, Michael
Recordset Name/Index: (R7)      King, Robert
Recordset Name/Index: (R8)      Callahan, Laura
Recordset Name/Index: (R9)      Dodsworth, Anne

Also, this thread is 2 years old so you cannot really "hijack" it.