• synch two linked tables different structure (97sp2)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » synch two linked tables different structure (97sp2)

    Author
    Topic
    WSjpgus
    AskWoody Lounger
    March 3, 2004 at 12:19 pm #401709

    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??

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      March 3, 2004 at 12:28 pm #793454

      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?

      Reply | Quote
      • WSjpgus
        AskWoody Lounger
        March 3, 2004 at 4:51 pm #793619

        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

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          March 3, 2004 at 6:47 pm #793684

          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?

          Reply | Quote
          • WSjpgus
            AskWoody Lounger
            March 4, 2004 at 1:52 pm #794203

            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.

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              March 4, 2004 at 2:24 pm #794217

              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…

              Reply | Quote
            • WSjpgus
              AskWoody Lounger
              March 4, 2004 at 4:34 pm #794290

              Thanks Hans
              ill give it whirl and report back

              RE: your thought…
              [indent]

              A phrase like “probably changes when an inventory adjustment is made” is dangerous – what if it isn’t changed consistently?

              [/indent]
              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

              Reply | Quote
            • WSjpgus
              AskWoody Lounger
              March 4, 2004 at 4:34 pm #794291

              Thanks Hans
              ill give it whirl and report back

              RE: your thought…
              [indent]

              A phrase like “probably changes when an inventory adjustment is made” is dangerous – what if it isn’t changed consistently?

              [/indent]
              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

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              March 4, 2004 at 2:24 pm #794218

              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…

              Reply | Quote
          • WSjpgus
            AskWoody Lounger
            March 4, 2004 at 1:52 pm #794204

            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.

            Reply | Quote
        • WSHansV
          AskWoody Lounger
          March 3, 2004 at 6:47 pm #793685

          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?

          Reply | Quote
      • WSjpgus
        AskWoody Lounger
        March 3, 2004 at 4:51 pm #793620

        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

        Reply | Quote
    • WSHansV
      AskWoody Lounger
      March 3, 2004 at 12:28 pm #793455

      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?

      Reply | Quote
    Viewing 1 reply thread
    Reply To: synch two linked tables different structure (97sp2)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.