Solved

Append query break in VBA

Posted on 2011-03-22
10
404 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
  • 4
  • 3
  • 3
10 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
try this and see what error is occurring ...

db.Execute "qraBOLPreArch", dbFailOnError

mx

0
 

Author Comment

by:StampIT
Comment Utility
mx,
      I don't get anything. The code completes as if there is nothing wrong.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ok.  What is the SQL for the query ?

mx
0
 
LVL 13

Expert Comment

by:lee555J5
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:lee555J5
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
no waste of time here.

mx
0
 

Author Closing Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now