Appending tables using VBA

[azhu.uwaterloo]

Hello,
Say for example I have two huge tables I want to append together, out
of the 200 fields, 190 of them have exactly the same name and will be
mapped to exactly the same place, but the rest 10 do not have the same
name although they do have a corresponding mapping to the target table
(say field: "Last Name" mapped to field name "LName" and so on), now,
one way to write the SQL string is of course to map the field one by
one, but is there anyway I could just write out the mapping of the 10
fields that are different and save the work to write out the mapping
of the rest 190 fields? If I can do that, hhow should I write the VBA
Module? Thanks a lot!

 

[Douglas J. Steele]

Hello,
Say for example I have two huge tables I want to append together, out
of the 200 fields, 190 of them have exactly the same name and will be
mapped to exactly the same place, but the rest 10 do not have the same
name although they do have a corresponding mapping to the target table
(say field: "Last Name" mapped to field name "LName" and so on), now,
one way to write the SQL string is of course to map the field one by
one, but is there anyway I could just write out the mapping of the 10
fields that are different and save the work to write out the mapping
of the rest 190 fields?

No, it's an all-or-nothing proposition.

If I can do that, hhow should I write the VBA Module? Thanks a lot!

Given you only need to do it once, even doing it manually shouldn't be that
onerous.

You can get a list of the fields in a table using code like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs("NameOfTable")
For Each fldCurr In tdfCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr

 

[azhu.uwaterloo]

hello,
I am sorry but I forgot to mention that it is just one of the 10
similar tables I have to do...
Thanks for the answers though.