How can I insert multiple rows in a sql query in ms access?

Question

I'm in a class that designs databases and I don't have any background in it. It's required for my major but it's not my particular discipline. We're using MS Access and in the SQL code I can't figure out how to insert multiple rows of data into a table in one query. Is there a way to do this?

INSERT INTO tblMEMBER ( MEMB_ID, MEMB_FNAME, MEMB_LNAME, MEMB_ADDR_L1, MEMB_ADDR_L2, MEMB_CITY, MEMB_STATE, MEMB_ZIP, MEMB_PRIPHONE, MEMB_ALTPHONE, MEMB_EMAIL, MEMB_SPONSOR, MEMB_JOINDATE, MEMB_SKILL1, MEMB_SKILL2 )
VALUES (1001, 'Jane', 'Smith', '200 Eagle Lane', '202 Eagle Lane', 'Sussex', 'WI', '53089', '262-341-8211', '262-565-2233', 'jsmith@yahoo.com', 'Karen Shelly', 02-16-2014, 'Cooking', null);  

This works by itself but then when I copy and paste it and then change the values, I just get an error saying that there are characters after the semicolon. Also, is it possible to insert the rows in the create table query?

So I realized I just didn't understand the syntax and that I needed to just make more queries. Thank you for your answers.

Answer 1

The short answer is that Microsoft Access neither supports multiple statements in the same batch, nor using a table value constructor like insert tbl (col) values (val1),(val2).

There are workarounds however, but they would require you to use VBA scripting. I'm pretty sure you can't insert data as a part of the create statement either (again, without using VBA).

Edit: I missed the duplicate post offering a possible solution.