Hello,
First @torreone has presented the correct solution to your problem and the comments should be converted to an actual answer.
But what is not covered is ‘why’ and how you could not find an answer with a search. The problem is with converting your statement into an actual string in basic. Normally this is simply surrounding the characters with quotes.
Your original statement working in Tools->SQL
:
insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null
Now create a string by surrounding with quotes:
SQL = "insert into "Ledenarchief" ("archlidid","Naam") select "lidid" + 1000 ,"Naam" from "Leden" where "Datum ontslag" is not null or "datum overlijden" is not null"
This creates a problem as once the second quote is found the string ends and the interpreter is looking for more basic syntax. Since Table and Fields names here need to be surrounded with quotes themselves, to be used within a string they need to be double quoted. For your string no other quoting is necessary and the result is:
SQL = "insert into ""Ledenarchief"" (""archlidid"",""Naam"") select ""lidid"" + 1000 ,""Naam"" from ""Leden"" where ""Datum ontslag"" is not null or ""datum overlijden"" is not null"
The need for quoting Table and Field names can be overcome depending upon the database used (you do not specify).
HSQLDB can use uppercase without quotes (no spaces). This can make SQL statements a lot easier to enter.