Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Answered: Newbee: Painted myself into corner

    I am upsizing a access database to SQLexp 2012. I installed the server and everything went well. I copied the tables everything still looking good. I needed to add 12 holidays the a table. same holidays every year so I copy the previous years records and paste then on the bottom of the table. this is when I realized I'm in trouble. I go to change the year of the first record. error: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows). so I try a few things and eventually decide this is not going to work. ok did you know there's no undo. so I highlight the 12 rows and hit delete. error: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows). So I highlight one row and click delete. Surprise: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows). I am new at this but does this error make any since at all. The changed I was making would have made the row unique. The change would have only effected one row. I can change them in access and reload the table but what good is that.

  2. Best Answer
    Posted by Pat Phelan

    "You've discovered one of the fundamental differences between a file oriented product like MS-Access and a truly relational product like SQL Server.

    The underlying problem (which is never explicitly cited by the error messages) is that there isn't a valid candidate key. That key would be a column or a group of columns which are guaranteed to uniquely identify exactly one row.

    The fix is to add an IDENTITY column, which is like an Access AUTONUM, then make that new IDENTITY column a PK (Primary Key) for the table. Once you do this, any view that includes the new column will be editable without complaint.

    This is a common problem when doing FE (Front End)/BE (Back End) development in Access, but one that you rarely if ever encounter when doing a simple MDB based application. While this is certainly a hurdle to cross and something you've probably never encountered with MS-Access, the benefits of MS-SQL are worth many times more than the few problems that you encounter.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You've discovered one of the fundamental differences between a file oriented product like MS-Access and a truly relational product like SQL Server.

    The underlying problem (which is never explicitly cited by the error messages) is that there isn't a valid candidate key. That key would be a column or a group of columns which are guaranteed to uniquely identify exactly one row.

    The fix is to add an IDENTITY column, which is like an Access AUTONUM, then make that new IDENTITY column a PK (Primary Key) for the table. Once you do this, any view that includes the new column will be editable without complaint.

    This is a common problem when doing FE (Front End)/BE (Back End) development in Access, but one that you rarely if ever encounter when doing a simple MDB based application. While this is certainly a hurdle to cross and something you've probably never encountered with MS-Access, the benefits of MS-SQL are worth many times more than the few problems that you encounter.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    Great answer, I wanted to add that I had imported the data from Access with SQL Server. I guess going that way somehow it dropped the Primary key. I later found a article about upsizing and by using the upsizing wizard in Access I was able to migrate the data and keys with no problem.

Posting Permissions

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