A SQL Eror! Please Help

[cantene]

Dear Friends,

My code is here:
sSQL = "INSERT INTO Stock VALUES " & _
"SELECT Ticker, Name FROM " & MyTableName & " " & _
"EXCEPT " & _
"SELECT Ticker, Name FROM Stock"

pSQL = "INSERT INTO Price VALUES " & _
"SELECT Ticker, Date, Open, High, Low, Close, Volume FROM " & MyTableName & " " & _
"EXCEPT " & _
"SELECT Ticker, Date, Open, High, Low, Close, Volume FROM Price"

Debug.Print sSQL
Debug.Print pSQL

accApp.DoCmd.RunSQL sSQL
accApp.DoCmd.RunSQL pSQL

The debug.print gives

INSERT INTO Price VALUES SELECT Ticker, Date, Open, High, Low, Close, Volume FROM Hongkong_02102001 EXCEPT SELECT Ticker, Date, Open, High, Low, Close, Volume FROM Price


What's wrong with my code?
Is it Access support the use of EXCEPT?
Thanks

[BruceG]

First, I do not believe Access supports the EXCEPT keyword. I've seen these kind of "set" operations in Oracle (with MINUS, INTERSECT, etc.) but not Access. You'll have to figure out some other way to exclude the unwanted rows.

Second, you're combining the two formats of the INSERT statement - you have to use one or the other. The formats are either:
(1) To insert one row into a table:
INSERT INTO Table (columns) VALUES (values)
- or -
(2) To insert any number of rows:
INSERT INTO Table (columns) SELECT query.

In your case, I believe you want to drop the word VALUES.