System Resources Exceeded

[BallunarCrew]

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

 

[dhookom]

I would use SQL like

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

BTW: please use TGML to format your postings ;-)

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

 

[BallunarCrew]

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

 

[dhookom]

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

 

[BallunarCrew]

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

 

[dhookom]

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

 

[missinglinq]

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.ph...base-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