Solved

Must have Field Length

Posted on 2001-07-23
4
506 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:fordraiders
  • 2
  • 2
4 Comments
 
LVL 57
ID: 6309472
What's the end goal here?  To calculate storage requirements?  Not quite sure what your asking for/looking to do.

Jim.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 6309586
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
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 75 total points
ID: 6309872
You want to include the Size property of the field object:

r![Comments] = f.Properties("Description").Value & " Size: " & f.Size


Jim.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 6309998
J.Dettman,
Thanks
I can you the example and expand on the original code now.

Thanks
fordraiders
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now