By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,533 Members | 2,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,533 IT Pros & Developers. It's quick & easy.

Problem saving binary data in OLE Object field

P: n/a
Hello.

I am attempting to write binary data from a file to an
OLE Object field, and then write the file back out from
the database. I am reading and writing the files in
binary mode, and using GetChunk and AppendChunk to read
and write binary data from the OLE Object field. I am
using VBA and DAO for this experiment. The OLE Object
field is being used to store Long Binary data.
I have tried four variations as follows:

1. a. Read file data into a byte array
b. AppendChunk byte array to DAO field
c. GetChunk DAO field data into byte array
d. Write byte array to file

This SHOULD be the correct method, but it doesn't
work. I don't know why. The output file is the same
size as the input file. The first half of the output
file matches the first half of the input file.
The second half of the output file is garbage.
2. a. Read file data into a string variable
b. AppendChunk string variable to DAO field
c. GetChunk DAO field data into string variable
d. Write string variable to file

With this approach, the output file is half the
size of the input file. The output file matches the
first half of the input file.

Obviously, the devil here is that VB stores string
data internally as double-byte characters. With
this in mind, I can actually get this approach
to work if I calculate the chunk size based on a
doubled input file size. But this doubles the storage
size in the database, since the data is saved as
double-byte characters.
The other two methods mixed the string and byte
array methods (read string/write byte array,
read byte array/write string). Neither worked.

Suggestions?

Thanks
-Mark
Mar 10 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"Mark" <no****@thanksanyway.org> wrote:
Hello.

I am attempting to write binary data from a file to an
OLE Object field, and then write the file back out from
the database.


Ok, I checked out the Stephen Lebans examples that
Bri referred me to. It turns out that the code I am using
is clearly based on the ReadBlob() and WriteBlob()
code that he uses from Microsoft's KB article
Q103257 (this code has been modified and re-posted
without credit on dozens of other sites.) I tried using
this sample code verbatim, and it works just like my
code did when using a string variable with GetChunk
and AppendChunk.*

So I guess my question is: is there any way to use
VBA to write binary data to an OLE Object field
without using a BSTR and suffering the storage
penalty of saving the binary data as double-byte
characters?

Thanks again
-Mark

* The only difference is that the code in Q103257
doesn't have to lie about the input file size. I'm not
sure why, but I'll figure it out.


Mar 10 '06 #2

P: n/a
Bri
I wonder if the problems you are having with the String has anything
to do with Unicode. I'm no expert on it but I seem to recall that it
takes twice the space that a traditional string would. Also, the AC97
help states that there is a 65,535 character limit for binary data in a
Memo field. This may be different for AC2K+ versions.

As for the problem with the OLE Object field, I've never used one the
way you are trying, so I don'tknow what is happening to it. Here is a
link to a Lebans page that deals with OLE object fields and files:
http://www.lebans.com/oletodisk.htm

--
Bri

Mar 10 '06 #3

P: n/a
"Bri" <no*@here.com> wrote:
I wonder if the problems you are having with the String has anything to
do with Unicode.


Definitely. I believe the correct way to do this is to use a byte array
instead of a string, and I think I figured out where I got that wrong.
I am now able to store binary data in an OLE Object field with only
about 8k of overhead per record, which isn't too bad for my purposes.
The only remaining glitch is an extra 18 bytes of null data that gets saved
at the end of each record.
Mar 10 '06 #4

P: n/a
I don't know what sample MDB on my site that Bri referred you to but perhaps
you are looking at the wrong one. Just read the data into a Byte array and
write it back out the same way with Get and Put. There are several examples
of this on my site and in my NG postings.

My code works as advertised. Your changing of the existing logic to use
String variables and then stating it does not work is your problem not mine.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
"Mark" <no****@thanksanyway.org> wrote in message
news:c7********************@w-link.net...
"Mark" <no****@thanksanyway.org> wrote:
Hello.

I am attempting to write binary data from a file to an
OLE Object field, and then write the file back out from
the database.


