向Microsoft Access数据库批量上传附件的VBA代码问题排查

问题背景 #

需要将文件夹中的文件批量串行上传至Microsoft Access数据库，为每条记录的Attachment字段添加对应文件。使用Str(i)生成文件名时，因正数转换后数字前带空格，导致找不到“ 1.jpg”这类文件；改用Format函数后，又提示“上传命令需要对象”。

第一段代码的问题分析 #

Sub AttachPhotos()

Dim Filename As String
Dim i As Integer
Dim db As DAO.Database

Set db = OpenDatabase("G:\My Drive\Takis\Graffiti.accdb")
Set Photo = db.OpenRecordset("Table1")
Photo.Edit
Set Photos = Photo.Fields("Photo").Value

For i = 1 To 3486

Rem If i <= 9 Then
Rem Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(1, "0")) & ".jpg"
Rem ElseIf (i > 9) And (i <= 99) Then
Rem Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(2, "0")) & ".jpg"
Rem ElseIf (i > 99) And (i <= 999) Then
Rem Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(3, "0")) & ".jpg"
Rem ElseIf (i > 999) Then
Rem Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(4, "0")) & ".jpg"
Rem End If

Photos.AddNew
Filename = "G:\My Drive\Takis\SS_photendix\" & Str(i) & ".jpg"
Photos.Fields("FileData").LoadFromFile (Filename)
Photos.Update
Photo.Update
Next

db.Close
Set db = Nothing
Set Photo = Nothing
Set Photos = Nothing

End Sub

• Str(i)的空格问题：Str函数转换正数时会在数字前添加一个空格（用于表示正号），生成的文件名会变成类似"G:\...\ 1.jpg"，系统无法找到带前导空格的文件。
• 记录集逻辑错误：
  • Photo.Edit放在循环外，且循环内每次执行Photo.Update，会反复修改同一条主记录（第一条）。
  • 主记录集Photo从未移动（没有MoveNext），所有文件都会被添加到第一条记录的Photo附件字段中，而非每条记录对应一个文件。

第二段代码的问题分析 #

Sub AttachPhotos()

Dim Filename As String
Dim i As Integer
Dim db As DAO.Database

Set db = OpenDatabase("G:\My Drive\Takis\Graffiti.accdb")
Set Photo = db.OpenRecordset("Table1")
Photo.Edit
Set Photos = Photo.Fields("Photo").Value

For i = 1 To 3486

If i <= 9 Then
Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(1, "0")) & ".jpg"
ElseIf (i > 9) And (i <= 99) Then
Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(2, "0")) & ".jpg"
ElseIf (i > 99) And (i <= 999) Then
Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(3, "0")) & ".jpg"
ElseIf (i > 999) Then
Filename = "G:\My Drive\Takis\SS_photendix\" & Format(i, String(4, "0")) & ".jpg"
End If

Photos.AddNew
Rem Filename = "G:\My Drive\Takis\SS_photendix\" & Str(i) & ".jpg"
Photos.Fields("FileData").LoadFromFile(Filename).Value
Photos.Update
Photo.Update
Next

db.Close
Set db = Nothing
Set Photo = Nothing
Set Photos = Nothing

End Sub

• Format函数使用错误：Format(i, String(1, "0"))是冗余写法，String(1,"0")生成的是单个"0"，其实可以直接用Format(i, "0000")统一生成4位带前导零的数字，无需分多段判断。
• LoadFromFile语法错误：LoadFromFile是方法，不是属性，不能链式访问.Value，正确写法是Photos.Fields("FileData").LoadFromFile Filename（或带括号(Filename)）。
• 同第一段的记录集逻辑错误：主记录集未移动，始终操作第一条记录，且Photo.Edit和Photo.Update的位置错误。

修正后的代码 #

Sub AttachPhotos()
    Dim Filename As String
    Dim i As Integer
    Dim db As DAO.Database
    Dim rsMain As DAO.Recordset
    Dim rsAttach As DAO.Recordset

    ' 打开数据库
    Set db = OpenDatabase("G:\My Drive\Takis\Graffiti.accdb")
    ' 打开主表记录集（假设Table1有3486条记录，与文件数量对应）
    Set rsMain = db.OpenRecordset("Table1", dbOpenDynaset)

    i = 1
    ' 遍历主表每条记录
    Do While Not rsMain.EOF And i <= 3486
        ' 生成文件名：用CStr避免空格，或用Format生成带前导零的文件名
        ' 如果不需要前导零，用CStr(i)；需要4位前导零用Format(i, "0000")
        Filename = "G:\My Drive\Takis\SS_photendix\" & CStr(i) & ".jpg"
        
        ' 检查文件是否存在
        If Dir(Filename) <> "" Then
            rsMain.Edit
            ' 打开当前记录的Attachment字段记录集
            Set rsAttach = rsMain.Fields("Photo").Value
            ' 添加新附件
            rsAttach.AddNew
            rsAttach.Fields("FileData").LoadFromFile Filename
            ' 可以设置附件的文件名（可选）
            rsAttach.Fields("FileName") = CStr(i) & ".jpg"
            rsAttach.Update
            ' 关闭附件记录集
            rsAttach.Close
            Set rsAttach = Nothing
            ' 更新主记录
            rsMain.Update
        End If
        
        ' 移动到下一条主记录
        rsMain.MoveNext
        i = i + 1
    Loop

    ' 清理对象
    rsMain.Close
    db.Close
    Set rsMain = Nothing
    Set db = Nothing
End Sub

修正要点 #

• 用CStr(i)替代Str(i)，避免数字前的空格；如果需要固定位数的文件名（如0001.jpg），改用Format(i, "0000")。
• 遍历主记录集的每条记录，确保每个文件对应一条主记录。
• 为每条主记录单独打开/关闭Attachment字段的记录集，避免对象引用错误。
• 添加文件存在检查，防止因文件缺失导致报错。

内容的提问来源于stack exchange，提问作者Costas Kitis