Need To Link Multiple Images Into Existing Database

[LadyAdler]

Greetings!
I have inherited a rather monsterous project in the form of an inventory database that is bumping into its storage ceiling due to some 3000+ images which are attached to the item entries. My task is to update the database so that it can continue to be used without archiving older inventory items. I have already updated the forms so that future entries will link the necessary images. However, I am stumped on how to deal with the existing entries. Going through them one at a time to link their images is looking like a massively tedious task. I am, admittedly, pretty subpar on VBA, and thought that maybe there was a possible solution on the backend. Any thoughts would be greatly appreciated. I'm in 2007/2010.

 

[apr pillai]

I think you need a one time job on the images (if they are on your disk) to rename them with some field value (say ProductID) of Products Table. Once that is done then you can load the image into an Image-Frame on the Form when a record is current on the Form.

For example: The ProductID of the current record is 12345, and the corresponding image name is 12345.jpg then on the current Event Procedure of the Form, to load the image into an image control, looks like the following code:

Private Sub Form_Current()
Dim strImagePath As String, pic As String
On Error GoTo Form_Current_Err
strImagePath = "c:\FolderName\"
pic = Me![ProductID] & ".jpg"
strImagePath = strImagePath & pic
If Len(Dir(strImagePath)) > 0 Then
  Me!Image38.Picture = strImagePath
Else
  Me!Image38.Picture = ""
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
'Not necessary to display a message here
Resume Form_Current_Exit

End Sub

The image type can be .bmp, .jpg, .png or .gif, but all of them should be any one of this type.

If you have all the images on your disk then you don't need the same images embedded in the OLEObject field on the table. You can remove them by updating a null value into that particular field, with an update Query.

After that you may compact the database to reduce it's size.