linson's BLOB storage + no bloat

[sime]

I was very pleased to find Larry Linson's tutorial on various ways to
store images in an Access database. As anyone who searches on this
subject would know, the main problem with storing images in the
database (eg. as OLE object) is to avoid database bloat. Larry's method
was great, however each file I stored was still contributing twice it's
weight to the overall .mdb size.

Larry's article is here: http://members.tripod.com/accdevel/imaging.htm

Fortunately, I found a solution to this problem from a couple of old
posts by Roger Abbit. This shows me how to avoid storing the file as a
Unicode format (double-byte) string. Before writing the data to the
field, it is converted to a binary string using ChrB$.

Roger's posts are here:
http://groups.google.com.au/group/c...f9f17?lnk=st&q=&rnum=3&hl=en#62811456f14f9f17
http://groups.google.com.au/group/c...a2425?lnk=st&q=&rnum=6&hl=en#6ada375027ca2425

I would update the relevant threads, but they have been closed by
moderators, so hence a new post for the benefit of weary travellers. If
either of the original authors reads this post: thanks!

Regards, Simon

 

[Larry Linson]

Thanks for the comments.

Larry Linson
Microsoft Access MVP

 

[aaron.kempf]

if you used Access Data Projects you wouldn't have to deal with
obnoxious bloat.

MDB is DED and Larry is a pansy.

Use Data Projects.
And either way-- dont' store documents inside a database; unless it's
in XML and then use SQL 2005.

it has a real XML datatype and it friggin rocks!!


-Aaron
ADP Nationalist

 

[sime]

But that's only relevant if you are storing String data in the BLOB field.
Why would you store string data in a BLOB field instead of a normal Text or
Memo field?

I am storing binary data in a BLOB field. I don't know why the data is
being read in as text, I am just hacking other people's code.

 

[sime]

if you used Access Data Projects you wouldn't have to deal with
obnoxious bloat.

Sure, just email me the .exe and I'll give it a burl.

 

[sime]

Hi Stephen

First, this is Larry's code that reads the file into the table. I
followed Larry's instructions, except I was not able to create a "Long
Binary" field in my table, I just didn't seem to have that option and
not even the references KnowledgeBase articles told me how, so I just
used an OLE field.

This code is what caused my files to occupy double space in the
database. I've trimmed out the irrelevant parts (error checking etc,
general comments).

*** Larry Linson's FileToBLOB snippet ************
Open Source For Binary Access Read As SourceFile

FileLength = LOF(SourceFile)

NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize

FileData = String$(LeftOver, 32)
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)

FileData = String$(BlockSize, 32)
For i = 1 To NumBlocks
Get SourceFile, , FileData
T(sField).AppendChunk (FileData)
Next i

T.Update
Close SourceFile

*** Example end ************

Now, this is the method offered by Roger Abbit, which is working very
well for me. I've left his comments in too because they *seem* to refer
to problem I was having. Although I can't say what Roger has against
APIs...

*** Roger Abbit's FileToBLOB snippet ************

nFileNum = FreeFile
Open sFile For Binary Access Write As nFileNum
'
' Read the blob
sFileData = fldBlob.GetChunk(0, fldBlob.FieldSize)
'
' Widen the string so Put can convert it back!
' This bit is stupid but hours can be wasted finding a better way
in VBA
' without resort to API's.
' StrConv should do this with vbNarrow/vbWide but does not appear
to work!
' One can question performing a direct put in the loop instead of
formulating
' another string.
Dim lngItem As Long
sDataOut = String$(fldBlob.FieldSize, 0)
For lngItem = 1 To LenB(sFileData)
'
' Coerse the Byte to a character
Mid$(sDataOut, lngItem, 1) = Chr$(AscB(MidB(sFileData, lngItem,
1)))
Next lngItem
sFileData = vbNullString
'
' Write the file
Put #nFileNum, , sDataOut

*** Example end ************