We help IT Professionals succeed at work.

How to loop currentdb.execute INSERT INTO command in Access (VBA)

Medium Priority
1,173 Views
Last Modified: 2012-05-11
Hello,

I am transferring data from one of my MDB tables into another (originally imported from a csv).
The code I have works, except it only moves over the data from the first record.  For example, if I have three records to move over, it only sends one over.  What's wrong with this?

Currentdb.Execute "INSERT INTO tblInput([Field1], [Field2]) SELECT [Field1], [Field2] FROM tblTransfer
Currentdb.Execute "DELETE * FROM tblTransfer

Open in new window


where tblTransfer is a temp table where values are held until completion of import.
Comment
Watch Question

Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the quick reply.  However, it doesn't seem to recognize dbFailOnError.
The Intellsense is not capitalizing the letters.

Is that the exact syntax?  You don't have a closing quote or nextline symbol _
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Sorry ... typo - it was inside the double quotes .... this will work.

CurrentDb.Execute "INSERT INTO tblInput([Field1], [Field2]) SELECT [Field1], [Field2] FROM tblTransfer", dbFailOnError

Author

Commented:
OK. Thanks.  No error seems to be occurring.  What would be the next step?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
ok.  What happens if you run that SQL in the Access query designer directly ?

mx

Author

Commented:
Invalid SQL Statement:  Expected DELETE, INSERT PROCEDURE, SELECT or UPDATE required.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Well, here is a generic example:

INSERT INTO Table2 ( FIELD1, FIELD2 ) SELECT Table1.FIELD1, Table1.FIELD2 FROM Table1;

Author

Commented:
Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 field(s) to null due to a type conversion failure and it didn't add 0 record(s) to the table due to key violations, etc.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
None of this is making sense. Weird.

Can you upload the db?

mx

Author

Commented:
It's on a Server - I'm remoting in.  Plus I'm really not supposed to.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
This error message:

"Microsoft Access set 0 field(s) to null due to a type conversion failure and it didn't add 0 record(s) to the table due to key violations, etc."

There are usually 3 or 4 parts. I don't see the third or 4th. See image
Capture1.gif

Author

Commented:
I got it.  I got this from Microsoft's website:

Validation Rule Violation — Check the design of the destination table for any required fields. If any field is required and you do not append data to this field, you get this error. Also, check the destination table for any Text fields with Allow Zero Length set to No. If you are appending blank data to a Text field that has Allow Zero Length set to No, you can get this error. Also, check for validation rules for the fields. For example, you may have the following validation rule for the Quantity field: >=10  — in this case, you cannot append records with a quantity less than 10.

Had to enter in my missing fields.

So just one last question.  I don't see the macro option to runQuery.  How do I run this append query from my form's command button?

Author

Commented:
Nevermind.  I got it.  

docmd.openquery ("QueryName")

Thanks for the help!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.