1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL database query

Discussion in 'HTML, Graphics & Programming' started by deadeyedic30, 2 Feb 2006.

  1. deadeyedic30

    Wise Guy

    Joined: 28 Jan 2005

    Posts: 1,545

    Location: Too Far from a good woman

    Posting this for a friend,
    I'm in the process of converting a Microsoft Access database to work with a SQL database instead. The SQL database has already been created and I can create new and modify records through the Access front-end but when I go to delete/rename/copy I get an error 'it didn't add 1 records to the table due to key violations'. This, I'm assuming, is because I need to do cascade updates?? I don't know how to go about this."
     
  2. happytechie

    Soldato

    Joined: 18 Oct 2002

    Posts: 5,601

    Location: Surrey

    you need to cascade deletes not updates ;) , the copy will fail because access understands this as a copy of an existing record (and an existing key) the SQL Server database sees it as an insert operation and throws a duplicate key error.

    There are lots of ways round the problem but you'll need to give us more information about the design of your form and the design of your database before anyone can help.

    How are you managing keys (and FK constraints) in the SQL Server database? How did you manage keys and FK constraints in the access database.

    HT
     
  3. deadeyedic30

    Wise Guy

    Joined: 28 Jan 2005

    Posts: 1,545

    Location: Too Far from a good woman

    What exact information do you need? I didn't design either the Access database nor the SQL database. It's quite a small database with only 26 tables, 8 of these are the only ones used per transaction and the rest are never edited through the Access front-end. I was to just change the queries and forms to point to the linked SQL tables instead of the previous Access tables. I have got copy working now (the first two tables were being created in wrong order) so it's just the delete and rename that are giving me that error. Any help would be greatly appreciated as I have hit a dead end with it!!