Getting field names for about 30 tables

[Andreww]

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

 

[SusanV]

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...

 

[Pieter Wijnen]

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

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...

--

 

[Andreww]

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

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





--
----------------------------------------
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

 

[Andreww]

Problem:

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

Run time error 3134

Syntax error in INSRT INTO statement.

Andy ideas?

 

[Douglas J. Steele]

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

 

[aaron.kempf]

if you used a real database -- like Access Data Projects; then it would
be as simple as 'select name from syscolumns where id = (select id from
sysobjects where name = 'myTableName')

there is a shortcut with object_id function but i dont care for that
shortcut; it just looks too wierd; it confuses people

-Aaron