Print a list of table Field names (1 Viewer)

Rats

Registered User.
Local time
Tomorrow, 02:05
Joined
Jan 11, 2005
Messages
151
Just a quickie- i hope. :rolleyes:

I am in the process of trying to merge two large databases. I have four tables that really should be one to work in the new DB. I just need to be able to print a list of fields in each table so that I can work on the new combined structure before making the changes. I know I can use the documenter but this produces a very comprehensive list which is much more than I need.
Is there anyway of producing a similar list without the detail, just showing the field names.

Thanks
 

allan57

Allan
Local time
Today, 18:05
Joined
Nov 29, 2004
Messages
336
Peter

I use the following to keep track of field names within a database, this then provides data for you to make any custom reports you wish.

Create the following Table

Application_Table_Field_Definitions
txtField_Name (text 65)
txtTable_Name (text 65)
intSequence (integer)
txtDescription (text 100)
fPkey (yes/no)
txtType (text 35)
intLength (integer)

Create a form with the following code in it

Private Sub Form_Load()

DoCmd.Maximize

GetTableDefinitions

End Sub

Sub GetTableDefinitions()

Dim dbs As Database
Dim rstApplicationTableFieldDefinitions As Recordset

Dim tdfDetails As TableDef
Dim fldDetails As Field
Dim idxDetails As Index

Dim strPrimaryKeyNames(1 To 10) As String

Dim intPrimaryKeyCount As Integer
Dim intPrimaryKeyCounter As Integer
Dim intProgressBarCounter As Integer

Dim varRtn As Variant

On Error Resume Next

Set dbs = CurrentDb

DoCmd.RunSQL ("DELETE Application_Table_Field_Definitions.* FROM Application_Table_Field_Definitions;")
Set rstApplicationTableFieldDefinitions = dbs.OpenRecordset("Application_Table_Field_Definitions", , dbAppendOnly)
varRtn = SysCmd(acSysCmdInitMeter, "Processing tables...", dbs.TableDefs.Count)

For Each tdfDetails In dbs.TableDefs

If (tdfDetails.Attributes And (dbSystemObject Or dbHiddenObject)) = 0 And _
tdfDetails.Name <> "Application_Table_Field_Definitions" Then

intPrimaryKeyCount = 0

For Each idxDetails In tdfDetails.Indexes

If idxDetails.Primary Then

For Each fldDetails In idxDetails.Fields

If intPrimaryKeyCount = 10 Then Exit For

intPrimaryKeyCount = intPrimaryKeyCount + 1

strPrimaryKeyNames(intPrimaryKeyCount) = fldDetails.Name

Next fldDetails

Exit For

End If

Next idxDetails

For Each fldDetails In tdfDetails.Fields

rstApplicationTableFieldDefinitions.AddNew

rstApplicationTableFieldDefinitions!txtTable_Name = tdfDetails.Name

rstApplicationTableFieldDefinitions!intSequence = fldDetails.OrdinalPosition

rstApplicationTableFieldDefinitions!txtField_Name = fldDetails.Name

rstApplicationTableFieldDefinitions!txtDescription = fldDetails.Properties("Description")

For intPrimaryKeyCounter = 1 To intPrimaryKeyCount

If fldDetails.Name = strPrimaryKeyNames(intPrimaryKeyCounter) Then

rstApplicationTableFieldDefinitions!fPKey = True

Exit For

End If

Next intPrimaryKeyCounter

Select Case fldDetails.Type

Case dbBoolean

rstApplicationTableFieldDefinitions!txtType = "Yes/No"
rstApplicationTableFieldDefinitions!intLength = 1

Case dbByte

rstApplicationTableFieldDefinitions!txtType = "Byte"
rstApplicationTableFieldDefinitions!intLength = 1

Case dbInteger

rstApplicationTableFieldDefinitions!txtType = "Integer"
rstApplicationTableFieldDefinitions!intLength = 2

Case dbLong

If (fldDetails.Attributes And dbAutoIncrField) Then

rstApplicationTableFieldDefinitions!txtType = "Auto Number"

Else

rstApplicationTableFieldDefinitions!txtType = "Long Integer"

End If

rstApplicationTableFieldDefinitions!intLength = 4

Case dbCurrency

rstApplicationTableFieldDefinitions!txtType = "Currency"
rstApplicationTableFieldDefinitions!intLength = 8

Case dbSingle

rstApplicationTableFieldDefinitions!txtType = "Single"
rstApplicationTableFieldDefinitions!intLength = 4

Case dbDouble

rstApplicationTableFieldDefinitions!txtType = "Double"
rstApplicationTableFieldDefinitions!intLength = 8

Case dbDate

rstApplicationTableFieldDefinitions!txtType = "Date/Time"
rstApplicationTableFieldDefinitions!intLength = 8

Case dbText

rstApplicationTableFieldDefinitions!txtType = "Text"
rstApplicationTableFieldDefinitions!intLength = fldDetails.Size

Case dbLongBinary

rstApplicationTableFieldDefinitions!txtType = "OLE Object"

Case dbMemo

rstApplicationTableFieldDefinitions!txtType = "Memo"

Case dbGUID

rstApplicationTableFieldDefinitions!txtType = "Replication ID"
rstApplicationTableFieldDefinitions!intLength = 16

Case Else

rstApplicationTableFieldDefinitions!intType = "Unknown"

End Select

rstApplicationTableFieldDefinitions.Update

Next fldDetails

End If

intProgressBarCounter = intProgressBarCounter + 1

varRtn = SysCmd(acSysCmdUpdateMeter, intProgressBarCounter)

Next tdfDetails

varRtn = SysCmd(acSysCmdClearStatus)

rstApplicationTableFieldDefinitions.Close
dbs.Close
Set rstApplicationTableFieldDefinitions = Nothing
Set dbs = Nothing

DoCmd.Close acForm, Me.FormName, acSaveNo

End Sub


Allan
 

Rats

Registered User.
Local time
Tomorrow, 02:05
Joined
Jan 11, 2005
Messages
151
Wow, it's never as simple as you think. Thanks for the response that piece of code will come in very handy.

Merry Christmas
 

nharrison

Registered User.
Local time
Today, 13:05
Joined
Jun 11, 2009
Messages
55
In line 19 you have an error, there is an arbitrary space in the word "Definitions"

Code:
Set rstApplicationTableFieldDefinitions = dbs.OpenRecordset("Application_Table_Field_[B]Definit  ions[/B]", , dbAppendOnly)

For all those copy and pasters out there...
 

alktrigger

Aimless Extraordinaire
Local time
Today, 14:05
Joined
Jun 9, 2009
Messages
124
I love it when people use the search function! good work nharrison
 

raskew

AWF VIP
Local time
Today, 13:05
Joined
Jun 2, 2001
Messages
2,734
Hi -

This will create/recreate tblFields and populate it with table/field info for all tables in your data base. After copying/pasting it to a standard module, name immaterial so long as it's not the name of one of the subs, invoke it by entering Call GetField2Description from the debug window.

Code:
Sub GetField2Description()
'**********************************************************
'Purpose:   1) Deletes and recreates a table (tblFields)
'           2) Queries table MSysObjects to return names of
'              all tables in the database
'           3) Populates tblFields
'Coded by:  raskew
'Inputs:    From debug window:
'           Call GetField2Description
'Output:    See tblFields
'**********************************************************

Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Test As String, NameHold As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field, strSQL As String
n = 0
Set db = CurrentDb
' Trap for any errors.
    On Error Resume Next
tName = "tblFields"

'Does table "tblFields" exist?  If true, delete it;
docmd.SetWarnings False
   docmd.DeleteObject acTable, "tblFields"
docmd.SetWarnings True
'End If
'Create new tblTable
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"

strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
   ' Get number of records in recordset
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
End If

Set rs2 = db.OpenRecordset("tblFields")

For i = 0 To n - 1
  fielddescription = " "
  Set td = db.TableDefs(i)
    'Skip over any MSys objects
    If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
       NameHold = rs!Name
       On Error Resume Next
       For Each fld In td.Fields
          fielddescription = fld.Name
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = NameHold
          rs2!FieldName = fielddescription
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes
          rs2!FldDescription = fld.Properties("description")
          rs2.Update
       Next fld
  
       Resume Next
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub

