List of fieldnames

[frankiem]

Does anybody know of a way to get the list of field names in an Access table using SQL or VB? I want to be able to load a list box with the field names when one of a group of tables is selected.

 

[Terpsfan]

There is probably an easier way to do it but this gets the job done. Just set a reference to ADO in your project.

Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset
Private Sub Command0_Click()
Dim fld As ADODB.Field
Set objConn = CurrentProject.Connection
Set objRec = New ADODB.Recordset

objRec.Open "tblAccounts", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

For Each fld In objRec.Fields
objConn.Execute "Insert Into tblFields (Field) Values ('" & fld.Name & "')"
Next fld
Set objRec = Nothing
objConn.Close
Set objConn = Nothing
Set fld = Nothing

 

[Terpsfan]

This would work for any table that you wanted to select

Dim objConn As ADODB.Connection
Dim objRec As ADODB.Recordset
Private Sub Command0_Click()
Call getfields("tblAccounts&quot
End Sub
Public Function getfields(tblName As String)
Dim fld As ADODB.Field
Set objConn = CurrentProject.Connection
Set objRec = New ADODB.Recordset

objRec.Open tblName, objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable

For Each fld In objRec.Fields
objConn.Execute "Insert Into tblFields (Field) Values ('" & fld.Name & "')"
Next fld
Set objRec = Nothing
objConn.Close
Set objConn = Nothing
Set fld = Nothing
End Function

 

[jebry]

Hi!

You can do this easier in a list box by setting the row source type to Field List and make the row source the name of the table selected: Me!ListBox.RowSource = "TableName". You don't say how the user will be selecting the tables so I can't be more specific. I do have a database with a form set up to do this. Actually, it is set up as a query builder, except it doesn't have the code behind it to actually build the query. The person I did it for said that they wanted to code that part themselves so I never did. You are welcome to look at the interface if you want to.

hth
Jeff Bridgham
bridgham@purdue.edu