Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Getting field names for about 30 tables

Discussion in 'Information Technology' started by Andreww, Jul 28, 2006.

  1. Andreww

    Andreww
    Expand Collapse
    Guest

    Hi - I have about 30 tables which have been imported from text files
    (manually).

    They all have similar but generally not exactly the same structures.

    I want to export the structure - just field names - for all table into
    a single table together with the name of the table for all of my
    tables.

    I could do it manually but want to automate the process.

    eg table1 has name, postcode, target1
    table2 has full_name, postal_code, target1
    table3 has name,postcode,target_1

    What I want to end up with is a single table with:


    Rec Field1, Field2 Field3 Field4
    1 table1 name postcode target1
    2 table2 full_name postal_code target1
    3 table3 name postcode target_1

    Even if I could do it singularly I could manually put into 1 but would
    be good to do all together.


    I tried using the analyze, document but this just produces a printed
    report. If I could export that to excel/a table I could cobble the
    above together.

    The idea post doing this is to get all data into 1 table by mapping
    original field names onto a common convention.


    TIA

    Andrew
     
  2. Loading...


  3. SusanV

    SusanV
    Expand Collapse
    Guest

    There may be a better easier way to do this, but you *can* export the
    documenter report to a csv (excel) - it's fugly though - you'd have to clean
    it up and pivot it...
    --
    hth,
    SusanV

    "Andreww" <andrew.whittam@gmail.com> wrote in message
    news:1150900882.173801.242020@p79g2000cwp.googlegroups.com...
    > Hi - I have about 30 tables which have been imported from text files
    > (manually).
    >
    > They all have similar but generally not exactly the same structures.
    >
    > I want to export the structure - just field names - for all table into
    > a single table together with the name of the table for all of my
    > tables.
    >
    > I could do it manually but want to automate the process.
    >
    > eg table1 has name, postcode, target1
    > table2 has full_name, postal_code, target1
    > table3 has name,postcode,target_1
    >
    > What I want to end up with is a single table with:
    >
    >
    > Rec Field1, Field2 Field3 Field4
    > 1 table1 name postcode target1
    > 2 table2 full_name postal_code target1
    > 3 table3 name postcode target_1
    >
    > Even if I could do it singularly I could manually put into 1 but would
    > be good to do all together.
    >
    >
    > I tried using the analyze, document but this just produces a printed
    > report. If I could export that to excel/a table I could cobble the
    > above together.
    >
    > The idea post doing this is to get all data into 1 table by mapping
    > original field names onto a common convention.
    >
    >
    > TIA
    >
    > Andrew
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    LRe: Getting field names for about 30 tables

    Sub DocumentTableFields()
    Dim Db AS DAO.Database
    Dim TDef AS DAO.TableDef
    Dim Fld AS DAO.Field
    Dim FldCount as Long
    Dim insSQL As string
    Dim ValSQL as String

    Set Db = Access.CurrentDb()
    Db.Execute "DROP TABLE DBA_COLUMNS" 'Drop (or Delete from if so desired)"
    insSQL = "CREATE TABLE DBA_COLUMNS ( TABLENAME TEXT(50)"
    For FldCount = 1 To 10 ' Max NoOf Fields
    insSQL = insSQL & " , Field" & FldCount & " TEXT(50)"
    Next ' FldCount
    Db.Execute insSQL & ")", DAO.dbSeeChanges ' Create Table

    For Each Tdef In Db.TableDefs
    If VBA.Left(TDef.Name,4) <> "MSYS" And VBA.Left(Tdef.Name,1) <> "~" Then
    ' Skip system/temp tables
    insSQL = "INSERT INTO DBA_COLUMNS (TableName"
    ValSQL = "('" & Tdef.Name & "'"
    FldCount = 0
    For Each Fld In TDef.Fields
    insSQL = insSQL & ",Field" & FldCount +1
    ValSQL = ValSQL & ",'" & Fld.Name & "'"
    FldCount = FldCount +1
    Next ' Fld
    db.Execute insSQL & ") " & valSQL & ")", DAO.dbSeeChanges
    End If
    Next 'TDef
    End Sub


    Baring Typos & no Err Handling this should do it

    HTH

    Pieter

    "SusanV" <svanallen@nospam-mvps.org> wrote in message
    news:%23Rf6ZGUlGHA.5108@TK2MSFTNGP03.phx.gbl...
    > There may be a better easier way to do this, but you *can* export the
    > documenter report to a csv (excel) - it's fugly though - you'd have to
    > clean it up and pivot it...
    > --
    > hth,
    > SusanV
    >
    > "Andreww" <andrew.whittam@gmail.com> wrote in message
    > news:1150900882.173801.242020@p79g2000cwp.googlegroups.com...
    >> Hi - I have about 30 tables which have been imported from text files
    >> (manually).
    >>
    >> They all have similar but generally not exactly the same structures.
    >>
    >> I want to export the structure - just field names - for all table into
    >> a single table together with the name of the table for all of my
    >> tables.
    >>
    >> I could do it manually but want to automate the process.
    >>
    >> eg table1 has name, postcode, target1
    >> table2 has full_name, postal_code, target1
    >> table3 has name,postcode,target_1
    >>
    >> What I want to end up with is a single table with:
    >>
    >>
    >> Rec Field1, Field2 Field3 Field4
    >> 1 table1 name postcode target1
    >> 2 table2 full_name postal_code target1
    >> 3 table3 name postcode target_1
    >>
    >> Even if I could do it singularly I could manually put into 1 but would
    >> be good to do all together.
    >>
    >>
    >> I tried using the analyze, document but this just produces a printed
    >> report. If I could export that to excel/a table I could cobble the
    >> above together.
    >>
    >> The idea post doing this is to get all data into 1 table by mapping
    >> original field names onto a common convention.
    >>
    >>
    >> TIA
    >>
    >> Andrew
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4026 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  5. Andreww

    Andreww
    Expand Collapse
    Guest

    Re: LRe: Getting field names for about 30 tables

    Peter - Many thanks, I'll give it a go!


    Pieter Wijnen wrote:

    > Sub DocumentTableFields()
    > Dim Db AS DAO.Database
    > Dim TDef AS DAO.TableDef
    > Dim Fld AS DAO.Field
    > Dim FldCount as Long
    > Dim insSQL As string
    > Dim ValSQL as String
    >
    > Set Db = Access.CurrentDb()
    > Db.Execute "DROP TABLE DBA_COLUMNS" 'Drop (or Delete from if so desired)"
    > insSQL = "CREATE TABLE DBA_COLUMNS ( TABLENAME TEXT(50)"
    > For FldCount = 1 To 10 ' Max NoOf Fields
    > insSQL = insSQL & " , Field" & FldCount & " TEXT(50)"
    > Next ' FldCount
    > Db.Execute insSQL & ")", DAO.dbSeeChanges ' Create Table
    >
    > For Each Tdef In Db.TableDefs
    > If VBA.Left(TDef.Name,4) <> "MSYS" And VBA.Left(Tdef.Name,1) <> "~" Then
    > ' Skip system/temp tables
    > insSQL = "INSERT INTO DBA_COLUMNS (TableName"
    > ValSQL = "('" & Tdef.Name & "'"
    > FldCount = 0
    > For Each Fld In TDef.Fields
    > insSQL = insSQL & ",Field" & FldCount +1
    > ValSQL = ValSQL & ",'" & Fld.Name & "'"
    > FldCount = FldCount +1
    > Next ' Fld
    > db.Execute insSQL & ") " & valSQL & ")", DAO.dbSeeChanges
    > End If
    > Next 'TDef
    > End Sub
    >
    >
    > Baring Typos & no Err Handling this should do it
    >
    > HTH
    >
    > Pieter
    >
    > "SusanV" <svanallen@nospam-mvps.org> wrote in message
    > news:%23Rf6ZGUlGHA.5108@TK2MSFTNGP03.phx.gbl...
    > > There may be a better easier way to do this, but you *can* export the
    > > documenter report to a csv (excel) - it's fugly though - you'd have to
    > > clean it up and pivot it...
    > > --
    > > hth,
    > > SusanV
    > >
    > > "Andreww" <andrew.whittam@gmail.com> wrote in message
    > > news:1150900882.173801.242020@p79g2000cwp.googlegroups.com...
    > >> Hi - I have about 30 tables which have been imported from text files
    > >> (manually).
    > >>
    > >> They all have similar but generally not exactly the same structures.
    > >>
    > >> I want to export the structure - just field names - for all table into
    > >> a single table together with the name of the table for all of my
    > >> tables.
    > >>
    > >> I could do it manually but want to automate the process.
    > >>
    > >> eg table1 has name, postcode, target1
    > >> table2 has full_name, postal_code, target1
    > >> table3 has name,postcode,target_1
    > >>
    > >> What I want to end up with is a single table with:
    > >>
    > >>
    > >> Rec Field1, Field2 Field3 Field4
    > >> 1 table1 name postcode target1
    > >> 2 table2 full_name postal_code target1
    > >> 3 table3 name postcode target_1
    > >>
    > >> Even if I could do it singularly I could manually put into 1 but would
    > >> be good to do all together.
    > >>
    > >>
    > >> I tried using the analyze, document but this just produces a printed
    > >> report. If I could export that to excel/a table I could cobble the
    > >> above together.
    > >>
    > >> The idea post doing this is to get all data into 1 table by mapping
    > >> original field names onto a common convention.
    > >>
    > >>
    > >> TIA
    > >>
    > >> Andrew
    > >>

    > >
    > >

    >
    >
    >
    > --
    > ----------------------------------------
    > I am using the free version of SPAMfighter for private users.
    > It has removed 4026 spam emails to date.
    > Paying users do not have this message in their emails.
    > Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  6. Andreww

    Andreww
    Expand Collapse
    Guest

    Re: LRe: Getting field names for about 30 tables

    Problem:

    db.Execute insSQL & ") " & valSQL & ")", DAO.dbSeeChanges

    Run time error 3134

    Syntax error in INSRT INTO statement.

    Andy ideas?



    Andreww wrote:

    > Peter - Many thanks, I'll give it a go!
    >
    >
    > Pieter Wijnen wrote:
    >
    > > Sub DocumentTableFields()
    > > Dim Db AS DAO.Database
    > > Dim TDef AS DAO.TableDef
    > > Dim Fld AS DAO.Field
    > > Dim FldCount as Long
    > > Dim insSQL As string
    > > Dim ValSQL as String
    > >
    > > Set Db = Access.CurrentDb()
    > > Db.Execute "DROP TABLE DBA_COLUMNS" 'Drop (or Delete from if so desired)"
    > > insSQL = "CREATE TABLE DBA_COLUMNS ( TABLENAME TEXT(50)"
    > > For FldCount = 1 To 10 ' Max NoOf Fields
    > > insSQL = insSQL & " , Field" & FldCount & " TEXT(50)"
    > > Next ' FldCount
    > > Db.Execute insSQL & ")", DAO.dbSeeChanges ' Create Table
    > >
    > > For Each Tdef In Db.TableDefs
    > > If VBA.Left(TDef.Name,4) <> "MSYS" And VBA.Left(Tdef.Name,1) <> "~" Then
    > > ' Skip system/temp tables
    > > insSQL = "INSERT INTO DBA_COLUMNS (TableName"
    > > ValSQL = "('" & Tdef.Name & "'"
    > > FldCount = 0
    > > For Each Fld In TDef.Fields
    > > insSQL = insSQL & ",Field" & FldCount +1
    > > ValSQL = ValSQL & ",'" & Fld.Name & "'"
    > > FldCount = FldCount +1
    > > Next ' Fld
    > > db.Execute insSQL & ") " & valSQL & ")", DAO.dbSeeChanges
    > > End If
    > > Next 'TDef
    > > End Sub
    > >
    > >
    > > Baring Typos & no Err Handling this should do it
    > >
    > > HTH
    > >
    > > Pieter
    > >
    > > "SusanV" <svanallen@nospam-mvps.org> wrote in message
    > > news:%23Rf6ZGUlGHA.5108@TK2MSFTNGP03.phx.gbl...
    > > > There may be a better easier way to do this, but you *can* export the
    > > > documenter report to a csv (excel) - it's fugly though - you'd have to
    > > > clean it up and pivot it...
    > > > --
    > > > hth,
    > > > SusanV
    > > >
    > > > "Andreww" <andrew.whittam@gmail.com> wrote in message
    > > > news:1150900882.173801.242020@p79g2000cwp.googlegroups.com...
    > > >> Hi - I have about 30 tables which have been imported from text files
    > > >> (manually).
    > > >>
    > > >> They all have similar but generally not exactly the same structures.
    > > >>
    > > >> I want to export the structure - just field names - for all table into
    > > >> a single table together with the name of the table for all of my
    > > >> tables.
    > > >>
    > > >> I could do it manually but want to automate the process.
    > > >>
    > > >> eg table1 has name, postcode, target1
    > > >> table2 has full_name, postal_code, target1
    > > >> table3 has name,postcode,target_1
    > > >>
    > > >> What I want to end up with is a single table with:
    > > >>
    > > >>
    > > >> Rec Field1, Field2 Field3 Field4
    > > >> 1 table1 name postcode target1
    > > >> 2 table2 full_name postal_code target1
    > > >> 3 table3 name postcode target_1
    > > >>
    > > >> Even if I could do it singularly I could manually put into 1 but would
    > > >> be good to do all together.
    > > >>
    > > >>
    > > >> I tried using the analyze, document but this just produces a printed
    > > >> report. If I could export that to excel/a table I could cobble the
    > > >> above together.
    > > >>
    > > >> The idea post doing this is to get all data into 1 table by mapping
    > > >> original field names onto a common convention.
    > > >>
    > > >>
    > > >> TIA
    > > >>
    > > >> Andrew
    > > >>
    > > >
    > > >

    > >
    > >
    > >
    > > --
    > > ----------------------------------------
    > > I am using the free version of SPAMfighter for private users.
    > > It has removed 4026 spam emails to date.
    > > Paying users do not have this message in their emails.
    > > Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Re: LRe: Getting field names for about 30 tables

    db.Execute insSQL & ") VALUES(" & valSQL & ")", DAO.dbSeeChanges

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Andreww" <andrew.whittam@gmail.com> wrote in message
    news:1150907266.736802.303380@c74g2000cwc.googlegroups.com...
    > Problem:
    >
    > db.Execute insSQL & ") " & valSQL & ")", DAO.dbSeeChanges
    >
    > Run time error 3134
    >
    > Syntax error in INSRT INTO statement.
    >
    > Andy ideas?
    >
    >
    >
    > Andreww wrote:
    >
    >> Peter - Many thanks, I'll give it a go!
    >>
    >>
    >> Pieter Wijnen wrote:
    >>
    >> > Sub DocumentTableFields()
    >> > Dim Db AS DAO.Database
    >> > Dim TDef AS DAO.TableDef
    >> > Dim Fld AS DAO.Field
    >> > Dim FldCount as Long
    >> > Dim insSQL As string
    >> > Dim ValSQL as String
    >> >
    >> > Set Db = Access.CurrentDb()
    >> > Db.Execute "DROP TABLE DBA_COLUMNS" 'Drop (or Delete from if so
    >> > desired)"
    >> > insSQL = "CREATE TABLE DBA_COLUMNS ( TABLENAME TEXT(50)"
    >> > For FldCount = 1 To 10 ' Max NoOf Fields
    >> > insSQL = insSQL & " , Field" & FldCount & " TEXT(50)"
    >> > Next ' FldCount
    >> > Db.Execute insSQL & ")", DAO.dbSeeChanges ' Create Table
    >> >
    >> > For Each Tdef In Db.TableDefs
    >> > If VBA.Left(TDef.Name,4) <> "MSYS" And VBA.Left(Tdef.Name,1) <> "~"
    >> > Then
    >> > ' Skip system/temp tables
    >> > insSQL = "INSERT INTO DBA_COLUMNS (TableName"
    >> > ValSQL = "('" & Tdef.Name & "'"
    >> > FldCount = 0
    >> > For Each Fld In TDef.Fields
    >> > insSQL = insSQL & ",Field" & FldCount +1
    >> > ValSQL = ValSQL & ",'" & Fld.Name & "'"
    >> > FldCount = FldCount +1
    >> > Next ' Fld
    >> > db.Execute insSQL & ") " & valSQL & ")", DAO.dbSeeChanges
    >> > End If
    >> > Next 'TDef
    >> > End Sub
    >> >
    >> >
    >> > Baring Typos & no Err Handling this should do it
    >> >
    >> > HTH
    >> >
    >> > Pieter
    >> >
    >> > "SusanV" <svanallen@nospam-mvps.org> wrote in message
    >> > news:%23Rf6ZGUlGHA.5108@TK2MSFTNGP03.phx.gbl...
    >> > > There may be a better easier way to do this, but you *can* export the
    >> > > documenter report to a csv (excel) - it's fugly though - you'd have
    >> > > to
    >> > > clean it up and pivot it...
    >> > > --
    >> > > hth,
    >> > > SusanV
    >> > >
    >> > > "Andreww" <andrew.whittam@gmail.com> wrote in message
    >> > > news:1150900882.173801.242020@p79g2000cwp.googlegroups.com...
    >> > >> Hi - I have about 30 tables which have been imported from text files
    >> > >> (manually).
    >> > >>
    >> > >> They all have similar but generally not exactly the same structures.
    >> > >>
    >> > >> I want to export the structure - just field names - for all table
    >> > >> into
    >> > >> a single table together with the name of the table for all of my
    >> > >> tables.
    >> > >>
    >> > >> I could do it manually but want to automate the process.
    >> > >>
    >> > >> eg table1 has name, postcode, target1
    >> > >> table2 has full_name, postal_code, target1
    >> > >> table3 has name,postcode,target_1
    >> > >>
    >> > >> What I want to end up with is a single table with:
    >> > >>
    >> > >>
    >> > >> Rec Field1, Field2 Field3 Field4
    >> > >> 1 table1 name postcode target1
    >> > >> 2 table2 full_name postal_code target1
    >> > >> 3 table3 name postcode target_1
    >> > >>
    >> > >> Even if I could do it singularly I could manually put into 1 but
    >> > >> would
    >> > >> be good to do all together.
    >> > >>
    >> > >>
    >> > >> I tried using the analyze, document but this just produces a printed
    >> > >> report. If I could export that to excel/a table I could cobble the
    >> > >> above together.
    >> > >>
    >> > >> The idea post doing this is to get all data into 1 table by mapping
    >> > >> original field names onto a common convention.
    >> > >>
    >> > >>
    >> > >> TIA
    >> > >>
    >> > >> Andrew
    >> > >>
    >> > >
    >> > >
    >> >
    >> >
    >> >
    >> > --
    >> > ----------------------------------------
    >> > I am using the free version of SPAMfighter for private users.
    >> > It has removed 4026 spam emails to date.
    >> > Paying users do not have this message in their emails.
    >> > Get the free SPAMfighter here: http://www.spamfighter.com/len

    >
     

Share This Page