By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,213 Members | 2,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,213 IT Pros & Developers. It's quick & easy.

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

P: n/a
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL ("CREATE TABLE test_table AS SELECT * FROM test_table WHERE 1=2")
  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
Nov 23 '10 #1
Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
This should work:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as string
  2. strSQL="SELECT * INTO [New table name goes here]" & _
  3.        " FROM [Original Table Name goes here]" & _
  4.        " WHERE 1=2;"
  5. docmd.SetWarnings false
  6.   DoCmd.RunSql strSQL
  7. docmd.setwarnings True
Nov 23 '10 #2

P: n/a
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..
Nov 23 '10 #3

NeoPa
Expert Mod 15k+
P: 31,560
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.
Nov 23 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.