Office 365 - Access 2016 issue

[Snowflake68]

Hi this is a long shot and sorry for the long thread but I thought I would throw this out there to see if anyone else has experienced a similar issue using Access 2016 within Office 365 (and Windows 10)

I have built an application using Access 2013 which up until now there has been no problems with the end users and has been in use for 2 years now.

However, some users have since upgraded to Access 2016 (office 365) and also have new laptops but are now having an issue with one area of the application every time they use that part of the application.

Basically the user is retrieving a quote which has been previously saved within the application (local tables). They use a tick box (on a datasheet form) to select which quote they want to retrieve for editing and running reports etc.

The code below is what runs when they tick the box and it is at the point where they receive the confirmation message to say the 'Customer Deal Retrieved' that the system says not responding and then after a while you get a 'Run-time error '2004' saying "There isn't enough memory to perform this operation. Close Unneeded programs and try the operation again"
Then if you click the Debug button you then get 'Unexpected Error' quitting message.

I believe that the message probably has nothing to do with memory and is just a red herring; as all laptops with the issue have 8GB RAM.

Not sure if has anything to do with the version of Access but it doesnt appear to be as I have also tested using a laptop with the same spec and same Access version and with a copy of the offending application and have not been able to replicate the error myself.

I work remotely so don't have direct access to the any of the laptops in question but I have witnessed the issue via screen share. So I have requested that that one of the laptops to be sent to me so that I can try and get to the bottom of it.

The laptop arrives this Friday so hopefully I will be able to source the issue and hopefully resolve it, but I just wondered if anyone has any idea of where to start looking as the code works OK for me and also for lots of other people. Could this be something to do with Windows 10 itself?

Private Sub Select_AfterUpdate()
      
    DoCmd.RunCommand acCmdSaveRecord
    
    If Me.Authorised > 0 Or Me.SubmittedNoAuthReq > 0 Then

        If MsgBox("Quote " & strListRef & " has already been Authorised." & Chr(13) & Chr(10) & "Do you wish to continue??", 52, "Authorisation Received") = vbNo Then

            DoCmd.SetWarnings False

' Reset Select quote to 'No'
            DoCmd.OpenQuery "D_DeselectQuote"

            DoCmd.SetWarnings True
            Exit Sub

        Else: GoTo Continue
       End If
    End If

Continue:

    DoCmd.OpenForm "frmPreviewSelectionNoOfItems", acNormal, , , , acHidden

    DoCmd.OpenForm "LookupListReference", acNormal, , , , acHidden


    If Forms!frmPreviewSelectionNoOfItems.Form.Recordset.RecordCount = 0 And Forms!LookupListReference.DealType = "New" Then
        
        'do nothing

        Else:

        DoCmd.RunMacro "A_Save_Deal"
        DoCmd.Close acForm, "frmPreviewSelectionNoOfItems"
        DoCmd.RunMacro "1bb_ResetTablesCurrentCustomer"

        [Forms]![frmSrchCust]![txtSearchCriteria].Enabled = False
        [Forms]![frmSrchCust]![cmdSearch].Enabled = False
        [Forms]![frmSrchCust]![CmdClearSearch].Enabled = False
        [Forms]![frmSrchCust]![cmdResetForm].Enabled = False
        [Forms]![frmSrchCust]![cmdshowHide].Enabled = False
        [Forms]![frmSrchCust]![chkProspect].Enabled = False
        [Forms]![frmSrchCust]![frmSrchCustSubForm].Enabled = False

    End If

    DoCmd.SetWarnings False

    DoCmd.Close acForm, "frmPreviewSelectionNoOfItems"
    DoCmd.Close acForm, "LookupListReference"

    DoCmd.RunMacro "A_RetrieveDeal"
    DoCmd.RunMacro "A_PrepareDeleteSaveDeal"
    DoCmd.RunMacro "A_DeleteSavedDeals"

    DoCmd.Close acForm, "frmEditDeal"
    DoCmd.Close acForm, "frm2CustomerProspectSelection"

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "D_Customer_PreviousDealsReset"
    DoCmd.OpenQuery "D_Customer_PreviousDeals"

    DoCmd.OpenForm "frm2CustomerProspectSelection"

    DoCmd.SetWarnings False

    DoCmd.OpenQuery "2c_CustomerSalesData"

    DoCmd.OpenQuery "3cc_CostDelivered"
    DoCmd.OpenQuery "3d_MarginPerBottle"
    DoCmd.OpenQuery "3d_MarginPerBottle%"

