Refer to a Recordset Variable by name

[Galaxiom]

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 recordsets based on an argument.

Clearly, because it comes from a query I can't pass the object reference directly as one would when calling a function from VBA so I must pass the recordset name.

How do I convert that name to a recordset reference inside the function?

If it was DAO I could use the Recordsets collection. I guess I could do the same for the ADO Recordset by adding it to a Collection with its name as the Index when it is created.

However it seems like there should be some straightforward way to refer to the variable by its name. Something like CallByName() but for variables instead of Class Properties and Methods.

 

[VilaRestal]

Could you use the Eval function

Let's say your main function is

FuncMain(ByRef rs As ADODB.Recordset)

Use Eval("FuncMain(" & RecordsetName & ")")

I've not tried so I'm not sure.

 

[Galaxiom]

Thanks for the suggestion but no. It is kind of the opposite that would be needed. The reference to the function in the datbase is fine, it is the reference to the recordset variable that can't be seen by the database.

It could probably be done with TempVars but I prefer to stick with the mdb format.

The Collection seems to be the easiest way to go. It works anyway.

The other way is to expose the recordset as a property of a Class object and Get it with CallByName(). It just doesn't work in a Standard Module. It could just be set up as a Public property of a form.

However it probably would not have been a bad idea to encapsulate the whole thing in a Class. (You know, the separation of the presentation and the business logic.) But it was one of those projects that started out just to parse a text report and display it. Feature creep has set in big time though and I probably should restructure it anyway.

Whichever way passing the name to get the variable isn't that it is complex to do but it just seems there should be something like a Variables Collection in the Application Object of a function to Get them.

 

[VilaRestal]

I meant to use Eval in the query.

You might have to wrap Eval in your own function to allow a query to use it.

You could then refer to the recordset by name as well as the function by name:


e.g.

SELECT Eval('ProcessRecordset(rs1)') FROM Table1 WHERE ...

and of course 'rs1' could passed as a parameter to the query:

SELECT Eval('ProcessRecordset(' & @Recordset & ')') FROM Table1 WHERE ...


If that's not what you're trying to do then you've lost me.

Edit: My attempts to use Eval in a query failed but I could use Eval2:

Public Function Eval2(ByVal str As String) As Variant
    Eval2 = Eval(str)
End Function

 

[Galaxiom]

Many thanks for running it by your thoughts. I really wanted to confirm that I wasn't missing something.

I had tried every way I could come up with using Eval but I hadn't thought of cascading Eval expressions. Creative yes, but sadly that doesn't work either.

The fundamental issue is that Eval cannot return a reference to a variable from the variable's name no matter how it is couched. Eval only return strings and numbers from an expression so it is able to take a variable, object reference or a function as a string and return the resulting value.

There just doesn't seem to be a function to return a reference to a variable from the name for a variable held in a Standard Module. The type of variable makes no difference. It seems it can't even be done for a string or numeric variable. All you can get is the value.

It can be done for Public variables in a Class object because these are exposed as a pseudo-property of the class object. Simply Set or Let another variable to that "property".

Declaring the variable as Public in a form module is probably the easiest way to deal with it. Otherwise use the Collection if the variable is in a Standard Module because it needs to be Global.

 

[VilaRestal]

I see.

That's probably for the best. It would be very easy to get one or more VB errors for every record in the query when using Eval in it.