"Access has stopped working" due to vba routine taking too long (1 Viewer)

HalloweenWeed

Member
Local time
Today, 06:36
Joined
Apr 8, 2020
Messages
213
It is my suspicion that the memory is being used up by the transfer and storage of the "long text" fields, as many of them have about 1000 characters per record, multiplied by over 4000 records, and is expected to gain almost 2000 records per year.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 28, 2001
Messages
27,001
If you imported 4000 records of 1000 characters each, that is 4 MB, which has a long way to go in order to account for 1.2 GB.

Besides the side effects associated with the questions I asked you earlier, here are things I was considering.

1. If you had a JOIN query that was improperly constrained so as to become a Cartesian-product query, you would get a potentially huge number of records. Part of Access query processing includes making a list of records to be joined. An improperly constrained multi-table query could very easily consume a LOT of memory. I'm not sure if that possibility has been ruled out yet. Do you have a non-union query that references two source tables without using a formal JOIN syntax?

2. If you had a lookup field with lots of options, we've seen where they form Cartesian products if referenced in a SELECT clause but not in a WHERE clause. Fortunately, you suggested you didn't have any of those.

3. The "System Resources" problem is hard to pin down because "system resources" is a difficult term to define. (Many things qualify as such.) But the key is that system resources are a limited WINDOWS resource and once they are totally consumed, your app is helpless. Here is a link that might offer SOME help. But no guarantees.


This is a similar (but not identical) article on the "resources" question.

 

HalloweenWeed

Member
Local time
Today, 06:36
Joined
Apr 8, 2020
Messages
213
If you imported 4000 records of 1000 characters each, that is 4 MB, which has a long way to go in order to account for 1.2 GB.

Besides the side effects associated with the questions I asked you earlier, here are things I was considering.

1. If you had a JOIN query that was improperly constrained so as to become a Cartesian-product query, you would get a potentially huge number of records. Part of Access query processing includes making a list of records to be joined. An improperly constrained multi-table query could very easily consume a LOT of memory. I'm not sure if that possibility has been ruled out yet. Do you have a non-union query that references two source tables without using a formal JOIN syntax?

2. If you had a lookup field with lots of options, we've seen where they form Cartesian products if referenced in a SELECT clause but not in a WHERE clause. Fortunately, you suggested you didn't have any of those.

3. The "System Resources" problem is hard to pin down because "system resources" is a difficult term to define. (Many things qualify as such.) But the key is that system resources are a limited WINDOWS resource and once they are totally consumed, your app is helpless. Here is a link that might offer SOME help. But no guarantees.


This is a similar (but not identical) article on the "resources" question.

Just to be clear:
1. I currently have no queries that reference more than one source, and so certainly no Join queries. I tried that (JOIN query) months ago, didn't like the results, also had problems with "access has stopped working," and since then have rebuilt my database from scratch without it.

2. As you said: no issue there.

3. As far as the record locks, I have the following line in two of the forms which are always ran first:
Code:
    DBEngine.SetOption dbMaxLocksPerFile, 200000
as shown in two of the previously attached files. This should more than cover that. The second iteration of this line is only there because when troubleshooting I don't run the first one. I'm hoping that my institution will pony up for the upgrades that I should have, and that may allieviate my problems, however I have very little expectation that it will actually happen. Thank you for your suggestions.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 28, 2001
Messages
27,001
I'm hoping that my institution will pony up for the upgrades that I should have,

If you can get a larger VM than a 4 Gb machine, that might help with the "out of physical memory" problem. Or if you can get a physical desktop with more than 4 GB of RAM, that again might help. However, unless you can reliably get past the "out of resources" error, NO memory upgrade will do any good. That is not something that correlates with physical memory availability.

Here is a reasonable article on Windows Resources that will help you understand "out of resources."


What it DOESN'T tell you is that resources are tracked by a table so that when you open something that has a graphic component (for example), that graphic resource has a "fast pointer" to find it. Resources of a non-graphic nature also exist, such as file information. What matters is that Windows keeps track of the things listed in that article for future use. The only way to get more resources (that I have found) is to not use so many resources - because unlike Doritos, ... Windows won't make more. Unfortunately, if you are in a tightly controlled environment, that option is not open to you. All that is left is usually just to chase down the operation that is eating your socks for you.
 

HalloweenWeed

Member
Local time
Today, 06:36
Joined
Apr 8, 2020
Messages
213
Belated update:
16 GB SRAM and
Code:
DoEvents
DBEngine.Idle dbRefreshCache
inserted into my time-consuming loops took care of the problem. :)

My thanks to

The_Doc_Man

Isaac

and everyone else for your help.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 28, 2001
Messages
27,001
Glad to hear that. Having more physical memory gives you a place to work that doesn't involve heavy disk-based swapping and doesn't as quickly run into issues with virtual memory. I could speculate as to the actual effect of those two lines you added in your loops, but it would be speculation, nothing else. I'll offer this much: A DoEvents and the dbRefreshCache options allow Windows to "catch up" because it uses "Read-Ahead, Write-Behind" logic to optimize buffer usage and will therefore defer some necessary actions. With more physical memory it has more room for buffers and thus runs into a "memory crunch" much less often. But which specific kind of crunch? Damned if I know. Doesn't matter either, if your problem is fixed. You are welcome for any help I provided.
 

SHANEMAC51

Active member
Local time
Today, 13:36
Joined
Jan 28, 2022
Messages
310
It strongly appears that Windows is stopping the Access execution before it finishes, killing the process, after 10 minutes run time
the most problematic part of the code and incomprehensible
Code:
'The main loop per record
Do While (Not Xl_qry.EOF)
'find Sharepoint "Long text" table index
LngTxtIndexFnd = True
With tblLngTxt
.MoveLast
    Do While .AbsolutePosition > -1
    If Xl_qry!ID = !ID Then Exit Do 'Record found
    If .AbsolutePosition > 0 Then
        .AbsolutePosition = .AbsolutePosition - 1
    Else
        LngTxtIndexFnd = False  'Record not found
        Exit Do
    End If
    Loop
End With
 

Users who are viewing this thread

Top Bottom