How to store table column names into array using access vba

Question

I am pretty new to VBA . I have a table contains more than 30 fields. How do i store the column names into array using vba

My vba Code

Dim tdf As TableDef
Dim fld As Field
Dim o As Integer

Set tdf = db.TableDefs(tablename)
Dim n As Integer
n = tdf.Fields.Count
ReDim tablecolumns(0 To n) As String

For o = 0 To n
    tablecolumns(n) = fld.Name(o)
Next o

Giving an error in this line

  tablecolumns(n) = fld.Name(o)

edit: whoops. Brain melt - thought this was Excel. Comment removed. — Cor_Blimey, Apr 24 '14 at 21:14
There a number of ways to do this. For example you could [query the Msysobjects table](http://stackoverflow.com/a/4895805/119477) and then loop over the recordset if you're talking about an Access table. — Conrad Frix, Apr 24 '14 at 21:16
`tablecolumns(n) = fld.Name(o)`-->`tablecolumns(o) = tdf.Fields(o).Name` and `For o = 0 To n`--> `For o = 0 To n-1` — Dmitry Pavliv, Apr 24 '14 at 21:30
Darn it @simoco, post some of these as answers so the questions can be closed :) — Blackhawk, Apr 24 '14 at 21:35

Blackhawk · Accepted Answer · 2014-04-24T21:34:29.750

1

Ah, I'm modifying my answer to match your code.

Dim tdf As TableDef
Dim fld As Field
Dim o As Integer

Set tdf = db.TableDefs(tablename)
Dim n As Integer
n = tdf.Fields.Count
Dim tablecolumns() As String
ReDim tablecolumns(0 To n-1)

For o = 0 To n-1
    tablecolumns(o) = tdf.Fields(o).Name
Next o

edited Apr 24 '14 at 21:34

answered Apr 24 '14 at 21:29

Blackhawk

5,984
4
27
56

How to store table column names into array using access vba

1 Answers1