UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Primary Key Error, Access 2010    
 
   
swarm6
post Nov 4 2019, 07:35 PM
Post#1



Posts: 91
Joined: 9-May 12



Hi All -

I'm trying to run an Append query, but it is bumping up against existing primary key. Here's my SQL:

INSERT INTO tblmealbillac ( Site, OrderDate, DateStamp )
SELECT DISTINCT tblMealBillRate.Site, CDate([Forms]![frmMealBill]![txtMealDate]) AS Expr1, Now() AS TmeStmp
FROM tblMealBillRate
WHERE (((tblMealBillRate.StartDate)<=[Forms]![frmMealBill]![txtMealDate]) AND ((tblMealBillRate.EndDate)>=[Forms]![frmMealBill]![txtMealDate] Or (tblMealBillRate.EndDate) Is Null));


As you can see I am not including the Primary Key field.

Go to the top of the page
 
MadPiet
post Nov 4 2019, 07:39 PM
Post#2



Posts: 3,367
Joined: 27-February 09



Not sure what's going on without seeing the destination table's definition (oh right, can't really do that with Access). =(

It sounds like you have a unique key or similar defined on the destination table still. Maybe make a copy of the destination table and test removing and then re-adding constraints and testing the inserts.
Go to the top of the page
 
swarm6
post Nov 4 2019, 08:32 PM
Post#3



Posts: 91
Joined: 9-May 12



Yes, the destination table has a unique and non duplicated primary key.

But I have not assigned a number to the appending records.
Go to the top of the page
 
projecttoday
post Nov 4 2019, 08:44 PM
Post#4


UtterAccess VIP
Posts: 11,292
Joined: 10-February 04
From: South Charleston, WV


Does this work by itself?

SELECT DISTINCT tblMealBillRate.Site, CDate([Forms]![frmMealBill]![txtMealDate]) AS Expr1, Now() AS TmeStmp
FROM tblMealBillRate
WHERE (((tblMealBillRate.StartDate)<=[Forms]![frmMealBill]![txtMealDate]) AND ((tblMealBillRate.EndDate)>=[Forms]![frmMealBill]![txtMealDate] Or (tblMealBillRate.EndDate) Is Null));

What happens if you remove DISTINCT (from the full insert query) and run it?

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Nov 4 2019, 08:54 PM
Post#5



Posts: 3,367
Joined: 27-February 09



Can you post a sample of these two tables?

If you're getting a key violation, it means one of a few things:
1. there are fields in the destination table that are defined as NOT NULL and you're trying to insert nulls. (like fields are required)
2. you're inserting duplicated into a column that is set to unique.

It's just hard to tell what's wrong from here without seeing any data or the table definitions.
Go to the top of the page
 
swarm6
post Nov 4 2019, 08:58 PM
Post#6



Posts: 91
Joined: 9-May 12



I tried removing DISTINCT, and had the same issue.

The query ran fine until it hit numbers in the table I'm appending that matched what the query wanted to add. The only thing is, I have not dictated what the new ID number should be.

Go to the top of the page
 
swarm6
post Nov 4 2019, 09:00 PM
Post#7



Posts: 91
Joined: 9-May 12



The issue is that the table I'm inserting into has an autonumber primary key.

The query is trying to add a number that is already used in the primary key. I thought it would just add a number for the ID that is higher than the numbers already there.
Go to the top of the page
 
MadPiet
post Nov 4 2019, 10:03 PM
Post#8



Posts: 3,367
Joined: 27-February 09



Don't think that matters. I created a table like this...

CODE
CREATE TABLE Table1 (SomeName Text(50) not null , ID INT autonumber Primary key);


and then slapped together some code to insert records, and it works fine:

CODE
Public Sub AddPerson(ByVal ID As Long, ByVal Fname As String)
    Dim strSQL As String
    
    strSQL = "INSERT INTO Table1 (SomeName, ID) VALUES ('" & Fname & "'," & ID & ");"
    Debug.Print strSQL
    CurrentDb.Execute strSQL
End Sub


So post some code. I have no idea why yours isn't working.
Go to the top of the page
 
cheekybuddha
post Nov 5 2019, 02:50 AM
Post#9


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


Hi swarm6,

Please download the db in this thread.

Use the db to select your database, and when the listbox populates with your tables choose tblmealbillac and tblMealBillRate and extract the definitions.

Post the output here

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Nov 5 2019, 03:43 AM
Post#10


UtterAccess VIP
Posts: 11,292
Joined: 10-February 04
From: South Charleston, WV


You say adding a number that is already in the primary key gives a problem. But you're adding it to a different column, right? Maybe that column is set as unique. Check the index.

--------------------
Robert Crouser
Go to the top of the page
 
swarm6
post Nov 5 2019, 09:11 AM
Post#11



Posts: 91
Joined: 9-May 12



No, actually I'm allowing the existing table to add its own ID because it is an autonumber.
Go to the top of the page
 
swarm6
post Nov 5 2019, 09:38 AM
Post#12



Posts: 91
Joined: 9-May 12



I pared my db down to just the 2 tables and have uploaded it here.

I couldn't get the extraction to work easily and didn't want to spend too much time working on it. Thank you for the idea, though.

Can you see my tables?
Attached File(s)
Attached File  DDI_SampleDB.zip ( 39.37K )Number of downloads: 4
 
Go to the top of the page
 
swarm6
post Nov 5 2019, 09:42 AM
Post#13



Posts: 91
Joined: 9-May 12



Thank you for the effort. Yes, mine worked fine until it reached the same numbers in the ID that it wanted to use (existing data).

I have not specified any ID numbers nor did I Insert into that ID field.

If all else fails, I'll have to renumber the existing data (luckily I can) starting at 1 and hopefully the autonumber will pick up from there.
Go to the top of the page
 
cheekybuddha
post Nov 5 2019, 10:00 AM
Post#14


UtterAccess Moderator
Posts: 11,909
Joined: 6-December 03
From: Telegraph Hill


INSERT INTO tblmealbillac ( Site, OrderDate, DateStamp )

tblMealBillAc doesn't contain such a field

--------------------


Regards,

David Marten
Go to the top of the page
 
swarm6
post Nov 5 2019, 10:11 AM
Post#15



Posts: 91
Joined: 9-May 12



I think my table had become corrupt. It's working now.

Thank you to everyone for helping me!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 08:37 AM