I have this SQL statement:

INSERT INTO tblAnalysis
SELECT * FROM table2;

It generated the following error message and is highlighting "tblAnalysis
Compile error:
Expected: End of statement

Why??

I think you may have tried to drag the entire table (to be inserted) into the append row. Try specifying the field to be appended to (ie: SELECT tblName.chrFieldName).

Tim

Hi Tim,
What I want to do is INSERT the value of each field FROM Table2 INTO tblRGAAnalysis. Must I specify all the fields in the FROM table?

Thanks!!

Hi Tim,
What I want to do is INSERT the value of each field FROM Table2 INTO tblRGAAnalysis. Must I specify all the fields in the FROM table?

Thanks!!

must you specify all the fields in the FROM table? no, but if you don't, there has to be an exact match in the number of columns and their datatypes

in particular, if the receiving table has an autonumber, you will need to list the fields, because the autonumber column cannot have values fed into it, so it has to be excluded from the list of fields

rudy
http://rudy.ca/

What I forgot to mention is that I have 21 fields(values) in Table2 which I like to insert into tblAnayses that I created in SQL statement. I really prefer not to list all the fields in the table. Could you help?

Thanks!

Hello Rudy,
I have an array of 20 elements which I like to store it in a table.
The array function stored the values into each element(cell) of the array,
I created the table definition in which the values of array is to be stored in SQL statement. They are working fine.

But in the INSERT statement is not working. It is giving me a syntax error on "INSERT INTO tblRGAAnalysis" in the SQL statement.
I verified the Table2(FROM Table) table definition and values they matched up to TO table.



'table definition for receiving table
Public Sub cmdCreateTbl_Click()
Dim StrSQL As String

StrSQL = "Create Table tblRGAAnalysis "
StrSQL = StrSQL & "(Quarter TEXT, 0 NUMBER, 1 NUMBER, 2 NUMBER, 3 NUMBER, "
StrSQL = StrSQL & "4 NUMBER, 5 NUMBER, 6 NUMBER, 7 NUMBER, 8 NUMBER, "
StrSQL = StrSQL & "9 NUMBER, 10 NUMBER, 11 NUMBER, 12 NUMBER, 13 NUMBER, "
StrSQL = StrSQL & "14 NUMBER, 15 NUMBER, 16 NUMBER, 17 NUMBER, 18 NUMBER, "
StrSQL = StrSQL & "19 NUMBER, totRGA NUMBER);"

Set db = CurrentDb
db.Execute StrSQL
RefreshDatabaseWindow

INSERT INTO tblRGAAnalysis
SELECT * FROM Table2;

End sub

Could you help?

Thanks!!

i don't know that "sub" language (vb? asp?), only sql

but i bet it's your numeric column names

after all this time, i bet you could have typed the select list columns

try putting them in square brackets


rudy