Error with AppendChunk() and MEMO field

[Mark]

Hello.

I am attempting to use AppendChunk() to write binary data to a memo field
in Access 2000. My initial call to AppendChunk() results in a data type
conversion error. Any suggestions? Here is a VBA function which demonstrates
the problem.

Thanks
-Mark

Public Function appendchunk_fai l_test() As Boolean
On Error GoTo appendchunk_fai l_test_ERROR
appendchunk_fai l_test = True
Dim sStmt As String
Dim sFilename As String
Dim chunk() As Byte
Dim lSize As Long
Dim nHandle As Integer

sFilename = InputBox("Enter a binary file name")
nHandle = FreeFile
Open sFilename For Binary Access Read As nHandle
lSize = LOF(nHandle)

'For test purposes, just write a single small chunk
If lSize < 2048 Then Err.Raise 5002, "appendchunk_fa il_test()", "Please
select a larger binary file"
ReDim chunk(2048)
Get nHandle, , chunk()
Close nHandle

'Got our test chunk. Now create temporary table
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Const csTable = "__TEST__"

Set db = CurrentDb()
Set tbl = db.CreateTableD ef(csTable)
Set fld = tbl.CreateField ("pkid", vbLong)
tbl.Fields.Appe nd fld
Set fld = Nothing
Set fld = tbl.CreateField ("photo_stream" , dbMemo)
tbl.Fields.Appe nd fld
Set fld = Nothing
db.TableDefs.Ap pend tbl
Set tbl = Nothing

sStmt = "SELECT pkid, photo_stream FROM " & csTable
Set rs = db.OpenRecordse t(sStmt, dbOpenDynaset)
rs.AddNew
rs!pkid = 1
'DATA TYPE CONVERSION ERROR raised with next statement
rs.Fields("phot o_stream").Appe ndChunk chunk()
rs.Close
Set rs = Nothing

'Delete temporary table
db.TableDefs.De lete csTable
Set db = Nothing

appendchunk_fai l_test_ERROR:
appendchunk_fai l_test = False
MsgBox "Error #" & Err.Number & vbCrLf & Err.description , ,
"appendchunk_fa il_test()"
GoTo appendchunk_fai l_test_EXIT

appendchunk_fai l_test_EXIT:

End Function

[Anthony England]

"Mark" <no****@thanksa nyway.org> wrote in message
news:4e******** *************** *******@w-link.net...

Hello.

I am attempting to use AppendChunk() to write binary data to a memo field
in Access 2000. My initial call to AppendChunk() results in a data type
conversion error. Any suggestions? Here is a VBA function which
demonstrates
the problem.

Thanks
-Mark

Public Function appendchunk_fai l_test() As Boolean
On Error GoTo appendchunk_fai l_test_ERROR
appendchunk_fai l_test = True
Dim sStmt As String
Dim sFilename As String
Dim chunk() As Byte
Dim lSize As Long
Dim nHandle As Integer

sFilename = InputBox("Enter a binary file name")
nHandle = FreeFile
Open sFilename For Binary Access Read As nHandle
lSize = LOF(nHandle)

'For test purposes, just write a single small chunk
If lSize < 2048 Then Err.Raise 5002, "appendchunk_fa il_test()", "Please
select a larger binary file"
ReDim chunk(2048)
Get nHandle, , chunk()
Close nHandle

'Got our test chunk. Now create temporary table
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Const csTable = "__TEST__"

Set db = CurrentDb()
Set tbl = db.CreateTableD ef(csTable)
Set fld = tbl.CreateField ("pkid", vbLong)
tbl.Fields.Appe nd fld
Set fld = Nothing
Set fld = tbl.CreateField ("photo_stream" , dbMemo)
tbl.Fields.Appe nd fld
Set fld = Nothing
db.TableDefs.Ap pend tbl
Set tbl = Nothing

sStmt = "SELECT pkid, photo_stream FROM " & csTable
Set rs = db.OpenRecordse t(sStmt, dbOpenDynaset)
rs.AddNew
rs!pkid = 1
'DATA TYPE CONVERSION ERROR raised with next statement
rs.Fields("phot o_stream").Appe ndChunk chunk()
rs.Close
Set rs = Nothing

