Automatically Link Photos An OLE Field in a Table

[ckendricks7]

I have a table in which one of the fields is the clients SSN (primary
key), several other fields, and then a field (OLE) which is intended to
be linked to the client photo, which are contained in another folder.
Each clients photo has the same file name as the clients SSN. Is there
a way to AUTOMATICALLY link the clients photo to the photo field in his
record. I have thousands of clients (potentially), and don't want to
have to go in and link or embed each photo individually.

 

[Wayne-in-Manchester]

You are always going to have to show the path to the photo. Such as

c:\My Pictures\Waynes file \ Picture 1
c:\My Pictures\Waynes file \ Picture 2

This is placed in the table in the OLE field (this can be done via a form if
nes.). There is no way to "automatically" place this data - in the same way
as there is no way to insert a person's name on a new record. You have to
type the path to the photo.

 

[bob]

You can do this as a batch process; sample code is included below. However, even though the name 'Linking'
suggests otherwise, a large amount of data is still stored in the table - for JPEG images this is many
times the size of the image files. In a recent test we OLE linked 32 MB of JPEG images, and the database
grew to 1.3 GB. These were actually quite small images, since using full-resolution digital photos caused
out-of-memory errors. It is also very dependent on having suitable OLE Server software installed and correctly
registered on your system(s) - installing new software can quickly break this, as can installing Office
2003 (which does not include the typical OLE Server application for JPEG and several other formats).

If you just want to display the photos on a form, a better approach is to use an image control. You already
have the info to generate the path to the file, so no additional linking or import process is required.
Code for this is also included below. You should also implement the registry fix to prevent the image
control from crashing if you scroll too quickly through records (and to inhibit the 'Importing' dialog).
To do this set the following registry keys:

HKEY_LOCAL_MACHINE\Software\Microsoft\ Shared Tools\Graphics Filters\Import\JPEG\Options

HKEY_CURRENT_USER\Software\Microsoft\ Shared Tools\Graphics Filters\Import\JPEG\Options

In both the above keys set the 'ShowProgressDialog' value to 'No', and note that the 'No' is case-sensitive.



Code to batch OLE Link:
**********************

Private Sub BatchLink_Click()

Dim strFile As String
Dim strFolder As String
Dim strFullPath As String

strFolder = "C:\images\"

DoCmd.GoToRecord , , acFirst

While Not Me.NewRecord

strFullPath = strFolder + Me!SSN + ".jpg"
strFile = Dir(strFullPath, vbNormal)

If (strFile <> vbNullString) Then

OLEBound1.OLETypeAllowed = acOLELinked
OLEBound1.SourceDoc = strFullPath
OLEBound1.Action = acOLECreateLink

End If

DoCmd.GoToRecord , , acNext
Wend

End Sub


'OLEBound1' is a Bound OLE Frame, bound to an OLE Object field.
'SSN' is a field in the form's data source that contains the filename.
'strFolder' is set to the folder that contains the image files.
'BatchLink' is a button on the form to launch the import process.


Code to display the images directly from files:
**********************************************

Private Sub Form_Current()

Dim strFile As String
Dim strFullPath As String

strFullPath = "C:\images\" + Me!SSN + ".jpg"
strFile = Dir(strFullPath, vbNormal)

If (strFile <> vbNullString) Then
Image1.Picture = strFullPath
Else
Image1.Picture = ""
End If

End Sub


'Image1' is an image control/frame.
'SSN' is a field in the form's data-source that contains the filename.
'C:\images\' is the folder containing the images. Amend as appropriate.


--
_______________________________________________________
http://www.ammara.com/dbpix/access.html
DBPix 2.0: Add pictures to Access, Easily & Efficiently