none
Access 2007 -- Table Field Names to Data Rows RRS feed

  • Question

  • In Access 2007, I am trying to get the Field Names from a Table to be individual rows in another table.  Does anyone have any suggestions on how to do that?
    Wednesday, April 13, 2016 8:10 PM

Answers

  • Hi AlyScher,

    you can use following VBA code to get the fieldnames of particular table and save it to another table.

    Private Sub ShowTableFields()
    Dim db As Database
    Dim tdf As TableDef
    Dim x As Integer
    Set db = CurrentDb
    For Each tdf In db.TableDefs
         If Left(tdf.Name, 4) = "stud" Then
          For x = 0 To tdf.Fields.Count - 1
          Debug.Print tdf.Name & "','" & tdf.Fields(x).Name
          db.Execute " INSERT INTO tabledata(tablename,fieldname) VALUES('" & tdf.Name & "','" & tdf.Fields(x).Name & "');"
          Next x
       End If
    Next tdf
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 14, 2016 6:50 AM
    Moderator

All replies

  • How many fields are we talking about? If not too many, you can do it manually. If too much, then you may have to use VBA and loop through them. Just a thought...
    Wednesday, April 13, 2016 9:08 PM
  • Hi AlyScher,

    you can use following VBA code to get the fieldnames of particular table and save it to another table.

    Private Sub ShowTableFields()
    Dim db As Database
    Dim tdf As TableDef
    Dim x As Integer
    Set db = CurrentDb
    For Each tdf In db.TableDefs
         If Left(tdf.Name, 4) = "stud" Then
          For x = 0 To tdf.Fields.Count - 1
          Debug.Print tdf.Name & "','" & tdf.Fields(x).Name
          db.Execute " INSERT INTO tabledata(tablename,fieldname) VALUES('" & tdf.Name & "','" & tdf.Fields(x).Name & "');"
          Next x
       End If
    Next tdf
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 14, 2016 6:50 AM
    Moderator