[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 898
  • Last Modified:

View image thumbnail from image SQL Server data type in OLE

I have a field as an image data type and the form object is OLE. Is it possible to have a preview image thumbnail?
The backend is on MS SQL Server 2005 and the frontend is on Access 2007.
0
Papote
Asked:
Papote
  • 4
  • 3
1 Solution
 
rob_farleyCommented:
The image data type doesn't actually mean it's a picture. It means it's binary data with a 2GB limit. In fact, you should be using varbinary(max) instead.

So... the easy answer is No. The harder answer is "Sure, but only if you write code in Access to shrink the picture and store that in a separate field"
0
 
puppydogbuddyCommented:
see if any of the methods and suggestions at this link help (you need to read the link in its entirety):
               http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24277552.html
0
 
PapoteAuthor Commented:
Is there a way to read the data from the BLOB and send it to an image control in the form ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
puppydogbuddyCommented:
see GLFord's comments below from the link I gave you above:

Using MS Access, I used the first Access function from this link: http://www.ammara.com/access_image_faq/read_write_blob.html 
 
Storing images, documents and other files as blobs offers an efficient and portable alternative to OLE Embedding. Using the code from the snippets, you can store any type of file as a blob, or extract a blob back to an identical disk file.  I used the code snippet to convert SQL-Server BLOBs to JPGs.  To use the snippet, you pass the Windows name to be given to the JPG as the first argument, and then the BLOB field as the second argument.  I converted a SQL Server table with 400 rows (400 BLOBS), into 400 individual JPGs, each with an individual name based on the key field of each row.  Perfect!  Thanks to all who participated in this question!

Usage Example:

The following examples show how you can use the FileToBlob and BlobToFile functions in a form. The form's record source includes a field named 'Image' of type 'OLE Object'. The implementation of FileToBlob and BlobToFile (below the line) should be placed in a module.

'Read the file 'Image-In.jpg' into the field named 'Image'.
FileToBlob "C:\Photos\Image-In.jpg", [Image]

'Extract the file contained in the field named 'Image' to a file named 'Image-Out.jpg'.
BlobToFile "C:\Photos\Image-Out.jpg", [Image]
_______________________________________________________
Here are other sources that probably accomplish the same thing although they were not used in this instance.
see http://www.lebans.com/loadsavejpeg.htm
or
read and implement method #3 at this link:
           http://www.jamiessoftware.tk/articles/handlingimages.html  

-------------------------------------------------------------------
'Function:  BlobToFile - Extracts the data in a binary field to a disk file.
'Parameter: strFile - Full path and filename of the destination file.
'Parameter: Field - The field containing the blob.
'Return:    The length of the data extracted.
Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
    On Error GoTo BlobToFileError

    Dim nFileNum As Integer
    Dim abytData() As Byte
    BlobToFile = 0
    nFileNum = FreeFile
    Open strFile For Binary Access Write As nFileNum
    abytData = Field
    Put #nFileNum, , abytData
    BlobToFile = LOF(nFileNum)

BlobToFileExit:
    If nFileNum > 0 Then Close nFileNum
    Exit Function

BlobToFileError:
    MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _
           "Error writing file in BlobToFile"
    BlobToFile = 0
    Resume BlobToFileExit

End Function


'Function:  FileToBlob - Loads a file into a binary field.
'Parameter: strFile - Full path and filename of the source file.
'Parameter: Field - The binary field into which the file is to be loaded.
Public Function FileToBlob(strFile As String, ByRef Field As Object)
    On Error GoTo FileToBlobError

    If Len(Dir(strFile)) > 0 Then
        Dim nFileNum As Integer
        Dim byteData() As Byte

        nFileNum = FreeFile()
        Open strFile For Binary Access Read As nFileNum
        If LOF(nFileNum) > 0 Then
            ReDim byteData(1 To LOF(nFileNum))
            Get #nFileNum, , byteData
            Field = byteData
        End If
    Else
        MsgBox "Error: File not found", vbCritical, _
               "Error reading file in FileToBlob"
    End If

FileToBlobExit:
    If nFileNum > 0 Then Close nFileNum
    Exit Function

FileToBlobError:
    MsgBox "Error " & err.Number & ": " & err.Description, vbCritical, _
           "Error reading file in FileToBlob"
    Resume FileToBlobExit

End Function

-------------------------------------------------------------------

Open in new window

0
 
PapoteAuthor Commented:
So basically I have to remove the OLE object and use the routine to convert the image to a BLOB in the field and then to preview the field by creating a temporary file and link the path to the image control?
0
 
puppydogbuddyCommented:
No, write the Blob in/out of an Access ole field for Access versions prior to version 2007.  The Blob method does not the database bloat problems of older methods.
       
The preferred method is to store the image as a binary large object bitmap (BLOB) in an OLE field of an MS Access table, extract the image when required and use an image control to display the image. Read the link I gave you to method #3 in the article from Jamie's software.

If the Blob is stored in a separate file, and not  in an Access table it recommended, as in Mr Ford's case, to extract the Blob from the file and read the BLOB in(store it) to an Access ole field, and manipulate it using the image control as discussed.  

If you still prefer to store image outside of Access, then see the Leban's  link I provided.
0
 
PapoteAuthor Commented:
Leban's Method #3 sounds good, but it is pretty vague on how to actually implement.
0
 
puppydogbuddyCommented:
If you are referring to
                  http://www.lebans.com/loadsavejpeg.htm
have you downloaded the zip file? The instructions are contained therein.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now