Function FieldType(intType As Integer) As String

Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"    '1
    Case dbByte
        FieldType = "dbByte"       '2
    Case dbInteger
        FieldType = "dbInteger"    '3
    Case dbLong
        FieldType = "dbLong"       '4
    Case dbCurrency
        FieldType = "dbCurrency"   '5
    Case dbSingle
        FieldType = "dbSingle"     '6
    Case dbDouble
        FieldType = "dbDouble"     '7
    Case dbDate
        FieldType = "dbDate"       '8
    Case dbBinary
        FieldType = "dbBinary"     '9
    Case dbText
        FieldType = "dbText"       '10
    Case dbLongBinary
        FieldType = "dbLongBinary" '11
    Case dbMemo
        FieldType = "dbMemo"       '12
    Case dbGUID
        FieldType = "dbGUID"       '15
End Select

End Function

HTH - Bob
 
Last edited:

Atomic Shrimp

Humanoid lifeform
Local time
Today, 18:05
Joined
Jun 16, 2000
Messages
1,954
In line 19 you have an error, there is an arbitrary space in the word "Definitions"
That's the forum software, I expect - it inserts spaces into long unbroken text to prevent a horizontal scrollbar appearing in the whole browser window.

For example, this was typed without spaces:

ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ

Putting it inside code tags preserves the text, because the horizontal scrollbar only affects the code box:

Code:
ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ
 

chipcain

New member
Local time
Today, 13:05
Joined
Sep 1, 2009
Messages
7
Just wanted to say thanks to Bob (raskew). This is exactly what I was looking for and it worked like a charm!

-chip
 

Monsora83

Registered User.
Local time
Today, 14:05
Joined
May 16, 2011
Messages
41
Hi -

This will create/recreate tblFields and populate it with table/field info for all tables in your data base. After copying/pasting it to a standard module, name immaterial so long as it's not the name of one of the subs, invoke it by entering Call GetField2Description from the debug window.

Code:
Sub GetField2Description()
'**********************************************************
'Purpose:   1) Deletes and recreates a table (tblFields)
'           2) Queries table MSysObjects to return names of
'              all tables in the database
'           3) Populates tblFields
'Coded by:  raskew
'Inputs:    From debug window:
'           Call GetField2Description
'Output:    See tblFields
'**********************************************************
 
Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Test As String, NameHold As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field, strSQL As String
n = 0
Set db = CurrentDb
' Trap for any errors.
    On Error Resume Next
tName = "tblFields"
 
'Does table "tblFields" exist?  If true, delete it;
docmd.SetWarnings False
   docmd.DeleteObject acTable, "tblFields"
docmd.SetWarnings True
'End If
'Create new tblTable
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"
 
strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"
 
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
   ' Get number of records in recordset
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
End If
 
Set rs2 = db.OpenRecordset("tblFields")
 
For i = 0 To n - 1
  fielddescription = " "
  Set td = db.TableDefs(i)
    'Skip over any MSys objects
    If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
       NameHold = rs!Name
       On Error Resume Next
       For Each fld In td.Fields
          fielddescription = fld.Name
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = NameHold
          rs2!FieldName = fielddescription
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes
          rs2!FldDescription = fld.Properties("description")
          rs2.Update
       Next fld
 
       Resume Next
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub
 
Function FieldType(intType As Integer) As String
 
Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"    '1
    Case dbByte
        FieldType = "dbByte"       '2
    Case dbInteger
        FieldType = "dbInteger"    '3
    Case dbLong
        FieldType = "dbLong"       '4
    Case dbCurrency
        FieldType = "dbCurrency"   '5
    Case dbSingle
        FieldType = "dbSingle"     '6
    Case dbDouble
        FieldType = "dbDouble"     '7
    Case dbDate
        FieldType = "dbDate"       '8
    Case dbBinary
        FieldType = "dbBinary"     '9
    Case dbText
        FieldType = "dbText"       '10
    Case dbLongBinary
        FieldType = "dbLongBinary" '11
    Case dbMemo
        FieldType = "dbMemo"       '12
    Case dbGUID
        FieldType = "dbGUID"       '15
End Select
 
End Function

HTH - Bob