'Confirmation Message
    MsgBox "Customer Deal retrieved:" & Chr(13) & Chr(10) & "Please click NEXT to continue.", vbOKOnly, "Edit Deal"

    DoCmd.SetWarnings True
    
    Forms!frm2CustomerProspectSelection.cmdNewQuote.Visible = True
    Forms!frm2CustomerProspectSelection.cmdChangeCustomer.Enabled = False
    
    [Forms]![frmSrchCust]![lblEnotria].Enabled = False
    [Forms]![frmSrchCust]![lblCustomer].Enabled = False
    
End Sub

 

[isladogs]

The first thing you should do is add proper error handling to this procedure.
You would then be able to identify where the error(s) occur.

In fact I urge you to do this for ALL procedures.

If its a memory issue, you also need to know and replicate what the users were doing prior to this. You may have a memory leak elsewhere.

I'd also check version info on your and their machines in case its due to a recent update.

 

[Snowflake68]

The first thing you should do is add proper error handling to this procedure.
You would then be able to identify where the error(s) occur.

In fact I urge you to do this for ALL procedures.

If its a memory issue, you also need to know and replicate what the users were doing prior to this. You may have a memory leak elsewhere.

I'd also check version info on your and their machines in case its due to a recent update.

Thanks Colin. I usually do have some basic error handling to trap expected error codes but with this one as it doesnt point at any specific part of the code. I am still fairly new to VBA and especially error trapping and really dont know how to properly debug.

When you say version info what do you mean? I have compared the version numbers of Access together with build numbers and all is the same so apart from comparing windows updates I think ive covered most things already.

As for what the users are doing prior to the error they simply open up the app and then retrieve a quote which is what they are all doing. I think i will have to wait for the laptop to arrive and see for myself but if/when I find the issue I will update this post.

Thanks again

 

[isladogs]

Thanks Colin. I usually do have some basic error handling to trap expected error codes but with this one as it doesnt point at any specific part of the code. I am still fairly new to VBA and especially error trapping and really dont know how to properly debug.

It's the unexpected errors that you need to trap with code similar to this
Replace MyprocedureName with whatever yours is called

Private Sub MyProcedureName()

On Error GoTo Err_Handler

    ...all procedure code goes here

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in MyProcedureName procedure: " & Err.Description
    Resume Exit_Handler

End Sub

[QUOTE
When you say version info what do you mean? I have compared the version numbers of Access together with build numbers and all is the same so apart from comparing windows updates I think ive covered most things already.

As for what the users are doing prior to the error they simply open up the app and then retrieve a quote which is what they are all doing. I think i will have to wait for the laptop to arrive and see for myself but if/when I find the issue I will update this post.

Thanks again[/QUOTE]

Great - you've covered issues with build numbers etc which is what I meant...

 

[arnelgp]

Have you seen this link http://www.solvusoft.com/en/errors/runtime-errors/microsoft-corporation/microsoft-access/2004-there-isn-t-enough-memory-to-perform-this-operation/

 

[isladogs]

Have you seen this link http://www.solvusoft.com/en/errors/runtime-errors/microsoft-corporation/microsoft-access/2004-there-isn-t-enough-memory-to-perform-this-operation/

Hi arnel

In my view, links like this are generic and just designed to get people to purchase software they usually don't need...

 

[The_Doc_Man]

If you have a problem where your code suddenly becomes unresponsive, that sounds like a loop. The usual way to find these is to have some logging routine (I kept mine in a general module so I could use it anywhere) to make an entry in a logging table.

You get to some place you know the code can reach, then liberally sprinkle "waypoint" logging entries that write the waypoint name and maybe the value of the Timer() function (milliseconds since midnight). So you run your code and it goes west on you. But if you do the logging via an INSERT INTO (SQL) sequence rather than a recordset, your last entry will survive the crash.

So then you go back in and see where your waypoint logging stopped. If you have to refine this, you go back into the code and remove the early waypoints (because you know you get to the next one after it). Re-sprinkle the waypoint logging if needed as a way to refine the code segment you are trying to find. Eventually, you will get it down to a single routine or a single segment within that routine. Then you can single-step.

If you do this, be sure that you don't put a unique key on the value of the timer() that you store because with GHz machines and msec timers, you can still get in about a million instructions between Timer() ticks.

 

[Snowflake68]

Well I have received the laptop this morning and have been trying to identify the line in the code that is causing the issue.

I have managed to narrow it down to closing and opening another form. If I comment out that part of the code then all works ok. The only reason I close and open the form is to basically requery it so I have changed the code to requery the form instead of closing it and reopening it.

However although I have sorted out the issue (on the face of it) I haven't identified why it produces the error as its just closing and opening a form. The form doesnt do too much in the OnLoad event apart from enable or disable some other buttons and it is bound to a table with a single record.

I also thought that maybe the form or control was corrupt so I rebuilt the form that produces the error from scratch again and still get the error.

 

[Snowflake68]

Quick update on this mornings findings.

I moved the two lines of code to run after the confirmation message and all is working perfectly. Its very odd why the procedure now works just by changing the order of when the form opens and closes.

 DoCmd.Close acForm, "frm2CustomerProspectSelection"
 DoCmd.OpenForm "frm2CustomerProspectSelection"

Prior to changing when the above two lines run it was simply running a few queries between closing and reopening the form. The queries run updates on some other local tables although most of the queries are linked to the table that is bound to the frm2CustomerProspectSelection form that is closing and opening.

This maybe an issue that I wont ever get to the bottom of the exact cause but at least I have managed to sort it out by moving code around. I am just concerned that there is something waiting in the midst to come back and bite me.

 

[The_Doc_Man]

It is interesting that closing and re-opening a form would kvetch like that. I tend to think mechanistically (and therefore often think myself down a long, meandering garden path), but this sounds ALMOST like you have a limited virtual memory space, which usually means a small swap file.

Go to your Control Panel >> System >> Advanced System Settings >> Performance >> Advanced Tab. Check the space you have set up for the virtual memory. Recommended settings are from equal to the size of RAM to twice the size of RAM. Much less than RAM size and you WILL LIKELY have a problem, including terribly slow performance. More than twice the size or RAM and you are being somewhat wasteful except in very rare circumstances that a single user with "ordinary" utilities is unlikely to experience.

When you do a Close followed by an Open, what happens is that you were occupying some of your virtual space with that form's open structures. So you close it and Windows (not Access) does some virtual memory management to mark that space as having been released after having been used. Then you open something else again - but for complex reasons, you cannot re-use your own released virtual memory just yet - because it is "dirty." This has to do with something called "object re-use protocols" that are present because of U.S. Government security requirements.

Normally, memory release is no biggie, as Windows just gives you more RAM and adjusts your VM space accordingly. But if your allocated virtual memory size is too small, the system has to work harder to make room for the expansion at the same time that Windows (not Access) is going back to do object re-use protocols on the memory you just released - which is why I said it is "dirty." Windows allows for the immediate release of memory but then uses something called "write-behind" to "clean" that dirty memory by updating your swap-file copy. It HAS to do that because your VM - as a whole - has not yet been released and might need retrieval.

Back to the point at hand... that newly released memory is in transition so another chunk has to be allocated. The exact timing of all of that eludes me because Windows is not EXACTLY transparent on exact timings of events. But dropping and immediately re-using big chunks of virtual memory back-to-back is a memory-stressing event. Add to that a message-box closure (which also consumes and then releases system memory, particularly if the message box was Modal) and I guess it is possible for things to get complicated enough to lead to a memory deadlock (the hang). Changing the timing of the event allows Windows to "sanitize" the memory of the message box first and THEN deal with the forms and thus bypass the hang.

That was a long and meandering garden path for the theory, but the practical side is just go to the control panel to see if your VM is a little bit low. And if it is, adjust it up a little. Most machines work in units of 1000 KB at a shot. I would say to just boost your VM space by 1/2 of your physical RAM size.

However, since you have a workaround based on closing and opening that form in a different place, you might not even want to bother with adjusting VM allocation.

 

[Snowflake68]

It is interesting that closing and re-opening a form would kvetch like that. I tend to think mechanistically (and therefore often think myself down a long, meandering garden path), but this sounds ALMOST like you have a limited virtual memory space, which usually means a small swap file.

Go to your Control Panel >> System >> Advanced System Settings >> Performance >> Advanced Tab. Check the space you have set up for the virtual memory. Recommended settings are from equal to the size of RAM to twice the size of RAM. Much less than RAM size and you WILL LIKELY have a problem, including terribly slow performance. More than twice the size or RAM and you are being somewhat wasteful except in very rare circumstances that a single user with "ordinary" utilities is unlikely to experience.

When you do a Close followed by an Open, what happens is that you were occupying some of your virtual space with that form's open structures. So you close it and Windows (not Access) does some virtual memory management to mark that space as having been released after having been used. Then you open something else again - but for complex reasons, you cannot re-use your own released virtual memory just yet - because it is "dirty." This has to do with something called "object re-use protocols" that are present because of U.S. Government security requirements.

Normally, memory release is no biggie, as Windows just gives you more RAM and adjusts your VM space accordingly. But if your allocated virtual memory size is too small, the system has to work harder to make room for the expansion at the same time that Windows (not Access) is going back to do object re-use protocols on the memory you just released - which is why I said it is "dirty." Windows allows for the immediate release of memory but then uses something called "write-behind" to "clean" that dirty memory by updating your swap-file copy. It HAS to do that because your VM - as a whole - has not yet been released and might need retrieval.

Back to the point at hand... that newly released memory is in transition so another chunk has to be allocated. The exact timing of all of that eludes me because Windows is not EXACTLY transparent on exact timings of events. But dropping and immediately re-using big chunks of virtual memory back-to-back is a memory-stressing event. Add to that a message-box closure (which also consumes and then releases system memory, particularly if the message box was Modal) and I guess it is possible for things to get complicated enough to lead to a memory deadlock (the hang). Changing the timing of the event allows Windows to "sanitize" the memory of the message box first and THEN deal with the forms and thus bypass the hang.

That was a long and meandering garden path for the theory, but the practical side is just go to the control panel to see if your VM is a little bit low. And if it is, adjust it up a little. Most machines work in units of 1000 KB at a shot. I would say to just boost your VM space by 1/2 of your physical RAM size.

However, since you have a workaround based on closing and opening that form in a different place, you might not even want to bother with adjusting VM allocation.

WOW thanks for the very helpful post. I have just checked the VM and it is only 1280 with 8GB RAM so I am just going to set it higher and see what happens. I will let you know how I get on.

Thanks again

 

[Snowflake68]

Quick update: I increased the VM to 16000 but didnt fix it, still get the error. So back to the drawing board, im afraid.

 

[isladogs]

As Doc has already stressed, getting the VM 'right' is a balancing act.
It may be that 16MB is too large

Another thing that may be worth investigating is to increase the MaxLocksPerFile setting in the registry. By default this is set at 9500.
If you get problems you could try increasing it in steps starting at say 15000

See this thread: https://www.access-programmers.co.uk/forums/showthread.php?t=296647&highlight=MaxLocksPerFile
Worth reading various links in that thread as well