Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 10th, 2010, 06:12 PM
Registered User
Join Date: Jan 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy 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

Similar Threads
Thread Thread Starter Forum Replies Last Post
delete a user form using vba sms Access VBA 2 June 27th, 2008 07:15 AM
creaing user form with vba sms Access VBA 3 June 13th, 2008 07:50 AM
INSERT into Access Database from VBA for Excel zuerlein Excel VBA 0 June 3rd, 2007 01:51 AM
How to insert bulk of data through form in databas ashuphp Beginning PHP 1 April 11th, 2007 07:36 AM
Bulk insert deyakhatib SQL Server 2000 6 March 4th, 2004 04:09 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.