Is there a way to select all the field NAME by a SQL statement??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know how to get all the table name by
SELECT DISTINCT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Like "tbl*") AND ((MSysObjects.Type)=1));

Is there a way to select all the field NAME by a SQL statement??
 
Not in Access. There are lots of code samples that do this.

--
Duane Hookom
MS Access MVP


VC said:
I know how to get all the table name by
SELECT DISTINCT MSysObjects.Name FROM MSysObjects WHERE
Click to expand...
(((MSysObjects.Name) Like "tbl*") AND ((MSysObjects.Type)=1));
 
Function ShowFields(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Function FieldTypeName(n As Long) As String
'Purpose: Converts the numeric results of DAO fieldtype to text.
'Note: fld.Type is Integer, but the constants are Long.
Dim strReturn As String 'Name to return

Select Case n
Case dbBoolean
strReturn = "Yes/No" '1
Case dbByte
strReturn = "Byte" '2
Case dbInteger
strReturn = "Integer" '3
Case dbLong
strReturn = "Long Integer" '4
Case dbCurrency
strReturn = "Currency" '5
Case dbSingle
strReturn = "Single" '6
Case dbDouble
strReturn = "Double" '7
Case dbDate
strReturn = "Date/Time" '8
Case dbBinary
strReturn = "Binary" '9
Case dbText
strReturn = "Text" '10
Case dbLongBinary
strReturn = "OLE Object" '11
Case dbMemo
strReturn = "Memo" '12
Case dbGUID
strReturn = "GUID" '15
Case dbBigInt
strReturn = "Big Integer" '16
Case dbVarBinary
strReturn = "VarBinary" '17
Case dbChar
strReturn = "Char" '18
Case dbNumeric
strReturn = "Numeric" '19
Case dbDecimal
strReturn = "Decimal" '20
Case dbFloat
strReturn = dbFloat '21
Case dbTime
strReturn = "Time" '22
Case dbTimeStamp
strReturn = "Time Stamp" '23
Case Else
strReturn = "Field type " & n & "unknown"
End Select
FieldTypeName = strReturn
End Function
 
Thanks a lots

Allen Browne said:
Function ShowFields(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each fld In tdf.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

Function FieldTypeName(n As Long) As String
'Purpose: Converts the numeric results of DAO fieldtype to text.
'Note: fld.Type is Integer, but the constants are Long.
Dim strReturn As String 'Name to return

Select Case n
Case dbBoolean
strReturn = "Yes/No" '1
Case dbByte
strReturn = "Byte" '2
Case dbInteger
strReturn = "Integer" '3
Case dbLong
strReturn = "Long Integer" '4
Case dbCurrency
strReturn = "Currency" '5
Case dbSingle
strReturn = "Single" '6
Case dbDouble
strReturn = "Double" '7
Case dbDate
strReturn = "Date/Time" '8
Case dbBinary
strReturn = "Binary" '9
Case dbText
strReturn = "Text" '10
Case dbLongBinary
strReturn = "OLE Object" '11
Case dbMemo
strReturn = "Memo" '12
Case dbGUID
strReturn = "GUID" '15
Case dbBigInt
strReturn = "Big Integer" '16
Case dbVarBinary
strReturn = "VarBinary" '17
Case dbChar
strReturn = "Char" '18
Case dbNumeric
strReturn = "Numeric" '19
Case dbDecimal
strReturn = "Decimal" '20
Case dbFloat
strReturn = dbFloat '21
Case dbTime
strReturn = "Time" '22
Case dbTimeStamp
strReturn = "Time Stamp" '23
Case Else
strReturn = "Field type " & n & "unknown"
End Select
FieldTypeName = strReturn
End Function
Click to expand...
 
Back
Top