Bulk load images to access db with VBA moodule

Question

I am trying to run something that I found online (see code block below).

I am getting the error message:

Compile Error: Method or data member not found" and it is highlighting .LoadFromFile

Any ideas on why this is stuck here?

Public Sub OneTimeImport()

    Dim strPath As String
    strPath = "myfilepath"

    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject") 'this avoids adding the reference to this - but you loose the easier to write code stuff
   
    Dim con As ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set con = CurrentDb.Connection 'this part might need some adjustment

        rs.Open "SELECT * FROM dbo_Bor_spr_Surface_Master"
            If Not rs.EOF Then
                Do While Not rs.EOF
                    If fs.FileExists(strPath & "\" & rs("Seed_ID") & ".jpg") Then
                        rs("Photo").LoadFromFile strPath & "\" & rs("Seed_ID")
                        rs.Update
                    End If
                    rs.MoveNext
                Loop
                End If
            rs.Close
        con.Close

    Set rs = Nothing
    Set con = Nothing
    Set fs = Nothing
    
End Sub

jacouh · Accepted Answer · 2021-03-07 22:32:05Z

2

As ADO (ActiveX Data Objects) record field has no .LoadFromFile() method, so you have error. But DAO (Data Access Objects) record field2 has this mothod. So we modify your code as this (code tested under Microsoft Access 2019 Pro):

'
' strPath: directory path for photos
' fs: file system object
' rs: DAO.recordset
' rs2: DAO.recordset 2
'
Sub OneTimeImport()

  Dim strPath As String
  

  Dim fs As Object
  
  
  'this avoids adding the reference to this - but you loose the easier to write code stuff
  Set fs = CreateObject("Scripting.FileSystemObject")

  '
  'Dim con As ADODB.Connection
  'Dim rs As New ADODB.Recordset
  '
  Dim rs As DAO.Recordset
  
  Dim rs2 As DAO.Recordset
 
  strPath = "myfilepath"

  'Set con = CurrentDb.Connection 'this part might need some adjustment

  Set rs = CurrentDb.OpenRecordset("SELECT * FROM dbo_Bor_spr_Surface_Master")
'
  Do While Not rs.EOF
    '
    If fs.FileExists(strPath & "\" & rs("Seed_ID") & ".jpg") Then
      '
      ' edit the main record:
      '
      rs.Edit
      
      
      '
      ' make a sub-record:
      '
      Set rs2 = rs("Photo").Value
      
      rs2.AddNew
      rs2("FileData").LoadFromFile strPath & "\" & rs("Seed_ID") & ".jpg"
      rs2.Update
      rs2.Close
      '
      rs.Update
    End If
    '
    rs.MoveNext
    '
  Loop
'
' clear memory:
'
  Set rs2 = Nothing
  
  rs.Close
  Set rs = Nothing
  Set fs = Nothing
End Sub

To use this, Photo column must be Access "Attachments data type", ref. https://support.microsoft.com/en-us/office/attach-files-and-graphics-to-the-records-in-your-database-d40a09ad-a753-4a14-9161-7f15baad6dbd

edited Mar 7, 2021 at 22:32

answered Mar 4, 2021 at 22:29

jacouh

8,8055 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Over a year ago

Thank you for that, I do believe it is close to working. I am now getting "Run-time error '3259': Invalid field data type." at the same Load line as LoadFromFile is on. The filed referenced there is "Photos" which I have set to attachment data type in the table designer.

jacouh Over a year ago

This time I've tested and worked with modifications.

Collectives™ on Stack Overflow

Bulk load images to access db with VBA moodule

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related