MS Access 2007 Append query trouble

Question

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?

Thanks

It seems like that your table has an autonumber ID field and you are trying to append to that. Otherwise, how you work with the data depends very much on what you want to end up with. — Fionnuala
Does your table have any foreign key relationships defined with another table? — JohnFx
An autonumber field determines the default value for that field. However, without a unique constraint you can insert a duplicate value into the autonumber field. IOW, executing a DML INSERT which duplicates an existing autonum will not trigger a key violation error. — HansUp
It will trigger the error if the Autonumber field is the PK, since the PK has a unique constraint on it. — David-W-Fenton

HansUp HansUp · Accepted Answer · 2011-09-29T19:16:36

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

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 Append query trouble

1 Answers