Help! Append query wont create Autonumbers!!!!

[sesanchez]

Hey all.

Yup... I know it sounds weird but I have an append query that will not create the autonumbers in the APPENDed table.

Heres the deal. I have a temp table that must feed data into the database. The query I have is

INSERT INTO [tbl_users] ( Customer, [First], Middle, [Last], UserID, [Password], Email, [Date Added], [Added By], [Deleted By], Active, Comments, [Date Deleted] )
SELECT [tbl_TEMPusersImport].Customer, [tbl_TEMPusersImport].First, [tbl_TEMPusersImport].Middle, [tbl_TEMPusersImport].Last, [tbl_TEMPusersImport].UserID, [tbl_TEMPusersImport].Password, [tbl_TEMPusersImport].Email, [tbl_TEMPusersImport].[Date Added], [tbl_TEMPusersImport].[Added By], [tbl_TEMPusersImport].[Deleted By], [tbl_TEMPusersImport].Active, [tbl_TEMPusersImport].Comments, [tbl_TEMPusersImport].[Date Deleted], [tbl_TEMPusersImport].[Email Sent]
FROM [tbl_TEMPusersImport];

This query shows me an error of zero rows inserted because of key violations. Now... for my own sanity, I created a new table copying the structure from the tbl_users table... AND THE QUERY WORKS!

INSERT INTO [Copy Of tbl_users] ( Customer, [First], Middle, [Last], UserID, [Password], Email, [Date Added], [Added By], [Deleted By], [Web Recon], [Web Order], iReports, iDeposits, HEAT, iATM, eAdvice, [Deposit Director], Active, Comments, [Date Deleted], [Email Sent] )
SELECT [Copy Of tbl_TEMPusersImport].Customer, [Copy Of tbl_TEMPusersImport].First, [Copy Of tbl_TEMPusersImport].Middle, [Copy Of tbl_TEMPusersImport].Last, [Copy Of tbl_TEMPusersImport].UserID, [Copy Of tbl_TEMPusersImport].Password, [Copy Of tbl_TEMPusersImport].Email, [Copy Of tbl_TEMPusersImport].[Date Added], [Copy Of tbl_TEMPusersImport].[Added By], [Copy Of tbl_TEMPusersImport].[Deleted By], [Copy Of tbl_TEMPusersImport].[Web Recon], [Copy Of tbl_TEMPusersImport].[Web Order], [Copy Of tbl_TEMPusersImport].iReports, [Copy Of tbl_TEMPusersImport].iDeposits, [Copy Of tbl_TEMPusersImport].HEAT, [Copy Of tbl_TEMPusersImport].iATM, [Copy Of tbl_TEMPusersImport].eAdvice, [Copy Of tbl_TEMPusersImport].[Deposit Director], [Copy Of tbl_TEMPusersImport].Active, [Copy Of tbl_TEMPusersImport].Comments, [Copy Of tbl_TEMPusersImport].[Date Deleted], [Copy Of tbl_TEMPusersImport].[Email Sent]
FROM [Copy Of tbl_TEMPusersImport];

The only difference is that the original table is a linked table... Would that make the difference? and... what would be a workaround if this is the issue?

Thanks

Sergio

 

[Trevor G]

In the table you are appending to do you have an ID field set to AutoNumber or Just Number?

 

[sesanchez]

Thanks,

It is an Autonumber

 

[xenou]

This query shows me an error of zero rows inserted because of key violations. Now... for my own sanity, I created a new table copying the structure from the tbl_users table... AND THE QUERY WORKS!

What indexes are set on the table (primary key and other unique indexes)? It could be that the key violation and the creation of autonumbers are two different things here. If you only copied the structure and not the data that would account for the data working locally -- the conflicting keys may not come into play when you insert into a table without any data already in it.