How to prevent “out of memory” error while uploading files with access-VBA?2019 Community Moderator ElectionUpload pdf via multipart-HTML-Post does change fileStrange out of memory issue while loading an image to a Bitmap objectuploading file from Access 2010How to clear memory to prevent “out of memory error” in excel vba?VBA running out of memoryOut of memory error on accessError while Search QueryDef in Access VBAMemory out of bound while uploading a large filePowerPoint VBA “Out of Memory”Out of Memory Error in VBA CodePrevent out of memory error with spring controller that accepts a file
A vote on the Brexit backstop
How to make sure I'm assertive enough in contact with subordinates?
Has a sovereign Communist government ever run, and conceded loss, on a fair election?
Averaging over columns while ignoring zero entries
Create chunks from an array
Was it really inappropriate to write a pull request for the company I interviewed with?
Precision notation for voltmeters
Tabular environment - text vertically positions itself by bottom of tikz picture in adjacent cell
Generating a list with duplicate entries
Short story about cities being connected by a conveyor belt
How can I portion out frozen cookie dough?
Is there a math expression equivalent to the conditional ternary operator?
What does *dead* mean in *What do you mean, dead?*?
Should we avoid writing fiction about historical events without extensive research?
How does a sound wave propagate?
Why do we say 'Pairwise Disjoint', rather than 'Disjoint'?
What exactly is the meaning of "fine wine"?
What is the oldest European royal house?
A running toilet that stops itself
Do I need a return ticket to Canada if I'm a Japanese National?
How spaceships determine each other's mass in space?
Draw this image in the TIKZ package
Why aren't there more Gauls like Obelix?
I am the light that shines in the dark
How to prevent “out of memory” error while uploading files with access-VBA?
2019 Community Moderator ElectionUpload pdf via multipart-HTML-Post does change fileStrange out of memory issue while loading an image to a Bitmap objectuploading file from Access 2010How to clear memory to prevent “out of memory error” in excel vba?VBA running out of memoryOut of memory error on accessError while Search QueryDef in Access VBAMemory out of bound while uploading a large filePowerPoint VBA “Out of Memory”Out of Memory Error in VBA CodePrevent out of memory error with spring controller that accepts a file
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-Disposition: form-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.
So: Is there a way to extend the ram available to access as there is > 3GB free ram on the computer?
And if not, is there a way to change the code so that there is not as much memory needed at once?
Update:
How I upload the file is the result of this question of mine on stack overflow: Upload pdf via multipart-HTML-Post does change file
vba file ms-access upload out-of-memory
asked 2 days ago
Gener4torGener4tor
35416
|
show 6 more comments
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-Disposition: form-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.
So: Is there a way to extend the ram available to access as there is > 3GB free ram on the computer?
And if not, is there a way to change the code so that there is not as much memory needed at once?
Update:
How I upload the file is the result of this question of mine on stack overflow: Upload pdf via multipart-HTML-Post does change file
vba file ms-access upload out-of-memory
asked 2 days ago
Gener4torGener4tor
35416
You could try closing your streams each time, maybe use the actual libraries also, so no create objects, not 100%
– Nathan_Sav
2 days ago
@Nathan_Sav: I added a .Close before every "end with" but I could not see any changes.
– Gener4tor
2 days ago
3
I don't understand why you're reading the stream data into a byte array, then writing that byte array to a stream, then reading that stream back into a byte array and then sending it. Afaik this causes the entire file to be in memory at least 4 times, while it should be in memory at most twice. Ideally, however, you'd use a chunking upload (so that only a few KB needs to be in memory at once), but that's a pretty advanced subject.
– Erik A
2 days ago
@Erik A: I dont think the file is in memory 4 times at once. I load it into the byteArray. Then I load it into the other stream and delete the fist stream. Then I load it into the webRequest. But of course Im open for any other way to do this. How I upload the file is the result of this question of mine on stack overflow: stackoverflow.com/questions/52389091/…
– Gener4tor
2 days ago
Is Rubberduck loaded? It's a useful add-in, but takes its toll in terms of memory available to the host process - is it 32-bit or 64-bit Access?
– Mathieu Guindon
2 days ago
|
show 6 more comments
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-Disposition: form-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.
So: Is there a way to extend the ram available to access as there is > 3GB free ram on the computer?
And if not, is there a way to change the code so that there is not as much memory needed at once?
Update:
How I upload the file is the result of this question of mine on stack overflow: Upload pdf via multipart-HTML-Post does change file
vba file ms-access upload out-of-memory
asked 2 days ago
Gener4torGener4tor
35416
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-Disposition: form-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.
So: Is there a way to extend the ram available to access as there is > 3GB free ram on the computer?
And if not, is there a way to change the code so that there is not as much memory needed at once?
Update:
How I upload the file is the result of this question of mine on stack overflow: Upload pdf via multipart-HTML-Post does change file
vba file ms-access upload out-of-memory
vba file ms-access upload out-of-memory
asked 2 days ago
Gener4torGener4tor
35416
asked 2 days ago
Gener4torGener4tor
35416
edited 2 days ago
Gener4tor
asked 2 days ago
Gener4torGener4tor
35416
asked 2 days ago
Gener4torGener4tor
35416
asked 2 days ago
Gener4torGener4tor
35416
35416
You could try closing your streams each time, maybe use the actual libraries also, so no create objects, not 100%
– Nathan_Sav
2 days ago
@Nathan_Sav: I added a .Close before every "end with" but I could not see any changes.
– Gener4tor
2 days ago
3
I don't understand why you're reading the stream data into a byte array, then writing that byte array to a stream, then reading that stream back into a byte array and then sending it. Afaik this causes the entire file to be in memory at least 4 times, while it should be in memory at most twice. Ideally, however, you'd use a chunking upload (so that only a few KB needs to be in memory at once), but that's a pretty advanced subject.
– Erik A
2 days ago
@Erik A: I dont think the file is in memory 4 times at once. I load it into the byteArray. Then I load it into the other stream and delete the fist stream. Then I load it into the webRequest. But of course Im open for any other way to do this. How I upload the file is the result of this question of mine on stack overflow: stackoverflow.com/questions/52389091/…
– Gener4tor
2 days ago
Is Rubberduck loaded? It's a useful add-in, but takes its toll in terms of memory available to the host process - is it 32-bit or 64-bit Access?
– Mathieu Guindon
2 days ago
|
show 6 more comments
You could try closing your streams each time, maybe use the actual libraries also, so no create objects, not 100%
– Nathan_Sav
2 days ago
@Nathan_Sav: I added a .Close before every "end with" but I could not see any changes.
– Gener4tor
2 days ago
3
I don't understand why you're reading the stream data into a byte array, then writing that byte array to a stream, then reading that stream back into a byte array and then sending it. Afaik this causes the entire file to be in memory at least 4 times, while it should be in memory at most twice. Ideally, however, you'd use a chunking upload (so that only a few KB needs to be in memory at once), but that's a pretty advanced subject.
– Erik A
2 days ago
@Erik A: I dont think the file is in memory 4 times at once. I load it into the byteArray. Then I load it into the other stream and delete the fist stream. Then I load it into the webRequest. But of course Im open for any other way to do this. How I upload the file is the result of this question of mine on stack overflow: stackoverflow.com/questions/52389091/…
– Gener4tor
2 days ago
Is Rubberduck loaded? It's a useful add-in, but takes its toll in terms of memory available to the host process - is it 32-bit or 64-bit Access?
– Mathieu Guindon
2 days ago
You could try closing your streams each time, maybe use the actual libraries also, so no create objects, not 100%
– Nathan_Sav
2 days ago
You could try closing your streams each time, maybe use the actual libraries also, so no create objects, not 100%
– Nathan_Sav
2 days ago
@Nathan_Sav: I added a .Close before every "end with" but I could not see any changes.
– Gener4tor
2 days ago
@Nathan_Sav: I added a .Close before every "end with" but I could not see any changes.
– Gener4tor
2 days ago
3
3
I don't understand why you're reading the stream data into a byte array, then writing that byte array to a stream, then reading that stream back into a byte array and then sending it. Afaik this causes the entire file to be in memory at least 4 times, while it should be in memory at most twice. Ideally, however, you'd use a chunking upload (so that only a few KB needs to be in memory at once), but that's a pretty advanced subject.
– Erik A
2 days ago
I don't understand why you're reading the stream data into a byte array, then writing that byte array to a stream, then reading that stream back into a byte array and then sending it. Afaik this causes the entire file to be in memory at least 4 times, while it should be in memory at most twice. Ideally, however, you'd use a chunking upload (so that only a few KB needs to be in memory at once), but that's a pretty advanced subject.
– Erik A
2 days ago
@Erik A: I dont think the file is in memory 4 times at once. I load it into the byteArray. Then I load it into the other stream and delete the fist stream. Then I load it into the webRequest. But of course Im open for any other way to do this. How I upload the file is the result of this question of mine on stack overflow: stackoverflow.com/questions/52389091/…
– Gener4tor
2 days ago
@Erik A: I dont think the file is in memory 4 times at once. I load it into the byteArray. Then I load it into the other stream and delete the fist stream. Then I load it into the webRequest. But of course Im open for any other way to do this. How I upload the file is the result of this question of mine on stack overflow: stackoverflow.com/questions/52389091/…
– Gener4tor
2 days ago
Is Rubberduck loaded? It's a useful add-in, but takes its toll in terms of memory available to the host process - is it 32-bit or 64-bit Access?
– Mathieu Guindon
2 days ago
Is Rubberduck loaded? It's a useful add-in, but takes its toll in terms of memory available to the host process - is it 32-bit or 64-bit Access?
– Mathieu Guindon
2 days ago
|
show 6 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55026441%2fhow-to-prevent-out-of-memory-error-while-uploading-files-with-access-vba%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55026441%2fhow-to-prevent-out-of-memory-error-while-uploading-files-with-access-vba%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You could try closing your streams each time, maybe use the actual libraries also, so no create objects, not 100%
– Nathan_Sav
2 days ago
@Nathan_Sav: I added a .Close before every "end with" but I could not see any changes.
– Gener4tor
2 days ago
3
I don't understand why you're reading the stream data into a byte array, then writing that byte array to a stream, then reading that stream back into a byte array and then sending it. Afaik this causes the entire file to be in memory at least 4 times, while it should be in memory at most twice. Ideally, however, you'd use a chunking upload (so that only a few KB needs to be in memory at once), but that's a pretty advanced subject.
– Erik A
2 days ago
@Erik A: I dont think the file is in memory 4 times at once. I load it into the byteArray. Then I load it into the other stream and delete the fist stream. Then I load it into the webRequest. But of course Im open for any other way to do this. How I upload the file is the result of this question of mine on stack overflow: stackoverflow.com/questions/52389091/…
– Gener4tor
2 days ago
Is Rubberduck loaded? It's a useful add-in, but takes its toll in terms of memory available to the host process - is it 32-bit or 64-bit Access?
– Mathieu Guindon
2 days ago