Variable datatype (1 Viewer)

[majhl]

I have a little bit of code to extract the name and description of each variable in the table I name and results are written into a temporary table (tblTemp). I would also like to extract the data type of each variable, but am stuck as to the VBA designation of this property. Can anyone help?

Thanks.

<Code>

Dim dbs As Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim intX As Integer

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("TableName")

Set rst = dbs.OpenRecordset("tblTemp")

For intX = 0 To 300

Set fld = tdf.Fields(intX)

rst.AddNew
rst!Name = fld.Properties!Name
rst!Desc = fld.Properties!Description
rst!Type = fld!Properties!????Datatype????

rst.Update

Next intX


</code>

 

[DJkarl]

I have a little bit of code to extract the name and description of each variable in the table I name and results are written into a temporary table (tblTemp). I would also like to extract the data type of each variable, but am stuck as to the VBA designation of this property. Can anyone help?

Thanks.

<Code>

Dim dbs As Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim intX As Integer

Set dbs = CurrentDb()
Set tdf = dbs.TableDefs("TableName")

Set rst = dbs.OpenRecordset("tblTemp")

For intX = 0 To 300

Set fld = tdf.Fields(intX)

rst.AddNew
rst!Name = fld.Properties!Name
rst!Desc = fld.Properties!Description
rst!Type = fld!Properties!????Datatype????

rst.Update

Next intX


</code>

Use the Field .Type property, it will return an integer that is part of the DAO.DataTypeEnum, you could just store that information, although the number 5 or 10 won't be much use unless you have those memorized.

You could also pass the type to a function like the one below to get the text name of the datatype.

debug.print GetTypeName(fld.Type)

Function GetTypeName(tVal As Integer) As String
Select Case tVal
    Case dao.DataTypeEnum.dbBigInt
        GetTypeName = "BigInt"
    Case dao.DataTypeEnum.dbBinary
        GetTypeName = "Binary"
    Case dao.DataTypeEnum.dbBoolean
        GetTypeName = "Boolean"
    Case dao.DataTypeEnum.dbByte
        GetTypeName = "Byte"
    Case dao.DataTypeEnum.dbChar
        GetTypeName = "Char"
    Case dao.DataTypeEnum.dbCurrency
        GetTypeName = "Currency"
    Case dao.DataTypeEnum.dbDate
        GetTypeName = "Date"
    Case dao.DataTypeEnum.dbDecimal
        GetTypeName = "Decimal"
    Case dao.DataTypeEnum.dbDouble
        GetTypeName = "Double"
    Case dao.DataTypeEnum.dbFloat
        GetTypeName = "Float"
    Case dao.DataTypeEnum.dbGUID
        GetTypeName = "GUID"
    Case dao.DataTypeEnum.dbInteger
        GetTypeName = "Integer"
    Case dao.DataTypeEnum.dbLong
        GetTypeName = "Long"
    Case dao.DataTypeEnum.dbLongBinary
        GetTypeName = "LongBinary"
    Case dao.DataTypeEnum.dbMemo
        GetTypeName = "Memo"
    Case dao.DataTypeEnum.dbNumeric
        GetTypeName = "Numeric"
    Case dao.DataTypeEnum.dbSingle
        GetTypeName = "Single"
    Case dao.DataTypeEnum.dbText
        GetTypeName = "Text"
    Case dao.DataTypeEnum.dbTime
        GetTypeName = "Time"
    Case dao.DataTypeEnum.dbTimeStamp
        GetTypeName = "TimeStamp"
    Case dao.DataTypeEnum.dbVarBinary
        GetTypeName = "VarBinary"
    Case Else
        GetTypeName = "Unknown"
End Select

 

[majhl]

Use the Field .Type property, it will return an integer that is part of the DAO.DataTypeEnum, you could just store that information, although the number 5 or 10 won't be much use unless you have those memorized.

That's brilliant DJKarl! Thanks.