Solved

Append query break in VBA

Posted on 2011-03-22
10
406 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
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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