CODEWITHSUNDEEP
vbams-access
Tom
Tom

Reputation: 91

Bulk load images to access db with VBA moodule

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

Upvotes: 1

Views: 148

Answers (1)

jacouh
jacouh

Reputation: 8741

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

Upvotes: 2

Related Questions