Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
80 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

721 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