Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Append query break in VBA

Posted on 2011-03-22
10
Medium Priority
?
410 Views
Last Modified: 2012-08-13
Trying to execute an append query in VBA code with the following statement:
db.Execute "qraBOLPreArch". When I step through the code I can see that it executes however the table does not get updated. If I run the query in Access it runs fine. Is there another method I need to use to execute an append query in code? Thanks.
0
Comment
Question by:StampIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 75
ID: 35193669
try this and see what error is occurring ...

db.Execute "qraBOLPreArch", dbFailOnError

mx

0
 

Author Comment

by:StampIT
ID: 35193795
mx,
      I don't get anything. The code completes as if there is nothing wrong.
0
 
LVL 75
ID: 35194152
ok.  What is the SQL for the query ?

mx
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!

 
LVL 13

Expert Comment

by:lee555J5
ID: 35195376
Please post the whole procedure/function and the query object's SQL statement. I just tested on A2010, and it worked fine for me.

Re: "Is there another method I need to use to execute an append query in code?"
Personally, I prefer building the SQL statement in a code string variable (strSQL) and running

db.Execute strSQL, dbFailOnError
but there's nothing wrong with the way you're running it on a query object.

Lee
0
 
LVL 75
ID: 35195688
"but there's nothing wrong with the way you're running it on a query object."
Actually, there is because if unexpected errors occur, they will be silent.  the dbFailOnError parameter exposes these errors.

"I can see that it executes however the table does not get updated. If I run the query in Access it runs fine. "
Something else is going on here.  Yes ... what other code is associate with the line you posted?

mx
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35195830
mx, I agree with your recommendation for the dbFailOnError option in your first post. I use it every time I use db.Execute. My comment "but there's nothing wrong with the way you're running it on a query object." was specifically addressing the Asker's use of db.Execute vs some other query execution method.

Lee
0
 

Accepted Solution

by:
StampIT earned 0 total points
ID: 35198155
mx and Lee,
     I figured out what my problem was. The query was working but updating a table in database B. I was checking the same table name in database A. My bad. Sorry to have wasted your time. What do you suggest I do with the question? Split the points? Thanks for your responses.
0
 
LVL 13

Expert Comment

by:lee555J5
ID: 35199377
No waste of time. :-) All Q prod me to try and test problems and solutions I might not otherwise encounter in my own projects.

About the points
You solved it yourself. There should be an option for that.

Lee
0
 
LVL 75
ID: 35200492
no waste of time here.

mx
0
 

Author Closing Comment

by:StampIT
ID: 35239094
The question should have never been asked. It was suggested that I handle the question this way.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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