Copying Fields Using DAO

[linus]

Hello. Is there any way I can copy a field from one existing table and append it to another existing table, using DAO???

Ex.
Table 1:
____________________
| Name | Olderthen50 |
--------------------
|John | Yes |
--------------------
|Frank | No |
--------------------

Is there anyway I could copy the field Olderthen50 (Which is a yes/no field), and append it to another table?

I would appreciate any help.
ps. It isn't necessary for the data to be copied. Just the Field.

 

[Durkin]

The following code should do what you're looking for. All properties should be the same for the new field. The reason the 'On Error Resume Next' line is in there is because some properties do not have values that can be accessed at that stage.

Private Sub AddColumn()
Dim Db As DAO.Database
Dim FromTDef As DAO.TableDef
Dim ToTDef As DAO.TableDef
Dim OldFld As DAO.Field
Dim NewFld As New DAO.Field
Dim Prop As DAO.Property
Dim i As Integer

'Point your object variables to the correct Access Objects
Set Db = CurrentDb
Set FromTDef = Db.TableDefs("Table1&quot
Set ToTDef = Db.TableDefs("Table2&quot
Set OldFld = FromTDef.Fields("Field1&quot

'Loop through the properties of the existing field
'and assign the same values to the corresponding
'properties in the new field
For Each Prop In OldFld.Properties
On Error Resume Next
NewFld.Properties(Prop.Name).Value = Prop.Value
On Error GoTo 0
Next Prop

'Append the new field to the second table
ToTDef.Fields.Append NewFld

'if you want to delete the original field use the
'following statement. Otherwise leave it commented out
'FromTDef.Fields.Delete OldFld.Name

End Sub

 

[Durkin]

Alternatively, I think I should mention, you can open a table in design view, click on the little button beside the field that highlights the whole line and copy and paste it into the second table. If you only want to copy a field once then this is probably the best way to go about it.

Durkin

 

[linus]

Hey thanks Durkin.
It worked great.
I already knew about doing this at table level, but thanks for the tip anyway.

Regards,

Martin