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
52 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 45

Accepted Solution

by:
aikimark earned 500 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 45

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 45

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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