-
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
-
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.
