Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import data from other .mdb (97 or 2000)

    I have a database that I have had to change some forms in. Since this is for a user in another city, she sends me her most recent copy to make changes to and then I need to get those changes into her production version.

    I'm hoping someone can help. Is there a way to have the new version import the data from EVERY table in her copy without writing huge amounts of code?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Import data from other .mdb (97 or 2000)

    It sounds as if your database is a single file, which can become a real problem in the situation you describe. The simple solution is to split the database into a front end (forms, reports, queries, etc.) and a back end (tables and relationships) and link the tables in the back end to the front end. Then all you need do is replace the front end without touching the back end. There are a couple of gotchas in this: you can't use Seek on attached tables and you need to make provision for relinking the tables in the updated front end. There is no really safe way to do what you're asking, and the only way to do it without code is with a whole series of queries to delete the data in a table and append the data from an attached table. Since you have to attach the data file anyhow for this, you're making extra work for yourself. Split it and save yourself headaches.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import data from other .mdb (97 or 2000)

    Amplifying on Charlotte's comments, I would definitely split the database - the few negatives she mentions are far outweighed by the benefits. If you want to know more about splitting a database, check out our Database Splitting Tutorial. This or most other approaches work fine until you have to add or delete fields or even tables - then it gets complicated, and you will need to develop some tools (probably queries) to do the data updates. BTW, if somebody suggests replication as the answer, I wouldn't. It will deal with some of the issues you face, but it gets complicated pretty fast. We have a client in Denver who has a person that works near Tucson, AZ who is using a replicated back-end, but it's a database that is very stable, with no design changes in the last two years. (If you want a brief into to replication, we also have a tutorial on it.)
    Wendell

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import data from other .mdb (97 or 2000)

    Normally I would split the database and have done so for other applicaitons in the past with great success.

    Unfortunately, this user frequently will copy the .mdb to a cd, take it home, work on it, and then copy it back again.

    I'm not sure how well this could be accomplished with a split database.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import data from other .mdb (97 or 2000)

    That shouldn't really cause a problem - in this case the user would copy only the back-end (once she had a copy of the front-end at home), and then move the back-end the other way. That's pretty much how we solved the problem with the user in Arizona - he actually sends and Excel workbook with additions to the Denver office, and then they send him a new back-end with the updates. The back-end database is replicated, but mostly because they also travel with a laptop and make updates which need to be synched back in. (Replication over dial-up facilities is painfully slow and often encounters errors, sometimes even corrupting the database.)
    Wendell

Posting Permissions

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