Would anyone know if there is a way to make this work for linked tables as well? I only recieve results from tables within my access db (tried on 2 seperate db's).
 

JANR

Registered User.
Local time
Today, 19:05
Joined
Jan 21, 2009
Messages
1,623
...
Code:
strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1 [B][COLOR=red]Or MSysObjects.Type=6[/COLOR][/B])"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"
...

Add the Bold red part to get Linked Tables.

JR
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:05
Joined
Jan 20, 2009
Messages
12,849
This reminds me of doing something similar once and piqued a curiosity that I had never followed up. I tried to get the field data type name using the TypeName function (which only works for variables).

TypeName(currentdb.TableDefs("sometable").Fields(0))

The answer is the same for every field I tried in Access2007: Field2

No doubt it is the TypeName of the field object and I imagine different version of Access could give a different result. Anyone know what defines the number in the Field TypeName?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Sep 12, 2006
Messages
15,614
Galaxiom

these are the field type values. Not sure what the missing ones are.

fldtypes(1) = "YesNo"
fldtypes(2) = "Byte"
fldtypes(3) = "Integer"
fldtypes(4) = "Long"
fldtypes(5) = "Currency"
fldtypes(6) = "Single"
fldtypes(7) = "Double"
fldtypes(8) = "Date"
fldtypes(9) = "Binary"
fldtypes(10) = "Text"
fldtypes(11) = "OLE" 'longbinary?
fldtypes(12) = "Memo"
fldtypes(13) = "13"
fldtypes(14) = "14"
fldtypes(15) = "ReplicationID" 'GUID
fldtypes(16) = "16"
fldtypes(17) = "17"
fldtypes(18) = "18"
fldtypes(19) = "19"
fldtypes(20) = "20"

'the red entries were taken from BobAskew's post above, which deals with similar stuff
is an OLE a longbinary?


it comes from field.type (numeric value) in the fields collection of the tabledef object.

I know that will make sense to you.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:05
Joined
Jan 20, 2009
Messages
12,849
it comes from field.type (numeric value) in the fields collection of the tabledef object.

These are the field datatypes and are returned by:
Code:
currentdb.TableDefs("sometable").Fields(0).Type

Do a TypeName on that expression and you get "Integer" because it is the TypeName of the enumerated numeric data type of the field.

This is different. Field2 is the TypeName of the Field object itself in both accdb and mdb files in Access 2007.

It hadn't occurred to me that field object itself had a TypeName which is why I originally expected to get the field DataTypeName.

I don't know what expression could return what I would anticipate is an integer from the field object enumerated type because the Type property of the field returns the field's Data Type.

Try it in the Immediate window:

Code:
? TypeName(currentdb.TableDefs("sometable").Fields(0))

I would be curious what it returns in the earlier version of Access.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Sep 12, 2006
Messages
15,614
No - i think you are misunderstanding. There is a numeric item "type", which is one item in the fields collection in the tabledef object. The number indicates the type of the field/object. I am not sure if there are constants for the object types. So the type of the type field is numeric. BUt the value of the type field has meaning.

eg I just did this

MsgBox (CurrentDb.TableDefs("sometable").Fields("somefield").Type)

it showed 10, which is a text field.


Depending on the type, other items in the collection can have a value or not. eg for text there is length field, which is meaningless for numbers.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:05
Joined
Jan 20, 2009
Messages
12,849
No - i think you are misunderstanding. There is a numeric item "type", which is one item in the fields collection in the tabledef object.

Dave, I am not misunderstanding at all and I am well aware that the Type property of a field is the datatype of the field (an Integer). Indeed I posted similar line of code that you did.

But did you try the other expression I posted?

Code:
TypeName(Currentdb.TableDefs("sometable").Fields(0))

What I am saying is that when the TypeName function is applied directly to the field object, it comes up with a surprising result. "Field2"

The TypeName function can be applied to any variable, object or collection and it comes up with what you would expect.

Database for TypeName(Currentdb)
TableDef for TypeName(Currentdb.TableDefs("sometable"))

I was just wondering what was the story with the TypeName of a Field returning "Field2". What is with the 2?

I guess A95 might return "Field" and A2000 return "Field1". It is just one of those curiosities that one stumbles upon when entering something that doens do what we first expect.
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:05
Joined
Jan 23, 2006
Messages
15,364

