dynamic arrays???

i am comming up with errors adding data to my dynamic array

Code:

---

    Do Until dyna.EOF
         
         
          myarray(UBound(myarray)) = dyna("InterviewerShortDesc") 
    dyna.MoveNext
    Loop

---

im trying to extract data from my database and store it in an array called my array ???
its a dynamic array how do i add data to it???
here is my atemt to do it but i cant get it right!!
thanks for all your help

for n = 0 to dyna.eof - 1
redim preserve myarray(n)
myarray(n) = dyna("InterviewerShortDesc")
dyna.MoveNext
next

thankyou for that but i come up with an error as
"n not defined"
any ideas
thanks

dim n as integer '<- Integer or long (depends on your records)

for n = 0 to dyna.eof - 1
redim preserve myarray(n)
myarray(n) = dyna("InterviewerShortDesc")
dyna.MoveNext
next

watch the type of array you are using (string, integer, etc.) and convert dyna("InterviewerShortDesc")
to that type

It's a lot easier and faster (no Redim-ing required) to use a collection.

Dim MyCollection As New Collection

Do Until dyna.EOF
MyArray.AddItem dyna("InterviewerShortDesc")
dyna.MoveNext
Loop

Old habits die hard.

How do you define your collection?

Do you have to set your dyna = mycollection first or anything?

No. While johnnyboy23's original post leaves out the Set statement for dyna, etc., the collection will work just as it is after you correct a mistake I made; it should be MyCollection.Add and not MyCollection.AddItem.

To access the data in the collection you do:

Dim intIndex As Integer

For intIndex = 1 to MyCollection.Count
Debug.Print MyCollection.Item(intIndex)
Next

Code:

---

Public Function pophistory() As String

Dim dyna As Recordset
Dim theError As Long
Dim theid As Long
Dim myarray() As String


SQL = "select InterviewerShortDesc from interviewer"
    Set dyna = db.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough + dbSeeChanges)
   
    theError = Err
   
On Local Error GoTo 0
   
Select Case theError
Case 0
   
    Do Until dyna.EOF



Do Until dyna.EOF

'here is where  use my array

myarray(UBound(myarray)) = dyna("InterviewerShortDesc")


   
dyna.MoveNext

loop

---

i have added a watch to my expression myarray and can see the values from my database going into the myarray
but when the next bvalue comes in it changes the old value and does not add a new one to the array.???


hope this makes sense ....please be patient with me as im fairly new to VB
thanks

thanks for the info MartinLiss. I've never used collections this way before but I will now!

Thanks Guys!!!
thank for helping me out guys!!!!
the collections worked great!!

For those just finding out about collections, do a search on the net for a little DLL called "SpiderEye FlexBag". It's a drop in collection replacement (ie. it ahs all the same properties as a collection, plus it has a heap more, including the ability to set case senisitive statements and allow duplicates, and the assign a new item if that item doesn't exists (which stops the biggest error in collections - Key Not found).

Oh yeah, and it is many, many times faster than the intrinsic VB collection. Try adding 100,000 items to a collection and see how slow it gets. The flexbag is orders of magnitude faster. That said, remeber that arrays are still faster than both the collection and the flexbag, but much harder to use.

Ad lastly, it's free. Which is always a fine thing :)

- gaffa