Indirectly(?) referring to an array

[emmahope206]

Hi All,

I have a complex master database that is used to refresh the data in multiple smaller databases for groups of users in different business divisions, each one having different populations and a significant number of other factors too numerous to mention.

Simply put, I have numerous arrays defined, each with lists of table names, different 'actions' are then performed on each of these. By 'actions', I mean, one group of tables might be cut down by joining on 1 specific field, another group of tables might be cut down on 2 other specific fields etc and so on and so on.

My arrays are called strArr01, strArr02, strArr03, strArr04 etc etc. Some of the actions need to be repeated (such as 'drop table') on multiple arrays

For i = 0 To UBound(strArr01)
strSQL01 = "DROP TABLE [" & strArr01(i) & "]"
db.Execute strSQL01, dbFailOnError
Next i

...repeat for strArr02....repeat for strArr03....repeat for strArr04 etc etc etc.

i was hoping to be able to do something like

For j = 0 to 100 '(lets assume there are 100 arrays)
and then refer to "strArr0" & (j) in some way to be able to loop through these but this does not work.

Is there some method where I can 'indirectly' refer to the name of my array in this method? Please can someone advise?

Thanks

 

[jdraw]

How do you populate these arrays? How do you maintain it/them? If you had that data in tables, then it seems that data would be preserved between application sessions and could have a series of admin forms for maintenance.
Perhaps if we had more info or sample data more focused responses could be made.

 

[The_Doc_Man]

I think the answer to your question is a qualified "MAYBE"

You can perhaps do

strArrName = "strArr" & trim(cstr(j)) & "(i)"
strSQL01 = "DROP Table [" & strArrname & "]"

You might also wish to look at creating a collection of structures (such as arrays) where it is possible to refer to an element of the collection by member number with the collection OR via the name of the named item.

Collection object

Office VBA reference topic

docs.microsoft.com

 

[emmahope206]

Hi Jdraw - lists typed into the arrays in the code modules, the maintenance of the lists is infrequent, perhaps once or twice a year is required. The refresh of the data is regular (several times a week), hence the requirement for the complexity).

Very simplified example would be:
(in reality there are many more scenarios)

strArr01 = Array("table1", "table6","table12","table21","table8","table4)
strArr02 = Array("table3", "table5","table9","table4","table17")
strArr03 = Array("table2", "table11")
strArr04 = Array("table7", "table15","table22","table16","table27")

For i = 0 To UBound(strArr01)
strSQL01 = "DROP TABLE [" & strArr01(i) & "]"
db.Execute strSQL01, dbFailOnError
Next i

For i = 0 To UBound(strArr02)
strSQL01 = "DROP TABLE [" & strArr02(i) & "]"
db.Execute strSQL01, dbFailOnError
Next i

For i = 0 To UBound(strArr03)
strSQL01 = "DROP TABLE [" & strArr03(i) & "]"
db.Execute strSQL01, dbFailOnError
Next i

For i = 0 To UBound(strArr04)
strSQL01 = "DROP TABLE [" & strArr04(i) & "]"
db.Execute strSQL01, dbFailOnError
Next i

followed by much more code where the Arrays are re-used to do different things for different purposes.

My aim is to stop needing to physically repeat the same For Next loop multiple times (& all the similar ones i have) by indirectly referring to strArr0# where # is the number of the Array.

 

[The_Doc_Man]

If you have a table containing all of these names and the actions you intended to perform, you could iterate through a recordset rather than an array. Since you are calling in a db.Execute of an SQL string, any speed eked out by using in-memory arrays is countered by the initial effort to load the arrays, because arrays aren't saved with the .ACCDB or .MDB file. They would be part of the workspace, which is a memory construct that is erased when the app exits. If you make the list a table, that is a one-off action to populate that table, after which you can just step through the recordset to execute the actions proposed by / implied by the table entries.