Dynamically Name Object for Reference

[zmbd]

This is driving me nuts.
I'd like to use a loop to set the reference to about a dozen objects.
The recordsets are also able to be named by appending the loop count to the proper points in the code.

What I am doing is normalizing some old data.
In the past I would just simply hardcode

SET data_topic_1 = (object_1)
SET data_topic_2 = (object_2)
'(...)
SET data_topic_n = (object_n)

What I'd like to do:

'need these public for the forms and modules to work 
'with while importing into the proper fields
'and updating information.
Public data_archive_1 As dao.database
Public data_archive_2 As dao.database
Public data_topic_1 As dao.recordset
Public data_topic_2 As dao.recordset
Public data_topic_3 As dao.recordset
Public data_topic_4 As dao.recordset
'add another 15... 
'to finally get to...
Public data_topic_19 As dao.recordset
 
 
sub setuptopicrecordsets()
   Dim x As Integer
   Dim zstrRsName As string
'...air code...
'set up the references to the other database files etc..
'
'setup for data retrieval
   for x = 1 to 19
      zstrRsName = "data_topic_" & x
      set zstrRsName = ....(recordsetstring)....
   next x
'
'...air code...
'

of course I get a need object error at the set
I thought I had done something like this in the past. However, I just can't seem to remember what I did to get this to work, I can't seem to locate the old database (which is why I'm (re)doing this today," and hardcoding 19 references just doesn't sound very fun today with all of the other crud that has hit my lab.

Given the amount of time I've spent on this, I could have hardcoded the entire lot; however, at this point it's the principal of the thing...
[~.~] grr... dog/w/bone ... grr [~.~]




-when it rains it pours-




-z

[twinnyfo]

Hey Z,

I think I understand what you're trying to do. But, I wish I knew how you were planning to use the recordsets individually and maybe it would make more sense.

One of my thoughts was to use an String Array to store the 19 data topics, then just cycle through using one recordset (Set it to the new data set each time you loop through x).

Not quite sure if that is making any sense. I can kinda see it in my head what I am thinking of doing, but still not sure what the recordset is supposed to look like from your end.

We can fuddle through it together....

[Seth Schrock]

Rabbit helped me with something similar in this thread: Looping through groups of conrols. Couldn't you change the collection to contain recordsets?

[dsatino]

I would definitely use an array and loop through those.

[zmbd]

I'll have to look at the collection thing, I think that's what I did the last time using the counter as the key-value.

The SQL string is easy enough to write by counter, which is what I did, storing the string in the table and then pulling that value into the open-recordset command while setting the reference.

@TF: In short, and I have to gloss over some things for the company...
A mad man many years ago made a table for each sample sortof:
[tbl_customerid_sampleid] in database files named [yyyymmdd] and then he had a database file that listed every sample_id and which database file. The individual viewed databases like worksheets... it used to be fun to watch him pull the data.

yummy-yummy-yummy

Now this wouldn't be too bad, I have a database with multiple backends... which backend is connected depends on the user profile, provided there were some actual relationships in the system of files. What we have to do with these old archives is pull the customer id then build queries in the sample log which then lets us pull the information out of the other databases files...

Now this is where I come in....

For simple queries, I take all of these files, bring them in as recordsets, select the related fields between the recordsets so taking a bunch of unrelated files and making a relational database via frontend.

For more complex queries, I pull the records apart and store field names as record entries etc...

[twinnyfo]

What...
A...
Disaster!

[zmbd]

Oh, TwinnyFo, it is actually worse than I've given... I've greatly simplified the details.

The person believed that sharing knowledge was tantamount to losing ones position; thus, he'd never share how he would make the reports etc... While he was out on vacation, instead of bothering him I looked at what was going on in the databases and a day later pulled the reports and started the demystification of the data-pools. He was not very happy with me. Anyway, he has moved on and I've inherited the mess... then we were bought out by a huge company so the mess isn't as important as it use to be - just every now and again, we need something.

Thnx