Error with AppendChunk() and MEMO field

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_fail_test() As Boolean
On Error GoTo appendchunk_fail_test_ERROR
appendchunk_fail_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_fail_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.CreateTableDef(csTable)
Set fld = tbl.CreateField("pkid", vbLong)
tbl.Fields.Append fld
Set fld = Nothing
Set fld = tbl.CreateField("photo_stream", dbMemo)
tbl.Fields.Append fld
Set fld = Nothing
db.TableDefs.Append tbl
Set tbl = Nothing

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

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

appendchunk_fail_test_ERROR:
appendchunk_fail_test = False
MsgBox "Error #" & Err.Number & vbCrLf & Err.description, ,
"appendchunk_fail_test()"
GoTo appendchunk_fail_test_EXIT

appendchunk_fail_test_EXIT:

End Function

re: Error with AppendChunk() and MEMO field

"Mark" <nospam@thanksanyway.org> wrote in message
news:4e6dnW1um5HpTpDZnZ2dnUVZ_v-dnZ2d@w-link.net...[color=blue]
> 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_fail_test() As Boolean
> On Error GoTo appendchunk_fail_test_ERROR
> appendchunk_fail_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_fail_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.CreateTableDef(csTable)
> Set fld = tbl.CreateField("pkid", vbLong)
> tbl.Fields.Append fld
> Set fld = Nothing
> Set fld = tbl.CreateField("photo_stream", dbMemo)
> tbl.Fields.Append fld
> Set fld = Nothing
> db.TableDefs.Append tbl
> Set tbl = Nothing
>
> sStmt = "SELECT pkid, photo_stream FROM " & csTable
> Set rs = db.OpenRecordset(sStmt, dbOpenDynaset)
> rs.AddNew
> rs!pkid = 1
> 'DATA TYPE CONVERSION ERROR raised with next statement
> rs.Fields("photo_stream").AppendChunk chunk()
> rs.Close
> Set rs = Nothing
>
> 'Delete temporary table
> db.TableDefs.Delete csTable
> Set db = Nothing
>
> appendchunk_fail_test_ERROR:
> appendchunk_fail_test = False
> MsgBox "Error #" & Err.Number & vbCrLf & Err.description, ,
> "appendchunk_fail_test()"
> GoTo appendchunk_fail_test_EXIT
>
> appendchunk_fail_test_EXIT:
>
> End Function[/color]



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?

re: Error with AppendChunk() and MEMO field

"Anthony England" <aengland@oops.co.uk> wrote:[color=blue]
>
> 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.[/color]

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.

[color=blue]
> There are other basic issues you have wrong here - the error handling for
> instance is not properly structured.[/color]

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

[color=blue]
> A more fundemental issue is the underlying wisdom of storing binary files
> in a table - why not just store the paths in the db?[/color]

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 programmatically re-establish the links
from the database to the external file system.

-Mark

re: Error with AppendChunk() and MEMO field

"Mark" <nospam@thanksanyway.org> wrote:
[color=blue]
> 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.[/color]

And now it appears to work. Thanks!

[color=blue]
> Can you be more specific? My error handling seems to be working fine.[/color]

Ok, yeah, I see I left out the final "appendchunk_fail_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

re: Error with AppendChunk() and MEMO field

"Mark" <nospam@thanksanyway.org> wrote in message
news:UImdnSvoa5MFd5DZRVn-sg@w-link.net...[color=blue]
> "Mark" <nospam@thanksanyway.org> wrote:
>[color=green]
>> 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.[/color]
>
> And now it appears to work. Thanks!
>
>[color=green]
>> Can you be more specific? My error handling seems to be working fine.[/color]
>
> Ok, yeah, I see I left out the final "appendchunk_fail_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[/color]



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.OpenRecordset(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

re: Error with AppendChunk() and MEMO field

Mark wrote:
[color=blue]
> 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).[/color]

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

re: Error with AppendChunk() and MEMO field

"Bri" <not@here.com> wrote:[color=blue]
>
> 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?[/color]

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.

re: Error with AppendChunk() and MEMO field

Mark wrote:[color=blue]
> "Bri" <not@here.com> wrote:
>[color=green]
>>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?[/color]
>
>
> 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.[/color]

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

re: Error with AppendChunk() and MEMO field

"Bri" <not@here.com> wrote:[color=blue]
>
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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 programmatically
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

re: Error with AppendChunk() and MEMO field

[color=blue]
>Is this peculiar to the OLE Object field[/color]

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.
[color=blue]
>I proposed a checksum-based method to help solidify the links[/color]

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" <nospam@thanksanyway.org> wrote:[color=blue]
>"Bri" <not@here.com> wrote:[color=green]
>>
>> 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.[/color]
>
>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.
>[color=green]
>> 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.[/color]
>
>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 programmatically
>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
>
>
>[/color]

re: Error with AppendChunk() and MEMO field

Mark wrote:[color=blue]
> 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.[/color]

