Recordset - system rosurce exceeded

[Visterio]

Hi everyone,

I have a problem with system rosurce error.

The macro works perfectly - really quickly (about 30 - 40 second to check what components are needed to be produced).

The problem is that macro:
- increase size of the data base (from 70 MB to 210 MB)
- after executing the macro I offten see the "3035 error - system resource exceeded" - esspecialy when I want to open the table updated by macro.

I tap into the whole Internet but found nothing that could help me.

I already:
- changed in the registry the key: MaxLocksPerFile - to 500.000

I think there was no problem unitl I created second recordset (signallist) - before I did it I updated the signallist table via sql query but performance was really poor) - about 50 minutes (now 40 seconds but there's the error).

Here's my code. Can anyone help me and check it? Maybe I did something wrong?

Thank you very much.

Sub MainEngine()
DoCmd.SetWarnings False
 
TableName = "DlaPlanisty_Sorted"
'funcion "CzyTabIstnieje" checks if the table exists
If CzyTabIstnieje(TableName) Then
    DoCmd.Close acTable, TableName
    DoCmd.DeleteObject acTable, TableName
End If

QryToTbl = "SELECT * INTO DlaPlanisty_Sorted" & _
    " FROM (SELECT tbl_BOM.Key, tbl_OO_SumedUp.[Mtrl No], tbl_OO_SumedUp.[Order Number], format([MinOfRequest Date],""yyyy-mm-dd"") AS [Request Date], tbl_OO_SumedUp.[SumOfQty Open] AS [Qty Open], tbl_BOM.Component, tbl_BOM.Level, (([tbl_OO_SumedUp].[SumOfQty Open]*[tbl_BOM].[Req Component Qty])/1000) AS Demand, tbl_Routing.[Work Center], """" as Data, """" AS [Braknie]" & _
    " FROM (tbl_OO_SumedUp LEFT JOIN tbl_BOM ON tbl_OO_SumedUp.[Mtrl No] = tbl_BOM.Header) LEFT JOIN tbl_Routing ON tbl_BOM.Component = tbl_Routing.Material" & _
    " WHERE (((tbl_BOM.Key) is not null and tbl_BOM.Component is not null))) AS DlaPlanisty" & _
    " ORDER BY DlaPlanisty.[Request Date], DlaPlanisty.[Order Number], DlaPlanisty.[Mtrl No], DlaPlanisty.Key;"

CurrentDb.Execute QryToTbl

strSQL = "UPDATE SignalList SET Demand = 0"
CurrentDb.Execute (strSQL)

Dim Streambase As DAO.Database
Dim RstPlanista As DAO.Recordset
Dim RstSignalList As DAO.Recordset
Dim StrSQL_Planista As String
Dim StrSQL_SignalList As String
Set Streambase = CurrentDb
Set RstPlanista = Streambase.OpenRecordset("DlaPlanisty_Sorted")
Set RstSignalList = Streambase.OpenRecordset("SignalList")
 
RstSignalList.Index = "PrimaryKey"
KeyPrev = 0
Do Until RstPlanista.EOF
    
KeyActual = RstPlanista!Key
    
    'Check if the component is chagned (current record vs previous record)
    If KeyPrev <> KeyActual Then
        
        Material = RstPlanista!Component
        Demand = RstPlanista!Demand
                
        Level = RstPlanista!Level
        
        RstSignalList.Seek "=", Material
        If RstSignalList.NoMatch Then
           Stock = 0
           DemandPrev = 0
        Else
           Stock = RstSignalList!Stock
           DemandPrev = RstSignalList!Demand
        End If
       
        RstSignalList.Edit
        RstSignalList!Demand.Value = DemandPrev + Demand
        RstSignalList.Update
      
    End If
    
'check if production of component is needed
If ProdukcjaKonieczna = False Then
            
    If LevelBase < Level Then
            
    Else
        
        If Stock < DemandPrev + Demand Then
            'Production needed becuase there's no stock
                
            If Stock - DemandPrev < 0 Then
                PozostaloDoUzycia = 0
            Else
                PozostaloDoUzycia = Stock - DemandPrev
            End If
            
            IleBraknie = Demand - PozostaloDoUzycia
            
            RstPlanista.Edit
            RstPlanista!Braknie.Value = IleBraknie
            RstPlanista.Update
             
            ProdukcjaKonieczna = True
        Else
            'production not needed
            ProdukcjaKonieczna = False
            LevelBase = RstPlanista!Level
        End If
        
    End If
Else
    'Production needed
    
    
        If Stock < DemandPrev + Demand Then
            'roduction needed
                
            IleBraknie = DemandPrev + Demand - Stock
                
            RstPlanista.Edit
            RstPlanista!Braknie.Value = IleBraknie
            RstPlanista.Update
                
            ProdukcjaKonieczna = True
        Else
            'Production not needed
                
            ProdukcjaKonieczna = False
            LevelBase = RstPlanista!Level
        End If
End If
KeyPrev = KeyActual
RstPlanista.MoveNext
Loop
    RstPlanista.Close
    RstSignalList.Close
    Streambase.Close
    Set RstPlanista = Nothing
    Set RstSignalList = Nothing
    Set Streambase = Nothing

End Sub

 

[jdraw]

Have you tried stepping through your code, or setting some breakpoints to see what is actually happening? You could try some Debug.Print variable name(s).

Have you tried using/testing with a small set of records to make sure the logic is what you need?

What version of Access are you using?


When you use Currentdb.Execute, I recommend you use the dbFailOnError option.
This is not causing the resource issue, but is a good practice.

 

[Visterio]

jdraw thanks for your reply.

It's Access 2010 - main table has less than 98.000 records and the second has 41.000. The loop is going through the larger table (record by record)

I listened to your advice and I've just set a breakpoint at the end of the loop (but every 5000 records). What I've noticed is:

- setting the recordsets increases the size of the file by 11 MB (70 -> 81 MB)
- looping throught the table from records 0 - 50.000 increases the size of the file by another 50MB (so after 50.000 records it's 131MB)
- and then every 5.000 records macro increases the size of the file by another 7 - 9 MB
- finally file reaches 210 MB

The idea is good and updated values in the tables are caluclated properly - no mistakes. But the table which was updated (only one column in it was updated) opens visibly longer and offten It cannot be open becuase there's an error "system resurce exceeded".

Then I have to repair the data base and here are two options:
- database is repaired and there's no further errors
- databasa cannot be repaired becuase while reparing there's an error that database cannot find the object ''.... And this is strange becuase the object is blank -> ' (ANY NAME - completly nothing) ' so acctualy it looks like ''. But I hit OK and click on repair one more time and it works

About testing with smaller set of records I stopped tha macro after 80.000 records (so 20.000 left to the finish) and there was no error but the table opens significantly slower and data base hase about 180 MB.

And what is funny, sometimes I get the resource exceeding error during the last loop run.

Thanks for tha advice with the dbFailOnError.