[AccessD] Field Names

Here is some code I use to populate a table with all the fields in all
the tables in a database with some info about the fields.

Option Compare Database

Sub TableNames()

    Set MyDb = CurrentDb()
    ' Set AllTableDefs to definitions of all tables in the database:
    Set AllTableDefs = MyDb.TableDefs
     ' Set tbl Table Definitions to save table name and field
information
    Set myds = MyDb.OpenRecordset("tbl Table Definitions", dbOpenTable)
    
    Dim Table_Name As String, Field_Name As String, Field_Size As
Integer, Field_Type As String
    
    For I = 0 To AllTableDefs.Count - 1
        'Get the name of the table and save it to a variable
        Table_Name = AllTableDefs(I).Name
        Set SingleTableDef = AllTableDefs(I)
        ' Display the properties of each field in the table:
        For J = 0 To SingleTableDef.Fields.Count - 1
           ' save the name of the field in the table selected to a
variable
            Field_Name = SingleTableDef.Fields(J).Name  'Name of field
            Field_Size = SingleTableDef.Fields(J).Size  'Size of field.
            Select Case SingleTableDef.Fields(J).Type   'Type of field.
                Case 1
                    Field_Type = "Byte"
                Case 3
                    Field_Type = "Integer"
                Case 4
                    Field_Type = "Long Integer"
                Case 5
                    Field_Type = "Currency"
                Case 6
                    Field_Type = "Single"
                Case 7
                    Field_Type = "Double"
                Case 8
                    Field_Type = "Date/Time"
                Case 10
                    Field_Type = "Text"
                Case 11
                    Field_Type = "OLE Object"
                Case 12
                    Field_Type = "Hyperlink"
                Case 16
                    Field_Type = "Replication ID"
                Case 20
                    Field_Type = "Decimal"
            End Select
           
           'Save the info about each filed to the table tbble Table
Definitions
            With myds
                .AddNew
                    ![Table_Name] = Table_Name
                    ![Field_Name] = Field_Name
                    ![Field_Size] = Field_Size
                    ![Field_Type] = Field_Type
                .Update
            End With
        Next 'next field in table
     Next 'next table

   End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Thursday, January 11, 2007 2:36 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Field Names

Thanks,

But I figured it out...I was just confused.

Thanks All,

mark


>From: "Mark A Matte" <markamatte at hotmail.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>Subject: [AccessD]  Field Names
>Date: Thu, 11 Jan 2007 20:15:48 +0000
>
>Hello All,(You to Keith)
>
>I know we've talked about it...but I can't remember where...I need to
get a
>list of all field names by table...I know I can use the analyzer...but
its
>not in a very userfriendly format.
>
>Ideas?
>
>Thanks,
>
>Mark A. Matte
>
>P.S...Don't be scared Keith...most likely if its close enough...we
won't
>have to see the cloud...just vaporize...sorry.
>     _____
>    /         \
>    |         |
>      \      /
>        \  /
>        /   \....Boom?
>
>_________________________________________________________________
>Communicate instantly! Use your Hotmail address to sign into Windows
Live
>Messenger now. http://get.live.com/messenger/overview
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Communicate instantly! Use your Hotmail address to sign into Windows
Live 
Messenger now. http://get.live.com/messenger/overview

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com