Solved

How to store file attachments?

Posted on 2013-12-16
13
429 Views
Last Modified: 2013-12-20
If the users of Access database connected to SQL Server (ADP actually)  want to store various files as attachments to the records, how you guys implement it?

For example, if it's customer service application, they need to store estimates, emails, and such, pertaining to the case. If it's quality application, the pictures showing the problem. And so on.
0
Comment
Question by:Vadim Rapp
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 60 total points
Comment Utility
Depends :) either in the database itself using a varbinary datatype. Or as a link to the file. You have to be very careful storing links as you can be sure you'll want to move them at some point. When I've done this I store a relative link and then the root path is an application setting.

When you have lots of small files I'd stuff them in the database. Either way they get stored on disk, its just often easier to manage everything inside the database.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 60 total points
Comment Utility
Let me state the the only binary files you should ever store in a database are a few limited images for things like report headers or letterhead.

But storing it all in the DB is an absolutely horrible idea.

My last company bought a document imaging SW that stored the majority of images in the tif format on disk external to the DB. The DB itself was over 60GB after about 4 years. You say not bad. The images on disk were in the 3TB range and growing when I departed the company.

The best practice is to store it on disk and just have pointers to it. We added a new TB of SAN storage and moved 300 GB of the images on disk from the old drive to the new one. It took about two days working from the server's console. The update to the SQL table took about 30 seconds.

Trying to imagine handling a 3TB SQL DB is scary. The backup alone would take days. The restore would probably be in weeks.

So I say the best method to NOT store it in the DB.
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
I still say it depends Jimpen :)

Its definitely not as cut and dried as you claim. In the example you describe I would absolutely agree - no question. But many systems aren't as cut and dried as that. It depends on the size of the items, the size of the application, and the budget for development. After having tried it both ways I tend to do a mix depending on how the data is used.

Its also not difficult to move them out of the DB later if the size does become an issue.
0
 
LVL 40

Author Comment

by:Vadim Rapp
Comment Utility
In fact we already have one ADP application where I implemented storing on disk. Let's see... 18,423 items, 3.63GB, since 8/2008. Almost all files are PDF's (I trained the users that PDF is the only way to exchange and store the files). The largest file is mp4 video, 25MB

Now is the time to create another ADP application, also with attachments, so I decided to run this method by you guys.
0
 
LVL 40

Author Comment

by:Vadim Rapp
Comment Utility
...by the way, if you guys are familiar with ADP, and want to make a consulting buck, drop me a note. This new application somehow inflicts an internal resistance in me.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 120 total points
Comment Utility
+1 for store on disk.

 And you might want to have a look at this:

http://www.ammara.com/dbpix/access.html

 Haven't used it myself, but DatabaseMX has and liked it a lot.  Seems to be the choice for image control in Access.

Jim.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Hey vadimrapp1,

Didn't even look at the asker when responding.

I wouldn't go the ADP route as there is no future in it. Essentially M$ with Access 2007 did not make an .accdp equivalent to the adp.  The 2010 and below support. I haven't even checked if Acc 2013 will support ADP.

In your example of 19K documents in 3.63GB. If you can keep the 25MB or less limit then at 100K files you'll only be in the 1TB size, maybe 1.5.

Try handling that.  I just think you're better off being external.
0
 
LVL 40

Author Comment

by:Vadim Rapp
Comment Utility
> I wouldn't go the ADP route as there is no future in it

Sure, but I go from the future defined by business requirements, and from what I see, at least in our co., whatever may appear on the horizon in the next 50 years, ADP still can do :-)
And I know no tool that would even approach ADP in programmer's productivity when creating sql server-driven applications (depends on one's personal skills/preferences, of course).
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
whatever may appear on the horizon in the next 50 years, ADP still can do :-)

Not a problem -- just a suggestion.

But I still am thoroughly against storing binary files, no matter how small, in a DB.
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 200 total points
Comment Utility
Hi,

I also use ADP and SQL Server (an absolute dream team in comparison to ACCDB) for my projects. Although it will not be supported anymore in A2013 (completely removed) I would always go on using that combination if the SQL Server is not greater than 2008 R2 which can be used with A2010 ADP without any problem.

The best way of storing a file is of course using a fileserver and storing a link because it would not increase the database file size. Up to SQL Server 2005 this would be the way to go. But it has the disadvantage that you must always ensure that the links doesn't point to a not existing file.

Since SQL Server 2008 there is a very good hybrid solution: You can simply assign a column as varbinayry(MAX) as usual and add a FILESTREAM attribute to this column. In this case you can save the files into the varbinary column but SQL Server will create a corresponding file in the normal file system and saves a link internally (and keep it updated). The created files are stored using a GUID so nobody can use the files directly (as they cannot identify it). Additionally it is possible to not expose the created files to the Windows file system so the folders created are always seems to be empty. You can access them only through SQL Server.

