Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    14

    Unanswered: Migrating Access Database to SQL server

    Hello,

    I am new to this forum and as well with SQL. I have database on Acess 2007 and my manager wants me to move it to SQL server and still able to do run the report and etc like they do in Microsoft Access. I import the database using import function in the sql but now I am not sure what to do now. I try to search on you tube and try to add the relation ship between table but somehow in our customer table we have same customer but two different office and in our database they are listed as a seperate customer when i try to setup relationship between them I get the following error message.

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Customer' and the index name 'PK_Customer_1'. The duplicate key value is (ATEL LEASING).
    Could not create constraint. See previous errors.

    How can I fix this? or any other way to import day from access and run the report.

    Please help I am new to SQL and not sure what I am doing.

    Thanks,
    Arpit

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your Access data file is corrupt, it has two rows with the same primary key.

    First order of business, make a backup copy of your Access data file and take that copy offline. In other words burn a copy to a CD or copy it onto a flash drive, then put the copy in a safe in another room.

    Next, attempt to repair your Access data file. This will often fix any problems with the file structure that can prevent upsizing to SQL Server. If the repair is successful, try to upsize your Access data file to SQL Server again. If the repair fails, go get the original file and copy it back onto your computer then put the copy back in a safe place.

    Either way, let us know how things fare at this point!

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

  3. #3
    Join Date
    Mar 2012
    Posts
    14
    I don't this its corrupt. Beause its working fine in Access and generating reports also.

    Please take a look at this following.

    Customer Number Company Name Facility Alias
    1053 KINDER MORGAN IL KINMORG
    1054 KINDER MORGAN NY KINMORG

    Let me know what your thaughts on this.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Find the rows for the PK of "ATEL LEASING" and see what you get. By definition, there can only be one row for a PK and according to the error message that you posted there is more than one row for ATEL LEASING.

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

  5. #5
    Join Date
    Mar 2012
    Posts
    14
    Yes you are correct Pat but their customer number and their facility address is different. Is it not possible to have same column but different cusomer Number in SQL server.

    CustNO Cust Name Facility Alias
    1119 ATEL LEASING IA ATELLEA
    1153 ATEL LEASING IL ATELLEA

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If that is the case, then you need to change the PK definition in either MS Access or in the upsizing wizard. The CustNO appears to be the PK instead of the Customer Name. Whatever is used for the PK, it must be unique for every row... No duplicates are allowed for PKs.

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

  7. #7
    Join Date
    Mar 2012
    Posts
    14
    Thanks Pat. Its working now but now i have another issue with different table.

    - Unable to create relationship 'FK_Projects$_Sales Manager'.
    The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Projects$_Sales Manager". The conflict occurred in database "Project Database", table "dbo.Sales Manager", column 'SalesManager'.

    dbo.Salesmanager ------------------> dbo.Project under column name SalesMAanger
    Aloisio 1
    Chuck 2
    Doug 3
    Field 4
    Fred 5
    Harold 6
    Jeff 7
    Kelly 8
    Mark 9
    Tim 10
    NULL NULL

    Sorry for this I never touch sql before and now i am stuck with this.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm guessing on this, but if you delete the row wih NULL NULL this problem ought to go away. You can't have a primary key for a NULL column, and the Wizard appareently processed the Foreign Key and coughed up a bogus error message.

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

Posting Permissions

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