MS access can't append all the records in the append query

Question

I know this question has been asked several times, but none of the solutions solve my problem.

I am trying to insert a row into a table named 'Audit Trail' in MS Access 2007.

The primary key is column 'Activity No'

The following is my query:

INSERT INTO [Audit Trail]
([Activity No], [Project Id], [Activity Date],[Activity Location],[Activity Description],[Data Reference (File No)],[Participant Id],[Person Reporting],[Comments]) 
VALUES
('6','SFT 2013','1/1/2014','AAA','AAA','AAA','AAA','AAA','AAA')

The Activity No column's datatype is Number (initially it was autonumber but I changed it later), and activity date is of datetime, the rest are Text.

It throws me an error

MS access can't append all the records in the append query

Microsoft Access can't append all the records in the append query. Microsoft Access set 0 fields to Null due to type conversion failuer, and it didn't add 1 >records to the table due to key violation, 0 records due to lock violation, and 0 records >due to validation rule violation. Do you want to run the query anyway?

This table does not have any foreign keys either. It had one earlier but I deleted the relationship.

I can however enter the same values through the UI of the table.

If this is an audit table where you are archiving the old copies of records when an update is made and you want to have more than one audit record for any given [Activity No] then [Activity No] cannot be the Primary Key. (The Primary Key for a table cannot contain duplicates.) — Gord Thompson
Hi, this table contains just a few records as yet, all entered through the table UI . Not trying to insert records with duplicate values for the primary key. — learn_code
Well then like @HansUp says, you should check for some other unique index that could be preventing the INSERT from succeeding. — Gord Thompson

HansUp HansUp · Accepted Answer · 2013-05-25T14:44:03

You said "Activity No column's datatype is Number ... and activity date is of datetime".

So discard the quotes surrounding the number you insert into [Activity No], and use the # date delimiters around the value you insert into [Activity Date]. I also prefer to use yyyy-mm-dd format for literal date values.

INSERT INTO [Audit Trail]
    (
        [Activity No],
        [Project Id],
        [Activity Date],
        [Activity Location],
        [Activity >Description],
        [Data Reference (File No)],
        [Participant Id],
        [Person Reporting],
        [Comments]
    )
VALUES
    (
        6,
        'SFT 2013',
        #2014-01-01#,
        'AAA',
        'AAA',
        'AAA',
        'AAA',
        'AAA',
        'AAA'
    )

MS access can't append all the records in the append query

1 Answers