Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-08-25
7
Medium Priority
?
90 Views
Last Modified: 2016-08-26
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
Comment
Question by:Airmaster
  • 4
  • 3
7 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 41771606
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
 

Author Comment

by:Airmaster
ID: 41771765
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
 
LVL 46

Expert Comment

by:aikimark
ID: 41771791
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Airmaster
ID: 41771871
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
 

Author Comment

by:Airmaster
ID: 41771873
Unless you have any other great comments, I will close this out.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 41772032
glad this worked for you
0
 

Author Comment

by:Airmaster
ID: 41772097
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question