Routine for listing field names of a table

[Zorcon]

I am using Access 2002. I need a VBA routine that will give me the names of
all the fields in a table. I would like to simply define the table as a
string and then have it upload the field names in a dimensioned array that I
have already defined. I know this should be easy, but I'm having trouble with
Access Help in seeing how to do this. Any assistance would be greatly
appreciated. Thanks!

 

[fredg]

I am using Access 2002. I need a VBA routine that will give me the names of
all the fields in a table. I would like to simply define the table as a
string and then have it upload the field names in a dimensioned array that I
have already defined. I know this should be easy, but I'm having trouble with
Access Help in seeing how to do this. Any assistance would be greatly
appreciated. Thanks!

Something like this?

Public Sub ArrayFieldNames()

On Error GoTo Err_Handler
Dim strTableName as String
Dim AllFields() As String
ReDim Preserve AllFields(1)
Dim db As DAO.Database
Set db = CurrentDb
Dim tbf As DAO.TableDef
Dim fld As DAO.Field
Dim intX As Integer

TableName = InputBox("Enter the name of the table.")

Set tbf = db(strTableName)

For Each fld In tbf.Fields
AllFields(intX) = fld.Name
intX = intX + 1
Next

For intX = 0 To UBound(AllFields)
Debug.Print AllFields(intX)
Next intX
Set db = Nothing

Exit_Sub:
Exit Sub
Err_Handler:
If Err = 9 Then
ReDim Preserve AllFields(UBound(AllFields) + 1)
Resume
Else
Resume Exit_Sub
End If
End Sub

 

[Marshall Barton]

I am using Access 2002. I need a VBA routine that will give me the names of
all the fields in a table. I would like to simply define the table as a
string and then have it upload the field names in a dimensioned array that I
have already defined.

If the table is local and you are using DAO, then you can
just loop thrugh the TableDef's Fields collection.

Set db = CurrentDb()
With db.TableDefs(stringwithtablename)
For Each fld In .Fields
ary(k) = fld.Name
k = k + 1
Next fld
End With
Set db = Nothing

 

[Zorcon]

Thanks to you both. That's just what I was looking for. It's the currentdb
that was missing in my routines.

Z