Multiple queries run from VBA code (1 Viewer)

Erik_seb

New member
Local time
Yesterday, 23:50
Joined
Feb 24, 2018
Messages
9
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
 

isladogs

MVP / VIP
Local time
Today, 06:50
Joined
Jan 14, 2017
Messages
18,186
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
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:50
Joined
Oct 29, 2018
Messages
21,358
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.
 

Erik_seb

New member
Local time
Yesterday, 23:50
Joined
Feb 24, 2018
Messages
9
Thank you so much all that have replied. I will dig into this and se if i can understand it :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:50
Joined
Feb 28, 2001
Messages
27,001
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?
 

Mark_

Longboard on the internet
Local time
Yesterday, 23:50
Joined
Sep 12, 2017
Messages
2,111
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()
 

Users who are viewing this thread

Top Bottom