synch two linked tables different structure (97sp2)

Topic

New Reply

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

Replies

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

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

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

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

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

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

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

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

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

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

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

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