Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: Import customer records into to new db from old

    I have a requirement to import old customer & order records into a new db. I need help with the best method of doing this. I tried various methods but what I'm doing isn't working. Mainly I've tried paste/ append, but the relationship issue, henders the progress.

    The new database is ready to go, now I just have to get all the old customers/orders into my new database...Is this possible.??

    I can get the Customers from the old customer table (but they come with the plus sign next to each name ('related' to old Orders table ??). When I've tried to copy all the old Orders records, it just isn't working. I've even reversed the order, first copy the old orders, then the customers..

    OK, OK, please don't laugh, the newbie'ness' is showing again.
    Thanks for any help, or direction, or if someone has an example (that would be great).....

    p.s. there are approx. 2200 customers and orders....the relationships in each db are different. Also when importing (for paste/append), I've unchecked (in options) the relationship box

    imrosie

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Get in to your new database with the new tables. Import the old tables into it (if the table names are the same you'll see a 1 at the end of the name of the table imported.)


    The problem comes into play when you have relational tables based on the primary key or autonumber field. In your new table, make this field a number value (which you'll change to an autonumber field and establish the relationships later after the correct numbers have been imported to that table). You may need to remove all relationships on some of the existing tables.

    Create an append query where you're appending MyTableName1 into MyTableName and any other tables.

    Once you're done appending the records into the new tables, change the number field representing the autonumber field to an autonumber field. Then establish the relationships again.
    Last edited by pkstormy; 08-12-07 at 11:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    102
    Thank you Paul,,,
    I'll give this a try...I'd removed relationship before and tried 'copying' all the customer records into my new empty database....you guessed it, it didn't work. I tried an 'append' query, but didn't' have much success. Obviously I'm doing something wrong...

    I'll try your steps and see what comes up. I'll post back with results, thanks again Paul
    imrosie

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If this doesn't work, you can try having just a new blank customers table with no relationships and also have an autonumber field in that main customer table which you're going to append the old autonumber to. I may have been wrong on having a number field and changing it to an autonumber (I do this sometime with my SQL Server tables) but I believe you can just append autonumber fields to autonumber fields. This might be Access version specific though as I can't remember if it's possible or not in older Access versions.

    Once you get the data imported into the new customer table, then build your relational table linking to that autonumber field.

    So here would be the steps...
    1. Open Access db which will have the new customer table.
    2. Create the new customer table (assuming that you can't utilize the old customer table by just renaming it as the new one.)
    3. Import old customer table and other old relational tables into this Access mdb.
    4. Create and run an append query to append old customer table to new customer table, which has old autonumber appending to new autonumber field.
    5. Create relational table and then impliment relationships from new customer table to the that table.
    6. Create and run an append query on the old relational table to the new relational table making note of records which don't import due to no value in the new customer table to match against (you can create an unmatched query via the wizard to find any data in the relational table which doesn't have a matching value in the main table.)

    Remember, once you establish a relational table, you won't be able to break the relational rules of a number/linked field value in the relational table which doesn't match the main table. If you try to append a record which doesn't have a matching value in the main table, Access will complain on that record. Also, you can't have 2 autonumber fields in a table (Access or SQL Server).
    Last edited by pkstormy; 08-12-07 at 23:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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