Airmaster
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Okay, works just fine. Here are the changes I made to the original code listing.
Maybe there is a more elegant way of doing it, but works.
I think the Microsoft documentation is misleading/not specific enough.
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 Maybe there is a more elegant way of doing it, but works.
I think the Microsoft documentation is misleading/not specific enough.
ASKER
Unless you have any other great comments, I will close this out.
glad this worked for you
ASKER
Just as an additional comment, on the original code, it asks you to do a
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 * from FSTest", con, adOpenDynamic, adLockOptimisticAfter 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
ASKER
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.