Results 1 to 7 of 7
  1. #1
    Premanshu is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010

    how to fetch image stored in access in (Long Binary Data) format..??

    Hi Everyone,

    I have a Access Database which contains some data with some images (images in Long Binary Data format).

    I have made a code which can fetch answers based on my question from the database.
    please see the code below:-
    (the below code is in Word VBA)

    Sub FetchAnswer()
    Dim oWorkspace As DAO.Workspace
        Dim oDatabase As DAO.Database
        Dim oRecordset As DAO.Recordset
        Set oWorkspace = DAO.CreateWorkspace("", "admin", "", dbUseJet)
        Set oDatabase = oWorkspace.OpenDatabase("D:\2008 Knowledge Base")
        Set oRecordset = oDatabase.OpenRecordset("Answers")
    Do While Not oRecordset.EOF
    If oRecordset.Fields("SampleQuestion").Value = SampleQuestion Then
        Ans = oRecordset.Fields("AnswerMemo").Value
        Exit Do
    End If
    range("c15").value = Ans
    End Sub
    the above code is working fine, but i also need to pull images corrosponding to the answer which is also stored in this same database.

    But I have no idea how to code a macro for that.

    Can anyone help me on this, I Desperately need this code...please help


  2. #2
    c_smithwick is offline Underpaid Programmer
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Lakeside, CA
    If you are storing the images as Binary Large Objects in an OLE field in your database, you can use the below to retrieve the info and write it to a file, and then use Word VBA to insert the file as an image:

    ' PURPOSE:
    '   Writes BLOB (Binary Large Object) information stored in the specified table and field to the specified disk file.
    '   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.
    Function WriteBLOB(rstData As Recordset, strField As String, strDest As String) As Long
      Dim intNumBlocks  As Integer
      Dim intDestFile   As Integer
      Dim i             As Integer
      Dim lngFileLength As Long
      Dim lngLeftOver   As Long
      Dim strFileData   As String
      Dim varRetVal     As Variant
      On Error GoTo Err_WriteBLOB
           ' Get the size of the field.
    10:    lngFileLength = rstData.Fields(strField).FieldSize
    20:    If lngFileLength = 0 Then
    30:      WriteBLOB = 0
    40:      Exit Function
    50:    End If
           ' Calculate number of blocks to write and leftover bytes.
    60:    intNumBlocks = lngFileLength \ BlockSize
    70:    lngLeftOver = lngFileLength Mod BlockSize
           ' Remove any existing destination file.
    80:    intDestFile = FreeFile
    90:    Open strDest For Output As intDestFile
    100:   Close intDestFile
           ' Open the destination file.
    110:   Open strDest For Binary As intDestFile
           ' SysCmd is used to manipulate the status bar meter.
    120:   varRetVal = SysCmd(acSysCmdInitMeter, "Writing BLOB", lngFileLength / 1000)
           ' Write the leftover data to the output file.
    130:   strFileData = rstData.Fields(strField).GetChunk(0, lngLeftOver)
    140:   Put intDestFile, , strFileData
           ' Update the status bar meter.
    150:   varRetVal = SysCmd(acSysCmdUpdateMeter, lngLeftOver / 1000)
           ' Write the remaining blocks of data to the output file.
    160:   For i = 1 To intNumBlocks
             ' Reads a chunk and writes it to output file.
    170:     strFileData = rstData.Fields(strField).GetChunk((i - 1) * BlockSize + lngLeftOver, BlockSize)
    180:     Put intDestFile, , strFileData
    190:     varRetVal = SysCmd(acSysCmdUpdateMeter, ((i - 1) * BlockSize + lngLeftOver) / 1000)
    200:   Next i
           ' Terminates function
    210:   varRetVal = SysCmd(acSysCmdRemoveMeter)
    220:   Close intDestFile
    230:   WriteBLOB = lngFileLength
    240:   Exit Function
    250:   WriteBLOB = -Err
    260:   Exit Function
    End Function

  3. #3
    Premanshu is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010
    Hi C_Smithwick

    First of all thank you very much for your responce, it's a great help to me.

    This seems to be excellent, however I am not so gud with vba, so kindly could you please advice me where do i need to put this function (like in word vba or in access vba) and then how do i call this function.

    Please advice.


  4. #4
    c_smithwick is offline Underpaid Programmer
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Lakeside, CA
    What you are trying to do will require quite a bit of familiarity with Word VBA as well as Access - much more so than the simple fix you are asking for. You have to do much more than just "call" this function. You will need a reference to your word document, know where in the document you want to load your picture file and also be able to manipulate it's position and size if needed - all through VBA. Not so easy for a novice. I would have to spend a bit of time myself working out all the kinks. I would suggest if you want to go forward with this particular task and requirement that you gain some significant skills with both Word and Access VBA or pawn this off on some other expert to write it for you.

  5. #5
    Premanshu is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010

    you are right, i'll have to gain some knowledge to compleate this on both word and access vba, i'll do it and also see if there is anyone else also in the forum who could help.... meanwhile if you come up with any solution please let me knew... that would be a great help to me

    Thank you very much for all your help and support c_smithwick
    have a nice day


  6. #6
    Premanshu is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2010

    Graphics filter unable to convert file

    Hi c_smithwick

    first of all thanks for your help in giving me the function to convert the blob to file.

    Further in this i have managed to set up the code in my word vba but I am now facing a new problem with this...

    I converted a few images from blob to file and then pasted them problematically in the word but when i tried to do this same thing with some blobs in a different table of this same database (access file) I am getting an error saying "The Graphics filter was unable to convert this file"

    please help me on this as well if you have any clue why it's happening.

    many thanks in advance


  7. #7
    Max D's Avatar
    Max D is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Premanshu, you can open the file with some hex viewer and compare its structure to good file of this type.

    Maybe there is some OLE header behind or something totally wrong inside.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Query to fetch last row in a table
    By gunapriyan in forum Queries
    Replies: 10
    Last Post: 02-20-2010, 12:46 AM
  2. Replies: 0
    Last Post: 10-04-2009, 04:11 AM
  3. Please Help, How Do I Format this Data?
    By txdave36 in forum Access
    Replies: 6
    Last Post: 09-09-2009, 04:31 PM
  4. SQL Server/VBA Data Types - Long & BigInt
    By Patrick.Grant01 in forum Programming
    Replies: 0
    Last Post: 06-05-2009, 09:24 AM
  5. stored procedure return value to access form
    By rbw940 in forum Programming
    Replies: 0
    Last Post: 10-13-2008, 01:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums