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

i'm working o开发者_开发问答n 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?

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