Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Must have Field Length

Posted on 2001-07-23
4
Medium Priority
?
514 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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