Fordraiders
asked on
Must have Field Length
I have the following function returning a newtable "Table1" with definitions from another table "GIS".
The new table contains the table definitions but I don't know the "FieldLength" of the table requested.
Is there any way to revise this code to include the
"Field Length" of the table that i'am requuesting information from.
******* Start of Code
Dim d as database
Set d = CurrentDB
Dim r as recordset
Dim f as Field
Dim t as tabledef
Set t = d.CreateTableDef("setup") 'definition table setup
' definition setup in new table
Set f = t.CreateField("FieldName", dbText, 50)
t.Fields.Append f
Set f = t.CreateField("FieldType", dbText, 15)
t.Fields.Append f
Set f = t.CreateField("Comments", dbText, 150)
t.Fields.Append f
d.TableDefs.Append t
Set t = Nothing
Set r = d.OpenRecordset("Table1") 'NameOfDestTblHere
set t = d.Tabledefs("GIS") ' Requested information from this table
For each f in t.Fields
r.AddNew
r![FieldName] = f.Name
r![FieldType] = fType(f.Type)
r![Comments] = f.Properties("Description" ).Value
r.Update
Next f
r.Close
Set f = Nothing
Set t = Nothing
Set d = Nothing
******* End of code
Public Function fType(ByVal f As Long) As String
Select Case f 'Value
Case dbBoolean: fType = "Boolean" ' 1
Case dbByte: fType = "Byte" ' 2
Case dbInteger: fType = "Integer" ' 3
Case dbLong: fType = "Long" ' 4
Case dbCurrency: fType = "Currency" ' 5
Case dbSingle: fType = "Single" ' 6
Case dbDouble: fType = "Double" ' 7
Case dbDate: fType = "Date/Time" ' 8
Case dbBinary: fType = "Binary" ' 9
Case dbText: fType = "Text" ' 10
Case dbLongBinary: fType = "Long Binary (OLE Object)" ' 11
Case dbMemo: fType = "Memo" ' 12
Case dbGUID: fType = "GUID" ' 15
Case dbBigInt: fType = "Big Integer" ' 16
Case dbVarBinary: fType = "VarBinary" ' 17
Case dbChar: fType = "char" ' 18
Case dbNumeric: fType = "Numeric" ' 19
Case dbDecimal: fType = "Decimal" ' 20
Case dbFloat: fType = "Float" ' 21
Case dbTime: fType = "Time" ' 22
Case dbTimeStamp: fType = "Time Stamp" ' 23
Case Else: fType = "(Unknown)"
End Select
End Function
Thanks
fordraiders
The new table contains the table definitions but I don't know the "FieldLength" of the table requested.
Is there any way to revise this code to include the
"Field Length" of the table that i'am requuesting information from.
******* Start of Code
Dim d as database
Set d = CurrentDB
Dim r as recordset
Dim f as Field
Dim t as tabledef
Set t = d.CreateTableDef("setup") 'definition table setup
' definition setup in new table
Set f = t.CreateField("FieldName",
t.Fields.Append f
Set f = t.CreateField("FieldType",
t.Fields.Append f
Set f = t.CreateField("Comments", dbText, 150)
t.Fields.Append f
d.TableDefs.Append t
Set t = Nothing
Set r = d.OpenRecordset("Table1") 'NameOfDestTblHere
set t = d.Tabledefs("GIS") ' Requested information from this table
For each f in t.Fields
r.AddNew
r![FieldName] = f.Name
r![FieldType] = fType(f.Type)
r![Comments] = f.Properties("Description"
r.Update
Next f
r.Close
Set f = Nothing
Set t = Nothing
Set d = Nothing
******* End of code
Public Function fType(ByVal f As Long) As String
Select Case f 'Value
Case dbBoolean: fType = "Boolean" ' 1
Case dbByte: fType = "Byte" ' 2
Case dbInteger: fType = "Integer" ' 3
Case dbLong: fType = "Long" ' 4
Case dbCurrency: fType = "Currency" ' 5
Case dbSingle: fType = "Single" ' 6
Case dbDouble: fType = "Double" ' 7
Case dbDate: fType = "Date/Time" ' 8
Case dbBinary: fType = "Binary" ' 9
Case dbText: fType = "Text" ' 10
Case dbLongBinary: fType = "Long Binary (OLE Object)" ' 11
Case dbMemo: fType = "Memo" ' 12
Case dbGUID: fType = "GUID" ' 15
Case dbBigInt: fType = "Big Integer" ' 16
Case dbVarBinary: fType = "VarBinary" ' 17
Case dbChar: fType = "char" ' 18
Case dbNumeric: fType = "Numeric" ' 19
Case dbDecimal: fType = "Decimal" ' 20
Case dbFloat: fType = "Float" ' 21
Case dbTime: fType = "Time" ' 22
Case dbTimeStamp: fType = "Time Stamp" ' 23
Case Else: fType = "(Unknown)"
End Select
End Function
Thanks
fordraiders
ASKER
j,
Looking to create a new table with field definitions, (of another table)
instead of having to go into "Design View" of a table and find all the information about a specific table.
This new table will be constructed as the code suggests.
However, it does not go out and look at the "field length"
of text fields.
Hope this helps
Dave
Looking to create a new table with field definitions, (of another table)
instead of having to go into "Design View" of a table and find all the information about a specific table.
This new table will be constructed as the code suggests.
However, it does not go out and look at the "field length"
of text fields.
Hope this helps
Dave
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
J.Dettman,
Thanks
I can you the example and expand on the original code now.
Thanks
fordraiders
Thanks
I can you the example and expand on the original code now.
Thanks
fordraiders
Jim.