时间戳

首页 > 解决方案 > 使用 VBA moodule 批量加载图像以访问数据库

vba - 使用 VBA moodule 批量加载图像以访问数据库

问题描述

我正在尝试运行我在网上找到的东西（参见下面的代码块）。

我收到错误消息：

编译错误：找不到方法或数据成员”，它突出显示 .LoadFromFile

关于为什么卡在这里的任何想法？

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

标签： vbams-access

解决方案

由于 ADO（ActiveX 数据对象）记录字段没有 .LoadFromFile() 方法，因此您有错误。但是 DAO (Data Access Objects) 记录 field2 有这个方法。因此，我们将您的代码修改为这样（在 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

要使用它，照片列必须是访问“附件数据类型”，参考。https://support.microsoft.com/en-us/office/attach-files-and-graphics-to-the-records-in-your-database-d40a09ad-a753-4a14-9161-7f15baad6dbd

推荐阅读