Populate array with Items in a table column

[SirDrums]

Hello,

I would like to create an array that is populated with the items found in a column in a database.

Say if in my data base I have a table called names and in that table I have a column called First names that is contains well....first names. I would like to create an array that contains the names in that column

This is what I have so far:

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rs As DAO.Recordset
Dim fnames() As String
Dim RecCnt As Integer
Dim Max As Integer
 
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("Path to the database")
Set rs = db.OpenRecordset("Select FirstNames From Names")
Max = rs.RecordCount
ReDim lcsfields(1 To Max)
For RC = 1 To Max
lcsfields = lcsfields & ????
 Next RC
 
.....

I have no idea if I am going about it right but that is what I have dug up so far.

Eventually I want to use the names to build a SQL string... but I first need to get the names in some sort of list.... I think...

 

[Poppa Smurf]

You want to populate the first names to array then use the first names to build a sql query. What will be the purpose of the names in the sql query? You could use a list box on a form to do the same thing if you require the names to be the criteria in the sql query.

 

[stopher]

Here's the code to populate an array. Note that you have to force the recordset to the last record in order to get a correct RecordCount (because you are specifying a dynaset).

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim rs As DAO.Recordset
Dim fnames() As String
Dim RecCnt As Integer
Dim Max As Integer
 
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\Users\Chris\Documents\myNames.accdb")
Set rs = db.OpenRecordset("Select FirstNames From Names")

rs.MoveLast
rs.MoveFirst
Max = rs.RecordCount

ReDim lcsfields(1 To Max)

RecCnt = 1

Do While Not rs.EOF
    lcsfields(RecCnt) = rs!FirstNames
    RecCnt = RecCnt + 1
    rs.MoveNext
Loop

Set rs = nothing

But as Poppa Smurf said, it might help if you explain more about what you are trying to do.

For instance, if you are trying to build a string, why not build the string straight away without using an array.

Chris