BallunarCrew
Programmer
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
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