|
|
| (42 intermediate revisions by 8 users not shown) |
| Line 1: |
Line 1: |
| {{Bug}} | | {{Fixed bug|}} |
|
| |
|
| The STB_MedB laptop gets the following error message when sending:
| | This problem was fixed with [[CCMDB.accdb_Change_Log_2019#2019-12-07]]. |
|
| |
|
| :: '''Microsoft Access - System resource exceeded'''
| | See page history for details, deleting from here so they don't pollute the searches. |
| | |
| Internally this is also noted in the dbengine object as error number 3035.
| |
| | |
| The error occurs specifically when the send program runs [[Query send check centralized is owner]].
| |
| | |
| {{Discuss|
| |
| *main office needs documentation here in order to get better idea which laptop, how often and perhaps this would help us ID a pattern.[[User:TOstryzniuk|Trish Ostryzniuk]] 16:48, 2019 October 1 (CDT)}}
| |
| == Current status of this problem ==
| |
| Please state if your laptop currently has this problem e.g.
| |
| * HSC_A1 - '''has system resource problem'''-- Ttenbergen 11:00, 2019 September 19 (CDT)
| |
| * HSC_B2 - doesn't have system resource problem-- Ttenbergen 11:00, 2019 September 19 (CDT)
| |
| List of locations:
| |
| ===GRA===
| |
| * '''GRA_N5'''
| |
| **2019-Sep-26: sent ok today --[[User:Malcudia|Malcudia]] 15:43, 2019 September 26
| |
| *** no problems sending today [[User:Lkaita|Lisa Kaita]] 14:28, 2019 October 4 (CDT)
| |
| * '''GRA_ICU''' have not had a system resource problem [[User:Gens|Gens]] 07:51, 2019 October 2 (CDT)
| |
| **2019-Sept-30: worked when I was there Sept 30 for testing -- Ttenbergen 15:25, 2019 October 1
| |
| *** Grace ICU no problems sending today[[User:Lkaita|Lisa Kaita]] 14:28, 2019 October 4 (CDT)
| |
| * '''GRA_N3'''
| |
| **2019-Sep-30: happened Sept 30 -Tina fixed by splitting the sending-- [[User:Lkaita|Lisa Kaita]] 13:25, 2019 September 30
| |
| *** no problems today [[User:Lkaita|Lisa Kaita]] 14:28, 2019 October 4 (CDT)
| |
| ===HSC===
| |
| * HSC_A4
| |
| **'''SRE error as of 2019-10-08''' - "sending batch error" this AM (thought I could send before Pagasa arrived today) but none when I first sent yesterday. Pagasa asked me to re-send as some of the data was missing yesterday.
| |
| * HSC_B3
| |
| **'''SRE error reported by Iris 2010-10-07'''
| |
| * HSC_D4
| |
| **It worked!
| |
| * HSC_H4
| |
| **Sent with no problems
| |
| * HSC_ICUa
| |
| **'''not yet reported'''
| |
| * HSC_ICUb
| |
| **'''not yet reported'''
| |
| * HSC_ICUd
| |
| **2019-Sep-26 Sent, no error message, but Pagasa ID'd that no files went into master CFE database.
| |
| **it worked. Sending was fine
| |
| | |
| ===STB===
| |
| * STB_MISI - '''not yet reported'''
| |
| * STB_CCU - '''not yet reported'''
| |
| * STB_CICU - '''not yet reported'''
| |
| * STB_MedA - doesn't have system resource problem --[[User:Ppiche|Pamela Piche]] 12:06, 2019 September 19
| |
| **No issue with send today on STB MedA [[User:Ppiche|Pamela Piche]] 14:57, 2019 October 4 (CDT)
| |
| * STB_MedB - encountered error with send 9/24/19-- [[User:Ppiche|Pamela Piche]] 15:57, 2019 September 24
| |
| * STB_MedD - '''Debbie reported SRE Error reported'''
| |
| <!-- no system resource exceeded window. Sending complete. Seemed a little slow, but o/w seems o.k.--[[User:DPageNewton|DPageNewton]] 11:43, 2019 September 19 (CDT)
| |
| **system resource exceeded error window occurred today when trying to send.[[User:DPageNewton|DPageNewton]] 17:09, 2019 October 7 (CDT) -->
| |
| **unable to send. system resource exceeded error. [[User:DPageNewton|DPageNewton]] 14:40, 2019 October 8 (CDT)
| |
| | |
| == Interim work-around to have Pagasa send for a collector who gets this error ==
| |
| Pagasa and I had a look at Debbie’s data, and it seems to be a matter of there really being too many records. MS Access is reaching its limit. For testing I tried to send with the first half of your patients and with the second half, and each works. Of course that is not a solution, but it confirms that the volume is the issue. In the long term we need to find an alternative to MS access, and to bring down the backlog, but that won’t be quick.
| |
| | |
| In the meantime, and at least until Trish and Julie are back and have cleared their vacation backlog and have a chance to discuss, the following will have to be our work around:
| |
| *Debbie, stop trying to send, this error will keep coming up.
| |
| *Once a week, coordinate with Pagasa for her to send your completed files. You will need to work very closely together on this so we don’t lose records. Here is how it will work:
| |
| **Debbie, finish going through all data entry for that day that would benefit from Patient Copier since you will need to delete records as part of this
| |
| **Pagasa and Debbie, establish a phone call between the two of you
| |
| **Debbie, close out of Access and do a news and backup and leave Access closed
| |
| **Pagasa, download the newest backup file, delete the incompletes, and send the completes (3-5min)
| |
| **If all goes well, Pagasa will tell Debbie to open Access and delete the completes
| |
| **Debbie, you would need to delete the completes because otherwise it will be impossible otherwise to distinguish between the completes that Pagasa sent, and the completes she has not yet sent
| |
| **Debbie, do one more News and Backup to move that to the newest status
| |
| This should allow files to keep being offloaded from Debbie’s laptop slowly, but it causes extra work for Debbie and Pagasa, so this is not a long term solution. We will need to discuss a longer term solution when Trish and Julie are back.
| |
| | |
| == Observation - A new build of Windows10 was deployed at around the same time this became more common again ==
| |
| * In order to find out the running Windows 10 version do this:
| |
| ** Press the Windows key + R
| |
| ** Type winver
| |
| ** Press Enter
| |
| ** The result will be the About Windows window with information on the current running build. e.g: Version 1809 (OS Build 17763.720)
| |
| ** Source: https://www.ilicomm.com/how-to-find-out-which-build-and-version-of-windows-10-you-have/
| |
| {{Discuss | Herman, do we have an idea which versions would be the "working" vs "broken" ones? }}
| |
| | |
| == Observation - Timing of this becoming a problem again ==
| |
| Pagasa observed that this became a problem again around the same time we changed the schedule of [[PHI Loader.accdb]]. We discussed possible connections but could not think of anything causal. Just putting this down here in case someone else can think of the connection. Ttenbergen 15:34, 2019 September 12 (CDT)
| |
| | |
| == Solution approach: Query size limiting ==
| |
| The error happens when trying to run [[Query send check centralized is owner]]. That query actually ties into the [[Centralized data.mdb]]'s L_Log. This makes for a very large record set, and have a max size of 1GB rather than the general Access limit of 2GB.
| |
| | |
| * 13:29, 2019 September 4 (CDT) - optimized the query further, seemed to eliminate error for now, but error has recurred since
| |
| | |
| == Solution approach: disconnecting wifi ==
| |
| Darryl from STB desktop had suggested that turning off wifi might fix this problem. Since they don't actually have wifi in their area this is easy to try. This worked during his tests, but they have had additional problems sending after that.
| |
| | |
| During additional tests at HSC Tina found out that sending is faster with wifi disabled, so she added some lines to the sending code that disconnect wifi at the start of sending.
| |
| | |
| == Solution approach: Maxlock setting ==
| |
| *https://access-programmers.co.uk/forums/showthread.php?t=285553 sugggests to apply the following
| |
| **DBEngine.Idle dbRefreshCache
| |
| **DbEngine.SetOption dbMaxLocksPerFile, 1000000
| |
| Tried, doesn't solve the problem
| |
| | |
| MaxBuffer
| |
| | |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 2.x
| |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 3.x
| |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0
| |
| | |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE
| |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Jet 3.x
| |
| | |
| MaxLocksPerFile
| |
| | |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE - conf 1000000
| |
| Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Jet 3.x
| |
| | |
| == Solution approach: Microsoft Hotfixes ==
| |
| * https://support.microsoft.com/en-us/help/2760394/description-of-the-access-2010-hotfix-package-ace-x-none-msp-october-3
| |
| ** Hotfix is no longer available, suggests we use newer software.
| |
| ** confirmed this is not installed
| |
| | |
| * https://support.microsoft.com/en-us/help/2726928/system-resource-exceeded-error-message-when-you-perform-a-query-in-acc
| |
| ** https://support.microsoft.com/en-us/help/2553447
| |
| | |
| Noticed that we may not be getting Office updates installed ([https://support.microsoft.com/en-us/help/4501270/may-2019-updates-for-microsoft-office newest] not present)
| |
| | |
| ==Status==
| |
| As of 09:48, 2019 September 4 (CDT) this is still a problem, and recently more of a problem.
| |
| | |
| Next things I will try:
| |
| * try to send from local to local - the query works fine when I run it while connected to a local version of CFE
| |
| * updating ccmdb.mdb to .accdb; if that is the solution we will also need to update many scripts and VBS/VBA programs.
| |
| | |
| === Solution Approach - Access 64 bit ===
| |
| * https://stackoverflow.com/questions/34184018/does-upgrading-to-64-bit-access-fix-system-resource-exceeded-error
| |
| ** Using MS Office 64 bit?
| |
| {{Discuss |emailed Brandon to see if this might be possible to test. }}
| |
| | |
| == Related articles ==
| |
| {{Related Articles}}
| |
| | |
| '''Considerations'''
| |
| | |
| * https://stackoverflow.com/questions/34184018/does-upgrading-to-64-bit-access-fix-system-resource-exceeded-error
| |
| ** Is it possible to update to 2010 64bit before going accdb?
| |
| | |
| * https://stackoverflow.com/questions/19550367/access-2010-system-resource-exceeded
| |
| ** 32/64 Bit issue?
| |
| ** Forms too big?
| |
| ** Other programs using lots of memory?
| |
| ** Windows 10 over Windows 7?
| |
| ** Run Access in Windows 7/XP compatibility mode?
| |
| | |
| * https://stackoverflow.com/questions/54717598/system-resource-exceeded-when-appending-a-query-access-2016
| |
| ** Big queries?
| |
| *** Had heard about update queries in general being more likely to cause this error; ours isn't an update on. It is large-ish, but as small as it can be. When the query runs successfully it finishes in ~5 seconds, so I kind of doubt the size is the issue.
| |
| | |
| * https://accessexperts.com/blog/2018/04/22/getting-system-resource-exceeded-messages-heres-one-way-to-fix-it/
| |
| ** Windows 10?
| |