'Delete temporary table
db.TableDefs.De lete csTable
Set db = Nothing

appendchunk_fai l_test_ERROR:
appendchunk_fai l_test = False
MsgBox "Error #" & Err.Number & vbCrLf & Err.description , ,
"appendchunk_fa il_test()"
GoTo appendchunk_fai l_test_EXIT

appendchunk_fai l_test_EXIT:

End Function

If you look at the help file for the AppendChunk Method it states:
"...Appends data from a string expression to a Memo or Long Binary Field
object in a Recordset."
but you are trying to use an array of bytes.

There is an example given at:
http://support.microsoft.com/?kbid=210486
which shows a file being stored in, and read from, a table.

There are other basic issues you have wrong here - the error handling for
instance is not properly structured. A more fundemental issue is the
underlying wisdom of storing binary files in a table - why not just store
the paths in the db?

[Mark]

"Anthony England" <ae******@oops. co.uk> wrote:

If you look at the help file for the AppendChunk Method it states:
"...Appends data from a string expression to a Memo or Long Binary Field
object in a Recordset."
but you are trying to use an array of bytes.
I have seen numerous examples on the Web that use an array of bytes,
but of course that doesn't mean they are correct or that they were
even tested. I'll try it with a fixed-length string.

There are other basic issues you have wrong here - the error handling for
instance is not properly structured.
Can you be more specific? My error handling seems to be working fine.

A more fundemental issue is the underlying wisdom of storing binary files
in a table - why not just store the paths in the db?

