• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 102
  • Last Modified:

Out of Memory: How to save and read files stored as FileStream in SQL Server 2008 from MS Access 2007 vba ?

I have been succesful using the procedure outline in:

https://www.experts-exchange.com/questions/26923910/How-to-save-and-read-files-stored-as-FileStream-in-SQL-Server-2008-from-MS-Access-2007-vba.html

The issue is that if it file is large, and for me, that was a file in the 100 MB range (50 MB is no issue), I get an out of memory problem at line 18: rs!FS = objStream.Read

I have plenty of RAM (4 GB free), so it isn't that simple.  I have tried adUseClient and adUseServer, but both a memory error, although the error messages read a bit different.

Do I need to somehow write the file in chunks < 100 MB?  If so, how?
0
Airmaster
Asked:
Airmaster
  • 4
  • 3
1 Solution
 
aikimarkCommented:
You might have to read and append the stream in chunks.
There is a parameter you can specify on the ADODB.STREAM Read() method for the number of bytes you want to read.
https://msdn.microsoft.com/en-us/library/ms676702(v=vs.85).aspx

Depending on the recordset field, you might be able to use the APPENDCHUNK method
https://msdn.microsoft.com/en-us/library/ms678268(v=vs.85).aspx
0
 
AirmasterAuthor Commented:
Thanks, I will give that a shot.  I noticed the Read doesn't specify an offset, just the # of bytes to read.  If so, how do I specify the next set of bytes to read?

Also an odd note AppendChunk Method
The AppendChunk method does not operate on Field objects of a Record Object (ADO) object. It does not perform any operation and will produce a run-time error.
0
 
aikimarkCommented:
I read that too.  Not sure how/if it applies.

The way a stream behaves should be like a file open, your read operation leaves you positioned some place in the file, ready for the next read operation.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
AirmasterAuthor Commented:
Okay, works just fine.  Here are the changes I made to the original code listing.

Dim varStreamTemp As Variant, lngMaxBytes As Long

lngMaxBytes = 1000000
varStreamTemp = objStream.Read(lngMaxBytes)
Do Until IsNull(varStreamTemp)
  rs("FS").AppendChunk varStreamTemp
  varStreamTemp = objStream.Read(lngMaxBytes)
Loop  

Open in new window


Maybe there is a more elegant way of doing it, but works.

I think the Microsoft documentation is misleading/not specific enough.
0
 
AirmasterAuthor Commented:
Unless you have any other great comments, I will close this out.
0
 
aikimarkCommented:
glad this worked for you
0
 
AirmasterAuthor Commented:
Just as an additional comment, on the original code, it asks you to do a

rs.Open "Select * from FSTest", con, adOpenDynamic, adLockOptimistic

Open in new window


After adding that big file, the server ran to a crawl when encountering the embeded document.  This happened even if I ran it the SELECT from SQL Server Management Studio.

I got back that by replacing it with

rs.Open "Select TOP 1 * from FSTest", con, adOpenDynamic, adLockOptimistic

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now