Virtual Tables / Recordsets

[Kenln]

I have to read files names from a shared folder on the network. But only briefly to select the correct file, which will be based on the file name, which I won't know until I read them in.

I would like to store these names in a (virtual table) recordset. Is there a way to create a recordset that does not actually point to a physical table and populate that?

Thank you,

 

[gemma-the-husky]

dim filenames() as string - array that can be redimensioned

redim filenames(20) 'using a parameter which i forget to keep the values

then something like

x=0
thisfile = dir(path)
while thisfile <> vbnullstring
  x=x+1
  {code needed here to extend the array if x>arraysize}
  filenames(x) = thisfile
  thisfile = dir  - read the next item
wend

at the end of this you have an array of filenames

 

[Banana]

An alternative , if you need to be able to do filter or find or such operations that can only be done on recordset, is to use a disconnected ADO recordset.

Dim r As New ADODB.Recordset

r.Fields.Append "FileName", adVarChar, 255
r.Open

r.AddNew
r.Fields("FileName") = "foo"
r.Update

r.AddNew
r.Fields("FileName") = "bar"
r.Update

r.MoveFirst

Do Until r.EOF
    Debug.Print r.Fields("FileName")
    r.MoveNext
Loop

Or if you don't need a recordset (you don't want to filter, but you may want to find an element fast), use Collection instead.

Dim col As New Collection
Dim v As Variant

col.Add "bar", 1
col.Add "foo", 2

For each v in col
   Debug.Print v
Next

HTH.

 

[Kenln]

If I understand correctly, I should be able to use DMin and DMax to find the min/max values of a field in the disconnected recordset?????

 

[Banana]

Sorry, but you can't execute SQL upon recordsets. You can do a find or filter upon recordsets, but to get min/max, you would have to loop through the entire recordset and inspect the value to see if it's min/max.

If this is going to be a huge list, I would just create a temporary table with the columns I need for my purposes and insert into that table, execute queries upon it and when I'm done with it, truncate the table but keep the structure of the table for later use.

 

[Kenln]

Thanks,

I'll give it a try.