ms access: how to store the contents of a table in a string array?

Question

i'm working on a MS access database..

At some point i needed to create a module which contains a dynamic string array, the data to store in this array should be brought from a table, i use count(*) function on that table to define the size of the dynamic array.

what i need to know is how to fill the array with the contents of a columns of that table ( called Names);

to make it more clear: suppose i declared the array like this

dim myArray() as string
redim myArray(myTable.count(*))

where my table contains two fields: ID, Name

i want myArray(0) to hold the content of Name in the first record myArray(1) to hold the content of Name in the second record

and so on

how to do this?

Answer 1

I am not sure why you would want to use an array, but here is a way adapted from a Microsoft article:

Dim objConnection as ADODB.Connection
Dim objRecordSet as ADODB.Recordset
Dim arrTest as variant

Set objConnection = CurrentProject.Connection
Set objRecordSet = New ADODB.Recordset

objRecordSet.Open "SELECT * FROM YourTable" , objConnection, _
    adOpenStatic, adLockOptimistic

arrTest = objRecordSet.GetRows

objRecordSet.Close

objConnection.Close