Create new fields in a table based off of fields in another table 
Author Message
 Create new fields in a table based off of fields in another table

Access 2000 - Does anyone have a module that will allow me
to create a new table based off of the fields from another
table.  I have about 30 tables I would like to combine
into 1 table with all the fields but not duplicated like
fields.  Any help would be appreciated.


Tue, 26 Oct 2004 02:46:40 GMT  
 Create new fields in a table based off of fields in another table
Sounds interesting... I'll write one.

Do you want to add ALL the tables, or have the option of
typing in ONLY the table names you want to combine?

Quote:
>-----Original Message-----
>Access 2000 - Does anyone have a module that will allow
me
>to create a new table based off of the fields from
another
>table.  I have about 30 tables I would like to combine
>into 1 table with all the fields but not duplicated like
>fields.  Any help would be appreciated.
>.



Tue, 26 Oct 2004 04:55:46 GMT  
 Create new fields in a table based off of fields in another table
How about All tables.  You can email it to me if you'd
like.
Thanks
Heidi
Quote:
>-----Original Message-----
>Sounds interesting... I'll write one.

>Do you want to add ALL the tables, or have the option of
>typing in ONLY the table names you want to combine?

>>-----Original Message-----
>>Access 2000 - Does anyone have a module that will allow
>me
>>to create a new table based off of the fields from
>another
>>table.  I have about 30 tables I would like to combine
>>into 1 table with all the fields but not duplicated like
>>fields.  Any help would be appreciated.
>>.

>.



Tue, 26 Oct 2004 21:47:14 GMT  
 Create new fields in a table based off of fields in another table
Fields only is easy - I thought you wanted to take the
data also.  That wouldn't be so easy.

Option Compare Database

Public Sub CombineAllTables()
On Error GoTo Err_CombineTables

    Dim dbs As Database
    Dim tdfNewTable As TableDef, tdfExistingTable As
TableDef
    Dim fldToAppend As Field, fldNewField As Field,
fldDuplicate As Field
    Dim OKtoAddField As Boolean

    Dim stNewTable As String

    Set dbs = CurrentDb                         ' set the
current database

    stNewTable = "MyNewTable"
    Set tdfNewTable = dbs.CreateTableDef(stNewTable)

    For Each tdfExistingTable In dbs.TableDefs  'Each Table
        If Left(tdfExistingTable.Name, 4) = "MSys" Then
GoTo Next_Table ' ignore these system tables
        Debug.Print tdfExistingTable.Name
        For Each fldToAppend In
tdfExistingTable.Fields  'Each Field in the existing
table ...
            With fldToAppend
                OKtoAddField = True
                Debug.Print .Name
                For Each fldDuplicate In
tdfNewTable.Fields ' Check to make sure it's not a
duplicate
                    If fldToAppend.Name =
fldDuplicate.Name Then OKtoAddField = False
                Next
                If OKtoAddField Then        ' if not a
duplicate it field name then add it
                    Set fldNewField =
tdfExistingTable.CreateField(.Name, .Type, .Size)
                    tdfNewTable.Fields.Append fldNewField
                End If
            End With
        Next
Next_Table:
    Next
    dbs.TableDefs.Append tdfNewTable    ' Append the new
table to the tabledefs collection
    dbs.TableDefs.Refresh               'refresh the
tabledefs

Exit_Sub:

    Exit Sub

Err_CombineTables:
    MsgBox Err.Description
    GoTo Exit_Sub
End Sub

Quote:
>-----Original Message-----
>How about All tables.  You can email it to me if you'd
>like.
>Thanks
>Heidi
>>-----Original Message-----
>>Sounds interesting... I'll write one.

>>Do you want to add ALL the tables, or have the option of
>>typing in ONLY the table names you want to combine?

>>>-----Original Message-----
>>>Access 2000 - Does anyone have a module that will allow
>>me
>>>to create a new table based off of the fields from
>>another
>>>table.  I have about 30 tables I would like to combine
>>>into 1 table with all the fields but not duplicated
like
>>>fields.  Any help would be appreciated.
>>>.

>>.

>.



Wed, 27 Oct 2004 02:56:06 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Create new field in existing table exactly like field in second table

2. updating fields in table from field in another table

3. VB3 appending a field to a table, then entering data into the new field

4. Fill field in one table from field in another table

5. How to modified existing field and add new field in existing table for Access database

6. Create table from a combined field in another table

7. Creating new fields in Access tables using ADO 2

8. need help please:joined tables,add new entries based on one table columns

9. need help please:joined tables,add new entries based on one table columns

10. table.field value calculated based on query

11. sorting records from 1 table into several based on a field value

12. Adding a field to a table based on CrossTab Query

 

 
Powered by phpBB® Forum Software