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

"CREATE TABLE AS" (using ADODB Command Object)

P: n/a
I'm attempting to create a new table, and populate it using the fields
from two existing tables. The code is printed below. I get the error:
"Run-time error '-2147217900 (80040e14)': Syntax error in CREATE TABLE
statement." For what it's worth, when tested independently, the
"SELECT [...]" part of my CREATE TABLE statement works properly.

Sub test()

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "CREATE TABLE tbl1 (uid INTEGER PRIMARY KEY, foo
INTEGER)"
cmd.Execute , , adCmdText

cmd.CommandText = "INSERT INTO tbl1 (uid, foo) VALUES (1, 2)"
cmd.Execute , , adCmdText

cmd.CommandText = "CREATE TABLE tbl2 (uid INTEGER PRIMARY KEY, bar
INTEGER)"
cmd.Execute , , adCmdText

cmd.CommandText = "INSERT INTO tbl2 (uid, bar) VALUES (1, 3)"
cmd.Execute , , adCmdText

cmd.CommandText = "CREATE TABLE tbl3 AS (SELECT tbl1.[uid], tbl1.
[foo], tbl2.[bar] FROM tbl1 INNER JOIN tbl2 ON tbl1.[uid] = tbl2.
[uid])"
cmd.Execute , , adCmdText

End Sub

Jun 29 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You should be using SELECT INTO

This SQL will work:
SELECT
tbl1.foo,
tbl2.bar
INTO
tbl3
FROM
tbl1
INNER JOIN tbl2
ON tbl1.uid = tbl2.uid

On Jun 29, 4:35 pm, dave.j.thorn...@gmail.com wrote:
I'm attempting to create a new table, and populate it using the fields
from two existing tables. The code is printed below. I get the error:
"Run-time error '-2147217900 (80040e14)': Syntax error in CREATE TABLE
statement." For what it's worth, when tested independently, the
"SELECT [...]" part of my CREATE TABLE statement works properly.

Sub test()

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

cmd.CommandText = "CREATE TABLE tbl1 (uid INTEGER PRIMARY KEY, foo
INTEGER)"
cmd.Execute , , adCmdText

cmd.CommandText = "INSERT INTO tbl1 (uid, foo) VALUES (1, 2)"
cmd.Execute , , adCmdText

cmd.CommandText = "CREATE TABLE tbl2 (uid INTEGER PRIMARY KEY, bar
INTEGER)"
cmd.Execute , , adCmdText

cmd.CommandText = "INSERT INTO tbl2 (uid, bar) VALUES (1, 3)"
cmd.Execute , , adCmdText

cmd.CommandText = "CREATE TABLE tbl3 AS (SELECT tbl1.[uid], tbl1.
[foo], tbl2.[bar] FROM tbl1 INNER JOIN tbl2 ON tbl1.[uid] = tbl2.
[uid])"
cmd.Execute , , adCmdText

End Sub

Jun 29 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.