How to stop a query which utilizes a VBA function

Question

Is there a way to stop a query's execution which calls a VBA function in a code module?  For example, if I have a function called MyFunction() which is utilized in a select query (e.g. select MyFunction([field]) from table) and I run that query, how can I stop its execution? Pressing ctrl-break simply causes the VBA processing to pause with the familiar options of continue, end, debug, and help.

-Bruce

Answer 1

Is there a way to stop a query's execution which calls a VBA function in a code module?  For example, if I have a function called MyFunction() which is utilized in a select query (e.g. select MyFunction([field]) from table) and I run that query, how can I stop its execution? Pressing ctrl-break simply causes the VBA processing to pause with the familiar options of continue, end, debug, and help.

Doesn't pressing {Esc} usually stop a running query?  Does it work for you?

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

Hi Bruce Hulsey,

here we have only 2 options available i.e. ctrl+Break and using Esc Key.

there is no other options that can work with Access.

by the way why you want to stop the execution of the query ?

is it taking much time to get execute?

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 3

I really should apologize here as my simplified example is probably not relevant.  What I was actually trying to do was something more along the lines of 

select tbl1.x, tbl2.y from tbl1 inner join tbl2 on myfunction(tbl1.x, tbl2.x)=1

where the function was part of the join rather than a field being returned.  Internally this must result in hugely many more iterations of myfunction() than my original example would.  In this situation Esc does not stop the query.  Ctrl-Break will stop it by pausing code execution in the VBA module but at that point one cannot break out of the query itself.  This example is rather extreme but I have experienced similar circumstances in the past where long executing queries utilizing VBA will not stop with Esc and ctrl-Break will not stop the query but will pause VBA in debug mode.  Unfortunately off the top of my head I can't remember a simpler example than this one.  This is the first time I've ever attempted putting VBA in the join as above.  I was just curious if there was another way or perhaps a clever VBA hack that could be incorporated into embedded VBA functions which would let one 'escape' from a query that might otherwise take hours to run.

-Bruce

Answer 4

Deepak, yes.  My query was taking hours to run and never actually completed or displayed any records.  As I mentioned in my reply to Dirk earlier, the example I gave was probably not a good example.  I know I have had similar experiences in the past where the only way to stop a particular query was to use taskmgr to stop Access itself but I don't have a good example using simple selects and joins.  The example I should have stated was the following:

select tbl1.x, tbl2.y from tbl1 inner join tbl2 on myfunction(tbl1.x, tbl2.x)=1

Essentially I was trying to compare two sets of records.  For each record in tbl1 I wanted to see all records in tbl2 where the same field differed by only 1 character.  I wrote a simple VBA function to compute Hamming distance between two strings and built a select statement similar to what I gave above.  This works but takes a painfully long time even with small sets of records in tbl1 and tbl2.  Esc does not terminate such a query, and ctrl-break only pauses VBA in debug mode.

At any rate, while this is an extreme example I know I have seen such behavior in the past with simple joins where there was a VBA function returning a field value or perhaps in the where clause.  I was just wondering if there was a workaround short of terminating the Access process itself.  Thanks for your reply!

-Bruce

Answer 5

Esc does not terminate such a query, and ctrl-break only pauses VBA in debug mode.

Hi Bruce,

If the VBA is paused after a ctrl-break, is then a yellow colored line diplayed? And is that the line with the command for the query?

In that case you could go to the beginning of the yellow part and add a new line, e.g.  jan = jan <enter>. "jan" is just a global variable that I use for all kind of things while debugging. This new line now will take over the yellow color, and you can comment out the next line with the query instructiuon.

I have no experience with interrupting running queries, but in general debugging I use this technique to give the program a complete different program flow to study special effects, or - while editing - develop the right logic after an error.

Imb.

Answer 6

Good thought.  One could modify the function on the fly while in debug mode, bypassing all the code in the function by placing the following lines at the beginning of the function:

myfunction = 0 
exit function

However, this doesn't prevent the query itself from running, although each subsequent iteration of the function will run faster now.  Given that the kind of join i'm using is essentially cartesian, this function will still get executed many many times.  But you got me on the right track - simply deleting the function code (or rather cutting it and pasting it to notepad for safekeeping) while it is in debug mode will force a project reset which will in turn stop the query from running.  As long as I don't save the code module this seems to work well enough.

-Bruce

Answer 7

Hi Bruce Hulsey,

you had mentioned that,"you want to find Hamming distance."

now I understand why so much iterations are necessary.

is it necessary to make a query like this?

I mean that we can try to fetch the data from both table in vba code and then try to find the Hamming distance .

also I recommend you to use different different algorithms to find the hamming distance.

the one I had find like below may help you.

Calculate Hamming weight and/or distance in VBA

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Regards

Deepak

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 8

However, this doesn't prevent the query itself from running, although each subsequent iteration of the function will run faster now.  Given that the kind of join i'm using is essentially cartesian, this function will still get executed many many times.  But you got me on the right track - simply deleting the function code (or rather cutting it and pasting it to notepad for safekeeping) while it is in debug mode will force a project reset which will in turn stop the query from running.  As long as I don't save the code module this seems to work well enough.

Hi Bruce,

A different approach is to make a recordset of one table, loop through this table and do your tests with the second table.

In my systematics I would use my Active_set function as in:

    active_sql = "SELECT * FROM tbl1"
    Do While (Active_set(cur_set))
        do the processing
    Loop

This Active_set function contains all error handling with regard to working with recordsets. But - and that is the connection with your problem - you can also pass which progress meter you want to use. On any progress meter (in a progress form) there is s stop button. When you press the stop button, the active_stop boolean is set to true. The internal processing within the Active_set function does a MoveNext when active_stop = FALSE, but does a MoveLast when active_stop = TRUE. In this way the user can stop the loop.

This Active_Set function is a generalized function working for any recordset in any application.

Imb.

Answer 9

Hi Deepak,

I have a quite efficient function already for finding the Hamming distance but thank you for taking the time to look into it.  My more general problem is dealing with queries that cannot be terminated with Esc or ctrl-break and I think the workaround I posted (forcing a project reset from within the VBA code window) will work for this.

-Bruce

Answer 10

Yes, this kind of approach is a possibility and guarantees the easy ability to break out of a loop.  But it is a much more complex solution and in general looping through recordsets is a less efficient means of getting a result set than using SQL.  In this case though, who knows?  Recordsets may be more efficient here.  I was surprised to find that Access SQL would even support the kind of join I used but there is no way to know if Access' SQL optimization gives any performance edge over looping over the recordsets a billion times without testing and timing each approach.  I appreciate your contribution to the conversation though!

-Bruce

Answer 11

But it is a much more complex solution and in general looping through recordsets is a less efficient means of getting a result set than using SQL.  In this case though, who knows?  Recordsets may be more efficient here.

Hi Bruce,

As always, it is a trade off between speed of retrieval and speed of further processing.

Imb.

Answer 12

I think I inadvertently stumbled on what to do based on a suggestion from Bruce

...

Good thought.  One could modify the function on the fly while in debug mode, bypassing all the code in the function by placing the following lines at the beginning of the function:

myfunction = 0 
exit function

...

I used control - break and it interrupted the vba function being called.

I clicked on "DEBUG"

I left out the myfunction = 0 line and just put

exit function

as the first line in the function.

note that my function was to return a string.

after making the change I hit the play icon to resume.

I got a dialog pop up saying

Data type mismatch in criteria expression

I clicked OK and the query was stopped.

Of course I had to comment out the "exit function" line to get the vba function back to normal.

This seemed to work for me. Maybe it will help someone else.