Use a string to refer to a defined recordset object

[Bilbo_Baggins_Esq]

Is there I can use a built string to refer to an existing recordset object?

For example, I build a string "rs_Char4"
I need to be able to associate that string with an existing recordset object (.OpenRecordset, .MoveNext, .Close, etc)

I've built a function which has a parameter "rsToManage as Recordset"
But I can't seem to use my built strings for that parameter when I call that function.
The function works FINE when I manually type the defined recordset's name.
But I need to have a way to use the strings.

I thought about building an extra function which evaluates as a recordset, with a long select case statement to read the strings and pass a recordset as the function's result.

But I can't help but think there's got to be a more elegant solution.

 

[Minty]

Have a read of this thread

Refer to a Recordset Variable by name

How can I to refer to an existing ADO Recordset object variable in a function, given its name as a string? I have a custom function called from a query. That function includes writing data to an existing fabricated ADO Recordset. Now I want to generalise the function to write to different...

www.access-programmers.co.uk

 

[Bilbo_Baggins_Esq]

Have a read of this thread

Refer to a Recordset Variable by name

How can I to refer to an existing ADO Recordset object variable in a function, given its name as a string? I have a custom function called from a query. That function includes writing data to an existing fabricated ADO Recordset. Now I want to generalise the function to write to different...

www.access-programmers.co.uk

Thanks but no, Eval doesn't work for this.
Eval only returns strings or numbers, not objects.

To clarify (sometimes I suck at explaining)

I have several recordsets declared as public: rs_Char1, rs_Char2, rsChar3, etc.
I have a counter that runs 1 through 12.

I obviously can easily build the strings but I need to be able to efficiently connect that string value to the recordset object so I can take actions with the recordset(s).

 

[June7]

Can use string variable to refer to form and report objects by referencing with collection. Example: Forms(strForm). AFAIK, there is not a Recordsets collection. I just tested.

 

[Josef P.]

You can store recordset references in a collection or dictionary and assign a name to it.
Maybe an array is enough.

But why do you want something like that?

 

[Bilbo_Baggins_Esq]

You can store recordset references in a collection and assign a name to it.
But why do you want something like that?

How do I build a recordset collection?

 

[Josef P.]

Dim RsCollection as Collection
set RsCollection = new Collection
RsCollection.Add YourRecordsetReference, YourKey

Call:
set rs = RsCollection(YourKey)

Array:

dim RsArray(1 to 12) as DAO.Recordset
dim i as long
set rs(1) = db.Openrecordset(...)
...
for i = 1 to 12
    debug.print RsArray(i).RecordCount
next

 

[June7]

A user-defined collection, review https://learn.microsoft.com/en-us/o...ference/user-interface-help/collection-object and https://analystcave.com/vba-collection/

 

[Bilbo_Baggins_Esq]

Dim RsCollection as Collection
set RsCollection = new Collection
RsCollection.Add YourRecordsetReference, YourKey

Call:
set rs = RsCollection(YourKey)

I assume "YourKey" would be the string (or number) by which I want to call it?

 

[Josef P.]

I assume "YourKey" would be the string (or number) by which I want to call it?

Key = a string ... not a number.
You can also retrieve a collection entry via the index.

RsCollection.Add YourRecordsetReference  ' possible without key
...

Call:
set rs = RsCollection(1)
' or
for each rs in RsCollection
..
next

Collection object

Office VBA reference topic

learn.microsoft.com

 

[June7]

I tested and the Call: line causes compile error. I removed it.

 

[Bilbo_Baggins_Esq]

Key = a string ... not a number.
You can also retrieve a collection entry via the index.

RsCollection.Add YourRecordsetReference  ' possible without key
...

Call:
set rs = RsCollection(1)
' or
for each rs in RsCollection
..
next

Collection object

Office VBA reference topic

learn.microsoft.com

Hmmm, I can't seem to get this to work

Public obj_Acc_WS As Workspace
Public obj_Acc_DB As Database
Public rs_Char1 As Recordset
Public rs_Char2 As Recordset
Public rs_Char3 As Recordset
Public rs_Char4 As Recordset
Public rs_Char5 As Recordset
Public rs_Char6 As Recordset
Public rs_Char7 As Recordset
Public rs_Char8 As Recordset
Public rs_Char9 As Recordset
Public rs_Char10 As Recordset
Public rs_Char11 As Recordset
Public rs_Char12 As Recordset
Public rsCollection as Collection
Public str_RS as string

Sub TestingRsCollection()

Set obj_Acc_WS = DBEngine.Workspaces(0)
Set obj_Acc_DB = obj_Acc_WS.Databases(0)

Set rsCollection = New Collection

rsCollection.Add rs_Char1, "rs_Char1"
rsCollection.Add rs_Char2, "rs_Char2"
rsCollection.Add rs_Char3, "rs_Char3"
rsCollection.Add rs_Char4, "rs_Char4"
rsCollection.Add rs_Char5, "rs_Char5"
rsCollection.Add rs_Char6, "rs_Char6"
rsCollection.Add rs_Char7, "rs_Char7"
rsCollection.Add rs_Char8, "rs_Char8"
rsCollection.Add rs_Char9, "rs_Char9"
rsCollection.Add rs_Char10, "rs_Char10"
rsCollection.Add rs_Char11, "rs_Char11"
rsCollection.Add rs_Char12, "rs_Char12"

Debug.Print rsCollection.Count 'returns 12 correctly

str_RS = "rs_Char12"

'this below line throws "Object variable or With block variable not set" error.
Set rsCollection.Item(str_RS) = obj_Acc_DB.OpenRecordset("Files", dbOpenDynaset)

End Sub

 

[June7]

AFAIK, have to Add a recordset object that was already declared and set, not Add empty recordset object and then Set it.

 

[Bilbo_Baggins_Esq]

AFAIK, have to Add a recordset object that was already declared and set, not Add empty recordset object and then Set it.

Yeah, I seem to have just figured that out as you were typing the above.

I've decided to go ahead and open all 12 recordsets up front, then add them to the collection.
I may be able to figure out a process to add the recordsets to the collection (once declared and set) but I need to clear my head for now.

THANKS!

 

[June7]

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.

 

[Josef P.]

If you briefly describe what you actually want to do, we might find a suitable approach together.
Maybe you want to manage these recordsets in a class.

 

[The_Doc_Man]

This SOUNDS like you are trying to index a group of open recordsets by name to perform a type of "pointer" logic. There is already a Recordsets collection that occurs naturally as Workspace(n1).Database(n2).Recordsets(n3) but I don't think you can index that collection by name. On the other hand, you surely could try it. NOTE: ONLY returns already-opened recordsets. Doesn't know and doesn't care about closed ones.

Database.Recordsets property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[June7]

Okay, learned something new. There is a Recordsets collection but have to use database object prefix. I tried some code but can't get it to work.
Can't figure out Workspace syntax.

CurrentDb.Recordsets shows up in intellisense but when I try to reference collection item I get "does not exist" error.

 

[The_Doc_Man]

For what it is worth, CurrentDB provides a "snapshot" of Workspace(0).Databases(0) - and you might have had a problem referencing it if you went through Workspace(0) because there the reference is actually Databases ( 0 ) ... a PLURAL whereas I inadvertently typed it as though it were singular. In that context, it is not. My bad. (But it IS late here in my time zone. Anything I type after midnight local time should be suspect.)

Not going to swear to it, but I think this collection (which is only the OPEN recordsets) could be used to help in a catastrophic cleanup error trap handler that was going to "gracefully" shut down a database.

 

[Josef P.]

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