UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> System Resources Exceeded, Access 2013    
 
   
mari13598
post Jun 27 2019, 11:13 PM
Post#1



Posts: 24
Joined: 21-June 18



Hi again.

Everyday seems to bring a new problem. I love Access and have enjoyed many years with many successfully databases and with very little trouble, until recently. Like since the windows 10 upgrade.

I added 7K new records to a table bringing it to 177K records. Which I didn't think would be a problem for Access, but now I am getting the system resources exceeded message when trying to search or filter the table or when a continuous form is loaded, which uses a simple select query bound to the form to display all records for searching and filtering.

Should I split this one large table into another be database by itself?

Some additional info:

Access 2013 split database. All tables live in the be and are linked to fe.

windows 10 file explorer reports the size at 217,686 KB which is well under the 2 gig limit? Perhaps i am misreading this?

There are about 30ish tables with the table noted above being the largest. All other tables are small 10K or < records.

Max of 12 users and averages about 8 to 10 users normally.

Some complex union and queries with lots of calculations but those are running without error.

Compact and repair is run routinely.

What else can I tell you?

Thanks for any guidance.

Marianne

Go to the top of the page
 
MadPiet
post Jun 28 2019, 12:01 AM
Post#2



Posts: 3,221
Joined: 27-February 09



Should I split this one large table into another be database by itself?
I wouldn't - I'd just index it. And one thing I would definitely recommend is limiting the number of records you're loading when you open a form. Try to load the fewest records you can when you open a form. If you load a form based on an unfiltered table, that's could eat up a lot of memory. one way around it is to pick which subset of the data you want to see... Maybe create form to prompt the user for what records he wants and then you can pass that filter to the Open event of the form.

Check out OpenForm. One of the arguments is the Filter, which is basically a valid WHERE clause without the word "Where" at the front. then only the records that meet the criteria specified get loaded. If you're doing data entry, open the form in Data Entry mode - that way the existing records aren't even read.
Go to the top of the page
 
cheekybuddha
post Jun 28 2019, 01:41 AM
Post#3


UtterAccess VIP
Posts: 11,465
Joined: 6-December 03
From: Telegraph Hill


>> I added 7K new records to a table bringing it to 177K records. <<

How? If you used code, please show the code.

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Jun 28 2019, 02:43 AM
Post#4


UtterAccess VIP
Posts: 1,561
Joined: 4-June 18
From: Somerset, UK


There should be no reason to split your table and no benefit in doing so.
I agree with reducing the number of records loaded in your form but also suggest increasing the MaLocksPerFile setting in the registry.
The default is usually 9500. Try increasing in mall steps e.g. to 15000. Do not make the value too large.
For more info see https://www.access-programmers.co.UK/forums...MaxLocksPerFile

You may also need to increase the MaxBufferSize registry setting which is also discussed in the same thread

--------------------
Go to the top of the page
 
nvogel
post Jun 28 2019, 03:08 AM
Post#5



Posts: 977
Joined: 26-January 14
From: London, UK


It doesn't really make sense to split a table between databases.

If your numbers are right then it seems that your average row size may be quite large. 600 to 1000 bytes maybe? Not necessarily wrong though.

Have you considered putting the data into SQL Server or another SQL DBMS? Access works very well with SQL databases and a client-server SQL DBMS is a better way to share data between multiple users. It would also give you more options and flexibility in storage and indexing.


This post has been edited by nvogel: Jun 28 2019, 03:09 AM
Go to the top of the page
 
Phil_cattivocara...
post Jun 28 2019, 03:18 AM
Post#6



Posts: 326
Joined: 2-April 18



Could it be this Description of the Access 2013 hotfix package (Ace-x-none.msp): February 12, 2013
or this "System Resource Exceeded" error
or one of the many "inexplicable" Access'error with Windows 10 which Daniel Pineault often talks about? (now I cannot find a topic here but they are quite frequent)

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mari13598
post Jun 28 2019, 08:37 PM
Post#7



Posts: 24
Joined: 21-June 18



Thanks! I did add a filter to the open form event and it seems to be working. There will be times when we have to search the larger set of records and I will have to come up with a way to deal with that scenario.
Go to the top of the page
 
mari13598
post Jun 28 2019, 08:40 PM
Post#8



Posts: 24
Joined: 21-June 18



Thank you for the tip. I will read some of these tonight and maybe I can avoid some future unknown pitfalls.
Go to the top of the page
 
mari13598
post Jun 28 2019, 08:42 PM
Post#9



Posts: 24
Joined: 21-June 18



Upload is done via and Excel spreadsheet.
Go to the top of the page
 
mari13598
post Jun 28 2019, 08:45 PM
Post#10



Posts: 24
Joined: 21-June 18



I will check this out and I have made changes to registry settings, but I do not have admin rights on my laptop so this will have to be done by the IS dept and gets complicated. frown.gif
Go to the top of the page
 
isladogs
post Jun 29 2019, 03:21 AM
Post#11


UtterAccess VIP
Posts: 1,561
Joined: 4-June 18
From: Somerset, UK


Hi Mari
Regarding MaxLocksPerFile, I know from experience that this often solves the system resources exceeded error.
The good news is you can change the value at runtime using VBA such as
CODE
DBEngine.SetOption dbMaxLocksPerFile, 15000


The changes made are temporary so you won't need to edit the registry
In fact, I believe you can change the MaxBufferSize setting temporarily as well if needed using similar code

For more info, see https://docs.microsoft.com/en-us/office/cli...tion-method-dao

--------------------
Go to the top of the page
 
gemmathehusky
post Jul 1 2019, 07:29 AM
Post#12


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


maybe the system resources are exceeded. If you have a query that inadvertently creates a cartesian join your 177K records could transform into millions, and exceed the resources.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
mari13598
post Jul 4 2019, 04:20 PM
Post#13



Posts: 24
Joined: 21-June 18



Happy 4th of July!

uarulez2.gif

Thanks I will add the code.
Go to the top of the page
 
ngins
post Jul 11 2019, 10:04 PM
Post#14



Posts: 325
Joined: 18-August 05
From: DFW, TX, USA


QUOTE
Regarding MaxLocksPerFile, I know from experience that this often solves the system resources exceeded error.
The good news is you can change the value at runtime using VBA such as
CODE
DBEngine.SetOption dbMaxLocksPerFile, 15000


Looking in the registry, I see that MaxLocksPerFile is available as a registry item for version 12 of Access, but not version 16. So I wonder if this would work with Access 2016? Have you seen this make a difference with "system resource exeeded" errors in 2016 or later?

Thanks.
This post has been edited by ngins: Jul 11 2019, 10:04 PM

--------------------
Neil
Accessing since '96
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th August 2019 - 04:41 AM