7: Out of memory (Codes provided)

[ivan624888]

WHEN click a button in Access, which suppose to generate an excel report normally. But this time it shows error 7: Out of memory

Private Sub btn_Click()
On Error GoTo ErrCheck


    Dim strPeriod As String
    Dim intFY As Integer
    Dim strFMth As String


    Dim cnt As New ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    
    Dim fldCount As Integer
    Dim recCount As Long
    Dim iCol As Integer
    
     ' Open connection to the database
    cnt.Open CONNECT_STRING_ADO


    Dim intFYear As Integer


    'Prompt user for fyear:
    DoCmd.OpenForm "frmFYearSelect", acNormal, , , acFormEdit, acDialog


    If DLookup("status_ok", "tblTemp_FYearSelect") = True Then
        intFYear = DLookup("FYear", "tblTemp_FYearSelect")
    Else
        GoTo ExitRoutine
    End If




    ' Define the recordset:
    Set rst = fnDisconnectedRS("SELECT * FROM udv_rpt_extract_depr_current_KERRY WHERE fyear = " & intFYear & " ORDER BY asset_number, cost_centre, gl_number")


    ' Copy field names to the first row of the worksheet
    ' Create an instance of Excel and add a workbook


    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
    
    fldCount = rst.Fields.Count
    For iCol = 1 To fldCount
        xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
    Next
        
    ' Copy the recordset to the worksheet, starting in cell A2


    xlWs.Cells(2, 1).CopyFromRecordset rst
    
     ' Display Excel and give user control of Excel's lifetime
    xlApp.Visible = True
    xlApp.UserControl = True
        
    ' Auto-fit the column widths and row heights
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit


   
    
    '=========================
    rst.Close
    cnt.Close
   
    Set rst = Nothing
    Set cnt = Nothing
    
    ' Release Excel references
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
    
ExitRoutine:
    Exit Sub
    
ErrCheck:
    MsgBox err.Number & "->" & err.description
    Resume ExitRoutine
End Sub

Appreciate your suggestions.

 

[Joe4]

Welcome to the Board!

How many records is it trying to export to Excel?
Note that Excel has a row limitation, so if you exceed that, you will probably get errors.

EDIT:
I see that you posted this in the "Excel Questions" forum too (https://www.mrexcel.com/forum/excel-questions/1111564-7-out-memory.html).
If you are going to do that, please follow our Cross-Posting rules, and be sure to mention that in each post and provide links.
(That way, people can see what has already been tried and if the issue might already be solved.)

I will do that for this one, but keep that in mind for the future.

 

[ivan624888]

Thanks Joe4, its about 202470 rows to be generated in Excel.

 

[ivan624888]

Thanks Joe4, its about 202470 rows to be generated in Excel.

More:
OS:win10, office365

 

[Micron]

This isn't my area of expertise, but since you asked for suggestions, here's mine. You open a connection and if the status is not OK, you don't close the connection. Could you have memory leaks as a result? Maybe, but you don't say if the problem arises with one pass of this code or after several. Beyond that I probably won't be of much help - except to say you should always indicate which line triggers an error - assuming a procedure begins to run at all.

While you might not be entering the error handler when generating this message, here is another point: in your code, if execution enters the error handler none of the objects are destroyed, which is another potential memory hog. IMHO your construct should always be like (not to be taken literally):

Sub NameOfProcedure()
Dim Stuff

On Error GoTo Whatever
Set things
Do stuff

exitHere: <<< exit line label
On Error Resume Next << if no recordset to close, an error can occur if you invoke Close on one
Close recordset
Set all to Nothing
Exit Sub/Function

Whatever:
code for error messaging
Resume exitHere <<< this ensures all is closed/set to Nothing
End Sub/Function

 

[ivan624888]

This isn't my area of expertise, but since you asked for suggestions, here's mine. You open a connection and if the status is not OK, you don't close the connection. Could you have memory leaks as a result? Maybe, but you don't say if the problem arises with one pass of this code or after several. Beyond that I probably won't be of much help - except to say you should always indicate which line triggers an error - assuming a procedure begins to run at all.

While you might not be entering the error handler when generating this message, here is another point: in your code, if execution enters the error handler none of the objects are destroyed, which is another potential memory hog. IMHO your construct should always be like (not to be taken literally):

Sub NameOfProcedure()
Dim Stuff

On Error GoTo Whatever
Set things
Do stuff

exitHere: <<< exit line label
On Error Resume Next << if no recordset to close, an error can occur if you invoke Close on one
Close recordset
Set all to Nothing
Exit Sub/Function

Whatever:
code for error messaging
Resume exitHere <<< this ensures all is closed/set to Nothing
End Sub/Function

I have two servers Dev and production. The original code works fine on Dev , but in production report out of memory error. Both are win10 office 365