Insert Multiple Rows in Acceess in One SQL Statement

OK I was soooo close to giving up on this.

This works in Access. (Many say its impossible, but we are learning hacking skills here right?) XD

This is the problem: Access doesn't let you insert more than one record at a time in the one SQL statement, UNLESS the data is coming from another database query and not "just from hard-coded/explicit strings or numerical values" in the  query.

This is the solution: Trick Access into thinking the data is coming from a query.

I got the idea from 2 web pages. The first one said to try this, which I thought looked v hopeful:

https://stackoverflow.com/questions/6162982/insert-multiple-rows-using-one-insert-statement-in-access-2010

Create a table called OneRow with a single integer column. Insert one row.

Then:

INSERT INTO Production.UnitMeasure  SELECT 'FT2', 'Square Feet ', '20080923' FROM OneRow  UNION ALL SELECT 'Y', 'Yards', '20080923' FROM OneRow  UNION ALL SELECT 'Y3', 'Cubic Yards', '20080923' FROM OneRow

BUT even this wasn't good enough for Access, which (when done properly with the proper values) iy would work with only one row added (i.e. without the UNIONs there) but Access got stuck with an error on the UNION, like Access was saying "haha I see what you're trying to do here, and I'm not gonna let you" XD

SO from this page I got the idea to trick Access to make the triple query with the UNIONs look like it is really all the one query:

https://stackoverflow.com/questions/48828038/access-union-all-into-new-table

SELECT * FROM (SELECT * FROM RawTrade1801  UNION ALL  SELECT * FROM RawTrade1802);

See how the SELECT * FROM (     )   makes it appear like all the one query from the outside... This actually works.

Also you need to make the dummy table as mentioned above. My dummy table is called OneRow and it has only one row and one number field as the PK (not autonumber) and I just put a 1 in it, though you can prob use any value here.

This is the actual SQL which then works in my Access DB

INSERT INTO Dept
SELECT *
FROM (SELECT 14 AS did, 'Service' AS dname, 5 AS budget , 6 AS managerid FROM OneRow
UNION ALL SELECT 15, 'Space Flight', 3, 6 FROM OneRow
UNION ALL SELECT 16, 'Sales', 6, 7 FROM OneRow) ;

When you save this as a query and go back and look at it, Access will add some weird stuff at the end, but it still works (with or without the weird stuff there).

Problem solved ✅