Here's a simple function from my ADO class which loads a file from the server using a varbinary column and saves it to a local folder:

'---------------------------------------------------------------------------------------
' Property     : ADOLoadFromBinaryColumn
' Date         : 14.06.2012
' Purpose      : Loads a previously saved file from a varbinary column and saves it either to
'                the specified filename or the filename which was saved within the table
'                in the specified filename column.
' Requirements : A table with at least an ID (long), a filename (string) and a file column (varbinary)
' Parameters   : strTablename: Name of the table containing the binary files
'                strColumnnameFilename: Name of the column in the table which contains the path and filename
'                strColumnnameFile: Name of the column in the table which contains the binary file
'                lngID: ID of the row in the table where the file should be loaded from
'                strColumnnameID: Name of the column containing the primary key ID field as int
'                strFilenameToSave: Optional, if specified, must contain a path and filename where the
'                                   binary data should be saved to
'                intSaveOptions: Either adSaveCreateNotExist or adSaveCreateOverWrite
' Returns      : Returns the path/filename where the file was saved
'---------------------------------------------------------------------------------------
'
Public Function ADOLoadFromBinaryColumn(ByVal strTablename As String, _
                                        ByVal strColumnnameFilename As String, ByVal strColumnnameFile As String, _
                                        ByVal lngID As Long, ByVal strColumnnameID As String, _
                                        Optional ByVal strFilenameToSave As String = "", _
                                        Optional ByVal intSaveOptions As SaveOptionsEnum = SaveOptionsEnum.adSaveCreateNotExist) As String
    Dim rs As ADODB.Recordset
    Dim str As ADODB.Stream
    Dim strOutputFilename As String

    clsvar_strObjectError = "OK"
    On Error GoTo ADOLoadFromBinaryColumn_Error

    If Me.ADOOpenConnection = "OK" Then

        Set rs = New ADODB.Recordset
        With rs
            .ActiveConnection = clsvar_objADOConnection
            .CursorLocation = adUseServer
            .Source = "SELECT " & strColumnnameFilename & "," & strColumnnameFile & _
                    "  FROM " & strTablename & _
                    " WHERE " & strColumnnameID & "=" & lngID
            .Open , , adOpenForwardOnly, adLockReadOnly
            Set str = New ADODB.Stream
            str.Type = adTypeBinary
            str.Open
            str.Write (.fields(strColumnnameFile))
            strOutputFilename = IIf(strFilenameToSave = "", .fields(strColumnnameFilename), strFilenameToSave)
            str.SaveToFile strOutputFilename, intSaveOptions
            .Close
        End With
        str.Close
        Set str = Nothing
        Set rs = Nothing
    End If
    Me.ADOCloseConnection

ADOLoadFromBinaryColumn_Exit:
    ADOLoadFromBinaryColumn = strOutputFilename
    Exit Function

ADOLoadFromBinaryColumn_Error:
    Select Case Err.Number
    Case Else
        If Not clsvar_bolNoMsgBox Then ObjErr(clsvar_objADOConnection).fnErr "Class: " & cMODULENAME, "Sub: ADOLoadFromBinaryColumn"
        Me.ADOCloseConnection
        clsvar_strObjectError = "ERROR"
    End Select
    Resume ADOLoadFromBinaryColumn_Exit

End Function

Open in new window


You must of course adjust it a little bit, cannot be directly used outside the class. The method is to use an ADO stream object to save the file. Really simple and works perfectly with all varbinary columns, FILESTREAM or not.

This is the corresponding function to save a file to a binary column:

