[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-19
15
Medium Priority
?
760 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.
0
Comment
Question by:--TripWire--
  • 8
  • 7
15 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1400 total points
ID: 35426462
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
 

Author Comment

by:--TripWire--
ID: 35426481
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
 
LVL 75
ID: 35426510
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:--TripWire--
ID: 35426571
OK. Thanks.  No error seems to be occurring.  What would be the next step?
0
 
LVL 75
ID: 35426668
ok.  What happens if you run that SQL in the Access query designer directly ?

mx
0
 

Author Comment

by:--TripWire--
ID: 35427328
Invalid SQL Statement:  Expected DELETE, INSERT PROCEDURE, SELECT or UPDATE required.
0
 
LVL 75
ID: 35427393
Well, here is a generic example:

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

0
 

Author Comment

by:--TripWire--
ID: 35427504
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
 
LVL 75
ID: 35427529
None of this is making sense. Weird.

Can you upload the db?

mx
0
 

Author Comment

by:--TripWire--
ID: 35427572
It's on a Server - I'm remoting in.  Plus I'm really not supposed to.
0
 
LVL 75
ID: 35427614
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
 

Author Comment

by:--TripWire--
ID: 35427703
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
 

Author Comment

by:--TripWire--
ID: 35427741
Nevermind.  I got it.  

docmd.openquery ("QueryName")

Thanks for the help!
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1400 total points
ID: 35427750
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1400 total points
ID: 35427755
No ... don't use docmd.openquery ("QueryName")

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

mx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question