tableDefs (2002)

[jmnees]

I have the following function:

Function GetFieldNames(table As String) As Integer

Dim db As DAO.Database
Dim tdfTable As DAO.TableDef
Dim fldField As DAO.Field

Set db = CurrentDb()
Set tdfTable = db.TableDefs(table)

For Each fldField In tdfTable.Fields

Debug.Print fldField.Name

Next fldField

End Function

How do I get the data type and field size to print? I need a create table script to recreate the table structure in a SQL db. Any help would be appreciated.

[johnhutchison]

This code will print out all the properties of each field

For Each prp in fldField.Properties
if prp.Name <> "" then
debug.print prp.Name & " =" & prp
end if
Next prp


The data type and size are just two of the field's properies.

The code above is copied from Helene Feddema's book: DAO Object Model

[HansV]

Try this version:

Function GetFieldNames(table As String) As Integer
Dim db As DAO.Database
Dim tdfTable As DAO.TableDef
Dim fldField As DAO.Field
Dim strType As String

Set db = CurrentDb
Set tdfTable = db.TableDefs(table)

For Each fldField In tdfTable.Fields
Select Case fldField.Type
Case dbBoolean
strType = "Boolean"
Case dbByte
strType = "Byte"
Case dbCurrency
strType = "Currency"
Case dbDate
strType = "Date"
Case dbDouble
strType = "Double"
Case dbInteger
strType = "Integer"
Case dbLong
strType = "Long"
Case dbLongBinary
strType = "OLE"
Case dbMemo
strType = "Memo"
Case dbSingle
strType = "Single"
Case dbText
strType = "Text"
Case Else
strType = "Other type"
End Select
Debug.Print fldField.Name, strType, fldField.Size
Next fldField

Set fldField = Nothing
Set tdfTable = Nothing
Set db = Nothing
End Function

[jmnees]

Thanks Hans...