VBA Disable Escape Key (ESC), Interrupting Queries

[The_Doc_Man]

The articles I found complained that the change occurred at/about the time of the version change from Ac2003 to Ac2007. Very specific time frame. Not sure which version of Windows correspond to that change as I only upgrade when I buy new equipment. What I bought STAYS at what I bought until it don't work no more. (Except I finally DID upgrade my Office a couple of years ago in order to get a newer version on my Win7 and wife's Win10 so we could exchange files across the in-house net.

 

[isladogs]

Sounds like XP=>Vista or in my case XP=>Win7 ...
....if it was Windows rather than Access version that mattered.

 

[The_Doc_Man]

I would believe that the Windows Vista advent was part of it, because all of the stuff I heard about Vista was pretty bad. Bad enough that the Navy told Microsoft they would not be converting to Vista so XP had to be supported until something better than Vista came along. At least, that's what I heard from unofficial Navy channels.

The articles I mentioned included that the bad effect of <ESC> also applied to other programs. So I have to think it is more Windows-related.

But since I have retired, I don't experiment that much any more. Just a couple of pet projects involving some Word automation and a database that computes phases of the moons for a 4-moon fantasy world that is the home of my Sword-and-Sorcery novels.

 

[isladogs]

@Stormin

Although I think using a scheduled task to do this may be the best solution, try this very simple change to your Access code

Add this as the first line of your procedure

DoCmd.Hourglass True

then add

DoCmd.Hourglass False

when the code has completed

Retested several times pressing Esc in Excel as well as holding it down continuously
This was using my test db with 2.6 million records
The Access procedure ran with no error 3059 and completed successfully EVERY TIME

Knowing your 'luck', it may not work for you but worth a try anyway

 

[Mark_]

Both databases are in the same location in an always-on shared network location, but are not split into front-end and back-end (maybe an upgrade for the future).

For myself, I would make sure the database is split FIRST. Then you can create a small front end that only does things like this import. Look into the use of the /x command line switch as you can start your utility database calling a macro that opens your process.

Your scheduled task would then be something like "At 4am every day run {MyUtilityDatabase /x ReportPrepMacro}"

 

[Stormin]

Ah, if Task Scheduler has to be run from an always-on computer then I would have to get the IT department involved. The company I work for is very 'hot desk' orientated with all the computers being laptops (that we shut down and store each night) and no permanent workstations or offices at HQ. Additionally, all our main servers are in a different country altogether and run by a different bureaucracy that I'd have to get through too. As a non-IT guy, I think I'll put server-side task scheduling under the "Advanced, don't touch" section of this particular project considering my current skills/connections/time constraints :/

Regarding the hourglass code, that was actually already part of my original code so I can safely say that my 'luck' strikes again!

The approach I'm probably going to take when I tackle this issue again is along the lines of a bodge: I'll trap the "user cancelled" error and use a msgbox to check whether it was intentional or not, then trigger procedure exit or resume accordingly.

Thanks to everyone for their time and input!

 

[eshai]

use send keys to to run app to block the keyboard on access or block input to access by microsoft tools

 

[Stormin]

I forgot to update this, but I did code in my 'bodge' from post #26.

Even though it is limited, it works to alleviate the pain from accidental cancellations.

Code below for reference. Hopefully, it will help someone somewhere:

    'Run the queries
    On Error Resume Next
    j = 0
    For i = LBound(aQ, 1) To UBound(aQ, 1)
        'Check if the query is to be run
        If aQ(i, 7) = True Then
[COLOR=Blue]            'Reset error trapping
            lECount = 0
            lEAdjust = 0
            sQueryErrors = ""
            bMoveNext = False[/COLOR]
            'Write run order
            j = j + 1
            sRunOrder = j & " of " & lQCount
            'Get the query SQL
            sSQL = ""
            sSQL = GetQuerySQL(CStr(aQ(i, 1)))
            'Check if getting query SQL was successful
            If Err Then
                'Record failure and error message, clear error
                aQ(i, 3) = False
                aQ(i, 4) = "[GetSQL] Error " & Err.Number & ": " & Err.Description
                Err.Clear
            Else
                Do
                    'Run the query and time it
                    t_bef = Timer
                    db.Execute sSQL, dbFailOnError
                    lRCount = db.RecordsAffected
                    t_aft = Timer
[COLOR=blue]                    'Check if running query SQL was successful
                    If Err Then
                        'Add error info to string
                        sQueryErrors = _
                            sQueryErrors & _
                            Now & vbTab & "[" & Err.Number & "]" & vbTab & Err.Description & _
                            vbNewLine
                        'Count how many times we have encountered an error
                        lECount = lECount + 1
                        If lECount >= clUserCanceledThreshold + lEAdjust Then
                            'Something has likely gone wrong (e.g. not accidentally cancelled)
                            Select Case MsgBox( _
                                "During operation " & sRunOrder & " """ & CStr(aQ(i, 1)) & _
                                """, the following " & lECount & " error(s) was/were triggered:" & _
                                String(2, vbNewLine) & _
                                sQueryErrors, _
                                vbAbortRetryIgnore + vbExclamation)
                                Case vbAbort
                                    bAbort = True
                                    bMoveNext = True
                                Case vbRetry
                                    lEAdjust = lEAdjust + clUserCanceledThreshold
                                    bMoveNext = False
                                Case vbIgnore
                                    bMoveNext = True
                            End Select
                        End If
                        If bMoveNext = True Then
                            'Record latest failure and error message
                            aQ(i, 3) = False
                            aQ(i, 4) = "[Execute] Error " & Err.Number & ": " & Err.Description
                        End If
                        'Clear error
                        Err.Clear
                    Else
                        'Record success
                        aQ(i, 3) = True
                        bMoveNext = True
                    End If
                Loop Until bMoveNext = True[/COLOR]
            End If
            'Save the runtime
            aQ(i, 2) = t_aft - t_bef
            t_tot = t_tot + aQ(i, 2)
            t_bef = 0
            t_aft = 0
            'Keep track of total successes
            lSCount = lSCount + aQ(i, 3)
            'Save the log data to the refresh log detail table
            RefreshLogDetail_InsertNewRecord _
                RefreshID:=lRefreshID, _
                RunningOrder:=sRunOrder, _
                QueryName:=CStr(aQ(i, 1)), _
                DurationInSeconds:=CSng(aQ(i, 2)), _
                WasSuccessful:=CBool(aQ(i, 3)), _
                ErrorDescription:=CStr(aQ(i, 4))
            'If there is a table updated, record the
            ' update time in zhtblTableUpdates
            If aQ(i, 5) <> "" And aQ(i, 3) = True Then
                InsertTableImportDate _
                    ImportedTableName:=CStr(aQ(i, 5)), _
                    RecordsAffected:=lRCount, _
                    ManualDate:=dTimeStamp
            End If
            'Add to the finish message
            sMsgTimer = FormatTimer( _
                StartTimer:=0, _
                EndTimer:=CSng(aQ(i, 2)), _
                OutputFormat:=fMinsAndSecsShort)
            sMsgFull = _
                sMsgFull & vbNewLine & _
                j & ". " & CStr(aQ(i, 1)) & " = " & sMsgTimer & IIf(aQ(i, 3), "", " (Fail)")
            If bAbort Then Exit For
        End If
    Next i
    On Error GoTo -1
    On Error GoTo 0

If an error is encountered up to the threshold (I have mine set at 3) it stops the procedure and asks for user intervention. We can abort the entire procedure, re-run the current operation, or skip the current operation and continue with the rest of the procedure.