Solved

Append query break in VBA

Posted on 2011-03-22
10
405 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

832 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