Append Query Issue!!

[MarkNie]

Hi There

Hope everyone is well...

I all of a sudden am having a problem with an append query I wrote, is has been working for the last 8months and all of sudden an error appears, all the data is in the same format and everything, I just don't understand. Any help or suggestions would be great.

What I have done is the following:

I created an import excel spreadsheet, where I copy and format the data which I want to import.

I then created an import table in access where a macro transfers the data too, from the spreadsheet via the TransferSpreadsheet Action. This is all still working fine.
In the import table I have made all the fields memos.

I then wrote an append query which copies the info from the Import table to the main table. When I click on the append query it gives me an error that there is a key violation and this has never happened before.

The data types in the tblTracking is different to the tbl types of the import tbl, but this has never caused a problem neither. When I open the query in design view and then click on the view button it works fine. But when I run it it gives me an error.

Here is the code for the query:

INSERT INTO tblTracking ( YearID, PortfolioCode,
[Magic Code], ClientName, ProductType, Heritage, FundedDate, BookletType, [ABBA Booklet], MarketEnvironmentCode, [Commentary/Stock StoryDeadline], PerfDate, RunDeadline, [CRG Analyst Deadline], CPMDeadline, Deadline, [CRG Deadline], [CR Analyst], [Responsible Team], [Client Manangement], Reviewer, CPM, [Relationship Manager], [Factsheet Produced], Notes, FactsetFilesRequired, BarraDataRequired, PerformanceRequired )

SELECT [Please select the quarter ie Q2 1999] AS Expr1, tblTrackingImport.[Portfolio code],
tblTrackingImport.[Magic Code],
tblTrackingImport.[Client Name],
tblTrackingImport.[Product Type], tblTrackingImport.Heritage,
tblTrackingImport.[Performance Inception Date], tblTrackingImport.[Booklet Notes],
tblTrackingImport.[Abba/Manual],
tblTrackingImport.[Sector Market Environment BM Code (Abba use only)],
tblTrackingImport.[Commentary/Stock Story sign off], tblTrackingImport.[Perf Sign Off Date],
tblTrackingImport.[Run/Check Date],
tblTrackingImport.[Analyst hit deadline], tblTrackingImport.[CPM Review Date],
tblTrackingImport.[Final Deadline (& details)], tblTrackingImport.[Distribution Date],
tblTrackingImport.[CR Analyst],
tblTrackingImport.[Responsible Team], tblTrackingImport.CAM,
tblTrackingImport.Reviewer,
tblTrackingImport.CPM,
tblTrackingImport.[Client Advisor],
tblTrackingImport.[Factsheet and Book Produced], tblTrackingImport.[Expo regional attn/Abba Load/Manual], tblTrackingImport.[Factset Files Required inc Regional Carve outs],
tblTrackingImport.[Barra Data Required],
tblTrackingImport.[Performance Required]
FROM tblTrackingImport;

Any help will be greatly appreciated.

Thanks
Kind Regards
Mark

 

[rjoubert]

Does Access give you a warning message stating that not all records could be appended due to key violations, or unique constraints?

What are the key fields (or unique index fields) in your table (tblTracking)? Then check for duplicate values in those fields that are setup to be unique.

 

[MarkNie]

Hi rjoubert

Well thanks for that would have never looked there, somehow the data type for the TrackingID was set to number and not autonumber. Have no Idea how that is happend.

Thanks

Regards
Mark