grabbing table values

[awstewar]

How would I grab a table column using a number rather than using the name of the column. I have a table with x number of columns and y number of rows. I would like to get the first column and put it into a variant. I would like to loop through and do this for all of the columns. I don't know how many columns there are or their names. Does anybody know how to do this?

 

[ReAn]

Not so sure about this one but try this:

Dim tblColName as string

SQL = "SELECT * FROM tblMyTable"
Set rst = CurrentDB.OpenRecordset(SQL)

Dim i as Integer
i = 0
If Not rst.EOF Then
  For each q in rst
    if i = intNumberImLookingFor
      tblColName = q
    End If
    i = i + 1
  Next
End If

SQL = "SELECT " & tblColName & " FROM tblMyTable"

'You can now fill in the rest...

 

[scottn]

you can find the number of columns like this:

NumCols = rst.Fields.Count

You can reference a column using its index rather than its name:

Val = rst.Fields(0)

The indices are 0-based, so that line above will give you the value from the first column. To loop through all columns, then, you can just use a for loop:

For i = 0 to rst.Fields.Count - 1
    Val = rst.Fields(i)
Next i