7: Out of memory (Codes provided)

ivan624888

New Member
Joined
Oct 4, 2019
Messages
5
WHEN click a button in Access, which suppose to generate an excel report normally. But this time it shows error 7: Out of memory

Code:
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.
 
Last edited by a moderator:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Last edited:
Upvote 0
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):

Code:
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
 
Upvote 0
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):

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

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
Back
Top