Question Unable to Append All Data - Excel Import

[sig331]

I am trying to append an Excel file to a table. The append actually works, however, I get an error message. It says: "Microsoft Access was unable to append all the data to the table." Then it says "The contents of fields in 0 records were deleted, and 0 records were lost due to key violations."

The data appears to have imported fine, but I am not sure what the message is trying to tell me. Where should I look? I've looked at required fields, indexed fields, etc and everything seems ok. I've tried to make sure the formatting in Excel was good too. Here's the import snippet just in case.

    Dim fdg As FileDialog
    Dim vrtSelectedItem As Variant
    Dim strSelectedFile As String
 
    Set fdg = Application.FileDialog(msoFileDialogFilePicker)
 
    With fdg
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems                     
                 strSelectedFile = vrtSelectedItem
            Next vrtSelectedItem
        Else    
        End If
    End With
 
    Set fd = Nothing
    DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="tbl_ItemStatusInfo", filename:="" & strSelectedFile & "", HasFieldNames:=True

 

[spikepl]

An experiment. Make a copy of your Excel file. Keep header and first row, delete the rest. See what Access then says. If it works OK that means there is something in the Excel data. Add half of the data, see if it's OK. If not then remove half of of it etc ...

 

[speakers_86]

A few things to check.

Make sure referential integrity is not being violated.
Don't try to import a field to an autonumber.
Make sure the data types match, ie, don't import currency into a date field.

How many records are in the Excel file? How many actually were imported?

 

[spikepl]

Errr I must've been sleeping. speakers_86 is right. If you have an indexed field in your table that does not allow dupes, and you do not import values into that field, then you'll get an error like that. Or if you import the same value into all the records etc . for that field. Or if such records already exist in that table.