Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Append query break in VBA

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
StampIT
Asked:
StampIT
  • 4
  • 3
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
try this and see what error is occurring ...

db.Execute "qraBOLPreArch", dbFailOnError

mx

0
 
StampITAuthor Commented:
mx,
      I don't get anything. The code completes as if there is nothing wrong.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok.  What is the SQL for the query ?

mx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
lee555J5Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
lee555J5Commented:
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
 
StampITAuthor Commented:
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
 
lee555J5Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
no waste of time here.

mx
0
 
StampITAuthor Commented:
The question should have never been asked. It was suggested that I handle the question this way.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now