My project will store information about a large number of photographs.
My issue with storing only the file paths has to do with creating a database
whose informational integrity is wholly dependant upon an external data
repository (a host computer's file system and the file hierarchy.) If the
file
hierarchy is damaged or changed, or if the database is separated from the
host containing the matching file hierarchy, then the database becomes
useless.

My solution to this is to place a small thumbnail (approx 15-30kb)
of the photograph into the database. The database would also include the
path and filename of the full-resolution image(s).

An additional feature I am considering is to create a checksum of each
file and include the checksum with the database record. This could be
useful if it were necessary to programmaticall y re-establish the links
from the database to the external file system.

-Mark

[Mark]

"Mark" <no****@thanksa nyway.org> wrote:

I have seen numerous examples on the Web that use an array of bytes,
but of course that doesn't mean they are correct or that they were
even tested. I'll try it with a fixed-length string.
And now it appears to work. Thanks!

Can you be more specific? My error handling seems to be working fine.

Ok, yeah, I see I left out the final "appendchunk_fa il_test_EXIT", so my
code would have run over into the error handler when it reached the bottom.
That was an oversight in throwing together this sample code. It never got
that far so I didn't notice.

Thanks again
-Mark

[Anthony England]

"Mark" <no****@thanksa nyway.org> wrote in message
news:UI******** ************@w-link.net...

"Mark" <no****@thanksa nyway.org> wrote:

I have seen numerous examples on the Web that use an array of bytes,
but of course that doesn't mean they are correct or that they were
even tested. I'll try it with a fixed-length string.

And now it appears to work. Thanks!

Can you be more specific? My error handling seems to be working fine.

Ok, yeah, I see I left out the final "appendchunk_fa il_test_EXIT", so my
code would have run over into the error handler when it reached the
bottom.
That was an oversight in throwing together this sample code. It never got
that far so I didn't notice.

Thanks again
-Mark

This is the way I structure a standard sub with error handling. Note that
the Exit bit comes above the Error bit, so normally if there is no error the
sub follows the flow straight down and exits before the Error bit. This is
not quite like yours.
Notice also that even if an error occurs, my code still tries to clean up -
and this is what you should have done with opening and closing the file.
You open the file, but if an error occurs (as with the AppendChunk) you make
no effort to close the file again. In other words - you should try and
close the file in the Exit bit so the code always runs regardless of whether
an error occurred.
Private Sub MySub()

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM MyTable ORDER BY MyField"

Set dbs = CurrentDb

Set rst = dbs.OpenRecords et(strSQL)

While Not rst.EOF
rst.MoveNext
Wend

MsgBox "Done", vbInformation

Exit_Handler:

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Select

End Sub

[Bri]

Mark wrote:

My project will store information about a large number of photographs.
My issue with storing only the file paths has to do with creating a database
whose informational integrity is wholly dependant upon an external data
repository (a host computer's file system and the file hierarchy.) If the
file
hierarchy is damaged or changed, or if the database is separated from the
host containing the matching file hierarchy, then the database becomes
useless.

My solution to this is to place a small thumbnail (approx 15-30kb)
of the photograph into the database. The database would also include the
path and filename of the full-resolution image(s).

I've been bashing around ideas for a similar database. My own digital
photos have grown to the point that I don't store them on the PC. Once I
get to enough for a CD, I burn them onto two CDs (always have a
backup!). My problem has always been; what to do about thumbnails. Are
you using code in your db to create the thumbnails or do you have some
other app that you manually create them from? If you are imbedding them
into a Memo field, how are you displaying them?

Thanks for any insight you can give me on this.

--
Bri

[Mark]

"Bri" <no*@here.com > wrote:

My problem has always been; what to do about thumbnails. Are you using
code in your db to create the thumbnails or do you have some other app
that you manually create them from? If you are imbedding them into a Memo
field, how are you displaying them?

I'm afraid that my answers to your questions are "dunno" and "dunno."
I have not yet implemented this feature, so I haven't explored options
for a VBA-based reduction of the images to JPEG thumbnails.
I notice that Paint Shop Pro v7 (which is installed on my system)
appears as an OLE automation server, so that is one possibility.
or I may use Irfanview* to generate the thumbnails and then write
some VBA code to load them into the database.

I believe that an OLE Object field may be more appropriate than
a Memo field for storing this data (and I subsequently discovered that
AppendChunk() does work with a byte array if the data is being
appended to an Ole Object field.) There should be a way to display
the binary stream in a VB image control, although I have not found
it yet. If all else fails, I suppose I could write out the stream to a
temporary file and then use LoadPicture to display it in an Image
control.

-Mark
*http://www.irfanview.com is shareware and is the best tool I have
found so far or batch manipulation of image files. No financial
interest, just a happy user, blah blah etc.

[Bri]

Mark wrote:

"Bri" <no*@here.com > wrote:

My problem has always been; what to do about thumbnails. Are you using
code in your db to create the thumbnails or do you have some other app
that you manually create them from? If you are imbedding them into a Memo
field, how are you displaying them?

I'm afraid that my answers to your questions are "dunno" and "dunno."
I have not yet implemented this feature, so I haven't explored options
for a VBA-based reduction of the images to JPEG thumbnails.
I notice that Paint Shop Pro v7 (which is installed on my system)
appears as an OLE automation server, so that is one possibility.
or I may use Irfanview* to generate the thumbnails and then write
some VBA code to load them into the database.

I believe that an OLE Object field may be more appropriate than
a Memo field for storing this data (and I subsequently discovered that
AppendChunk() does work with a byte array if the data is being
appended to an Ole Object field.) There should be a way to display
the binary stream in a VB image control, although I have not found
it yet. If all else fails, I suppose I could write out the stream to a
temporary file and then use LoadPicture to display it in an Image
control.

-Mark
*http://www.irfanview.com is shareware and is the best tool I have
found so far or batch manipulation of image files. No financial
interest, just a happy user, blah blah etc.

Ah, so you are not any further along in this than I am. :{(
My version of PSP is 4 and I don't see any exposed objects for it. I
have Irfanview, but didn't like it as much as PSP, so I really haven't
used it a lot. I've been exploring another free app called XNview
(http://www.xnview.com/). He has a developer kit available, but I
haven't looked that closely a it yet. It has batch image manipulation
capability and I have been creating my thumbnails in bulk with that. I
have then been linking to them with an image control rather than
embedding them into a table. This means you can lose a thumbnail (just
like you can lose the full image), but the total size is a lot less than
with embedding. Access adds in a huge amount of overhead to store images
in OLE Object fields.

--
Bri

[Mark]

"Bri" <no*@here.com > wrote:

Ah, so you are not any further along in this than I am. :{(
My version of PSP is 4 and I don't see any exposed objects for it. I have
Irfanview, but didn't like it as much as PSP, so I really haven't used it
a lot.
Don't write off Irfanview. Unlike PSP it is not an image editor.
But it does have very good features for batch processing, and that
is what I use it for.
I have then been linking to them with an image control rather than
embedding them into a table. This means you can lose a thumbnail (just
like you can lose the full image), but the total size is a lot less than
with embedding. Access adds in a huge amount of overhead to store images
in OLE Object fields.

Is this peculiar to the OLE Object field, or would the Memo field
exhibit the same problem? Assuming that a binary stream can be
stored in a memo field, that might be a suitable way to store a small
(<65k) jpeg thumbnail.

Unfortunately, database size will grow substantially if images are
stored in the database. It is definitely a tradeoff. IMHO it may be
workable if only small renditions are stored in the database.
Of course you will still have quite a bit of work to do if you lose
your links to the external full-resolution images, but at least you
will have the db-based thumbnails to guide you in restoring the
links.

I proposed a checksum-based method to help solidify the links
to the external images. This would entail generating a unique
checksum value for each full-resolution image, and storing that
value in the database record. If the external links were lost,
the checksums could make it possible to programmaticall y
re-establish the external links by re-generating checksums
for the external images and matching them to the checksums
in the database. Obviously this is not foolproof, but it could
be quite helpful in disaster recovery.

-Mark

[bob]

Is this peculiar to the OLE Object field
The huge overhead is peculiar to OLE Embedding (and OLE Linking) – these are front-end techniques rather
than field types. OLE Embedding and Linking require a binary field; in Access this is the OLE Object field
type, but the problem is not with the field type (which really has nothing to do with OLE, it’s just a
binary field - the same problem occurs if you use OLE Embedding/Linking with binary fields in other databases,
such as the ‘Image’ field type in SQL Server).

You can store binary data in an OLE Object field directly, without using OLE Embedding, then there is no
such overhead (and various other problems are avoided too). Done this way, allowing 4k for each thumbnail,
the database could handle 500,000 thumbnails.

Memo fields are designed for text, not binary data.
I proposed a checksum-based method to help solidify the links
How could your ‘links’ be lost? If you store enough of the path and the filename to uniquely identify
the file that’s all you need; you don’t need to calculate checksums, no ‘programmatic re-establishment’
of links would be required.

For example, if your photos are stored as follows:

D:\photos\2002-12-04\PC221788.JPG

You could store ‘2002-12-04\PC221788.JPG ’ as your link (you may want to split this between 2 fields).
This is enough to uniquely identify the file, and you can easily move the file to a new root without needing
to change any records.

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

"Mark" <no****@thanksa nyway.org> wrote:"Bri" <no*@here.com > wrote:

Ah, so you are not any further along in this than I am. :{(
My version of PSP is 4 and I don't see any exposed objects for it. I have
Irfanview, but didn't like it as much as PSP, so I really haven't used it
a lot.

Don't write off Irfanview. Unlike PSP it is not an image editor.
But it does have very good features for batch processing, and that
is what I use it for.

I have then been linking to them with an image control rather than
embedding them into a table. This means you can lose a thumbnail (just
like you can lose the full image), but the total size is a lot less than
with embedding. Access adds in a huge amount of overhead to store images
in OLE Object fields.

Is this peculiar to the OLE Object field, or would the Memo field
exhibit the same problem? Assuming that a binary stream can be
stored in a memo field, that might be a suitable way to store a small
(<65k) jpeg thumbnail.

Unfortunatel y, database size will grow substantially if images are
stored in the database. It is definitely a tradeoff. IMHO it may be
workable if only small renditions are stored in the database.
Of course you will still have quite a bit of work to do if you lose
your links to the external full-resolution images, but at least you
will have the db-based thumbnails to guide you in restoring the
links.

I proposed a checksum-based method to help solidify the links
to the external images. This would entail generating a unique
checksum value for each full-resolution image, and storing that
value in the database record. If the external links were lost,
the checksums could make it possible to programmaticall y
re-establish the external links by re-generating checksums
for the external images and matching them to the checksums
in the database. Obviously this is not foolproof, but it could
be quite helpful in disaster recovery.

-Mark