Big Norm

New member
Local time
Today, 14:05
Joined
Aug 16, 2011
Messages
1
I typed in Allan's program to Print a list of table Field names . I am using Access 2000 and the program did not understand "Dim db As Database". How do I accomplish this with Access 2000.
 

DonLeverton

Registered User.
Local time
Today, 12:05
Joined
Apr 9, 2016
Messages
10
I have "rolled my own" version of this using the code supplied in this thread by raskew combined with some of Allen Browne's code which does much the same.

I was having some problems with raskew's code (I'm using Access 2016 and DAO)
Code:
'---------------------------------------------------------------------------------------
' Module    : modGetFieldInfo
' Author    : Don Leverton
'             Modified sample code from 2 different sources
'             raskew - ***** t=99194
'             Allen Browne - many thanks on many occasions! ****allenbrownedotcomslashfunc-06dothtml****
' Date      : 9/13/2016
' Purpose   : ' Loops through all local tables in the DB and gathers all field names, types, sizes and descriptions ...
'           : ' Then writes the resulting info to a table, which can then be used for a report.
'---------------------------------------------------------------------------------------

Option Compare Database
Option Explicit

Function TableInfo()
On Error GoTo TableInfoErr
    ' Credits for this concept and code excerpts are in the module header.
    ' Purpose:   Record the field names, types, sizes and descriptions for every table in the current DB.
    '            then write this info to records in "tblFields"
    
   Dim MyDB As DAO.Database
   Dim tdf As DAO.TableDef
   Dim fld As DAO.Field
   Dim rst As DAO.Recordset
   Dim MyTable As String
   Set MyDB = CurrentDb()
   Dim MyTest As String
      
    'Does table "tblFields" exist?
    MyTest = MyDB.TableDefs("tblFields").Name
    ' If "tblfields" DOES NOT exist it will throw error #3265, which is trapped in the error handler below,
    ' and the code will resume at the "Create Table:" line label.
    
    'If "tblfields" DOES exist, delete and re-create it.

      DoCmd.SetWarnings False
      DoCmd.DeleteObject acTable, "tblFields"
      DoCmd.SetWarnings True
    
CreateTable:
    'Create new "tblFields"
    MyDB.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"
    MyDB.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    
    'Now that we know we have a fresh, blank copy of the table, we can open it's Recordset and populate it.
    Set rst = MyDB.OpenRecordset("tblFields", dbOpenDynaset)
   
For Each tdf In MyDB.TableDefs
    MyTable = tdf.Name
    If Left(MyTable, 4) <> "MSys" And Left(MyTable, 1) <> "~" And MyTable <> "tblFields" Then

        With rst
                For Each fld In tdf.Fields
                    .AddNew
                        !Object = tdf.Name
                        !FieldName = fld.Name
                        !FieldType = FieldTypeName(fld)
                        !FieldSize = fld.Size
                        !FieldAttributes = fld.Attributes
                        !FldDescription = GetDescrip(fld)
                    .Update
                Next fld
        End With
        
    End If
Next tdf


TableInfoExit:
    Set rst = Nothing
    Set MyDB = Nothing
Exit Function

TableInfoErr:
   Select Case Err
   Case 3265     'If this error is thrown, it means that "tblFields" does not exist.
        GoTo CreateTable
   Case Else
        Debug.Print "TableInfo() Error " & Err & ": " & Error
   End Select
   Resume TableInfoExit
End Function


Function GetDescrip(obj As Object) As String
    On Error Resume Next
    GetDescrip = obj.Properties("Description")
End Function


Function FieldTypeName(fld As DAO.Field) As String
    'Purpose: Converts the numeric results of DAO Field.Type to text.
    Dim strReturn As String    'Name to return

    Select Case CLng(fld.Type) 'fld.Type is Integer, but constants are Long.
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        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 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"        '(no interface)
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15

        'Attached tables only: cannot create these in JET.
        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 = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23

        'Constants for complex types don't work prior to Access 2007 and later.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.Type & " unknown"
    End Select

    FieldTypeName = strReturn
End Function
 

Users who are viewing this thread

Top Bottom