Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 5th, 2005, 02:27 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default Access / MySQL Synchronization

Hi Everyone,

No hurry with this one, but if any of you have had any experience do this, or something like this... Any suggestions or ideas would be appreciated.

I will be building a complex multiuser application using Access as the Front and Backend. This system will be installed at a remote facility that only connects to the internet once or twice a day. The administrator for the system would like to synch the data in this database with a MySQL database linked with the website.

My sole idea right now, is to have these apps track any changes to the data, either add, edit or remove at the field level and store these changes in a table with the date & time of the change. When synchronization is requested, one system will generate a text file of changes, which will be sent to the other. The file will be analyzed, changes which only occur on one side will be added, while business rules, or manual intervention will resolve changes in both systems. When this is done, a text file of specific changes will be sent back to the first system and entered.

The other option I am looking at is to create the offline system using ASP with either a PHP or SQL Server Backend, but even then, the synching will still need to be figured out.

Thanks in advance,

Mike

Mike
EchoVue.com
__________________
Mike
EchoVue.com
 
Old December 5th, 2005, 09:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It sounds to me as though people will be changing both databases simultaneously, and what you want to do is to synchronize them so that each receives the other's changes.

For example, if you have NorthWind on one network, and NorthWind SQL on the web site, people will be adding orders to both databases, and then you want to add the changes from the local database to the SQL database, and then add the changes from the SQL database to the local database. Is this correct?

My suggestion is to not do this this way if this is the case. :D

Can you confirm with more facts and sample cases? I would like to help with this quandry.



mmcdonal
 
Old December 5th, 2005, 11:36 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Mike,

Sure - The company hosts events throughout the year. The offline system, which would be Access/SQL Server or something else is the one at the remote location thatt has limited connectivity. They also handle most of the administration. The online system is linked to a website, where the customers can log in and make changes. I would think generally most of the changes would occur on one side only, but there is always the chance of a change on both sides that would need to be handled.

Yell if you need more information - we may need to take it offline.

Thanks

Mike

Mike
EchoVue.com
 
Old December 5th, 2005, 01:20 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Very strange. I have the exact same scenario, except that the client has cable modem. So they are going to use the web site to update member information, and then use a local database to run events, and then synchronize the member data periodically on the local database by downloading and running a procedure to do dts functions into Access.

We will not have the synchronization issues, however.

I think if you make fields in your tables that track

DateCreated
UserCreated
DateModified
UserModified

Then you can do a match on the Modified fields. If any are mismatched, then you can have a priority list of usernames, or just take the latest DateModified record. The big problem is if one side is creating records and you have an FK's since the PKs will change as you roll them up.

Interesting problem. See the O'Reilly Access cookbok for the code for the fields listed above.

mmcdonal
 
Old December 5th, 2005, 02:49 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Thanks Mike,

I have been considering using PK which would identify the record as either online or offline, which would be more complcated that just setting the field to autonumber, but would help keep things straight. I'll go ahead and have a look at that code. I'll also let you know if I find anything else. This particular client has a cable modem in one location, and connects using satellite at the remote location. But I think for his work, he will just be taking a snap shot of the data - otherwise, I might well be looking at a 3-way synch!

Mike

Mike
EchoVue.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
Cross-domain synchronization NicoDeBruin General .NET 5 August 26th, 2007 02:34 PM
data synchronization abdulweb General .NET 0 August 26th, 2007 12:20 AM
--Javascript Synchronization-- tal Javascript 2 June 29th, 2007 11:33 AM
XML Synchronization prashant_dnmmkpk Biztalk 0 January 17th, 2005 09:16 AM
Synchronization Problem bveerendrakumar Pro VB 6 2 July 10th, 2003 05:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.