I didn't notice that it had batch processing, I'll have to look at it
again. I've been using the batch processing in XNview and hope to be
able to use his SDK to automate the creation of new thumbnails.
[color=blue]
> 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.[/color]

I think that Bob has done a better job of answering this than I could
have. I guess the key is whether you plan to dispay the thumbnail (I do)
or not. If you do, then you get the overhead. Larry Linson has chimed in
on a lot of the threads that discuss images in Access and has a webpage
that discribes the three major approaches to do it and what the benifits
of each are. Here is the link:
http://members.tripod.com/accdevel/imaging.htm
[color=blue]
> 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 programmatically
> 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.[/color]

Like Bob, I am unsure of what you are trying to accomplish here? Do you
plan to move the images around to different folders behind the DBs back?
And hope that it will be able to find them again?

--
Bri

re: Error with AppendChunk() and MEMO field

"Bri" <not@here.com> wrote:
[color=blue]
> Here is the link:
> http://members.tripod.com/accdevel/imaging.htm[/color]

Thanks! I'll take a look at that.
[color=blue]
> Like Bob, I am unsure of what you are trying to accomplish here? Do you
> plan to move the images around to different folders behind the DBs back?
> And hope that it will be able to find them again?[/color]

My _hope_ is that the files will stay where I put them, they won't get
renamed, they won't get deleted or moved, the database won't get
separated from the host that contains the file hierarchy, and so on.
Experience tells me that this is a risky assumption. I don't want to find
myself with a whole bunch of data that doesn't point anywhere any more.

Placing a small thumbnail in each database record will guarantee
that I have a reference for the information that I have entered,
even if the file hierarchy gets buggered.

-Mark

re: Error with AppendChunk() and MEMO field

"Mark" <nospam@thanksanyway.org> wrote:[color=blue]
> "Bri" <not@here.com> wrote:
>[color=green]
>> Here is the link:
>> http://members.tripod.com/accdevel/imaging.htm[/color]
>
> Thanks! I'll take a look at that.[/color]

This example still uses twice the necessary space
to store the data in the OLE Object field.
See my new thread on this question:

"Problem saving binary data in OLE Object field"

Thanks
-Mark

re: Error with AppendChunk() and MEMO field

Mark wrote:[color=blue]
> "Mark" <nospam@thanksanyway.org> wrote:
>[color=green]
>>"Bri" <not@here.com> wrote:
>>
>>[color=darkred]
>>>Here is the link:
>>>http://members.tripod.com/accdevel/imaging.htm[/color]
>>
>>Thanks! I'll take a look at that.[/color]
>
>
> This example still uses twice the necessary space
> to store the data in the OLE Object field.
> See my new thread on this question:
>
> "Problem saving binary data in OLE Object field"
>
> Thanks
> -Mark[/color]

I think this is what Bob was refering to. To just store binary data is
one thing, but in order to display the image from the binary data you
need the OLE overhead, which is substantial.

The method I think is the one that works best overall (and is the one
that Larry thinks is best in his discussions) is to only link to
external images. Of course, in your case this defeats the purpose you
have in mind.

--
Bri

re: Error with AppendChunk() and MEMO field

If you're taking exactly twice the space of the original file then you are most likely using strings somewhere.
Just change this to byte arrays and the doubling will disappear. Remember, if you're working with binary
data, avoid strings, avoid memo fields, avoid anything to do with text.

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


"Mark" <nospam@thanksanyway.org> wrote:[color=blue]
>"Mark" <nospam@thanksanyway.org> wrote:[color=green]
>> "Bri" <not@here.com> wrote:
>>[color=darkred]
>>> Here is the link:
>>> http://members.tripod.com/accdevel/imaging.htm[/color]
>>
>> Thanks! I'll take a look at that.[/color]
>
>This example still uses twice the necessary space
>to store the data in the OLE Object field.
>See my new thread on this question:
>
>"Problem saving binary data in OLE Object field"
>
>Thanks
>-Mark
>
>[/color]

re: Error with AppendChunk() and MEMO field

"bob" <exponent@hotmail.com> wrote:[color=blue]
>
> If you're taking exactly twice the space of the original file then you are
> most likely using strings somewhere.
> Just change this to byte arrays and the doubling will disappear.
> Remember, if you're working with binary
> data, avoid strings, avoid memo fields, avoid anything to do with text.
>[/color]
Yep, I finally did get it working with a byte array, and that solved the
problem
(well, mostly solved it, I still have eighteen bytes of null data being
appended
to each field, but that's probably a bug in my coding somewhere.)

-Mark

re: Error with AppendChunk() and MEMO field

"Mark" <nospam@thanksanyway.org> wrote:[color=blue]
> (well, mostly solved it, I still have eighteen bytes of null data being
> appended
> to each field, but that's probably a bug in my coding somewhere.)[/color]

And the winner is. . .sample code that requires "option base 1".