vb database array

[Katrina]

how do you read from a database and put it into an array?

[Thai]

assuming you have a connection to the database, I'll just call it cn for short, this is what you would do.

MyArray = cn.Execute("SELECT * FROM Table1")

That will throw all the fields into MyArray and you can access it by starting with the number 0.

Debug.Print MyArray(0), MyArray(1), etc, etc

Hope that helps,
Thai

[Brent Akester]

That won't work, entirely. There are a few steps missing...

First you'll need to get the total count of records retrieved from the DB (recRS.Recordcount). This is important so you'll know how big your array needs to be. Then you'll have to cycle through each record in your recordset and add it to your array:
(This is off the top of my head so there may be mistakes)

----------
dim arrMyArray() as variant 'or string, etc.
dim x as integer

'dimension the size of the array based on total
'records in your recordset
redim arrMyArray(recRS.recordcount -1)

'cycle through each record and add it to the array
for x = 1 to ubound(arrMyArray)
arrMyArray(x) = recRS!fieldname
next
-----------

Finally, instead of doing this you can also return a recordset to a calling function, instead of returning an array:

Public Function FunctionName() As Recordset

FunctionName = arrMyArray

End Function

[Brent Akester]

Ack! I made one small mistake in the second-to-last line. It should be:

Public Function FunctionName() As Recordset

'where recRS is the name of your recordset
FunctionName = recRS

End Function

[Thai]

works fine if you are only retrieving one recordset.. forgot to mention that.

Thai

[Radames]

Thai you mean one Column from the database? If this dont work with a recordset that has more columns I just wasted 2 hours!

[Heico]

You should use a collection instead of an array. Create a class module with and add the fields of your table as properties. Then create a collection (you can use the Class Builder Utility). Then create an object for each record you have and add it to the collection.

I can't explain it in a short message, so I added a little example, hope it helps.

[Radames]

Its OK what I did was store both the fields in a string separated by "/"
When I want to use the values, I use the split function with "/" as the delimiter. Works fine!