• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 872
  • Last Modified:

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

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.
0
--TripWire--
Asked:
--TripWire--
  • 8
  • 7
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sounds like you are getting a silent error - may a Key validation error. Try this to expose the error:

Currentdb.Execute "INSERT INTO tblInput([Field1], [Field2]) SELECT [Field1], [Field2] FROM tblTransfer , dbFailOnError
Currentdb.Execute "DELETE * FROM tblTransfer
0
 
--TripWire--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 _
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... typo - it was inside the double quotes .... this will work.

CurrentDb.Execute "INSERT INTO tblInput([Field1], [Field2]) SELECT [Field1], [Field2] FROM tblTransfer", dbFailOnError
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
--TripWire--Author Commented:
OK. Thanks.  No error seems to be occurring.  What would be the next step?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
ok.  What happens if you run that SQL in the Access query designer directly ?

mx
0
 
--TripWire--Author Commented:
Invalid SQL Statement:  Expected DELETE, INSERT PROCEDURE, SELECT or UPDATE required.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, here is a generic example:

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

0
 
--TripWire--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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
None of this is making sense. Weird.

Can you upload the db?

mx
0
 
--TripWire--Author Commented:
It's on a Server - I'm remoting in.  Plus I'm really not supposed to.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
--TripWire--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?
0
 
--TripWire--Author Commented:
Nevermind.  I got it.  

docmd.openquery ("QueryName")

Thanks for the help!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Basically what I initially said in my first post:

"Sounds like you are getting a silent error - may a Key validation error. Try this to expose the error:"

"How do I run this append query from my form's command button?"
Like so:

Private Sub btnRunAppendQry_Click()

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

Currentdb.Execute "DELETE * FROM tblTransfer", dbFailOnError

End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No ... don't use docmd.openquery ("QueryName")

Use the code you had originally ... that is best practice.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now