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:

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,083
Office Version
365
Platform
Windows
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:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,803
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
 

ivan624888

New Member
Joined
Oct 4, 2019
Messages
5
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
 

Forum statistics

Threads
1,089,467
Messages
5,408,430
Members
403,204
Latest member
pth

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top