Ok, I checked out the Stephen Lebans examples that
Bri referred me to. It turns out that the code I am using
is clearly based on the ReadBlob() and WriteBlob()
code that he uses from Microsoft's KB article
Q103257 (this code has been modified and re-posted
without credit on dozens of other sites.) I tried using
this sample code verbatim, and it works just like my
code did when using a string variable with GetChunk
and AppendChunk.*

So I guess my question is: is there any way to use
VBA to write binary data to an OLE Object field
without using a BSTR and suffering the storage
penalty of saving the binary data as double-byte
characters?

Thanks again
-Mark

* The only difference is that the code in Q103257
doesn't have to lie about the input file size. I'm not
sure why, but I'll figure it out.

Mar 11 '06 #5

P: n/a
"Stephen Lebans" <ForEmailGotoMy.WebSite.-WWWdotlebansdot...@linvalid.com>
wrote:
I don't know what sample MDB on my site that Bri referred you to but
perhaps you are looking at the wrong one. Just read the data into a Byte
array and write it back out the same way with Get and Put. There are
several examples of this on my site and in my NG postings.
I was referring to the code in Microsoft Kb article Q103257. I thought I
found
that from a reference on your site, but now I think I was mistaken about
that.
My apologies. I followed a _lot_ of links today, and it is hard to keep
track
of them all.
My code works as advertised. Your changing of the existing logic to use
String variables and then stating it does not work is your problem not
mine.


Actually, I don't think I have worked with any of the code from your site.
I am sure it is fine. And yes, as you have said, the correct approach is
to use a Byte array. Once again, my apologies.

-Mark
Mar 11 '06 #6

P: n/a
Bri

Stephen Lebans wrote:
I don't know what sample MDB on my site that Bri referred you to but perhaps
you are looking at the wrong one. Just read the data into a Byte array and
write it back out the same way with Get and Put. There are several examples
of this on my site and in my NG postings.

My code works as advertised. Your changing of the existing logic to use
String variables and then stating it does not work is your problem not mine.


Actually, I had referred him to Larry Linson's demo of three methods for
dealing with images:
http://members.tripod.com/accdevel/imaging.htm

I later referred a page on your site (at a time later than the message
that you replied to):
http://www.lebans.com/oletodisk.htm

I'm sure that it was Larry's site he meant to refer to as it shows the
three examples, including the one using the OLE Object field to hold an
image with the resulting huge overhead.

--
Bri

Mar 11 '06 #7

P: n/a
bob

The size of the field contents should be exactly the same as the size of the original file. You can easily
check this by running a query like the following:

SELECT Id, LenB([ImageField]) AS Expr1
FROM tblImages;

Note that the access mdb file grows in 64KB chunks, so there is a variable amount of slack space in there.
Also, variable length data is allocated in 1KB chunks, so there is a little slack for each record, but
this is usually much less than the slack space that the image file would waste in the filesystem.

--
__________________________________________________ _____
http://www.ammara.com/dbpix/access.html
DBPix 2.0: Add pictures to Access, Easily & Efficiently

"Mark" <no****@thanksanyway.org> wrote:
"Bri" <no*@here.com> wrote:
I wonder if the problems you are having with the String has anything to
do with Unicode.


Definitely. I believe the correct way to do this is to use a byte array
instead of a string, and I think I figured out where I got that wrong.
I am now able to store binary data in an OLE Object field with only
about 8k of overhead per record, which isn't too bad for my purposes.
The only remaining glitch is an extra 18 bytes of null data that gets saved
at the end of each record.


Mar 11 '06 #8

P: n/a
"Bri" <no*@here.com> wrote:

Stephen Lebans wrote:
I don't know what sample MDB on my site that Bri referred you to but
perhaps you are looking at the wrong one. Just read the data into a Byte
array and write it back out the same way with Get and Put. There are
several examples of this on my site and in my NG postings.

My code works as advertised. Your changing of the existing logic to use
String variables and then stating it does not work is your problem not
mine.


Actually, I had referred him to Larry Linson's demo of three methods for
dealing with images:
http://members.tripod.com/accdevel/imaging.htm


Yes, that is where I found the references to Q103257. I did not want
to identify the source in my apology to Stephen, lest I incur the
unhappiness
of yet another online consultant.

-Mark
Mar 11 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.