by VB-Kung-Fu (19 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Tue 9th December 2003
Date Added: Mon 8th February 2021
Rating: (1 Votes)
Returns a list of the names of fields in the specified table.
'GetTableFieldList
' Returns a list of the fields in the specified table.
' The field names are delimited by the specified
' delimiter and you can specify how many fields to
' return. If the field count is left unspecified
' all fields are returned.
Public Function GetTableFieldList(TblName As String, Optional Delimeter As String = vbCrLf, _
Optional FldCount As Long = -1) As String
Dim tdf As TableDef, fld As Field, res As String
If FldCount = 0 Then
Exit Function
ElseIf FldCount = -1 Then
On Error Resume Next
FldCount = CurrentDb.OpenRecordset(TblName).Fields.Count
If FldCount = -1 Then GoTo ShowMsg
End If
For Each tdf In CurrentDb.TableDefs
If StrComp(tdf.Name, TblName, vbTextCompare) = 0 Then
FldCount = IIf(Abs(FldCount) > tdf.Fields.Count, tdf.Fields.Count, Abs(FldCount))
For Each fld In tdf.Fields
res = res & fld.Name & Delimeter
FldCount = FldCount - 1
If FldCount <= 0 Then Exit For
Next
GetTableFieldList = Left(res, Len(res) - 1) 'remove trailing delimeter
Exit Function
End If
Next
ShowMsg:
MsgBox "Table '" & TblName & "' was not found in this database"
End Function