Solved

extracting images from Access 2000

Posted on 2002-06-27
18
775 Views
Last Modified: 2008-03-04
I have a database which is the back-end for a vendor-created product.  It is a namebadge system in which there is a camera connected to get photos of employees.  The images are stored as a blob format in the database.  Is there a way I can extract the images from the database for use in other applications?  When taking the photos, they are just automatically inserted into the database, and nowhere is there and option to save the photo elswhere or in another format.  I'm not knowledgeable about how blobs work (i.e., is a .jpg file that's converted to this format, etc.).  The table shows the blob field as a OLE Object datatype.
0
Comment
Question by:jward117
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 3

Expert Comment

by:marcoszorrilla
ID: 7114967
You can do a doubleclick over the field an automatically the PaintBrush is open with the photo, now Save as.....
Or Copy and Paste to an other graphics aplication.


Best Regards
Marcos.
0
 

Author Comment

by:jward117
ID: 7114980
this does not work either.  Gets this error:  A problem occured while Microsoft Access was communicating with the OLE server or Active X control.  Close the OLE server and restart it outside of Microsoft Access.  Then try the original operation again in Microsoft Access.  What do I need to do?
0
 
LVL 3

Expert Comment

by:marcoszorrilla
ID: 7115030
Well try to create a formt with the forms assistan and include this OLE field.

Open This form.

Click the right button of your mouse over this field with the image  and Copy....

The Paste over the Paint .....

Best Regards
Marcos.
0
 
LVL 14

Accepted Solution

by:
mgrattan earned 150 total points
ID: 7115039
The following code can be copied and then pasted into a new code module in Access.  You can then use the WriteBlob function to copy the objects in the binary data field of your table onto your hard drive.

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 (Access Basic
     '   code in Microsoft Access 2.0 and earlier) 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 photo...", _
                  FileLength \ 1000)

         ' Put first record in edit mode.
         'T.MoveFirst
         '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 (Access Basic
     '   code in Microsoft Access 2.0 or earlier) 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 " & T!EmpID & ".jpg", 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
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 7115040
When you place a button on the form you just need to call the function from the button click event.  Here are the steps:

1.  In design view, double-click on the button or right click and choose Properties to bring up the property sheet.
2.  Click the Event tab and locate the On Click event.  Double-click in the On Click field to select [Event Procedure] and then click the Build button (it has three dots on it) to open the code module for the event.
3.  Place the following code in the event procedure:


         Dim BytesWritten As Variant
         Dim db As DAO.Database
         Dim T As DAO.Recordset
         Dim Destination As String

         ' Open the table containing BLOB data
         Set db = CurrentDb()
         Set T = db.OpenRecordset("<table name>", dbOpenTable)

         T.MoveFirst
         Do Until T.EOF
             BytesWritten = WriteBLOB(T, "<Field Name>", Destination)
             T.MoveNext
         Loop

You will notice in the WriteBlob function there is a reference to "T!EmpID".  You will need to replace this with "T(sField)" so the function will use the field name passed to it.  In the sample code above, make sure you replace <table name> and <field name> with the appropriate table and field names that contain your BLOB data.

You can get creative with the Destination parameter of the above code and pull it from your table.  Let's say you have a field called EmployeeID; you can modify the Do Loop like this:

         Do Until T.EOF
             Destination = "c:\MyFiles\" & T!EmpID & ".jpg"
             BytesWritten = WriteBLOB(T, "<Field Name>", Destination)
             T.MoveNext
         Loop

Let me know if you have further questions.

Mike.
0
 

Author Comment

by:jward117
ID: 7115290
Mike,
I created the module as you've instructed (cut and pasted exact syntax you gave).  Created a form and command button as you've instructed as well.  When clicking on the button, it goes into the debugger.  Doesn't seem to like the code.  "db As DAO.Recordset" is highlighted...
0
 

Author Comment

