add photos to a table

[fnwtech]

I apologize if this has been answered, I searched, but didn't really know what keywords to use.

I have a table of students. One field is photo with the type OLE. I have a directory which has all of the photos in it and the photos are named by student ID (which is the primary key field in the table). Is there an easy way to update the table to put the picture file in the photo field without manually adding each one? I have about 600 to do!

Thanks

[ADezii]

I apologize if this has been answered, I searched, but didn't really know what keywords to use.

I have a table of students. One field is photo with the type OLE. I have a directory which has all of the photos in it and the photos are named by student ID (which is the primary key field in the table). Is there an easy way to update the table to put the picture file in the photo field without manually adding each one? I have about 600 to do!

Thanks

1. What, if any, is the relationship between a Student's ID and his/her Photo Image? If a Student's ID is 123456, then is his/her Photo named 123456.jpg?
2. If there is a relationship for naming conventions between ID and Photo, is it consistent?
3. What is the Name of the Table housing the [Student ID] and [Photo] Fields?
4. What is the Name and Data Type of the [Student ID] Field in the Table?
5. What is the Name and Data Type of the [Photo] Field in the Table?

[fnwtech]

1. What, if any, is the relationship between a Student's ID and his/her Photo Image? If a Student's ID is 123456, then is his/her Photo named 123456.jpg?
2. If there is a relationship for naming conventions between ID and Photo, is it consistent?
3. What is the Name of the Table housing the [Student ID] and [Photo] Fields?
4. What is the Name and Data Type of the [Student ID] Field in the Table?
5. What is the Name and Data Type of the [Photo] Field in the Table?

All student IDs are seven digit and the photo is named using the studentID.jpg (e.g. 1234567.jpg)
The table housing the student ID and photo fields is tblStudent.
The field name of the student id is StudentStateID and is a text field
The field name of the photo field is photo and is an ole object

[ADezii]

All student IDs are seven digit and the photo is named using the studentID.jpg (e.g. 1234567.jpg)
The table housing the student ID and photo fields is tblStudent.
The field name of the student id is StudentStateID and is a text field
The field name of the photo field is photo and is an ole object

Will get back to you on this one.

[ADezii]

All student IDs are seven digit and the photo is named using the studentID.jpg (e.g. 1234567.jpg)
The table housing the student ID and photo fields is tblStudent.
The field name of the student id is StudentStateID and is a text field
The field name of the photo field is photo and is an ole object

I think I have a better idea, one which you'll like. Why not store the Photos as Hyperlinks in tblStudent?

1. Change the [Photo] Field from OLE Object to Hyperlink.
2. Execute the code segment below, assuming your *.jpg Naming Convention is consistent as you previously indicated, all 600 Records will be updated with the appropriate Photo for each Student.
   

   'Must 1st set a Reference to the Microsoft Scripting Runtime
    
   'Accessing Folder Properties
   Dim fso As FileSystemObject
   Dim fldr As Folder
   Dim fil As File
   Dim strSQL As String
    
   Set fso = New Scripting.FileSystemObject
    
   'Creates an Instance of a Folder, replace with your own Folder
   Set fldr = fso.GetFolder("C:\Test\")
    
   For Each fil In fldr.Files
     strSQL = "Update tblStudent Set tblStudent.[Photo] = '" & fil.Path & "#" & fil.Path & _
              "' Where [StudentStateID] = '" & Left$(fil.Name, InStr(fil.Name, ".") - 1) & "';"
     DoCmd.SetWarnings False
       DoCmd.RunSQL strSQL
     DoCmd.SetWarnings True
   Next

[fnwtech]

I think I have a better idea, one which you'll like. Why not store the Photos as Hyperlinks in tblStudent?

1. Change the [Photo] Field from OLE Object to Hyperlink.
2. Execute the code segment below, assuming your *.jpg Naming Convention is consistent as you previously indicated, all 600 Records will be updated with the appropriate Photo for each Student.
   

   'Must 1st set a Reference to the Microsoft Scripting Runtime
    
   'Accessing Folder Properties
   Dim fso As FileSystemObject
   Dim fldr As Folder
   Dim fil As File
   Dim strSQL As String
    
   Set fso = New Scripting.FileSystemObject
    
   'Creates an Instance of a Folder, replace with your own Folder
   Set fldr = fso.GetFolder("C:\Test\")
    
   For Each fil In fldr.Files
     strSQL = "Update tblStudent Set tblStudent.[Photo] = '" & fil.Path & "#" & fil.Path & _
              "' Where [StudentStateID] = '" & Left$(fil.Name, InStr(fil.Name, ".") - 1) & "';"
     DoCmd.SetWarnings False
       DoCmd.RunSQL strSQL
     DoCmd.SetWarnings True
   Next

Okay, thanks. I will give it a try!