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??
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
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)
- This topic has 12 replies, 2 voices, and was last updated 21 years, 3 months ago.
Viewing 1 reply threadAuthorReplies-
WSHansV
AskWoody Lounger -
WSjpgus
AskWoody Lounger -
WSHansV
AskWoody LoungerMarch 3, 2004 at 6:47 pm #793684More 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? -
WSjpgus
AskWoody LoungerMarch 4, 2004 at 1:52 pm #794203Hans.
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.
-
WSHansV
AskWoody LoungerMarch 4, 2004 at 2:24 pm #794217A 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…
-
WSjpgus
AskWoody LoungerMarch 4, 2004 at 4:34 pm #794290Thanks Hans
ill give it whirl and report backRE: 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
-
WSjpgus
AskWoody LoungerMarch 4, 2004 at 4:34 pm #794291Thanks Hans
ill give it whirl and report backRE: 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
-
WSHansV
AskWoody LoungerMarch 4, 2004 at 2:24 pm #794218A 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…
-
-
WSjpgus
AskWoody LoungerMarch 4, 2004 at 1:52 pm #794204Hans.
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.
-
-
WSHansV
AskWoody LoungerMarch 3, 2004 at 6:47 pm #793685More 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?
-
-
WSjpgus
AskWoody Lounger
-
-
WSHansV
AskWoody Lounger
Viewing 1 reply thread -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Extended Windows Built-in Disk Cleanup Utility
by
bbearren
6 hours, 26 minutes ago -
Win 11 24H2 June 2025 Update breaks WIFI
by
dportenlanger
10 hours, 6 minutes ago -
Update from WinPro 10 v. 1511 on T460p?
by
CatoRenasci
7 hours, 24 minutes ago -
System Restore and Updates Paused
by
veteran
12 hours, 37 minutes ago -
Windows 10/11 clock app
by
Kathy Stevens
6 hours, 39 minutes ago -
Turn off right-click draw
by
Charles Billow
15 hours, 51 minutes ago -
Introducing ChromeOS M137 to The Stable Channel
by
Alex5723
19 hours, 22 minutes ago -
Brian Wilson (The Beach Boys) R.I.P
by
Alex5723
2 hours, 26 minutes ago -
Master patch listing for June 10, 2025
by
Susan Bradley
20 hours, 58 minutes ago -
Suggestions for New All in One Printer and a Photo Printer Windows 10
by
Win7and10
3 hours, 31 minutes ago -
Purchasing New Printer. Uninstall old Printer Software First?
by
Win7and10
1 day, 3 hours ago -
KB5060842 Issue (Minor)
by
AC641
1 day, 7 hours ago -
EchoLeak : Zero Click M365 Copilot leak sensitive information
by
Alex5723
1 day, 10 hours ago -
24H2 may not be offered June updates
by
Susan Bradley
2 hours, 42 minutes ago -
Acronis : Tracking Chaos RAT’s evolution (Windows, Linux)
by
Alex5723
1 day, 22 hours ago -
June 2025 updates are out
by
Susan Bradley
10 hours, 57 minutes ago -
Mozilla shutting Deep Fake Detector
by
Alex5723
2 days, 13 hours ago -
Windows-Maintenance-Tool (.bat)
by
Alex5723
1 day, 22 hours ago -
Windows 11 Insider Preview build 26200.5641 released to DEV
by
joep517
2 days, 15 hours ago -
Windows 11 Insider Preview build 26120.4250 (24H2) released to BETA
by
joep517
2 days, 16 hours ago -
Install Office 365 Outlook classic on new Win11 machine
by
WSrcull999
2 days, 15 hours ago -
win 10 to win 11 with cpu/mb replacement
by
aquatarkus
2 days, 7 hours ago -
re-install Windows Security
by
CWBillow
2 days, 19 hours ago -
WWDC 2025 Recap: All of Apple’s NEW Features in 10 Minutes!
by
Alex5723
2 days, 22 hours ago -
macOS Tahoe 26
by
Alex5723
2 days, 17 hours ago -
Migrating from win10 to win11, instructions coming?
by
astro46
7 hours, 49 minutes ago -
Device Eligibility for Apple 2026 Operating Systems due this Fall
by
PKCano
2 days, 7 hours ago -
Recommended watching : Mountainhead movie
by
Alex5723
2 days, 8 hours ago -
End of support for Windows 10
by
Old enough to know better
7 hours, 13 minutes ago -
What goes on inside an LLM
by
Michael Covington
8 hours, 10 minutes ago
Recent blog posts
Key Links
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.