Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    synch two linked tables different structure (97sp2)

    I have a products table in a local MS Access invoicing application. I also have a products table in a "mysql" database on a server. Both are linked tables in our BOM/production database (also MS Access). Both contain pretty much the same info though the structures are diferent. I must "respect" the structures of both becuase they reside in 3rd party database apps that would not function if I changed their structure. I need to "synch" the tables (not necesarily in real time) so that both tables have the latest products, descriptions and product specs. Is there a way to do this??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: synch two linked tables different structure (97sp2)

    Will both tables be updated independently of each other? In other words, do you need two-way synchronization? Or is one of the two tables the "master" from which the other must be updated?

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: synch two linked tables different structure (97sp2)

    Thank you for the response Hans.

    In an ideal world the synch would be 2 way, but I could live with a one way...

    Regards.

    Jason

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: synch two linked tables different structure (97sp2)

    More questions...

    1. Do the records contain a "last modified" date/time field? If so, that would help determining which records have to be synchronized.
    2. Is there an easy way to match records, i.e. some kind of unique ID that occurs in both tables, or do you have to find probable matches by comparing a number of fields?

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: synch two linked tables different structure (97sp2)

    Hans.

    Answers.

    1) the mysql table has a date added field and a last modified field
    The Access table has a stock-take date which seems to default to the date a product is added and probably changes when an inventory adjustment is made (this field could serve fairly well since I do not use the inventory functions for which this date field is intended)

    2) There is a products model number field in both tables that should be unique although it is not the primary key in the mysql table as it has a an autonum id field as primary.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: synch two linked tables different structure (97sp2)

    A phrase like "probably changes when an inventory adjustment is made" is dangerous - what if it isn't changed consistently?

    Start by making a backup copy of the Access database, and of the MySQL database (if possible, I have no experience with MySQL.)

    You could create a query based on both tables. Join them on the product model number field. Add the Last Modified field from the MySQL table to the query grid. In the Criteria line, enter >[NameOfOtherTable].[StockTakeDate] or <[NameOfOtherTable].[StockTakeDate] depending on which table you want to update. Of course, you must substitute the correct names for table and fields. Add the fields you want to update to the query grid. Then change the query to an update query. In the Update To line, enter [NameOfTable].[NameOfField] with the appropriate table and field names. Double check that you selected the correct fields. You can switch to datasheet view temporarily to see the values that are going to be changed (you see the OLD values, NOT the new ones), then switch back to design view. If you're satisfied that the query is going to do what you want, run it...

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: synch two linked tables different structure (97sp2)

    Thanks Hans
    ill give it whirl and report back

    RE: your thought...
    <hr>A phrase like "probably changes when an inventory adjustment is made" is dangerous - what if it isn't changed consistently?<hr>
    What I meant was that I do not really understand how the field is used because 1) I did not write the application and 2)I do not use the inventory functionality so I could not discern a consistent pattern from the values that are in that field other than that the date values seem to reflect the date when a given product was added to the db.

    I still think this date field will work for me the way you propose. Thanks a lot

    Truly,

    Jason

Posting Permissions

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