by:jward117
ID: 7115291
Mike,
I created the module as you've instructed (cut and pasted exact syntax you gave).  Created a form and command button as you've instructed as well.  When clicking on the button, it goes into the debugger.  Doesn't seem to like the code.  "db As DAO.Recordset" is highlighted...
0
 
LVL 57
ID: 7115952
jward117,

  You need a DAO reference.  Open a module in design view, click tools/references and check "Microsoft Data Access Objects 3.61" (that may not be exact but you'll spot it).

  However I think you'll find that it doesn't solve your problem.  It all depends on how the images are actually placed in the OLE field.

  If done with read/write BLOB, then the above code will work.  However if the software inserts the image via OLE, then Access places a wrapper around the object when saving it.

  When you do the Read Blob above, you'll get the wrapper as well.  Since the wrapper has never been documented, you're not going to know how to pull it apart.

  Give the above code a shot anyway as it will tell you how it was stored.  If it doesn't work, then you'll need to install the software that came with the camera on the station your using to get the OLE stuff to work.

Jim.

0
 
LVL 14

Expert Comment

by:mgrattan
ID: 7116477
As jdettman said, you need to add a reference for Microsoft DAO 3.6 in Tools, References.  After doing so, you will also need to move it up the list in priority so it is above the ADO (ActiveX Data Objects) reference.

In my experience, this code will work for most OLE objects; including JPG, Word, Excel, and PowerPoint files.  However, I have seen problems with some types of software that store the data in two separate records; in that case, you MAY be able to get the code to work my modifying it to do a FindFirst/FindNext to locate the pieces of the picture file that you need so they can be merged (your mileage may vary though; it depends on how the file was stored).
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jward117
ID: 7116740
I did that, and now the debugger has moved past that point, but has a problem with the "WriteBLOB" in:

Do Until T.EOF
    Destination = "C:\Temp\Blob\" & T!Photo & ".jpg"
    BytesWritten = WriteBLOB(T, "Photo", Destination)
    T.MoveNext
Loop

I really appreciate the time you've taken to help me with this, I'll bump up the points for you Mike.  Jim, I really appreciate your help too.
0
 

Author Comment

by:jward117
ID: 7116802
I did that, and now the debugger has moved past that point, but has a problem with the "WriteBLOB" in:

Do Until T.EOF
    Destination = "C:\Temp\Blob\" & T!Photo & ".jpg"
    BytesWritten = WriteBLOB(T, "Photo", Destination)
    T.MoveNext
Loop

I really appreciate the time you've taken to help me with this, I'll bump up the points for you Mike.  Jim, I really appreciate your help too.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 7117103
When you look at the database table, do you see more than one record that contains an OLE object for the same employee?
0
 

Author Comment

by:jward117
ID: 7117115
Thanks for all your help
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 7117126
You're welcome....so it's working now??
0
 

Author Comment

by:jward117
ID: 7117217
no, it's still wanting to debug as my recent comment states.  Sorry, I accepted your answer prematurely.  Regarding your other question, there is only the one OLE field, and each record only contains one photo/file.  I contacted the vendor and he said he'd send me a module which will extract the images for me.  If this module is useable with other databases and not totally specific to this program, I can send you a copy - but I'll need your email...
0
 
LVL 5

Expert Comment

by:Yog
ID: 7312935
mgrattan, any idea why this writeblob doesn't write an excel file, it works good for a bmp or a word doc etc. wondering why it doesn't for excel

yog
pls reply


0
 
LVL 5

Expert Comment

by:Yog
ID: 7312985
sorry i mean readblob to save an excel object into ole object data type.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 7313044
jward117:

Sorry, I didn't get email notification on your last comment.  My email is outuvtime@yahoo.com

Yog:

Sorry, I've not used these procedures for Excel files.  Have you tried searching the Microsoft Knowledge Base?  

Mike.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

19 Experts available now in Live!

Get 1:1 Help Now