Help - Access 2013 Consistently Freezes (1 Viewer)

[t00ley]

Hi All,

I have a an Access 2013 accdb database being developed, however I now have a problem where it consistently freezes in the same place (I'm on 64 bit laptop, windows 7).

I have a specific form whereby a command button runs VBA to import 2 files, convert the data based on a set of rules and output a csv file. All files are imported/export through saved specifications. At the end of the VBA, a completion messagebox does display and I can click vbOK, the messagebox disappears then the database is left hanging, except for the ribbon. All VBA procedures have error handling. This appear consistent in that it freezes only on this form, in the same place, I can only exit the same way and no errors ever display.

I've decompiled, deleted binary sections from the form (via text file in notepad), compacted, debugged etc but nothing seems to fix this issue.

I've just attempted to import the database into a newly created blank database, selecting everything (even through "Options"). This is where I had the first error......"Not all of the import specifications could be imported due to name conflicts, conflicts exist in 1 of the specifications....etc". Oddly, the specifications are named differently(2 x import & 1 x export) (viewed via Saved Imports & Saved Exports).

After deleting these specifications and then re-attempting to import into a new blank database the same error occurs. I've tried this twice, once with importing the specifications, once without & recreating but to no joy.

Does anyone have any idea's?

Thanks

Tooley

 

[Ranman256]

did you put a stop on the code right after the msgbox, then step thru the code?

 

[JHB]

Did you try a "Compact & Repair"?
You write you've errorhandling, but how are they formed? Do you have, "On error Resume next"?
I'll also suggest a decompile:
http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

 

[t00ley]

Hi,

Ranman, yes after the message box is displayed, I click OK then the vba steps to the next & last line End Sub.

JHB - I've done all that multiple times. Error handling for all except 2 procedures is as follows:

On Error GoTo Error_Handler

'Do stuff 

Exit_Handler:
    'Nothing to clear
    Exit Sub
Error_Handler:
    Select Case Err.Number
        Case Else
            Call sShowGenericErrorMessage(Err.Number, Err.Description, ls_ProcName)
    End Select
    Resume Exit_Handler
End Sub

The Generic Error sub called:

Public Sub sShowGenericErrorMessage(ll_ErrorNumber As Long, ls_ErrorDescription As String, ls_ProcName As String)

On Error Resume Next
    
    Dim ls_Msg_Title                   As String
    Dim ls_Msg_Opening                 As String
    Const li_Msg_Style                 As Integer = vbCritical 
    Const ls_Msg_ErrNo                 As String = "Error Number = "                                                                       
    Const ls_Msg_ErrDesc               As String = "Description = "
    Const ls_Msg_ProcName              As String = "Procedure = "
    Dim ls_Msg_ErrDetails              As String
    
    'Build error message
    ls_Msg_Title = gs_GenericErrTtl                                                                                                             'Value taken from Global dimension
    ls_Msg_Opening = gs_GenericErrMsg                                                                                                           'Value taken from Global dimension
    ls_Msg_ErrDetails = _
        ls_Msg_ErrNo & ll_ErrorNumber _
        & vbNewLine & vbNewLine _
        & ls_Msg_ErrDesc & ls_ErrorDescription _
        & vbNewLine & vbNewLine _
        & ls_Msg_ProcName & ls_ProcName                                                                                                         'Builds error details
    
    MsgBox ls_Msg_Opening & ls_Msg_ErrDetails, li_Msg_Style, ls_Msg_Title                                                                       'Display messagebox

Exit_Handler:
    'Nothing to clear
    Exit Sub
Error_Handler:
    'Not required as using Resume Next
    Resume Exit_Handler
End Sub

I've also removed resume next from this sub to test, but no errors appear.

Thanks

 

[The_Doc_Man]

Was this routine in place from the moment your problem started? Or did you use some other error reporting mechanism? If this code has been in place all this time, THIS is probably the source of your problem. The fact that you removed the Resume Next and no errors appeared doesn't prove anything. Did the HANG also occur when the Resume Next was absent? That would be more telling.

When using a generic sub like the one you showed, you do not want the RESUME to be in it. Not EVER. You didn't trap to this sub directly, but rather you just CALLED it, so you CANNOT use Resume from it. You must return from it via normal subroutine return methods. You MUST execute the Resume from the caller (i.e. your actual error handler code). Your problem might be this very subroutine.

Here is my take on what is going on.

When you take an event or call a function/sub from VBA, what happens is that the Access event manager OR your VBA code issues the equivalent of a Call instruction with the possibility of arguments being passed. Remember that VBA is not compiled, it is semi-compiled and then interpreted (sometimes called "emulated" though that term has other meanings.) That means that "CALL" is an emulation that could do many things.

Because of the Call in your VBA or a "fake Call" from the Access event manager, the details of the arguments AND the return address are placed on your user-mode stack in a Call Frame internal structure. You can open the "Show Call Stack" window to see the call frames above your current location. Call management is based on a repeatable and predictable process that always involves an explicit (VBA) or implicit (event-based) call.

When you take a trap, that occurrs based on some action that triggered an interrupt in the hardware (for math errors, power-fail, and address violations) or triggered a SIGNAL operation (for file system or O/S errors or "subscript out of range" errors), which is a software-generated trap. The hard or soft SIGNAL creates a TRAP frame on the stack. Given that one does not expect every action to incur an error, traps are not completely predictable. (You can predict that they WILL occur, but that's Murphy's Law at work.)

If you put a break point in your code in a trap handler (before your call to the generic handler), that trap is NOT separately visible in the "Show Call Stack" window because it is NOT based on a call frame. If you think about it, your execution point is still inside the code associated with the routine in question, just a different part of it, so you should not expect to see a call frame entry for it.

You cannot do a Return or an End Sub or an Exit Sub from a trap without resolving it (or deciding to ignore it, which IS one possible resolution). You MUST use a Resume because that is the instruction in VBA that triggers an internal stack manager routine (in MSAccess.EXE's compiled code) to clean up the trap structure.

In summary, NEVER treat a trap like a subroutine. A trap always, ALWAYS, ALWAYS requires a Resume in order to clean things up.

Now, the next question is: When you hang up like that, where are you? I wish I could be more definitive, but I can tell you that Access is lost, too. When you do a Return or Exit Sub or flow to an End Sub, that triggers a Call-back to the code that built the call frame. But ... that isn't a valid call frame on top of the stack any more, its a TRAP frame. So the Call-back encounters an error that would normally generate a trap - but you didn't dismiss the previous trap (because you haven't done a Resume yet) and a trap cannot interrupt a trap. So where you are is in some kind of deadly hardware or emulation embrace where Access cannot stay where it is and cannot go where it wants to go. So it gets in a status that looks very much like it is hung up.

And one more thing: I have experience in this. Do you know what "experience" means? That you can recognize your mistake when you make it again. So this is how I know what is going on.

 

[gemma-the-husky]

put a breakpoint at the msgbox, and then step through the code.

I imagine you are stock in a loop somewhere.

you may not be able to ctrl-break to pause the code.
If you add doevents somewhere judicious, then you will be able to, but it would have to be somewhere in the loop, so it's a bit moot, maybe, as you don't know where the error is!

I think docman is right.

the error display shouldn't error, and until you "resume" from it, there is no active error handler. Temporarily replace your error display with something like this, and see what happens.

I would nz() all calls to this sub, so you don't pass nulls.

so this sort of thing.
sShowGenericErrorMessage nz(errnumber,0), nz(errdesc,""), nz(procname,"")

Public Sub sShowGenericErrorMessage(ll_ErrorNumber As Long, ls_ErrorDescription As String, ls_ProcName As String)

doevents 'shouldn't need it, but just in case
msgbox "Error in Proc:  " & ls_procname & vbcrlf & _
   "Error: " & ll_errornumber & vbcrlf & _
   "Desc: " & ls_Error Description"
end sub

I am sure you will kick yourself when you realise whatever is going wrong.

 

[JHB]

What happen if you comment out the code lines, in which you do the import/export?
Do you import/export to a local folder, else you could try that.
I'm unsure about your error handling, I would comment them out until you find the problem.
Could you show the code?

 

[t00ley]

Thanks for the feedback.....I've narrowed down to a sub that runs some SQL. I've added a line at the beginning of the culprit sub to exit (so sub gets called, sub does nothing exits & continues with main macro) and the freeze doesn't occur.

There's a few things going on in this sub....
Loop through active rules (DAO Recordset)
Build SQL relevant to the rule to amend records in a table
run SQL (whilst logging changes)

I've limited to 1 rule where I know the SQL works, but it still freezes. I'll continue checking further later and update asap.

Thought best to let you know now so you don't think I'm being ignorant.

 

[The_Doc_Man]

Does the trap handler exist? And does it fire inside this sub?

The fact that a Macro is in effect may be significant. Macro error handling is not nearly as good as Form/VBA error handling. Can you, as a test, build a form that calls the code associated with this miscreant subroutine? You would, of course, want error handling enabled and would want a trap at the first instruction of the error routine.

By the way, the idea of using a common subroutine for error handling is NOT a bad one. I've done it more than once myself. For the one you showed us, it might be possible for the actual trap handler to look like this:

sShowGenericErrorMessage Err.Number, Err.Description, "routine name"
Resume Subr_Error_Occurred

You would have a label in your "regular" code that bypasses whatever else you are doing when an error is detected, so you can do an ordinary Exit Sub after the trap fires and is dismissed. Then EVERYTHING else is just End Sub or Exit Sub cases. Please don't take away from this thread that we believe anything is wrong with the approach of using a common error handler. It is good business to invent one wheel and then mass-produce it. If this handler is the bug-a-boo, it is implementation, not concept, that is at fault.

 

[t00ley]

Hi All,

Just to closeout, whilst not 100% sure, I think the form was hanging due to SQL running in the background even though the VBA fully completed and DoEvents are used. Essentially, there was one procedure that contained nested DAO.Recordsets to build a SQL script and then run the SQL. I've re-written reduced the number of loops and recordsets and all works now as expected. Basically, a poor design with too many loops etc, it was just confusing as all VBA appeared to complete (ie message box was added at the end to confirm completion). I've took on board the comments re error handling and changed accordingly. Thanks again

 

[The_Doc_Man]

That brings up another thread that has been beaten to death in another thread regarding multi-threaded operation of the Access DB engines. We beat that horse to death some time ago so I won't resurrect it.

This is the short comment: If this is a "pure" Access database, SQL code won't be running in the background. Access cannot run multi-threaded and the ACE or JET engine (probably ACE for your case) will not return control to your code until it is effectively finished with the query you gave it.

On the other hand, re-writing your loops to simplify and unravel the nesting certainly could have had such a positive result. I would attribute your success to the loop fixes. In any case, congrats on narrowing it down to the procedure with all the DAO code.

 

[t00ley]

Thanks again Doc.

If it helps others, stepping through the code etc didn't identify where the issue was being caused (at least for a novice like me). I identified the culprit procedure by logically & systematically preventing subs from being called from the main procedure until I found the culprit procedure. This was very quick and enabled me to focus on fixing the problem.

 

[The_Doc_Man]

The process of elimination is valid and obviously was helpful for you. Thanks for noting it in that level of detail, as other novice readers will benefit from it!