INSERT INTO from Access to SQL Server Backend Failing (1 Viewer)

[tmyers]

Above is the error I am receiving when trying to insert new records based off an unmatched query. I believe this is due to having the Identity Specification set to "yes" on the table. This is the first time I have run into this and most of my searches have only returned how to fix this on the server side rather than the Access side.

For the record, here is the query I am trying to run. All I did was load a refreshed Excel sheet and use the wizard to create an unmatched query that I am now trying to append to the SQL Server table.

INSERT INTO tblCustomers ( [Account #], [Customer ID], [Customer Name], [Account Status], [Outside Sales #], [Inside Sales #], [SIC Code], Prefix, [Customer Type], JobID )
SELECT [Sheet1 Without Matching tblCustomers].[Account #], [Sheet1 Without Matching tblCustomers].[Customer ID], [Sheet1 Without Matching tblCustomers].[Customer Name], [Sheet1 Without Matching tblCustomers].[Account Status], [Sheet1 Without Matching tblCustomers].[Outside Sales #], [Sheet1 Without Matching tblCustomers].[Inside Sales #], [Sheet1 Without Matching tblCustomers].[SIC Code], [Sheet1 Without Matching tblCustomers].Prefix, [Sheet1 Without Matching tblCustomers].[Customer Type], [Sheet1 Without Matching tblCustomers].JobID
FROM [Sheet1 Without Matching tblCustomers];

 

[Minty]

If you have an Identity column (Access will see it as an Autonumber Field) - and Identity Insert is switched off (The default), then you shouldn't try and insert a value into the identity column.

If you have Identity Insert switched on (You have to do this implicitly) then the opposite applies.

 

[tmyers]

I tried to both insert the my autonumber field and leave it out and I still received the error but I am reviewing it all again to see if I made a mistake.

 

[tmyers]

I just tried it again and it worked. I have no idea what I did wrong as I rebuilt the query in the same manner I did originally. Maybe I tried to insert the autonumber field the first time by accident and it just spazzed out.

Either way, thank you for your help Minty!