code to copy/paste a table structure only

[smiley0q0]

can anyone help me with the code to copy a table ('Tbl_A'), then paste it with a specific name ('Tbl_B') and only it's data structure, then delete the old one ('Tbl_A'), and rename the new one 'Tbl_B' to 'Tbl_A'.

make sense?

i tried to just delete the data in the table, but since there is over 400,000 rows, it takes FOREVER, and then tells me it only deleted a certain number of rows.

thank you for your help in advance
Smiley (-:

 

[Francescina]

The easiest way is creating a make-table query with the exact settings an re-using the SQL-code.

 

[smiley0q0]

can i set the primary keys doing that? if so, how?

 

[Francescina]

I fear that you need a minimum of programming knowledge in order to solve your problem. This means that you need to create a function in a module that does the job for you. Have a look at the online help: "docmd.runsql" is what you need.

One more hint:
If you delete the table with the 400.000 records the size of your MDB-file will NOT decrease. You need to close and compact the MDB to avoid its bloating.

 

[PaulF]

why not create a blank Table with the structure then just copy that to Tbl_A using the CopyObject Method, after Deleting the Original Tbl_A using the DeleteObject Method


DoCmd.CopyObject [destinationdatabase][, newname][, sourceobjecttype, sourceobjectname]

The following example uses the CopyObject method to copy the Employees table and give it a new name in the current database:

DoCmd.CopyObject, "Employees Copy", acTable, "Employees"

To Delete the Original TblA use DeleteObject
DoCmd.DeleteObject [objecttype, objectname]

DoCmd.DeleteObject acTable, "Tbl_A"

PaulF

 

[smiley0q0]

excellent Paul, that is what i ended up doing, and it works perfectly.

Thank you.