storing images in database in binary format

[thosecars82]

Hello
I am using this code showed below but I do not know why the images stored in binary form are increasing the size of the database too much. For example, a 800KB jpg image which is added that way to the Access database increase the mdb file in 1600KB. Considering that this way should not bloat the database according to http://jamiessoftware.tk/articles/handlingimages.html, I still do not understand why this is happening.

Anyways,would you recommend to store 10GB of image files in a database following the code below (third opion in http://jamiessoftware.tk/articles/ha...e file system?

I mean, even if I got that this code did not increase the database size more than the image file's size for each image added, at some point the database might reach 10GB or even more depending on the amount of pictures I have. Is that a good idea? By the way, what is the biggest file size allowed for an mdb file in Access 97, 2000, 2003, 2007 and 2010? If this size is just a few gigabytes, Isn't there a much more powerful and freeware database manager whose databases could grow up at least up to 100GB and somehow integrated/linked to show up in Access interface as if they really were access databases in case access was not powerful enough in order to store huge amount of data like the image files I told you about?
Otherwise, would this mean that for the time being my only options are either to store only the file paths of the images instead of the images themselves in the access database for a freeware solution or buying sql server in case I do not mind paying to build a huge database with many image files growing like 100GB?

Option Compare Database

Option Explicit
Const BlockSize = 32768


'**************************************************************
' FUNCTION: ReadBLOB()
'
' PURPOSE:

'   Reads a BLOB from a disk file and stores the contents in the
'   specified table and field.
'
' PREREQUISITES:
'   The specified table with the OLE object field to contain the
'   binary data must be opened in Visual Basic code and the correct
'   record navigated to prior to calling the ReadBLOB() function.
'
' ARGUMENTS:
'   Source - The path and filename of the binary information
'            to be read and stored.
'   T      - The table object to store the data in.
'   Field  - The OLE object field in table T to store the data in.
'
' RETURN:
'   The number of bytes read from the Source file.
'**************************************************************
Function ReadBLOB(Source As String, T As DAO.Recordset, _
sField As String)
    Dim NumBlocks As Integer, SourceFile As Integer, i As Integer
    Dim FileLength As Long, LeftOver As Long
    Dim FileData As String
    Dim RetVal As Variant

    On Error GoTo Err_ReadBLOB

    ' Open the source file.
    SourceFile = FreeFile
    Open Source For Binary Access Read As SourceFile

    ' Get the length of the file.
    FileLength = LOF(SourceFile)
    If FileLength = 0 Then
        ReadBLOB = 0
        Exit Function
    End If

    ' Calculate the number of blocks to read and leftover bytes.
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    ' SysCmd is used to manipulate status bar meter.
    RetVal = SysCmd(acSysCmdInitMeter, "Reading BLOB", _
             FileLength \ 1000)

    ' Put first record in edit mode.
    T.MoveLast
    T.Edit

    ' Read the leftover data, writing it to the table.
    FileData = String$(LeftOver, 32)
    Get SourceFile, , FileData
    T(sField).AppendChunk (FileData)

    RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)

    ' Read the remaining blocks of data, writing them to the table.
    FileData = String$(BlockSize, 32)
    For i = 1 To NumBlocks
        Get SourceFile, , FileData
        T(sField).AppendChunk (FileData)

        RetVal = SysCmd(acSysCmdUpdateMeter, BlockSize * i / 1000)
    Next i

    ' Update the record and terminate function.
    T.Update
    RetVal = SysCmd(acSysCmdRemoveMeter)
    Close SourceFile
    ReadBLOB = FileLength
    Exit Function

Err_ReadBLOB:
    ReadBLOB = -Err
    Exit Function

End Function

