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 Resource Exceeded Problem, Access 2010    
 
   
fiftyfour
post Oct 27 2017, 02:56 PM
Post#1



Posts: 826
Joined: 12-September 03
From: Illinois


I developed a db that's been working fine...until today...this db has about 70,000 records. I've had many many more records than that in other dbs I've developed. I'm using VBA to loop through a recordset to retrieve data that meet the criteria of the recordset for the current record. I've used this technique on hundreds of other db's I've developed with no problem. Now all of a sudden I'm getting an error msg: "System resource exceeded". I thought that the db was corrupt, so I reverted back to a copy of the db that worked for me two weeks ago. I exported the data from the 'corrupt' db to Excel, and then imported the data back into the good db (about 4400 records)...and I get the error message that 'Subscript out of range'. I know Access is more robust than this...what gives.

--------------------
You cannot change the wind...but you can adjust your sails
Go to the top of the page
 
theDBguy
post Oct 27 2017, 03:39 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,196
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Did you update the code or use a new one just before you started getting the error? If so, if you go back to the old version of the code (or the one without it), do you then stop getting the system exceed error? If so, then we can narrow the problem down to the new code.

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
fiftyfour
post Oct 27 2017, 04:43 PM
Post#3



Posts: 826
Joined: 12-September 03
From: Illinois


Thanks, but the code has not changed from two weeks ago...just the number of records. I'm going to delete all records (don't worry, I'll backup the old db first), import new data and run the program. We'll see what happens then. Thanks for the help.

--------------------
You cannot change the wind...but you can adjust your sails
Go to the top of the page
 
GroverParkGeorge
post Oct 28 2017, 09:06 AM
Post#4


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


There's a bit of a contradiction in the original post:

"...this db has about 70,000 records."

But also:

"...imported the data back into the good db (about 4400 records)"

What is the actual total number of records?

The second error message is different from the one in your subject title. That suggests two different problems, as well.

" 'Subscript out of range'"

That is a DATA problem, not a limitation in Access. Something in your code is expecting "some value" within a certain range. We can't know what that is, of course, unless we can see the code and the data involved.

However, the discrepancy in record counts (70,000 vs 4,400) suggests there's more going on than we've seen so far.

But do try again with a fresh start and post back if your problems persist.

--------------------
Go to the top of the page
 
cheekybuddha
post Oct 28 2017, 01:16 PM
Post#5


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


>> Subscript out of range is almost certainly an integer number field that has reached the limit <<

Are you sure, Colin? Normally that's an Overflow error.

Subscript out of Range errors more normally occur when you try and access an element of an array using an index that doesn't exist, or similarly try and access a member of a collection where the index/key doesn't exist.

Perhaps in the recordset code fiftyfour is trying to reference a mis-spelled field or looping through fields having forgotten to increment only as far as .Count - 1.

But if the error can also occur when an integer exceeds its limit I will have learned something new today smile.gif

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 29 2017, 10:53 AM
Post#6


UtterAccess VIP
Posts: 9,275
Joined: 6-December 03
From: Telegraph Hill


No problem, Colin!

It wasn't meant to be a correction - I just wanted to double-check as I wasn't sure!

thumbup.gif

d

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


Regards,

David Marten
Go to the top of the page
 
fiftyfour
post Oct 30 2017, 11:18 AM
Post#7



Posts: 826
Joined: 12-September 03
From: Illinois


I probably did not explain myself very well regarding the difference in the number of records: I have 4400 records from this week that I wanted to append to about 66k existing records, making a total of 70k. I had an integer variable that was counting the number of parts that meet criteria contained in a DCount function. The recordset loop failed on this DCount function. I changed the integer variable to a long variable and I still get the same error message. I did some googling on the "System resource exceeded' error message. The google revealed some bad stuff that involves a 'hot fix' and changes to the registry...and for some users the problem persists after this fix. Subscript out of range sometimes points to a corrupt db...that's why I reverted back to a db that worked two weeks ago (with 62K records). I thought that the '...exceed resource' error message may be a common problem with a fix that does not include downloading a 'hot fix' and changing the registry. I appreciate the help...I did delete the existing data and at the end of the week, I'll run the program again...we'll see what happens then and I'll keep you informed. In the meantime...google the error message and see what you come up with. Thanks to all.

--------------------
You cannot change the wind...but you can adjust your sails
Go to the top of the page
 
fiftyfour
post Oct 30 2017, 01:25 PM
Post#8



Posts: 826
Joined: 12-September 03
From: Illinois


Of course...I forgot all about the MaxLocks...I'll try that and see what happens.

--------------------
You cannot change the wind...but you can adjust your sails
Go to the top of the page
 
fiftyfour
post Oct 30 2017, 03:56 PM
Post#9



Posts: 826
Joined: 12-September 03
From: Illinois


For anyone interested, I tried everything and nothing worked. I moved the table with 70k records from the back end to the front end...and it works perfectly. Could it be the server where the back end was located?

--------------------
You cannot change the wind...but you can adjust your sails
Go to the top of the page
 
GroverParkGeorge
post Oct 30 2017, 04:28 PM
Post#10


UA Admin
Posts: 31,191
Joined: 20-June 02
From: Newcastle, WA


Sounds more like a network resource problem, give that bit of additional information.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 11:13 PM