Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update one mdb from another (2000)

    How do I update database B from code running within database A?
    I need to update a table in the remote database with either new records or updated versions of existing records. So think I should run two queries, a delete query, that deletes any matching records that might already exist in database B, then an append query, appending data from database A to database B.

    Happy Christmas

    Ian

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

    Re: Update one mdb from another (2000)

    It depends on what the exact situation is. Are the databases linked or is database B completely independent? If the latter, what exactly are you trying to accomplish? If you just want to overwrite the other table, that is simple enough, just export the table from dabase A to database B, assuming no one is *in* database B. If you provide more details someone can help you work it out.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update one mdb from another (2000)

    I am creating an order processing and customer management system for salespeople to use on the road. When the sales staff get back to the office after a trip they will update the database in the office with changes they have made whilst away. The trouble is, I have been asked to make the same system work in several offices, each with different directory structures and drive mappings, and I don't know where the various databases that need updating will end up. I don't want the users to have to link the tables themselves, especially as the system is already in use and, as I add new features, they would have to relink every time I sent them a new version.

    The set up for each salesperson includes a local settings mdb, which does not get overwritten when I send them a new version of the front end. What I want to do is to hold the locations of the office databases in the local settings mdb, and use these when they update the office database.

    So, essentially, I want to update one table with data from another. The first table will be in the local database, the second will be in a different database, and the path to that database will come from a parameter table.

    Does this help? This is the first time I've had to do anything more mentally taxing than choose between wine or beer for some days now, and I may not be firing on all cylinders yet.

    Ian

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

    Re: Update one mdb from another (2000)

    This sort of thing usually turns out to be more complicated than it appears on the surface. What you are talking about is some form of replication - and you have issues with two people updating the same record and how you figure out which change should ultimately be applied. The same is true if someone deletes a record and someone else updates the record. You also have challenges with primary keys, as autonumber fields no longer are reliable. I'm not a big fan of the Access replication feature, but in this case, it may be the best choice. You might find our Replication Tutorial useful if you haven't explored the feature previously. In general, the advice is to only replicate the back-end, and to issue replacements of the front-end as you make design changes. That presumes you aren't making table structure changes of course - those can get really nasty. If you have further questions or comments on replication, please post back.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update one mdb from another (2000)

    I am also not a fan of Access replication. I can get it to work but am usually forced to return to backup when something goes wrong, which results in loss of data. For this reason I am trying to avoid using it here. It would also be tough to set up when I don't know where all the files are going to be.

    I am using autonumbers with random increments, and have told the users that if they want these features, then replication will be strickly by datestamp - the youngest records always overwrite any duplicates. This is not really a problem as there is strict 'ownership' of each customer by the sales staff.

    Can I use the database path from my parameter table to reset links from code (without using DAO)?
    Or, more hopefully, can I create a recordset based on data in the the current mdb, then, with that recordset in memory somewhere, create a new connection and use the recordset to overwrite/append to records in the remote database?

    Ian

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

    Re: Update one mdb from another (2000)

    Answering your last question first, the Access Web has this and several other pieces of code, and in general they do not require using DAO. And of course it is a common practice to store the locations of various linked tables in a configuration table, and then provide a relinking procedure that automatically checks the links on open, and if they don't exist, relink using the paths stored in that configuration table.

    I do think however, that you have a sizeable task ahead of you. It appears if I understand correctlyi that you have several offices who are presumably at best connected with a WAN, and that each office has several salesstaff who travel with a copy of the database on their laptop. One question is whether you have a central location where you wish to keep all of the data? Beyond that, how do you proposed to deal with deleted records - that would require some sort of query to find records that didn't exist in the laptop copy, but do in the office copy. And of course there would lots of those, so you would have to have some tracking mechanism to know which laptop copy was being checked, and info in each record that reflects that. I understand the situation that sales people have their unique customers and orders, but over time those things tend to change, and then you have to do maintenance to deal with salesstaff changes. All in all, it sounds like reinventing the replication wheel to me.

    As to your experiences with replication, we actually don't see many problems with replicated back-ends, but lots with front-ends where design changes are replicated out. The trick seems to be to make a special replica from the design master, and every time changes are replicated to the design master, to turn around the replicate to the backup replica. If you don't do that, recovering from corruption can be really ugly, but with that approach, all you do is promote the backup replica to the design master, and then create a new backup replica before you do anything else. You will also have an issue of wanting to automate at least part of the replication process, as depending on people to do it on a regular basis is problematic. I would suggest you take a look at The Access Developer's Handbook as they have pretty extensive coverage of how to programatically manage replication.

    All the best of the New Year - it sounds like you have some fun ahead.
    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
  •