Clever Geek Handbook

Declare new name DAO.Recordset (dynamic)

Here's a typical example for creating a DAO Recordset:

Private Sub OpenOneRecordset()

        Dim dbExercise As DAO.Database
        Dim rsEmployees As DAO.Recordset

        Set dbExercise = CurrentDb
        Set rsEmployees = dbExercise.OpenRecordset("Employees")

        dbExercise.Close
        Set dbExercise = Nothing

End Sub

I want to create multiple recordsets, one for each name found in the array:

Public Sub OpenAllRecordsets()

    Dim dbExercise As DAO.Database
    Set dbExercise = CurrentDb

    For Each varTable In arrTables

        strRecordsetName = "rst" & varTable

        ''Above is a string...
        ''How can I use the string to declare this object?

        ''  !!!!!!!!! OBVIOUSLY THIS WON'T WORK...
        Dim strRecordsetName As DAO.Recordset
        Set strRecordsetName = dbExercise.OpenRecordset(varTable)

    Next

    dbExercise.Close
    Set dbExercise = Nothing

End Sub

I cannot figure out how to declare the name dynamically and then use it to create the recordset. I think this is handled similarly TableDefs

where I call the collection and add the member. What do you think?

UPDATE after first posted answer:

I am using these recordsets in a recursive function. It works, but I want to shorten the execution time. I recreated each recordset for a new record.

If nodeThis.hasChildNodes Then

    strTable = nodeThis.parentNode.nodeName

    Dim rsNewChild As DAO.Recordset         ' ***
    Set rsNewChild = cnn.OpenRecordset(strTable, dbOpenDynaset)  ' ***

    rsNewChild.AddNew

    '' ...populate fields
    For Each ...
        strName = nodeThis.nodeName            
        rsNewChild(strName) = nodeThis.Text
    Next

    rsNewChild.Update

    rsNewChild.close             ' ***
    Set rsNewChild = Nothing     ' ***

End If

But I know what records are needed, so I'd rather open them all at the beginning and then call as needed. This would allow me to delete the lines marked ***

. The problem is how to use a string (available in a function) to call a given set of records.

Reformulating the goal is more correct and useful: I need to take a string and use it to invoke the desired set of records:

[ BASED ON STRING ].AddNew

For Barranka's solution, I'm worried about the resources to loop through this array for each call. But I'll try, do some tests.

+3


source to share


2 answers


Just like TableDefs

built-in Collection

objects TableDef

, you can create your own Collection

objects Recordset

and refer to them by name as follows:

Dim cdb As DAO.Database, rst As DAO.Recordset, myRecordsets As Collection
Dim testArray(1) As String, tblName As Variant

' test data
testArray(0) = "People"
testArray(1) = "OtherPeople"

' build the collection
Set myRecordsets = New Collection
Set cdb = CurrentDb
For Each tblName In testArray
    Set rst = cdb.OpenRecordset(tblName, dbOpenTable)
    myRecordsets.Add rst, tblName
    Set rst = Nothing
Next

' use the members of the collection
Debug.Print myRecordsets("People").Fields("LastName").Value
myRecordsets("People").MoveNext
Debug.Print myRecordsets("People").Fields("LastName").Value
Debug.Print myRecordsets("OtherPeople").Fields("LastName").Value

Set myRecordsets = Nothing
Set cdb = Nothing

(Note that the .Add

object method parameters Collections

are equal value, key

, which is the opposite of how Dictionary

other associative arrays tend to order their arguments when a new entry is added.)



Edit re: update question

This works too

' add a new record
myRecordsets("OtherPeople").AddNew
myRecordsets("OtherPeople").Fields("LastName").Value = "NewPerson"
myRecordsets("OtherPeople").Update
+3


source


I would create an array of recordsets:



dim rs() as DAO.Recordset
' You must redim the array with something like: ReDim rs(1 to UBound(arrTables))
dim i as Integer
' ...
i = 1
for each varTable in arrTables
    Set rs(i) = dbExcercise.openRecordset(varTable)
    i = i + 1;
next varTable
+2


source






More articles:


All Articles