arrays and recordsets 1

[skinicod]

Hi,

Does anyone know how you can create a new recordset using data held in a public array??

 

[Tranman]

skinicod,
Here's a snippet:

Private Sub MakeRs()
Dim rs As New ADODB.Recordset
rs.Fields.Append "LName", adChar, 30
rs.Open
For myPointer = 0 to UBound(myArray)
rs.AddNew
rs.Fields("LName") = myArray(myPointer)
Next

rs.Sort = "LName DESC"
End Sub

You can check out the append method of the fields collection in the MSDN help online at:

http://msdn.microsoft.com/library/default.asp?

url=/library/en-us/dnword97/html/wdactivewd.asp

Be sure to give a length on text type fields.

I frequently use this technique for sorting files or arrays. Just load the data into a recordset that's not connected to a table or anything, then set the sort property. In general, if you're sorting a recordset, you need to set the CursorLocation Property to adUseClient, but you won't need to do that in this case because you're not connected to an external data source.

Of course, if your array is of a user-defined type, you can create and fill multiple fields...


Tranman

 

[skinicod]

Cheers thats fantastic

 

[lollyjar]

Is this also possible in DAO?

Cheers

 

[Tranman]

lollyjar,
As far as I know, you can't do the same thing in DAO because the fields collection is read only.

You could (I did it once in the old days), just create a recordset based upon a table (whose design you can modify on the fly), then load and maniplulate your data there. It sounds really inefficient, but if your table is not indexed, it will run very fast.

This begs the question, though; Why would you want to do this in DAO? ADO can be used in '97 by adding a reference, and is the default data access object library in 2K and XP. The only gotcha is that if you have references to both libraries, you have to qualify any references to objects whose name occurs in multiple libraries. For example, if you have references to both libraries, and you say:

Dim rs as Recordset

you will not know which type of recordset you're getting, so you need to say:

Dim rs as ADO.Recordset
or
Dim rs as DAO.Recordset

in order to assure you get the right type of recordset.

I'd just use ADO.

Tranman

 

[LeonAtRC]

Thanks Tranman!
I had no idea you could create a recordset without using tables or views.
You've saved me hours by not having to build a sorting routine for an array.

 

[Tranman]

Glad it helped. Skinicod did not specifically ask about sorting in the original post, but it seemed like that might be what (s)he was leading up to.

I'll never forget how happy I was when I figured out how to do this recordset sorting, and that I didn't have to write any more bubble sorts. I hated those things.

Tranman