Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-16-2019, 02:22 AM   #1
Erik_seb
Newly Registered User
 
Join Date: Feb 2018
Posts: 9
Thanks: 3
Thanked 0 Times in 0 Posts
Erik_seb is on a distinguished road
Red face Multiple queries run from VBA code

Hi,
im not very skilled in VBA (or access) and im a litle stucked.

My problem (challenge) is that i have aprox 25 different queries (both apend queries, delete and update) that i run once a month. These have to be run in a spesific order.

Up til now i have used VBA code as: openquery "my query name" with all warnings switch off (Set warnings (false).

Also tried: dbs.execute ....

The challenge is that:

1) both methods ignore warnings perfectly, but one or two queries run into system resources issues. When query runs the warning (if not switched of) would say that "task cant be undone if i continue". I say yes and case closed. With warnings turned off it seems that default answer is no and query is not run.

2) When i run with execute comand Acces goes straight into "not responding" and stays that way for hours. With openquery method this is not an issue. Im happy with openquery method if issue 1 could be solved. My users cant sit in front of the program for an hour (that is how long all queries take to run on a good day).

Is there any way to run all my queries and make sure that they run no matter what? And, maybe get a log or something that show how the queries are executed?


hopefully,

erik

Erik_seb is offline   Reply With Quote
Old 08-16-2019, 03:04 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,822
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Multiple queries run from VBA code

Try using dB.Execute sql, dbFailOnError

This will allow each query to run and only popup a message when an error occurs

If you have system resource errors, there are various things to consider.
1. Check for existing connections. You may be close to the Access limit of 255 connections
2. Increase the max locks per file registry setting either permanently or temporarily using code.
If you do a forum search for each of those you should find several threads of use including ones I've contributed to.

Hope that helps you move forward
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-16-2019, 04:47 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,631
Thanks: 38
Thanked 885 Times in 868 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Multiple queries run from VBA code

Hi erik. In addition to that, you might be able to use the RecordsAffected property to tell if the query was able to do anything or not.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 08-16-2019, 05:08 AM   #4
Erik_seb
Newly Registered User
 
Join Date: Feb 2018
Posts: 9
Thanks: 3
Thanked 0 Times in 0 Posts
Erik_seb is on a distinguished road
Re: Multiple queries run from VBA code

Thank you so much all that have replied. I will dig into this and se if i can understand it :-)
Erik_seb is offline   Reply With Quote
Old 08-16-2019, 05:37 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,093
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Multiple queries run from VBA code

If you have done some reading on the db.Execute method, you will understand that if you have a "dbFailOnError" option, it UNDOES (rolls back) the effect of the failing query, so you have the opportunity to know exactly which step failed and can issue a specific error message. But it also implies that you probably would want to understand error handling because that parameter will trigger an error that will signal (as an error interrupt) when the query fails.

Also, if you use db.Execute, you can then use db.RecordsAffected to see what, if anything, was done... but if it is possible that you can have 0 records affected without there being an error, you have to be ready to program your way around that situation by NOT declaring an error in that case. Like, maybe, a particular DELETE phase didn't have anything to delete but not because of an error - just because there was nothing ready to delete. (I've run into that a few times.)

You absolutely CAN do this using a string of .Execute followed by tests of .RecordsAffected and testing whether the error object was updated. It will be a bit tedious - but then, running a sequence of 25 action queries is already tedious, so what's new about that, right?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-16-2019, 08:31 AM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Multiple queries run from VBA code

If you can, please copy the code that you use to run your set of queries.

Several things you will want to do are
1) Make sure you check for success on each query and stop processing before a dependent query runs.
2) Make sure you have something to let an end user know it is still running with an idea of how far in to your process you are
3) Make sure you are allowing existing events to process between queries with a DoEvents()

Mark_ is offline   Reply With Quote
Reply

Tags
queries;run;multiple;help , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Queries using multiple queries ryetee Queries 3 02-27-2016 12:07 PM
Run Multiple queries using code and only export those with true values to excel Summer123 Modules & VBA 31 02-18-2011 12:20 PM
Huge report, multiple queries, multiple tables calford Reports 1 07-09-2006 06:02 PM
Identical Code for multiple Forms and multiple Reports llkhoutx Modules & VBA 1 08-09-2004 11:29 AM
Code in Queries ArcherSteve General 10 01-16-2004 01:38 PM




All times are GMT -8. The time now is 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World