none
How to prevent “out of memory” error while uploading files with access-VBA? RRS feed

  • Question

  • I loop through a folder and try to upload every file in the folder into an document management system with the following code:

    Dim webRequest As MSXML2.XMLHTTP60: Set webRequest = New MSXML2.XMLHTTP60

    Call webRequest.Open(HTTPPost, adress, False)

    Const Boundary As String = "AaB03x"

    Call webRequest.setRequestHeader(HTTPHeaderValueContentType, "multipart/form-data;boundary=" & Boundary)

    Call webRequest.setRequestHeader(HTTPHeaderValueCallingApplication, get_sys_db)

    Call webRequest.setRequestHeader("Connection", "Keep-Alive")

    Call webRequest.setRequestHeader("cache-control", "no-cache")

    Dim byteData As Variant

    With CreateObject("ADODB.Stream")

        .Type = StreamType.Binary

        .Mode = StreamMode.ReadWrite

        .Open

        Call .LoadFromFile(fileName)

        byteData = .Read  'Somethimes here I get the "out of memory" error

    End With

    'dim requestData as Variant

    With CreateObject("ADODB.Stream")

        .Mode = StreamMode.ReadWrite

        .Charset = WindowsCharset

        .Open

        .Type = StreamType.Text

        Dim uploadFilename As String

        uploadFilename = ZConverter.VariantToString(cut_file(fileName))

        .WriteText CreateContentDisposionText("itemName", uploadFilename, Boundary) 

        .WriteText CreateContentDisposionText("parentNickname", parentNickname, Boundary)

        .WriteText vbNewLine & "--" & Boundary & vbNewLine & "Content-Dis-data; name=""file""; filename=""" & fileName & """" & vbNewLine

        .WriteText vbNewLine

        .Position = StreamPositionStart

        .Type = StreamType.Binary

        .Position = .Size

        .Write byteData

        Set byteData = Nothing

        .Position = StreamPositionStart

        .Type = StreamType.Text

        .Position = .Size

        .WriteText vbNewLine & vbNewLine & "--" & Boundary & "--" & vbNewLine

        .Position = StreamPositionStart

        .Type = StreamType.Binary

        Call webRequest.Send(.Read) 'Here I get the "out of memory" error most of the time

    End With

    So this works fine for small files.

    But when I try to upload a file with 420 mb I get the error: "Not enough memory available for this task" (original: "Für diesen Vorgang ist nicht genügend Speicher verfügbar."). With the VBE (The development enviroment) open it fails even with 20 mb-files.

    Access needs ~ 55 mb ram with closed VBE and 140 mb with a open VBE. As the memory usage always falls back to this values I dont think there is a memory leak.

    As described here:

    https://social.msdn.microsoft.com/Forums/office/en-US/dd3717ed-2c76-4ed0-8af3-fef8473339fa/access-running-out-of-memory

    and here:

    https://social.msdn.microsoft.com/Forums/office/en-US/d01c466c-f6c5-4a5d-a1bf-818ce9b03261/system-resource-exceeded?forum=accessdev

    the max RAM available to ms-access should be 2 GB but the error messages thrown by vba are starting at ~500MB (as shown in the task-manager).

    So: Is there a way to extend the ram available to access? Is there some kind of switch?

    Wednesday, November 27, 2019 11:52 AM

All replies

  • I think the only way around the Out of memory condition is to read the file in chunks, not in one piece, something along the lines (pseudo code)

    Do while not EOF

      byteData = .Read(some chunk)

      .Write byteData

    Loop

    The 2GB limit in Access is very theoretical, Out Of Memory condition often occur much earlier. I'm usually doing the "chunked" read/write as soon as the files exceed 64MB in size. 

    Matthias Kläy, Kläy Computing AG

    Saturday, November 30, 2019 1:34 PM