September 5, 2008 08:55pm pdt

Member Login

Hall of Fame

Yearly

1capricorn1 2,372,276
2DatabaseMX 1,644,362
3LSMCons... 774,288
4boag2000 672,853
5peter57r 558,813
6GRayL 471,439
7harfang 445,253
8jimhorn 328,360
9cactus_d... 307,076
10matthews... 259,674
11TextReport 236,057
12hnasr 234,650
13angelIII 176,604
14ee_rlee 173,155
15mbizup 156,054
16puppydog... 155,767
17jimpen 152,284
18LPurvis 141,629
19jefftwilley 140,435
20JezWalters 138,435
21omgang 131,049
22Rick_Ric... 126,252
23Natchiket 123,463
24tbsgadi 123,218
25irudyk 122,851

	[x] Attachment Details

Ms Access Key Violations Append Query with AutoNumber in Destination Table

Zones: Microsoft Access Database, SQL Query Syntax

Tags: access, append, key, query, violations

Not sure how to solve this.... I've been searching solutions here and getting more confused...

I am trying to append a record to an attached table whose primary key is an autonumber. I get a key violation when I try to run an append query. My append query does not include a field that tries to write to the autonumber field. I thought the autonumber field would increment when I appended my record. On the other hand, I'm thinking a primary key can't be null.

Changing the autonumber primary key isn't an option.

What can I do? If it helps, I'm including the SQL view of my append query. "Hold" is my table name and it contains one record that I want to append to dbo_tblComingtoBookshelf. dbo_tblComingtoBookshelf has a primary autonumber field [ID]. This table is linked via ODBC from a SQL database.

         
             INSERT INTO dbo_tblComingtoBookshelf ( ConstitID, ParentID, Name, Name2, Address1, Address2, City, State, Zip, Country, Attention, Quantity, SVProductCode, OrderDate )
SELECT hold.ConstitID, hold.ParentID, hold.name, hold.name2, hold.Address1, hold.Address2, hold.city, hold.state, hold.zip, hold.country, hold.Attention, hold.Quantity, hold.SVProductCode, hold.OrderDate
FROM hold;

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.6

Question Stats

Zone: Microsoft

Question Asked By: suzasp

Solution Provided By: dqmq

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Open Discussion

Comment by suzasp

I'm awarding points but don't want to waste anyone's time. Now that I understand the real issue is with the SQL table and how Ms Access interprets that connection, I'm going to pursue a different avenue. I'm splitting points to all who replied with most to dqmq for amount of time.

Comment by suzasp

I'm following up on this issue with the real solution. Such a silly oversight....

It is possible to append from Access to SQL, even though the table has an "autonumber" field.
Trying to manually add a record in the SQL Server table solved the mystery. When I manually typed in the record to the sql table, 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! Then I carefully examined the properties of table instead of relying on the visual design view clue of an index key. Viola! Problem solved and I can append from my access table to SQL. The ID field automatically increments just as God intended.

Such a silly error - but I won't make it again!

Comment by aikimark

Yes. You can append data from an MSAccess table (0 or more rows) into an attached SQL Server table.

Example:
Insert Into AttachedSQLSVRtable (col, ..., col)
Select col,...,col
From ACCESStable
Where condition

20080723-EE-VQP-34 / EE_QW_2_20070628