Attaching files to database

[lmcc007]

I have a db that has about 3,000 records. Many of the records will have documents attached (Word, PDF, and images). All the attachments will be saved in a directory called Attachments. The document will be renamed using the ID number—e.g., 0001__bio, 0001__business card and so on. Currently, about 600 of them are set up in their own directory by company name.

Am I headed in the right direction or do I need to consider another way of storing attachments to a db? Is it best to have one huge directory for all attachments?

Any suggestions?

 

[DCrake]

First of all you are using the correct principle, however, having all files in one folder may prove cumbersome. If many attachments can be related to one specific record then it may be wise to have sub folders.

For example a Customer may have many PDF's, xls's, doc's etc, therefore create a folder based on the customers PK and store the files in there. Then if you want to retreive a file you know exactly where to look.

Conversley you could create sub folders for each type of file and store them in there so if you are looking for a pdf you look in the pdf folder.

Just my 2p worth

 

[lmcc007]

Thanks DCrake,

Of the 3,000 records about 1,300 have attachments. Most of the attachments are one document--image or business card. Less than 500 will have many attachments; others have no attachments. Now knowing that, would you create subfolders for each company that has attachments or put everything in one folder?

I guess I trying to figure out which is best for Access to process. I was leaning toward folders per company but then I thought that it would be a lot of folders for only one attachment.

Professionally, how would approach this situation?

 

[DCrake]

If you do not have many attachments per company you may consider naming conventions for your files. Preceed each file name with the companyId. Then if you are using a list box to view attachments you can filter on company id. What version of Acces are you using?

 

[lmcc007]

If you do not have many attachments per company you may consider naming conventions for your files. Preceed each file name with the companyId. Then if you are using a list box to view attachments you can filter on company id. What version of Acces are you using?

I am using Access 2007.

How do I set up a list box to view attachments?

Right now I have it set up where I enter a path and file name. The DisplayImage code I got works great for images, but the problem is Word and PDF documents are too big or it does not recognize it. So, I am playing around with the Attachment data type field. Not quite sure what I want so I thought I would have separate fields for images and use the Attachment data type field for other types of documents. But I keep thinking I am creating two separate fields, which is not correct.

Below is the code I am using on the form to view the images:

Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String

    Dim strResult As String
    Dim strDatabasePath As String
    Dim intSlashLocation As Integer
    
    With ctlImageControl
        If IsNull(strImagePath) Then
            .Visible = False
            strResult = "No image name specified."
        Else
            If InStr(1, strImagePath, "\") = 0 Then
                ' Path is relative
                strDatabasePath = CurrentProject.FullName
                intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
                strDatabasePath = Left(strDatabasePath, intSlashLocation)
                strImagePath = strDatabasePath & strImagePath
            End If
            .Visible = True
            .Picture = strImagePath
            strResult = "Image found and displayed."
        End If
    End With

End Functio

Thanks for your help!