Bulk INSERT into Access from VBA User Form
I built a 14 page Wizard with lots of questions. It is a survey style, and I want to insert this data in MS Access 2007. I read the chapter 20, starting Page 452.
I had to change the code to allow for variables names in the insert statement. Now I am stuck with the number of questions - error is too long statement from VBA error.
The INSERT command is something like:
objCommand.CommandText = " INSERT INTO Table" & _
"(FirstName, LastName)" & _
"VALUES(' " & strFirstName & " ', ' " & strLastName " ');"
I have 2 issues:
1. Is there a better way to use dynamic variables in the INSERT code above?
2. I have 70+ questions. If I follow the code above, VBE complains about too many & _ or too long statement...
I tried the above with 20 questions, which worked. Does someone know of a better way? Or how could I use 2 insert statements, BUT inserting into same row in MS Access - so same autonumber row, meaning first rows 1-20 are inserted, then rows 21-40 or something like that?
Thanks in advance