New table with structure only

[jl39775]

When I copy and paste a table a box appear asking me if I want to copy the structure only, structure or data, or append data to exisiting table. What I want to do is copy a table and only copy the structure and then rename the table. But, I want to do this with code. I do the following but I don't know how to prevent it from coping the data also. Once I create the new table with the structure only I will populate it by importing an excel file. I don't want to use ADO or DAO to delete the records in the new table unless this is the easiest way to resolve my problem. Will someone help?


DoCmd.CopyObject , "adActuals" & YYYYMM & "Final", acTable, "adActuals200107Final"


Thanks,

James

 

[Jacob Mathai]

Here is my thought.

If you use Transferdatabase with acImport, there is an option for "structure only'.

If the table already exists, you may want to delete the table first, and then Import.

Please hit the Help button to see an example.

 

[pdx_man]

Another thought would be to just CREATE the table using SQL.

From the Help File on CREATE TABLE:

Sub CreateTableX2()

	Dim dbs As Database

	' Modify this line to include the path to Northwind
	' on your computer.
	Set dbs = OpenDatabase("Northwind.mdb")

	' Create a table with three fields and a unique
	' index made up of all three fields.
	dbs.Execute "CREATE TABLE MyTable " _
		& "(FirstName TEXT, LastName TEXT, " _
		& "DateOfBirth DATETIME, " _
		& "CONSTRAINT MyTableConstraint UNIQUE " _
		& "(FirstName, LastName, DateOfBirth));"

dbs.Close

End Sub

 

[jl39775]

pdx_man,

you are thinking like me. That's what I did.

James