Results 1 to 6 of 6

Thread: 7: Out of memory (Codes provided)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool 7: Out of memory (Codes provided)

    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 Fluff; Oct 4th, 2019 at 05:02 PM. Reason: Added code tags

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: 7: Out of memory (Codes provided)

    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-...ut-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 by Joe4; Oct 4th, 2019 at 05:07 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 7: Out of memory (Codes provided)

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

  4. #4
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 7: Out of memory (Codes provided)

    Quote Originally Posted by ivan624888 View Post
    Thanks Joe4, its about 202470 rows to be generated in Excel.
    More:
    OS:win10, office365

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,727
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 7: Out of memory (Codes provided)

    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
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  6. #6
    New Member
    Join Date
    Oct 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 7: Out of memory (Codes provided)

    Quote Originally Posted by Micron View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •