Is it possible to use a query to get the field names of a table, like querying MSysObjects to get the table names? I am only using Access on the back end, using ColdFusion on the front end, so I cannot use built in language like Field List.
Thank-you for your response. I have used the Documentor to print the database schema. However, what I need to do is code based. I need to get the field names of a table to fill an HTML form select object. I'm using a SELECT Name FROM MSysObjects WHERE etc query to fill an HTML form select with the table names. Now I need to fill the field name select with the field names of the selected table.
Here is some code I used on a cross tab query make tbl, that made a wet table from data I just Imported, The table def had to be updated because I never new what data they were going to throw at me. So I wrote the following function and pass it the wet tbl and appended fields to the working tbl.
Function MyAddDef1(MyTableStr1 As String, MyTableStr2 As String)
Dim db As Database
Dim tdf1 As TableDef
Dim tdf2 As TableDef
Dim fld1 As Field
Dim fld2 As Field
Dim MyStr1 As String
Dim MyStr2 As String
Dim MyNewFieldDef As String
Dim MyStr4 As String
Dim flde As Boolean
Dim flde1 As Boolean
flde = True
flde1 = True
Set db = CurrentDb
Set tdf1 = db.TableDefs(MyTableStr2) 'wet
Set tdf2 = db.TableDefs(MyTableStr1)
For Each fld1 In tdf1.Fields
MyStr4 = fld1.Name
MyNewFieldDef = fld1.Name 'fld not found
If flde Then
For Each fld2 In tdf2.Fields
MyStr2 = fld2.Name
If MyStr2 = MyStr4 And flde Then
flde1 = False
flde = False
End If
Next fld2
End If 'If flde Then
If flde Then MyAddDef2 MyTableStr1, MyNewFieldDef
flde = True
Next fld1
Set db = Nothing
End Function
Private Function MyAddDef2(MyTableStr1 As String, MyNewFieldDef As String)
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Set db = CurrentDb
Set tdf = db.TableDefs(MyTableStr1)
Set fld = tdf.CreateField(MyNewFieldDef)
fld.Type = dbText
fld.Size = 30
fld.Required = False
fld.DefaultValue = "0"
tdf.Fields.Append fld
tdf.Fields.Refresh
Set db = Nothing
End Function
Thank you for the additional responses. The heitml product looks interesting, but my employer is committed to ColdFusion. However, based on the heitml information, I think I see a way I can do what I want in ColdFusion code. The Access code from Mike looks good too, but I don't how to integrate that with ColdFusion. What is now patently clear to me is that there is no simple query to get column names in Access. I appreciate everyone's help.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.