Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Unanswered: Need help for DB validation

    I curently creating a database for further transfer to SQL SERVER and use it with VB.NET.

    I tryed to transfer it to SQL SERVER and get these error:

    Erreur 0xc020901c: Tâche de flux de données 1: Une erreur s'est produite avec colonne d'entrée «*idlocation*» (181) sur entrée «*Destination Input*» (160). État de colonne retourné*: «*La valeur des données a enfreint les contraintes d'intégrité de la colonne.*».
    (Assistant Importation et Exportation SQL Server)

    So I asume they have some errors in my database.

    Can you help me complete it?Im not sure about my link between each table, etc...
    It's a program for non profit organisation, im nor pretty good in english so I will call them preacher.

    Each preacher can be assigned to a territory. They have a time limit for complete it.
    Teritory are assigned by another preacher that have the right to do it.
    Territory contain a lot of house.
    We can have information about who live in this house or not.
    Each preacher have to give a report about how many hour they do each mouth. No more then one rapport can be give by mouth.

    Can you help me validate my database? Ask me if im not clear.

    for my db

    db.accdb - download now for free. File sharing. Software file sharing. Free file hosting. File upload. FileFactory.com

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i cannot find any way to download your db from the site posted - why are these people still in business?

    the error message is about referential integrity - since i cannot play with your db i can only guess that field 'Destination Input' is a foreign key pointing at some table holding primary key 'idlocation' and that some specific value in 'Destination Input' does not exist (is not a valid 'idlocation')

    example

    tableVilles
    idville, nomville
    7, Paris
    8, Lyon
    9, Grenoble

    tableMaisons
    idmaison, idville, nomrue
    1, 8, rue du centre
    2, 7, place du marche
    3, 6, grande rue <<<<<<<< referential integrity error: ville 6 n'existe pas en tablevilles

    do you have an 'idlocation' foreign key value in 'Destination Input' that does not exist?
    do any of your Access tables use the 'Lookup Field' type?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2011
    Posts
    6
    I think this link work:
    db.accdb - 4shared.com - online file sharing and storage - download
    Did you speach french? I see french word in your text.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the error message you posted is in french so i added a couple of french words in my reply. i read & speak french, but my written french is pathetic.

    your second download site comes up on my machine in (?) japanese - google tells me that 4shared has many other language versions but i can't find any way to switch the link you provide into anything i can read. sorry.

    anyway, you should be able to diagnose for yourself with something like

    SELECT foreignkey, thisfield, thatfield, otherfield -- etc
    FROM tablewithforeignkey
    WHERE foreignkey
    NOT IN
    (SELECT primarykey
    FROM tablewithprimarykey);

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Oct 2011
    Posts
    6
    this link will work.
    db.accdb

    Curently I want others to check for mistake I probably made. I solve some of the problems but Im not sure if my database is corectly done.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes - this latest link works.
    i will have a look at it tomorrow morning (i have no MS-Access on this machine) and get back to you with the results.

    what tool did you use for the transfer to SS ?
    ...did you try with Microsoft's SSMA ?
    SSMA is very much better than MS-Access upsizing 'wizard' (and is free!).

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Oct 2011
    Posts
    6
    I tryed to use upgrade option inside sql server management tools. But for unknow reason this option jsut disapear lol

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your db uses some feature that is unique to Access-14 (2010) that makes it incompatible with my Access-12 (2007). i cannot open it.

    sorry that i cannot help.

    please have a look at SSMA and see if it helps
    Download Details - Microsoft Download Center - SSMA for Access v4.2 <<< OLD REV, see next post.

    izy
    Last edited by izyrider; 11-03-11 at 06:53.
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i had 10 minutes and i couldn't resist it.

    SSMA throws the following franglais error:
    Code:
    Preparing table db.[tbladresse]...
    Migrating data for the table tbladresse from the database db...
    Error occurred during data migration.
    See the log for the detailed information. La colonne 'idlocation' n'autorise pas DBNull.Value.
     Errors: Error occurred during data migration.
    See the log for the detailed information. La colonne 'idlocation' n'autorise pas DBNull.Value.
    Data migration complete: 0 rows of 1 migrated.
    you have a single test record in tbladresse with idlocation NULL.
    replace NULL in this test record with a valid idlocation and SSMA should migrate your db without any issue.

    SSMA log of the migration attempt is attached. SSMA makes many changes to your db during the migration - you might want to read through the log carefully before you start.

    i hope you will agree that even though SSMAs error message is in franglais, it is much easier to understand than
    "Erreur 0xc020901c: Tâche de flux de données 1: Une erreur s'est produite avec colonne d'entrée «*idlocation*» (181) sur entrée «*Destination Input*» (160). État de colonne retourné*: «*La valeur des données a enfreint les contraintes d'intégrité de la colonne.*».
    (Assistant Importation et Exportation SQL Server)
    " - if nothing else it specifies which table is in trouble.

    config for this attempt:
    Vista laptop
    SSMA 5.1.1105
    SS 9.00.5000.00 (2005-Express)
    Access2010-runtime (so that SSMA could read your 2010 db)

    the link i posted previously was to an earlier version (4.2) of SSMA that probably wont work with Access2010
    try here for SSMA 5.1
    http://www.microsoft.com/download/en...ng=en&id=26709

    izy
    Attached Files Attached Files
    Last edited by izyrider; 11-03-11 at 06:52.
    currently using SS 2008R2

  10. #10
    Join Date
    Oct 2011
    Posts
    6
    my main idea curently is trying to validate my database. I manually transfered my database to SQL server. Let me know if you see anything not corect.

Tags for this Thread

Posting Permissions

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