'**************************************************************
' FUNCTION: WriteBLOB()
'
' PURPOSE:
'   Writes BLOB information stored in the specified table and field
'   to the specified disk file.
'
' PREREQUISITES:
'   The specified table with the OLE object field containing the
'   binary data must be opened in Visual Basic code and the correct
'   record navigated to prior to calling the WriteBLOB() function.
'
' ARGUMENTS:
'   T           - The table object containing the binary information.
'   sField      - The OLE object field in table T containing the
'                 binary information to write.
'   Destination - The path and filename to write the binary
'                 information to.
'
' RETURN:
'   The number of bytes written to the destination file.
'**************************************************************
Function WriteBLOB(T As DAO.Recordset, sField As String, _
Destination As String)
    Dim NumBlocks As Integer, DestFile As Integer, i As Integer
    Dim FileLength As Long, LeftOver As Long
    Dim FileData As String
    Dim RetVal As Variant

    On Error GoTo Err_WriteBLOB

    ' Get the size of the field.
    FileLength = T(sField).FieldSize()
    If FileLength = 0 Then
        WriteBLOB = 0
        Exit Function
    End If

    ' Calculate number of blocks to write and leftover bytes.
    NumBlocks = FileLength \ BlockSize
    LeftOver = FileLength Mod BlockSize

    ' Remove any existing destination file.
    DestFile = FreeFile
    Open Destination For Output As DestFile
    Close DestFile

    ' Open the destination file.
    Open Destination For Binary As DestFile

    ' SysCmd is used to manipulate the status bar meter.
    RetVal = SysCmd(acSysCmdInitMeter, _
    "Writing BLOB", FileLength / 1000)

    ' Write the leftover data to the output file.
    FileData = T(sField).GetChunk(0, LeftOver)
    Put DestFile, , FileData

    ' Update the status bar meter.
    RetVal = SysCmd(acSysCmdUpdateMeter, LeftOver / 1000)

    ' Write the remaining blocks of data to the output file.
    For i = 1 To NumBlocks
        ' Reads a chunk and writes it to output file.
        FileData = T(sField).GetChunk((i - 1) * BlockSize _
           + LeftOver, BlockSize)
        Put DestFile, , FileData

        RetVal = SysCmd(acSysCmdUpdateMeter, _
        ((i - 1) * BlockSize + LeftOver) / 1000)
    Next i

    ' Terminates function
    RetVal = SysCmd(acSysCmdRemoveMeter)
    Close DestFile
    WriteBLOB = FileLength
    Exit Function

Err_WriteBLOB:
    WriteBLOB = -Err
    Exit Function

End Function

'**************************************************************
' SUB: CopyFile
'
' PURPOSE:
'   Demonstrates how to use ReadBLOB() and WriteBLOB().
'
' PREREQUISITES:
'   A table called BLOB that contains an OLE Object field called
'   Blob.
'
' ARGUMENTS:
'   Source - The path and filename of the information to copy.
'   Destination - The path and filename of the file to write
'                 the binary information to.
'
' EXAMPLE:
'   CopyFile "c:\windows\winfile.hlp", "c:\windows\winfil_1.hlp"
'**************************************************************
Sub CopyFile(Source As String, Destination As String)
    Dim BytesRead As Variant, BytesWritten As Variant
    Dim Msg As String
    Dim db As DAO.Database
    Dim T As DAO.Recordset

    ' Open the BLOB table.
    Set db = CurrentDb()
    Set T = db.OpenRecordset("BLOB", dbOpenTable)

    ' Create a new record and move to it.
    T.AddNew
    T.Update
    T.MoveLast

    BytesRead = ReadBLOB(Source, T, "Blob")

    Msg = "Finished reading """ & Source & """"
    Msg = Msg & Chr$(13) & ".. " & BytesRead & " bytes read."
    MsgBox Msg, 64, "Copy File"

    BytesWritten = WriteBLOB(T, "Blob", Destination)

    Msg = "Finished writing """ & Destination & """"
    Msg = Msg & Chr$(13) & ".. " & BytesWritten & " bytes written."
    MsgBox Msg, 64, "Copy File"
End Sub

[techgnome]

"would you recommend to store 10GB of image files in a database " --- NOT in an Access database. Even in SQL Server (where it can be handled a little better) I'd question storing that amount in the database. Here's the thing, Image datatypes, because of their binary nature are not stored within the normal storage buckets of the database. It actually puts them somewhere else. What ends up being actually stored in the table itself is a reference pointer that says "You'll find your binary data over here..." However, I believe these special buckets are still subject to page sizes... it's going to try to align the image data along natural page breaks, which might explain the bloat you are seeing. It's possible it maybe storing unicode data, I don't know. Either way 10G is a lot to be asking of a database, especially Access.

-tg

[si_the_geek]

In terms of official limits the maximum size of an Access database file is 2GB. However, you need a good mixture of skill and luck to keep it working once it gets bigger than about 1GB.

Storing the files in a folder rather than in the database is a valid (and often recommended) option. As you are using Access the users need to have permissions to read/write the database file in Explorer etc, in which case creating a sub-folder to store the files should not be an issue. The down-side is that backups etc are a little more awkward, and that you can potentially get a wider variety of errors.