MS Access中两张表UNION后Attachment列出现#error问题求助

问题原因 #

Access的UNION查询不支持保留**Attachment（附件）**字段的完整功能。附件字段是Access的特殊复合数据类型，存储了文件的关联信息，UNION操作会破坏这种关联，导致查询结果中附件字段无法正常渲染，显示#error。即使两张表的附件字段类型一致、单独查询正常，UNION后也会出现该问题。

解决方案 #

方案1：显式排除附件字段（无需显示附件时） #

修改VBA中的SQL语句，避免使用SELECT *，而是显式列出需要的字段，排除Attachment字段。示例：

' 替换原SELECT *的逻辑
If tableCounter = 0 Then
    fullQuery = "SELECT ID, Name, [OtherFields] FROM " & tableName ' 替换为实际需要的字段，移除附件列
    tableCounter = tableCounter + 1
Else
    fullQuery = fullQuery & " UNION SELECT ID, Name, [OtherFields] FROM " & tableName
    tableCounter = tableCounter + 1
End If

方案2：创建临时表合并数据（需保留附件功能时） #

如果必须保留附件的可点击功能，不要使用UNION查询，而是通过DAO记录集将多个表的数据逐个导入临时表，再基于临时表创建查询。修改后的VBA代码示例：

Public Sub CreateHoldsTableWithAttachments()
    Dim db As DAO.Database
    Set db = CurrentDb()
    
    ' 清理已存在的临时表和查询
    On Error Resume Next
    db.TableDefs.Delete "tmp_holds"
    db.QueryDefs.Delete "kw_holds"
    On Error GoTo 0
    
    ' 复制源表结构创建临时表
    Dim sourceTdf As DAO.TableDef
    Set sourceTdf = db.TableDefs("tb_hold1")
    Dim tmpTdf As DAO.TableDef
    Set tmpTdf = db.CreateTableDef("tmp_holds")
    For Each fld In sourceTdf.Fields
        tmpTdf.Fields.Append tmpTdf.CreateField(fld.Name, fld.Type, fld.Size)
    Next fld
    db.TableDefs.Append tmpTdf
    
    Dim tableNumber As Integer
    tableNumber = 1
    
    Do While True
        Dim tableName As String
        tableName = "tb_hold" & tableNumber
        
        ' 检查表是否存在
        Dim tableExists As Boolean
        tableExists = False
        For Each tdf In db.TableDefs
            If tdf.Name = tableName Then
                tableExists = True
                Exit For
            End If
        Next tdf
        
        If Not tableExists Then Exit Do
        
        ' 打开源表和临时表的记录集
        Dim rsSource As DAO.Recordset2
        Dim rsTmp As DAO.Recordset2
        Set rsSource = db.OpenRecordset(tableName, dbOpenDynaset)
        Set rsTmp = db.OpenRecordset("tmp_holds", dbOpenDynaset)
        
        ' 逐行复制数据（含附件）
        Do While Not rsSource.EOF
            rsTmp.AddNew
            For Each fld In rsSource.Fields
                If fld.Type = dbAttachment Then
                    ' 复制附件内容
                    Dim rsAttach As DAO.Recordset2
                    Set rsAttach = rsSource.Fields(fld.Name).Value
                    If Not rsAttach.EOF Then
                        rsAttach.MoveFirst
                        Dim rsTmpAttach As DAO.Recordset2
                        Set rsTmpAttach = rsTmp.Fields(fld.Name).Value
                        rsTmpAttach.AddNew
                        rsTmpAttach.Fields("FileData").Value = rsAttach.Fields("FileData").Value
                        rsTmpAttach.Fields("FileName").Value = rsAttach.Fields("FileName").Value
                        rsTmpAttach.Fields("FileExtension").Value = rsAttach.Fields("FileExtension").Value
                        rsTmpAttach.Update
                        rsTmpAttach.Close
                    End If
                    rsAttach.Close
                Else
                    ' 复制普通字段
                    rsTmp.Fields(fld.Name).Value = rsSource.Fields(fld.Name).Value
                End If
            Next fld
            rsTmp.Update
            rsSource.MoveNext
        Loop
        
        rsSource.Close
        rsTmp.Close
        tableNumber = tableNumber + 1
    Loop
    
    ' 创建基于临时表的查询
    Dim qdf As DAO.QueryDef
    Set qdf = db.CreateQueryDef("kw_holds", "SELECT * FROM tmp_holds")
    
    ' 释放对象
    Set rsSource = Nothing
    Set rsTmp = Nothing
    Set tmpTdf = Nothing
    Set sourceTdf = Nothing
    Set db = Nothing
End Sub

方案3：提取附件文件名（仅需查看文件名时） #

如果不需要打开附件，仅需查看文件名，可以在UNION查询中提取附件的FileName属性，但无法保留点击打开功能：

SELECT ID, Name, (SELECT FileName FROM tb_hold1.Attachment WHERE ParentID=tb_hold1.ID) AS AttachFileName FROM tb_hold1
UNION
SELECT ID, Name, (SELECT FileName FROM tb_hold2.Attachment WHERE ParentID=tb_hold2.ID) AS AttachFileName FROM tb_hold2

关键说明 #

• Access的UNION/UNION ALL查询仅支持文本、数字、日期等基本数据类型，对Attachment、OLE对象等复合类型的支持存在本质限制。
• 若必须保留附件的可交互功能，方案2的临时表导入是最可靠的方式，它直接复制了附件字段的完整关联数据。

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