Trying to insert multiple rows

[malhavoc]

I'm trying to insert multiple rows into a table at once, but I'm having problems.

I've tried a syntax like this:

INSERT INTO

( [FIELD1], [FIELD2], [FIELD3] ) VALUES
(( '1', 'A', '1' ),( '2', 'B', '2' ));

..but with no success.


On a site I even found this other way, but with no success either...

INSERT INTO

SELECT '1', 'A', '1'
UNION ALL
SELECT '2', 'B', '2';

Is it possible to insert multiple rows and I'm just missing the syntax, or is it not possible with Access?

 

[databasedonr]

Hey there,

Don't know how long you've been waiting for an answer, but I was trying to do this very thing and saw your thread.

If this helps, this is what I did to achieve the same results

In a VBA Module, I created a function, with the entires like so:

Function InsertValues()
CurrentDB().Execute "INSERT INTO TABLE (Field1, Field2) VALUES ('ValueA', 'ValueB')", dbFailOnError
CurrentDB().Execute "INSERT INTO TABLE (Field1, Field2) VALUES ('ValueC', 'ValueD')", dbFailOnError
End Function

You have to add each row as a separate command.

Once you've written the function, just call it. This is a bit tedious, but the only way I've found to do this. I've used it successfully to populate test data and also to distribute data for look-up tables.

That said, there's loads of smarter guys than me out there; maybe someone can weigh in on a simpler way?