'---------------------------------------------------------------------------------------
' Property     : ADOSaveToBinaryColumn
' Date         : 14.06.2012
' Purpose      : Saves a specified file to a varbinary column in a database table
' Requirements : A table with at least an ID (long), a filename (string) and a file column (varbinary)
' Parameters   : strTablename: Name of the table containing the binary files
'                strFilenameToSave: The path and filename to load the file from, will be saved into the
'                                   column specified with "strColumnnameFilename"
'                                   binary data should be saved to
'                strColumnnameFilename: Name of the column in the table which contains the path and filename
'                strColumnnameFile: Name of the column in the table which contains the binary file
'                strAdditionalColumns/Values: List of column names and corresponding list of values separated
'                                   by "strDelimiter", don't add value delimiters like single quote,
'                                   if there is an error in the values it will throw an error message
'                lngID/strColumnnameID: to specify the row and the ID field if the file should be saved
'                                   to an existing row. If the row doesn't exist it will be saved as a new row.
'                strDelimiter: See above
'                strFilenameToLoadFrom: Optional path/filename to load the binary file from if it is different from
'                                       the path/filename specified in "strFilenameToSave"
' Returns      : ID of the row where the file was saved to
'---------------------------------------------------------------------------------------
'
Public Function ADOSaveToBinaryColumn(ByVal strTablename As String, ByVal strFilenameToSave As String, _
                                      ByVal strColumnnameFilename As String, ByVal strColumnnameFile As String, _
                                      ByVal strColumnnameID As String, _
                                      Optional ByVal strAdditionalColumns As String, Optional ByVal strAdditionalValues As String, _
                                      Optional ByVal lngID As Long = 0, _
                                      Optional ByVal strDelimiter As String = ",", _
                                      Optional ByVal strFilenameToLoadFrom As String = "") As Long
    Dim rs As ADODB.Recordset
    Dim str As ADODB.Stream
    Dim strAddColumns() As String
    Dim strAddColumns2() As String
    Dim strAddValues() As String
    Dim bolInsert As Boolean
    Dim strColumnname As String
    Dim i As Long

    clsvar_strObjectError = "OK"

    On Error GoTo ADOSaveToBinaryColumn_Error
    If Not lngID = 0 Then
        If Me.ADOCount("*", strTablename, strColumnnameID & "=" & lngID) > 0 Then
            bolInsert = False
        Else
            bolInsert = True
        End If
    Else
        bolInsert = True
    End If

    If strAdditionalValues <> "" Then
        strAddColumns = Split(strAdditionalColumns, strDelimiter)
        strAddColumns2 = Split(strAdditionalColumns, strDelimiter)
        strAddValues = Split(strAdditionalValues, strDelimiter)
        For i = 0 To UBound(strAddColumns)
            strColumnname = strAddColumns(i)
            If Left(strAddColumns2(i), 1) <> "[" Then strAddColumns2(i) = "[" & strAddColumns2(i)
            If right(strAddColumns2(i), 1) <> "]" Then strAddColumns2(i) = strAddColumns2(i) & "]"
        Next
        strAdditionalColumns = Join(strAddColumns2, ",")
    End If


    If Me.ADOOpenConnection = "OK" Then
        Set str = New ADODB.Stream
        With str
            .Type = adTypeBinary
            .Open
            .LoadFromFile IIf(strFilenameToLoadFrom <> "", strFilenameToLoadFrom, strFilenameToSave)
        End With

        Set rs = New ADODB.Recordset
        With rs
            .ActiveConnection = clsvar_objADOConnection
            .CursorLocation = adUseServer
            If bolInsert Then
                .Source = "SELECT " & strColumnnameID & "," & strColumnnameFilename & "," & strColumnnameFile & _
                          IIf(strAdditionalColumns <> "", "," & strAdditionalColumns, "") & _
                        "  FROM " & strTablename
            Else
                .Source = "SELECT " & strColumnnameID & "," & strColumnnameFilename & "," & strColumnnameFile & _
                          IIf(strAdditionalColumns <> "", "," & strAdditionalColumns, "") & _
                        "  FROM " & strTablename & _
                        " WHERE " & strColumnnameID & "=" & lngID
            End If

            .Open , , adOpenKeyset, adLockOptimistic
            If bolInsert Then .AddNew
            .fields(strColumnnameFilename).Value = strFilenameToSave
            .fields(strColumnnameFile).Value = str.Read
            For i = 0 To UBound(strAddColumns)
                .fields(strAddColumns(i)).Value = strAddValues(i)
            Next i
            ADOSaveToBinaryColumn = .fields(strColumnnameID)
            .Update
            If bolInsert Then ADOSaveToBinaryColumn = .fields(strColumnnameID)
            .Close
        End With
        str.Close
        Set str = Nothing
        Set rs = Nothing
    End If


ADOSaveToBinaryColumn_Exit:
    Exit Function

ADOSaveToBinaryColumn_Error:
    Select Case Err.Number
    Case Else
        If Not clsvar_bolNoMsgBox Then ObjErr(clsvar_objADOConnection).fnErr "Class: " & cMODULENAME, "Sub: ADOSaveToBinaryColumn"
        Me.ADOCloseConnection
        clsvar_strObjectError = "ERROR"
    End Select
    Resume ADOSaveToBinaryColumn_Exit
End Function

Open in new window


Cheers,

Christian
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 120 total points
Comment Utility
<<Sure, but I go from the future defined by business requirements, and from what I see, at least in our co., whatever may appear on the horizon in the next 50 years, ADP still can do :-)>>

  That may or may not be true.  As SQL evolves, the ADP interface will not keep up with it.

  Your going to hit a brick wall with ADP's sooner or later, and especially now since it's been dropped from the product entirely.   Microsoft has been warning developers for the past couple of releases to switch back to ODBC with SQL.

Jim.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 60 total points
Comment Utility
In the document storage systems I've encountered, the files are stored in a directory tree with some generic file name and only the actual location (path), name, and document type are stored in the database.
0
 
LVL 40

Author Closing Comment

by:Vadim Rapp
Comment Utility
Thanks everyone for the interesting discussion. Separate thanks (and extra points) to Bitsqueezer for FILESTREAM- based solution.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now