Create an empty table that has the structure of another table - in VBA

[kopekiR]

Hi

I need to create a table that is empty, but has the data structure (i.e. the same columns) of another table. Both tables should be in the same DB.

I need this as part of my VBA code.

I searched the web and found this:

DoCmd.RunSQL ("CREATE TABLE test_table AS SELECT * FROM test_table WHERE 1=2")
 

It's not working.. The query itself doesn't even work in the query browser. Of course it doesn't work in VBA either. I get a message: "syntax error in create table statement".

What am I doing wrong?
Or else - is there a different solution direction to this?
Note that I need a solution in VBA for ms access, not for the query browser.

Many thanks
Rachel

[TheSmileyCoder]

This should work:

Dim strSQL as string
strSQL="SELECT * INTO [New table name goes here]" & _
       " FROM [Original Table Name goes here]" & _
       " WHERE 1=2;"
docmd.SetWarnings false
  DoCmd.RunSql strSQL
docmd.setwarnings True

[kopekiR]

Thank you, this works!!
From what I understand access will accept "select into" commands but not "create" commands.
Need to keep this in mind.
If anyone knows why this is, I'd love to know..

[NeoPa]

It's quite simple really Rachel. The SELECT INTO SQL string is in the correct format, whereas the CREATE TABLE SQL string isn't. Also, the CREATE TABLE SQL command is not a Make Table command. It creates the table structure only, with no facility for adding in any data.