Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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