Annoying system resource exceeded error

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Guys,

We recently moved servers to the cloud and now every other time when i run my macro to get data from access i get this error “system resource exceeded error”

now ive never had this issue before and funny enough i get this error sometimes so dont know how to get around this as if i share this with users i dont want them
To face this error issue...

im using office 365...

like i say it works but throws up that message every now and again when it comes to opening the recordset bit

how can i get around this or put some code At the point to over come this annoying issue

many thanks
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
Without seeing your code, it really is difficult to make any suggestions.

Also, how big is your Access database?
Do you regularly run "Compact and Repair" database on it to keep it lean?
Note that if you delete data from Access, it doesn't reduce the size of the database and doesn't really purge the data from the database until you run a "Compact and Repair" on it.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Without seeing your code, it really is difficult to make any suggestions.

Also, how big is your Access database?
Do you regularly run "Compact and Repair" database on it to keep it lean?
Note that if you delete data from Access, it doesn't reduce the size of the database and doesn't really purge the data from the database until you run a "Compact and Repair" on it.
Hiya

I compacted and repaired yesterday

The Access filesize is 888,676kb

This the code snippet im using - like I say it works but it may or may not come up with that error

Is it to do with something in the select statement or the [Team Manager] field I don't know?

VBA Code:
        Application.StatusBar = "Updating Agent Information"
        
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        
        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
            .ConnectionString = myDB8
            .Open
        End With
        
        'On Error GoTo CloseConnection
        
        With rs
            .ActiveConnection = cn
            .Source = "SELECT Date," & _
                      "muID," & _
                      "tvID," & _
                      "agentName," & _
                      "[Team Manager]," & _
                      "CCM," & _
                      "externalID" & _
                      " FROM tbl_Rolling_Agent_Info" & _
                      " WHERE Date >= #" & Format(DateValue(StartDate), "yyyy\/mm\/dd") & "# AND Date <= #" & Format(DateValue(EndDate), "yyyy\/mm\/dd") & "# " & _
                      " AND muID IN(2000,2001,2003,3003,4000)"
            .LockType = adLockReadOnly
            .CursorLocation = adUseClient
            .CursorType = adOpenForwardOnly
            .Open
                
            'On Error GoTo CloseRecordset
            
            Call DeleteAgentInfo
            Agent_Info.Range("A2").CopyFromRecordset rs
        End With
        
        'Resize Table
        lRowCount = Agent_Info.Range("A:I").Find(what:="*", After:=Agent_Info.Range("A1"), SearchDirection:=xlPrevious).Row
        tblAgentInfo.Resize Agent_Info.Range("A1:I" & lRowCount)
        
        'CloseRecordset:
        rs.Close
        
        'CloseConnection:
        cn.Close
        
        Set rs = Nothing
        Set cn = Nothing
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
A few things to check:

1. When this happens, try building the exact same directly query in Access (with the exact same criteria) and running it Access and see what happens. Perhaps some record has a data error which is causing issues.

2. Also, confirm how many rows that would be returned by your query. Remember, Excel has limits on the number of rows that can be returned to a single worksheet (1048576). Once again, running the query in Access would a a good way to check to see if you are exceeding this number.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

A few things to check:

1. When this happens, try building the exact same directly query in Access (with the exact same criteria) and running it Access and see what happens. Perhaps some record has a data error which is causing issues.

2. Also, confirm how many rows that would be returned by your query. Remember, Excel has limits on the number of rows that can be returned to a single worksheet (1048576). Once again, running the query in Access would a a good way to check to see if you are exceeding this number.
Hi - If i run exact same code in access i get the same error

im not returning more than 10k rows from the actual database

funny enough if it errors - it errors at the the recordset open - if i debug and press continue it works fine

so how can i add code at that open part to say if there is an error then trying the open again (basically mirroring me trying the open part again)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,996
Office Version
  1. 365
Platform
  1. Windows
Hi - If i run exact same code in access i get the same error
Then that seems to point to a data issue. If you locate what piece of data is causing the error, you may be able to address it.

One way to try to locate it is to start of with something real simple, pulling back one field from your table with no criteria, i.e.
VBA Code:
Select Date 
FROM tbl_Rolling_Agent_Info
If that works, then add in the next field. Keep repeating, adding in all the fields one-by-one, and checking that your query still works.
If you get an error anywhere along the way, then you know that the last field entered is the issue. So open your table up, and sort by that field and locate the bad data.

If all that works, then enter in your first criteria, and repeat the process we did above for all the criteria.
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you

I found that there it was creating syscompact error table (index on 1 of the fields not found)

but now I am faced with another weird issue

I have a query connection to pull back data from access and again I get read 10 at bottom followed by same resource error issue

this 1 defo has no errors but I have to refresh like 5/6 times and then query connection works
 

Watch MrExcel Video

Forum statistics

Threads
1,130,086
Messages
5,640,014
Members
417,122
Latest member
kirk5370

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top