Unanswered: Append Query to Attached SQL table with an Autonuber Field is not Working
Hello - I'd really appreciate some help with this....
I have a table that is attached from our web server SQL database into an MsAccess 2003 database.
When the table was created in SQL, the ID field was created as an int then specified as an identity and given an increment amount.
As an attached table in MsAccess, the design view looks like this:
ID - Autonumber - Primary Key
Name - text
Address - text
ProjectNo - text
OrderDate - date
Here's the sql view of the Ms Access query:
INSERT INTO dbo_tblOrders ( Name, Address, ProjectNo, OrderDate )
SELECT tblOrders.Name, tblOrders.Address, tblOrders.ProjectNo, tblOrders.OrderDate
When I try to run the query, the error I get is just a generic Access error without details of what is wrong. "Microsoft Office cannot append all the records... set (0) fields to null .... didn't add (1) record(s) to the table due to key violations... etc. That's as much as I know of the error.
Since the only key I have is on ID, then my guess is that it doesn't like that I'm not appending a value for ID. But I want it to increment the value itself.
I did try appending a value in ID - but that gave me the same error.
Is there a way to append from Ms Access to SQL where the table has an autonumber field or not?
Is it possible that this used to work in previous MsAccess versions but has changed? I know I've done this before... so it is driving me crazy.
From what I understand, SQL doesn't have an autonumber data type - but uses identity / autoincrement to accomplish the same thing. When I attach the table from SQL in the Access database, Access automatically defines the type as an Autonumber. The datatype hasn't changed.
The suggested task of adding a record in SQL Server solved the mystery. When I typed in the record, I discovered that a field I didn't realize was required was not being included in my append query. I doesn't appear as a "key" field in the design of the Access table. But when I typed in the exact record I was trying to append and skipped a field - that's when I got the valuable error message that told me the problem! Such a silly error - but I won't make it again! Thank you for your help.