Copying Table Structures in VBA

[Bram Weiser]

Hello, Everyone,

I know that I can manually copy/paste a table's structure
(without data) into a new table object, but how can that
be done in VBA?

(The only thing I found in the Help system,
DoCmd.CopyObject, seemed to focus on copying the table as
a whole {i.e., with the data intact}. Sure, I could do
that, and then delete the data from the new table, but I
was wondering if/how I could specify that I wanted only
the structure to be copied/pasted in the first place.)

Does anyone have an idea about this? If so, please feel
free to post here for all to see, but also, please, e-mail
me at (e-mail address removed).

Thank you very much.

Sincerely,
Bram Weiser

 

[Allen Browne]

Dim strSQL As String
strSQL = "SELECT * INTO MyNewTable FROM MyTable WHERE (False);"
dbEngine(0)(0).Execute strSQL, dbFailOnError

 

[Dirk Goldgar]

Dim strSQL As String
strSQL = "SELECT * INTO MyNewTable FROM MyTable WHERE (False);"
dbEngine(0)(0).Execute strSQL, dbFailOnError

Note that Allen's SQL-based suggestion will copy the fields and basic
structure, but not indexes, field descriptions, or extended properties.
If you want a complete copy of the table, you can use the
TransferDatabase method:

DoCmd.TransferDatabase _
acExport, _
"Microsoft Access", _
CurrentDb.Name, _
acTable, _
"MyTable", _
"MyNewTable", _
True

 

[Bram Weiser]

Dear Allen,

Thank you very much. I'll give it a shot.

Sincerely,
Bram Weiser