Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Question Unanswered: "Key Violantion error"? What key is that then?

    I try to run this SQL to populate a tempory table and I get told can't do that KEY VIOLATIONS the tablwe temp has no primary key so whats the issue?

    INSERT INTO temp ( ID, Name, NoCase, NoUnit, ClientID )
    SELECT Goods.ID, Goods.Name, 0 AS NoCase, 0 AS NoUnit, Customer.ID
    FROM Goods, Customer;

    The target table has no keys and the datatypes are all correct.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Matt_T_hat
    I try to run this SQL to populate a tempory table and I get told can't do that KEY VIOLATIONS the tablwe temp has no primary key so whats the issue?

    INSERT INTO temp ( ID, Name, NoCase, NoUnit, ClientID )
    SELECT Goods.ID, Goods.Name, 0 AS NoCase, 0 AS NoUnit, Customer.ID
    FROM Goods, Customer;

    The target table has no keys and the datatypes are all correct.
    Check your property settings for "unique" settings ... A field might not be a primary key but it can still be set to No Duplicates ... That would do it.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Also another thing to look at is the ID in temp an AutoNumber field? If yes then replace it with a Number field and that should remove the problem.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  4. #4
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    Indexed, duplicates OK on all.


    no key's set, no autonumbers on target table!
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    use SELECT INTO instead?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    use SELECT INTO instead?
    GOOD spot there Ted. Excellent!!!!
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Question I've not seen that SQL command before (is it access-able[!])

    Quote Originally Posted by Teddy
    use SELECT INTO instead?
    Code:
    INSERT INTO temp ( ID, Name, NoCase, NoUnit, ClientID )
    SELECT [Goods].[ID], [Goods].[Name], 0 AS NoCase, 0 AS NoUnit, [Customer].[ID]
    FROM Goods, Customer;
    That's my SQL at the mo' what is SELECT INTO and how does it differ to INSERT INTO?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  8. #8
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    SELECT INTO creates a new table and then inserts the select statement into that new table.

    INSERT INTO inserts into an existing table the select statement.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •