error sql in vb

I have a access database, one of the tables in that DB is the main table and one of the tables is a imported table with new records that is inserted into the main table. Inserting records gives no problem, but when I want to know is , which records didnot get inserted. This is how i am trying to do this.

docmd.runsql "create table Recordserrortable ( column1,.....)
docmd.runsql "insert into maintable select * from importedtable"

So far al works, table created and records inserted. Recordserrortable is the table where i want the records to be put if they do not get inserted.
In the following code i get an error "Syntax error in FROM clause."

docmd.runsql "select * from importedtable minus select * from maintable and insert into Recordserrortable"

What is wrong with this code.

XY - I have to ask you. Minus? I've never seen this SQL Syntax before, although strange things are afoot in Access ("strange things are afoot a the Circle K!" I wonder how many people can remember that quote?). If you want to select rows that are not in one table, but are in the second one, then something along the following lines would be better.

"Insert into RecordsErrorTable Select * from importedtable JOIN maintable on importedtable.YourPrimaryField <> maintable.YourPrimaryField"

This syntax assumes that the values in the primary field of your imported table and your main table are the same, as well as their data type. I believe that this should give you the results that you want, although Access is a strange creature. So, if this doesn't work, then let me know and I'll try something else.

Minus is Oracle syntax, too bad it wasn't standard SQL

Fisherman , it doesn't work, i still get the message "syntax error FROM clause"

Try it without using an Explicit JOIN statement... try using a Where clause.. like so...


"Insert into RecordsErrorTable Select * from importedtable, maintable WHERE importedtable.YourPrimaryField <> maintable.YourPrimaryField"

or maybe

"Insert into RecordsErrorTable Select * from importedtable, maintable WHERE NOT importedtable.YourPrimaryField = maintable.YourPrimaryField"

if the = sign on the last one doesn't work right, try the 'LIKE' operator instead. Access SQL is screwy.. I've never liked working in it very much

Sorry Fisherman, both of the statements didn't work.
But i found the statement that works only i made it with access query wizard. And if i look at the SQL design of it i see

SELECT * FROM imported table LEFT JOIN maintable ON importedtable. primaryfield=maintable.primaryfield WHERE (((maintable.primaryfield) is null));

when i run this sql in vb like this

qry = "SELECT ..........."
docmd.runsql qry

I get an error message
"error 3063 while executing":
"Duplicate output destination 'primaryfield'.

what is wrong

well, I don't know if this would have any bearing, but if you cut and pasted that SQL Statement, then there is a space between maintable and Primary field

you're right, the script works and it must have been some kind of spacing in the syntax. So thanks anyway Fisherman.