How can i make a copy of a table thru code?

[Mota]

Hi;
I want to make a table in my DB,based on another table.I used the "SELECT
......INTO" statement of SQL,but the field properties in the new made table
are not the same.For example,the Primary Key of the base table (that is just
a sample and empty table) would not go to the new table.Also for the "Allow
zero legth","Default Value" properties and so on.
I tried CreateTableDef method of the Current database in DAO,but i couldnt
work with it and failed.In fact,when i want to append new TableDef to its
collection,an error says that new TableDef has no Fields,and when i try to
append fields of the sample Table to it,another error says that an object
(Field) with this name already exist in the new TableDef.In addition,im not
sure of Field Properties inheritance in this method.
Can anyone please help me to make a table based on another one,just like its
parent,in the field properties?
I use Access 2000.
Thank you in advance.

 

[Graham R Seach]

Mota,

You can try either the CopyObject or TransferDatabase methods of the DoCmd
object.

DoCmd.CopyObject CurrentDb.Name, "tblMyNewTable", acTable,
"tblMyExistingTable"

DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name,
acTable, "tblMyExistingTable", "tblMyNewTable", False

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[MacDermott]

While it is possible to do this programmatically, it's pretty rare for this
to be a good database design.
Putting the same data into two tables leaves you no reliable way to keep the
two copies synchronized.

 

[Mota]

I dont want to Synch them.
Thank you for ur comment.

 

[Mota]

Thank you for ur help.

Mota,

You can try either the CopyObject or TransferDatabase methods of the DoCmd
object.

DoCmd.CopyObject CurrentDb.Name, "tblMyNewTable", acTable,
"tblMyExistingTable"

DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb.Name,
acTable, "tblMyExistingTable", "tblMyNewTable", False

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html