Load pictures into attachment data type with linked file names in access

So I have a table in access with file names I have been using as links in a form to view pictures. I would like to move these into an attachment database of photos so I can distribute the database to others without having to copy the file path names too.

I started some code to try it but not sure how to loop through the file paths because I have specific images I choose.

Here is an example of some of the data...so I would take the Tassel photo filepath and upload the picture to column PhotoT with datatype attachment.

EDIT:

I updated my code to get this to work. I added in a column import to the previous table. and added in seperate coding sections for each column. It works great! My database increased in size to 1.7gb. It was originally only 30mb with 60mb of pictures to update. Not sure where all the storage went too. The speed is a lot faster and its now self contained so thats great. If i had anymore picture i would have had to figure something else out haha

Option Compare Database
Option Explicit

Sub test()
      
    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset
    Dim rsA As DAO.Recordset
    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    Dim rstChild As Recordset2
    Dim strsql As String
   
    Dim noRows As String
    Dim Tasselpath As String
    
    
    '''''''''''''''''''''''''''''
    'add columns to table
    '''''''''''''''''''''''''''''
    If DoesTblFieldExist("InbredPicPaths", "PE") = False Then
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("InbredPicPaths")
    
    Set fld = tdf.CreateField("PT", dbAttachment)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("PS", dbAttachment)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("PE", dbAttachment)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("PBR", dbAttachment)
    tdf.Fields.Append fld
    
    Set tdf = Nothing
    
    
    End If
    
    '''''''''''''''''''''''''''''
    'Tassel
    '''''''''''''''''''''''''''''
    Set dbs = CurrentDb
    strsql = "SELECT InbredPicPaths.* FROM InbredPicPaths WHERE (((InbredPicPaths.Tassel)<>''))"

    Set rst = dbs.OpenRecordset(strsql)
    'Set fld = rst("Tassel")
    Set rstChild = rst.Fields("PT").Value
    
    If rstChild.RecordCount <= 0 Then
   'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
         

         Tasselpath = rst!Tassel

         rst.Edit
         Set rsA = rst.Fields("PT").Value
         rsA.AddNew
         rsA("FileData").LoadFromFile Tasselpath
         rsA.Update
          rsA.Close
        
         rst.Update
         'Next record
         rst.MoveNext
         
        
    Loop
    End If
        '''''''''''''''''''''''''''''
    'silk
    '''''''''''''''''''''''''''''
   ' Set dbs = CurrentDb
    strsql = "SELECT InbredPicPaths.* FROM InbredPicPaths WHERE (((InbredPicPaths.Silk)<>''))"
    Set rst = dbs.OpenRecordset(strsql)
    'Set fld = rst("Silk")
        Set rstChild = rst.Fields("PS").Value
    
    If rstChild.RecordCount <= 0 Then
   'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
         

         Tasselpath = rst!Silk

         rst.Edit
         Set rsA = rst.Fields("PS").Value
         rsA.AddNew
         rsA("FileData").LoadFromFile Tasselpath
         rsA.Update
          rsA.Close
        
         rst.Update
         'Next record
         rst.MoveNext
         
        
    Loop
    End If
        '''''''''''''''''''''''''''''
    'Braceroot
    '''''''''''''''''''''''''''''
    'Set dbs = CurrentDb
    strsql = "SELECT InbredPicPaths.* FROM InbredPicPaths WHERE (((InbredPicPaths.BraceRoot)<>''))"
    Set rst = dbs.OpenRecordset(strsql)
    'Set fld = rst("BraceRoot")
        Set rstChild = rst.Fields("PBR").Value
    
    If rstChild.RecordCount <= 0 Then
   'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
         

         Tasselpath = rst!BraceRoot

         rst.Edit
         Set rsA = rst.Fields("PBR").Value
         rsA.AddNew
         rsA("FileData").LoadFromFile Tasselpath
         rsA.Update
          rsA.Close
        
         rst.Update
         'Next record
         rst.MoveNext
         
        
    Loop
    End If
        '''''''''''''''''''''''''''''
    'Ear
    '''''''''''''''''''''''''''''
    'Set dbs = CurrentDb
    strsql = "SELECT InbredPicPaths.* FROM InbredPicPaths WHERE (((InbredPicPaths.Ear)<>''))"
    Set rst = dbs.OpenRecordset(strsql)
   ' Set fld = rst("Ear")
        Set rstChild = rst.Fields("PE").Value
    
    If rstChild.RecordCount <= 0 Then
   'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
         

         Tasselpath = rst!Ear

         rst.Edit
         Set rsA = rst.Fields("PE").Value
         rsA.AddNew
         rsA("FileData").LoadFromFile Tasselpath
         rsA.Update
          rsA.Close
        
         rst.Update
         'Next record
         rst.MoveNext
         
        
    Loop
    
    End If
    
    rst.Close
    Set rst = Nothing
    Set rsA = Nothing
    Set dbs = Nothing
    Set rstChild = Nothing
    
    End Sub

Solution

So from the picture it looks like you need to iterate through the rows of a table with a column containing the url of a file and then attach that file to an attachment type column in the same file. Assuming:

Here is code that does that.

Public Sub MovethroughTableAttachingPhotos(TableName As String, urlColumnName As String, attachmenttypeColumnName As String)
'adapted from  https://learn.microsoft.com/en-us/office/vba/access/concepts/data-access-objects/work-with-attachments-in-dao
Dim db As Database
Set db = CurrentDb
Dim rsTable As Recordset
Dim rsPhotos As Recordset
Set rsTable = db.OpenRecordset(TableName)
rsTable.MoveFirst 'avoids an error
Dim currentURL As String
Do Until rsTable.EOF
currentURL = rsTable(urlColumnName)
rsTable.Edit
Set rsPhotos = rsTable.Fields(attachmenttypeColumnName).value
rsPhotos.AddNew
rsPhotos.Fields("FileData").LoadFromFile (currentURL)
rsPhotos.Update
rsPhotos.Close 'placing here avoids an error
rsTable.Update
rsTable.MoveNext
Loop
'clean up
rsTable.Close 
Set rsPhotos = Nothing
Set rsTable = Nothing
Set db = Nothing
End Sub

'to call the subroutine : MovethroughTableAttachingPhotos "Photos", "PhotoAddress", "PhotoAttachment"