Annoying system resource exceeded error

[mahmed1]

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

 

[Joe4]

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]

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?

        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]

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]

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]

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.

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]

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