MS Access 2007追加查询问题 [英] MS Access 2007 Append query trouble

问题描述

我有一个追加查询，试图将某些记录追加到我的一个表中.但是，我收到一条错误消息:由于按键冲突，未添加1200条记录." 1200是我要附加的记录总数.我不明白为什么会收到此错误，因为目标表中的所有列都允许重复(即使此追加查询不重复任何信息)，并且如果我复制表的结构并将记录追加到那，一切正常.

I have an append query that is trying to append some records to one of my tables. However, I am getting an error that says "didn’t add 1200 records due to key violations." 1200 is the total number of records I am trying to append. I don’t understand why I am getting this error because all of my columns in the destination table allow duplicates (even though this append query doesn’t duplicate any information), and if I copy the structure of the table and append the records to that, everything works.

问题似乎是我将数据追加到已经有现有数据的表中.有人可以为我提供一些建议以解决此问题吗?

The problem seems to be that I am appending data to a table which already has existing data. Can someone please offer some suggestions for how I can work around this?

谢谢

推荐答案

验证您没有忽略表上的任何唯一索引.将此过程保存在标准模块中，然后从立即窗口"中使用目标表的名称进行调用.

Verify you haven't overlooked any unique indexes on your table. Save this procedure in a standard module and call it from the Immediate Window with the name of your destination table.

Public Sub InspectIndexes(ByVal pTable As String)
    Dim db As DAO.Database
    Dim i As Long
    Dim j As Long
    Dim strFields As String

    Set db = CurrentDb
    With db.TableDefs(pTable)
        Debug.Print "Indexes.Count = "; .Indexes.Count
        For i = 0 To (.Indexes.Count - 1)
        With .Indexes(i)
            Debug.Print i + 1 & ": Index Name = "; .name
            If .Primary Then
                Debug.Print vbTab & "Primary Key (Unique)"
            Else
                Debug.Print vbTab & "Unique: "; .Unique
            End If
            Debug.Print vbTab & "Fields.Count = "; .Fields.Count
            strFields = vbNullString
            For j = 0 To (.Fields.Count - 1)
                strFields = strFields & "; " & .Fields(j).name
            Next j
            strFields = Mid(strFields, 3)
            Debug.Print vbTab & "Fields: "; strFields
        End With
        Next i
    End With

    Set db = Nothing
End Sub

这里是示例输出，其中tblFoo具有3个索引:id上的主键(按定义唯一)； num_field1和num_field2上的唯一索引；以及parent_id上的非唯一索引.

Here is sample output where tblFoo has 3 indexes: primary key (unique by definition) on id; a unique index on num_field1 and num_field2; and a non-unique index on parent_id.

InspectIndexes "tblfoo"
Indexes.Count =  3 
1: Index Name = both_num_fields
    Unique: True
    Fields.Count =  2 
    Fields: num_field1; num_field2
2: Index Name = parent_id
    Unique: False
    Fields.Count =  1 
    Fields: parent_id
3: Index Name = pkey
    Primary Key (Unique)
    Fields.Count =  1 
    Fields: id

这篇关于MS Access 2007追加查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！