"CREATE TABLE AS" (using ADODB Command Object)

[dave.j.thornton]

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

[Jason Lepack]

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, [e-mail] 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