long binary data for storing images (1 Viewer)

[drshahriyar]

Hello. i am a novice user of access. In 2006 i developed a simple database with the help of this forum. Its called an endoscopy database. Endoscopy is a medical procedure for which the data was stored in the database. since it also involved the images, i used the Dbpix to incorporate the images in the databbase. Dbpix stores the images as long binary data and saves up lot of space. with more than 15thousand records now, i am in a fix. the database has reached its maximum limit of 2gb. My query is if there is any way i can transfer all the image files from the database into a storage folder? i will need some kind of code or a program since doing it individually will take ages.

 

[The_Doc_Man]

Basically, Image controls don't require you to store the image in the DB, you just need the file specification of the image file. You load the spec to the .Picture property in the image control and your image will be displayed. An image is going to take up dozens of KB but a file spec fits in 255 bytes most of the time. Therefore, make it a point to store the image specification in your DB and when the form with the image control is opened, the Form_Current routine can just load up the image spec to the right place in the control. You might never see any difference in speed between using the embedded image vs. an external image.

But to make that work, you need to extract the older images, export them, so that they CAN be given a name. Unless you have kept those older images on a backup device somewhere, in which case you could just create a new database and do selective copies of your other structures and data into the new file. I must admit that I don't deal with embedded image files that often so I'm a little shaky in extraction and cleanup.

You need to understand include the FileSystemObject (=FSO, part of the Windows Scripting library), which lets you copy, move, delete, rename, ... lots of common file operations, all from VBA. You can also manipulate or examine folders and trigger file selection dialogs. Here is a link to the top of the documentation tree for the FSO. From this link you can drill down to see what you can do with the FSO, exploring its methods, properties, and related objects (files and folders).

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

As to WHERE you put the images, one common method is to put the files in a child folder under the main DB folder. To understand how to get to that folder, remember that CurrentDB.Name gives you the fully qualified file spec of the current database file. If you have split the database into parts (front-end and back-end, or FE/BE) you can take Tables!name-of-a-table.Connect andsee the file spec of the BE file - in which case, the CurrentDB.Name is for the FE file.

There are security implications involved in having medical images in files, so you have to consider the regulatory implications of doing this, but they are not actually that different from the case of having the images embedded in the DB itself. More files to manage - but the regulatory side is about the same.

 

[GPGeorge]

I recently an across an article on extracting attachments from Access tables, but I'm having a senior moment trying to remember where or who. I want to say it was Phillip Steifel, possibly. If I find it I'll post a link. You can also do a search, of course.

 

[strive4peace]

maybe it was my Automate Word presentation, @GPGeorge ?

@drshahriyar, This code loops through an attachment field in a table and saves all the files externally.

Sub run_SaveAttachmentsToFiles()
'130117 strive4peace
   SaveAttachmentsToFiles   "MyTablename", "MyAttachmentFieldname", "MyFieldnamePK"
End Sub

Sub SaveAttachmentsToFiles( _
   ByVal sTableName As String _
    ,ByVal sFieldName_Att As String _
    ,ByVal sFieldName_ID As String _
    ,Optional ByVal sPath As String =   "" _
    ,Optional ByVal sTableNameChild As String =   "" _
    ,Optional ByVal sFilenameField As String =   "" _
   )
'130117 Crystal strive4peace
   'PARAMETERS
   ' sTableName = name of table with attachment field
   ' sFieldName_Att = name of attachment field
   ' sFieldName_ID = name of PK
   ' sPath - optional. If not specified, written to c:\CurrentDbPath\Attachments
   '   WRITE CHILD RECORDS
   '   assumption: FK = sFieldName_ID (same as parent table)
   ' sTableNameChild - optional. name of related table
   ' sFilenameField - fieldname in child table for Filename

    On Error GoTo Proc_Err

    Dim db As DAO.Database _
       ,rs As DAO.Recordset _
       ,rs2 As DAO.Recordset2 _
       ,fld2 As DAO.Field2
 
   Dim sPathFile As String _
       ,nNum As Long _
       ,sSQL As String

   nNum = 0

   If sPath =   "" Then
      sPath = CurrentProject.Path &   "\Attachments\"
      If Dir(sPath ,vbDirectory) =   "" Then
         MkDir sPath
         DoEvents
      End If
   Else
      If Right(sPath ,1) <>   "\" Then sPath = sPath &   "\"
   End If

   Set db = CurrentDb
   Set rs = db.OpenRecordset(sTableName ,dbOpenDynaset)

   Do While Not rs.EOF
      Set rs2 = rs.Fields(sFieldName_Att).Value
      With rs2
         Do While Not .EOF
       
            sPathFile = sPath _
               & sTableName &   "_" _
               & Replace( _
                  Replace(rs2.Fields(  "FileName").Value _
                       , ".jpg", "_" & rs(sFieldName_ID).Value &   ".jpg") _
                     , ".png" ,rs(sFieldName_ID).Value &   ".png")
                
            If Dir(sPathFile) <>   "" Then
               ' set attribute to Normal in case it is ReadOnly
               ' VBA.SetAttr sPathFile, vbNormal
               Kill sPathFile
            End If

            Set fld2 = rs2.Fields(  "FileData")
            fld2.SaveToFile sPathFile
            nNum = nNum + 1
       
            If sTableNameChild <>   "" And sFilenameField <>   "" Then
               'current database directory is stripped from path
               'if path starts with \ then it is relative to database directory
               sSQL =   "INSERT INTO " & sTableNameChild _
                 &   "(" & sFieldName_ID &   ", " & sFilenameField &   ")" _
                 &   " SELECT " & rs(sFieldName_ID).Value _
                 &   ", """ & Replace(sPathFile ,CurrentProject.Path, "") &   """;"
            
               With db
                 .Execute sSQL
                 If Not .RecordsAffected > 0 Then
                    If MsgBox(  "Error creating Child Record for " _
                       & sPathFile ,vbOKCancel, "Error -- continue anyway") = vbCancel Then
                          GoTo Proc_Exit
                    End If
                 End If
               End With
            End If
                
            .MoveNext
         Loop   'rs2
         .Close
      End With   'rs2
      rs.MoveNext
   Loop   'rs

   MsgBox   "Created " & nNum &   " Files from Attachments" _
      ,,  "Done"
 
Proc_Exit:
   On Error Resume Next
   'release object variables
   If Not rs Is Nothing Then
      rs.Close
      Set rs = Nothing
   End If
   If Not rs2 Is Nothing Then
      rs2.Close
      Set rs2 = Nothing
   End If
   Set db = Nothing
   Exit Sub

Proc_Err:
   MsgBox Err.Description,,_
        "ERROR " & Err.Number _
        &   "   SaveAttachmentsToFiles"

   Resume Proc_Exit
   Resume

End Sub

and now that I've posted this code, I read your question better and it seems that maybe you aren't using attachment fields? Perhaps someone reading this thread is and this code can help them

 

[moke123]

Dbpix stores the images as long binary data

Does this apply to you situation?

Read and Write Blobs - Access Image FAQ

Reading files into, and extracting files from, an OLE Object field as Blobs

www.ammara.com