INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

System Resources Exceeded

System Resources Exceeded

(OP)
Hi
I know there have been many posts on this topic but I am not finding an answer that works for us. We have a database that manages a doctors office. There is a lot of custom coding in it. We get the System Resources Exceeded error message frequently on .Update statements.
The database resides on a server and is accessed from PCs in the same office. This database is in Access 2013 but I also have the same issues on my development laptop running Access 2013. The server and office machines are Windows 8, the laptop is Windows 10.

The queries that set the record sets that are being updated are not complicated. Only one record at a time is being updated and typically it is a record being added. I have researched and made several changes to no avail. I have made sure each recordset if closed and set the Nothing. I changed the code from using code such as !fName to using .Fields("fName") since it was mentioned in another post that using the "!" is slow. This has not helped at all - if anything, it has made the situation worse.

We have cleaned up the server and there are over 77 gig free on the server, the pcs where Access runs are clean - over 800 gig free. There is plenty of memory on each machine. The development laptop is also clean.

I have read that running 32 bit Access on a 64 bit machine could be the problem but that running 64 bit Access is not a good idea. I am at a loss on what to do. I am not sure what resources are being exceeded. Quite often, when the code is shown with the error on the .Update line we can click the continue button and it runs fine from there.

I am trying to optimize the code to be as efficient as I can but I don't see it making a difference.
If I have this situation in the code below, is it best to open the recordset once and close it once or open and close it each time in the loop?

Set MyData - MyDB.OpenRecordset(tableName, dbOpendynaset)
While something is true
With MyData
.AddNew
.Fields("Field1") = "X"
.Fields("Field2") = "Y"
.Update
End With
Wend
MyData.Close
Set MyData = Nothing

or

While something is true
Set MyData - MyDB.OpenRecordset(tableName, dbOpendynaset)
With MyData
.AddNew
.Fields("Field1") = "X"
.Fields("Field2") = "Y"
.Update
End With
MyData.Close
Set MyData = Nothing
Wend
Most of the update statements that are failing are on things this simple.
Any other suggestions for this error? I am at my wits end as is my boss.
Thanks, Karen

RE: System Resources Exceeded

I would use SQL like

CODE --> vba

Dim strSQL
strSQL = "INSERT INTO [TableNameHere] ([Field1], [Field1]) " & _
    " VALUES( 'X' , 'Y' ) "
MyDB.Execute strSQL, dbFailOnError 

BTW: please use TGML to format your postings winky smile

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: System Resources Exceeded

(OP)
Duane,
This is Dr. Kyle. I am willing to pay you to help us resolve this problem. Are you willing to take a look at my database through a secure portal and see if you can solve this problem for us? Please respond to dr@spacechiro.com.
Thanks so much!
Dr. Kyle

RE: System Resources Exceeded

I don't do contract work as I want to be obligated only to my family and work (and some play). I'm not even sure it's appropriate to ask here. I'll post a link to this thread to a group of very good Access developers it that's alright.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: System Resources Exceeded

(OP)
That would be great Duane. I apologize if it was not an appropriate post.
Karen

RE: System Resources Exceeded

Asking for help like this might be okay. On the other hand if I was marketing a contracting business that would not be acceptable.

You may be contacted by someone.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: System Resources Exceeded

Quote (BallunarCrew)

The database resides on a server and is accessed from PCs in the same office


Given the above...this is obviously being used in a multi-user environment, and sounds as if a single copy of the database, residing on the server, is being accessed by all users. If this is the case, it may well be the problem!

Having multiple users sharing a single, non-split database, sitting on a network drive, is the sure way to repeated episodes of corruption, as well as speed and timing problems, which can result in running out of resources!

Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Here are two tutorials on Splitting a Database:

https://www.fmsinc.com/microsoftaccess/DatabaseSplitter/

http://www.hitechcoach.com/index.php?option=com_content&view=article&id=35:split-your-access-database-into-application-anddata&catid=24:design

Linq ;0)>

Hope this helps!

There's always more than one way to skin a cat!

All posts/responses based on Access 2003/2007

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close