Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    15

    Unanswered: Import and Export feature

    Hi All,

    Please can you advise.

    If I have a Access database, that contains say 20,000 records. I want to migrate that data to another DB.

    Will it be best if I use the import/export feature on Access or build a Vb app to do it? what are the pros and cons of using import/export?

    If I use a Vb app, the data will have to be dumped somewhere, lets say......Excel or CSV file. Again what are the pros and cons of this?.

    I really need this info, so please help me out. If my question is not clear please ask.

    Best regards,

    Daniel

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    To a certain extent it depends on what your 'receiving' database can accept, and if there is any other reformatting (adding other columns, drawing columns form other tables)

    In general unless you have a really wierd / specialist requirement then the Access export fucntion will do the job. Writing your own VBA functions isn't usually required and probably a waste of your valuable time.

    Out of curiosity what are you porting the data to? There may be a simple means of exporting data to the target system.

  3. #3
    Join Date
    Oct 2005
    Posts
    15
    Thanks for getting back.

    It's actually from one access to another access DB. Yes, additional formatting is required and the data being exported are from relational table. Will this not take time if I was to import / export? (20,000) record
    I've been told that CSV is the best option to export data to. However, I was thinking of using Excel since I have used this before.

    Do you know if it's easier import data on CSV file?

    Thanks
    Last edited by Daniel_Cooper; 10-17-05 at 09:52.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Is this a one shot deal? Recurring? What you might also want to consider is pulling the data from the one DB and posting it to the other via a VBA transfer routine ... Then you have control over any additions/subtractions/transformations ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Oct 2005
    Posts
    15
    It's a one shot deal.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    excel, csv, dBase2, vapour-base, text, blah blah
    ...avoid them like the plague for A -> A transfers!



    simplest is to link or import the old tables from (? a copy of) the new db

    then in the (? copy of) new db mess with queries until you get the data beaten into shape for the new db tables

    then append


    going via some other format will cause you unnecessary grief with datatypes etc

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I concur with izy.

    1) Duplicate the file, rename it and you have a new copy with all the original data.

    2) Use the clipboard to copy/paste the tables from old to new (you can drag and drop as well from one window to the other).

    3) In the new DB, link to the tables in the old DB and then you can use queries to move and alter the data at the same time.

    Never, ever make more work for your self and always take the path of least data conversions (in this case, the path is ZERO data ocnversions).

    tc

  8. #8
    Join Date
    Oct 2005
    Posts
    15
    All your feedback has been useful :-)

    Sorry for all the silly questions! I'm doing some research on data migration, before I can find the best solutions I need to know all the 'common' problems that often occur and research all possible ways of doing this.

    I stated data going to Excel because it's the only one I have briefly worked with. From here, uploading to new DB probably via SQL stored procedure. But sounds like Excel will have problems dealing with more complex data.

    Isnt it better to automate the process for better validation etc? rather than going through the whole import / export ?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lets make some assumptions
    1) you are moving data from one Access MDB to antoher MDB
    2) you data in its current MDB is reasonably 'tight', ie data integrity is proved / asserted by effective RI links

    Then you do not need to to export, as Izyrider suggests use File | Get External Data | Import to move the old tabels to the new

    you will then need to re-establish any RI links.

    If however your data is supect, or you have to merge data from separate tables then you may need to do something different. You could LINK the old tables, write an insert query doing the reformatting.

    However the fundamental is that an Access to Access transfer doesn't require any form of external text file (csv or similar). You don't need to do any VBA processing, which will be incredably slow, and possibly take a while to develop and prove.

    If however you 'want' to writs vba function then by all means do so.
    Last edited by healdem; 10-18-05 at 03:21.

Posting Permissions

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