Hi all,

I would like to add file attachments to a record when it (the record)
is displayed on a form. The attachments can be of any file extension
(.bmp, .doc, .txt, .xls...) and be able to store more than 1
attachment per record.

I am looking for advice on whether to:
a) just save the attachment file's full path, OR
b) save the actual file contents in a table field.

I guess each have its advantages/disadvantages. Saving the file path
uses less storage but breaks down when the file is moved by accident.
Vice versa when you store the file contents in a table.

Also, do I store each attachment's info in a Detail table of a
Master-Detail setup or can I use another method? I had planned on
using a ListBox to display the attachments, but maybe should use a
grid if I have a Master-Detail setup.


(just want to throw some ideas in the air before I jump in the deep
end  )

Regards,
Pat

Pat,

> I am looking for advice on whether to:
> a) just save the attachment file's full path, OR
> b) save the actual file contents in a table field.

I would recommend going with option b  because the images get backed up with
the data, you don't have to worry about files getting deleted, moved, or
renamed, and it still works even if it is a client/server connection across
a WAN.

> Also, do I store each attachment's info in a Detail table of a
> Master-Detail setup or can I use another method?

Yup.  That would be the way to go as it allows you to store any number of
attachments to any record.

One thing that I would recommend would be to have a couple of additional
fields that allow you to retrieve information about the file in the BLOB
field without having to open it up.  This would be things like the last
mofidied date, the file extention (or some other type of file indicator),
and the source file name without the path.

> I had planned on using a ListBox to display the attachments,
> but maybe should use a grid if I have a Master-Detail setup.

Either one will work, but it just depends on how much detail you want to
give the end user when displaying the selections.   If all you care about
displaying is the file name, then ListBox will do just fine.  But if you
want to display more than that, then a grid display might be warranted.

Hope this helps.

--

---------------------------------------
 Terry Swiers
 Millennium Software, LLC
 http://www.1000years.com
 http://www.atrex.com

 Atrex Inventory Control/POS -
    Big business features without spending big business bucks!

Atrex Electronic Support Options:
 Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp
 Email: mailto:support@atrex.com
 Newsgroup: news://news.1000years.com/millennium.atrex
 Fax: 1-925-829-1851
 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
 ---------------------------------------

Pat


Much as I love tables I'd recommend something like HKStreams for this (Google it).  I've used a mixture of ways - linked tables, in-memory table stored in blob field, multiple stream component (HKStreams) and just holding the file location.

You've spotted the major problem with the last idea - what to do if its moved. But it does have some advantages - you can use multiple apps to look at the file; its a lot easier to edit and save the changes back.

Which way to go depends on what you want to do with the attachments - so here's Roy's quick guide:

1. Edit and save changes - store location
2. View / alter with multiple apps - store location
3. Prevent changes - store in table (either blob or linked)
4. Delete when row deleted - store in table (blob)
5. Backup infrequently or not at all - who cares
6. Backup frequently, or every time database is - store in table (either blob or linked)
7. Backup every time table is - store in table (blob)
8. Frequent additions to row - store location or linked table
9. Frequent deletion of rows - store in table (blob)

I could probably add a load more but you should get the idea. My best personal example comes from email attachments. When I first built email into my app I copied the Eudora model with attachments stored as files on disk and just links set to them. Later when I wrote TMaN (my email & news client) I went more with the OE model and stored attachments in the table as a blob field using DBISAM in-memory tables (I'd found that deleting attachments from disk didn't work if the file was open somewhere so over the years an accumulation of unattached files has built up). With the latest app, and especially with background threads I've found its more efficient to use a list of attachments (name and size) for quick display and hold the attachments in a blob column stored in an HKStreams component.

Roy Lambert [Team Elevate]

Thanks Terry & Roy for your advice, appreciate it.

Pat

Pat,

<< I am looking for advice on whether to:
a) just save the attachment file's full path, OR
b) save the actual file contents in a table field. >>

We do b) here with the newsgroups articles table.   The .blb is 961 megs and
chugging right along.

<< Also, do I store each attachment's info in a Detail table of a
Master-Detail setup or can I use another method? I had planned on using a
ListBox to display the attachments, but maybe should use a grid if I have a
Master-Detail setup. >>

You'll want it to be a detail table if you are going to have multiple
attachments per master record.  We don't do that with the newsgroups because
the multiple attachments are stored as MIME-encoded attachments.

--
Tim Young
Elevate Software
www.elevatesoft.com

Just for interest

>We do b) here with the newsgroups articles table. The .blb is 961 megs and
>chugging right along.

Since I have others as well as your ngs my .blb is up to 3.8Gb

Roy Lambert

Roy,

<< Since I have others as well as your ngs my .blb is up to 3.8Gb >>

Cool.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:C011E1DC-CE2A-4843-A38D-A9F499B6EBF8@news.elevatesoft.com...
> Just for interest
>
>>We do b) here with the newsgroups articles table. The .blb is 961 megs and
>>chugging right along.
>
> Since I have others as well as your ngs my .blb is up to 3.8Gb
>
> Roy Lambert

..blb with images 5.2Gb  

Frans

>.blb with images 5.2Gb

ok - your's is bigger than mine - sob.

Roy Lambert