VBA Code for Indirect Back End Synchronization Over VPN?

Posted on 2006-06-26
Medium Priority
Last Modified: 2012-08-13
Hello ~

My client needs a reliable way to synchronize an Access 2003 back end database over a VPN running Small Business Server 2003, which enables mapping to Windows.  Based on earlier posts, it seems Indirect Synchronization offers the most reliable way to minimize data corruption.

Is there someone out there who can offer the VBA code or the structure for accomplishing this?

For example using:

db1 residing on C:\

db2 residing on H:\

I recognize this may be a tall order.  I appreciate your thoughts.

Best Regards, Jacob

Question by:Chi Is Current
LVL 16

Expert Comment

ID: 16990096
The easiest way to do this (which requires no code at all) is to avail the "Sychronize Now" button found on the Tools Pull down Menu under "Replication" and then Drag and drop that option to your own Custom Toolbar for the Custome's form (ie: the Main Menu).  Then the option is right there w/o any thing else you don't want in the way to distract the user.  The simply select it from the main menu and then they are set.

As for Indirect Sychronization that is a good option.  If you create an Anonomous replica the user need not be confused by a bunch of choices as to where they might sychronize too since that will be predetermined for them.

LVL 65

Expert Comment

ID: 16990128
Would this work 4 u?

U need to ensure your code is in the master, since when u replicate, u can't really make changes to the replica

Public sub SyncNo()

    Dim dbMaster As DAO.Database
    'Open the Master database
    Set dbMaster = opendatabase("C:\EE\Replication\Master.mdb")
    'This line sends changes made in each replica to the other i.e. Master to Replica and vice versa
    dbMaster.Synchronize CurrentProject.Path & "\" & CurrentProject.Name, dbRepImpExpChanges
    MsgBox "Done"

End Sub

the code above takes the current open replicated database and syncs with the master (dbMaster). Sync is done in both directions so any updates from master are also bought across
Note if new forms added, then u have to restart Replica


Author Comment

by:Chi Is Current
ID: 16990255
Thank you both for your replies here.

These are back end databases which contain only tables and avoids several issues.

It seems both your examples use direct synchronization.  Since this is happening over a VPN, and the databases will be large (100+ Mb) and will take some time, I want to absolutely minimize the likelihood of corruption if the connection or power fails on one end during the synchronization process.  Hence, I am thinking an INDIRECT synchronization scheme would be the best.

In your examples, what would happen if the connection was broken during the synchronization?

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 16

Expert Comment

ID: 16990420
Your concern is valid.  From the sounds of it it would seem that indirect synchronization is your safest bet.  If it fails it will simply send again until it succeeds.  That said, indirect synchronization requires the use of Replication Manager and a Server set up to host.  Microsoft provides an extensive document detailing the pros/cons and details on how to do all this and more at the following link...


There you'll find a downloadable file containing a lot of excelent information when it comes to Access replication.

LVL 77

Expert Comment

ID: 16990642
Hi jacobbarnett,
Never used it  myself, but Indirect replication requires 'Replication Manager', which is part of the developer tools package for your version of office.  

LVL 16

Accepted Solution

Rick_Rickards earned 2000 total points
ID: 16991794
For what it's worth I did a little testing just to see what would happen with a replicated database with hundreds of thousands of records that I was synchronizing with another replica.  During the synch I deliberately ended the task just to see what would happen.  Though I certainly don't recommend it, I was surprised to find that both copies, replica and master alike survived w/o any problems at all.  The only thing that happened was the data being passed had to be resent.  

To be honest I figured I'd have to do a compact/repair at least but as it turned out I didn't even have to do that.  As for why the replica wasn't confused that is less of a mystery.  Replicas store all their changes, design and data alike in one great big BLOB.  If the BLOB doesn't arrive in tact access regards it as a failed transmission and proceeds as though it were never sent.  Hence, even direct synchronization offers some security.

As for Peter57r's comment he is correct.  Replication Manager comes with the Developer tools CD.  I'm assuming you have that if you're intention is to set up a hub.  Bear in mind that that Hub needs to be am Windows 2000 Server (So far as I know Windows 2003 does not provide any support for Access Replication (at least if you intend to use it as a hub) and I doubt that Microsoft has any intention of getting Server 2003 to provide that support in the future.


Author Comment

by:Chi Is Current
ID: 17097147
Thank you all for your replies here.  In the interim I have been in multiple conversations w/ MS tech support who (after sifting through several individuals and lots of conflicting information) "officially" advise the following workaround (since there is no Replication Manager in any current MS product, i.e. Visual Studio Tools):

Create a design master, a hub replica and child replicas.  Place child replicas on each site's LAN server, directly synchronizing to one centralized hub and back up the hub often, before and after synchronizations. Basically: believe in direct synchronization.

Thank you Rick for testing the synchronization w/ an interruption.  I did this as well (with way fewer records) and found both the hub and the child replica were fine.  Your explanation is heartening too.

'Seems like plenty of backups is the key to keeping things copasetic.

Best Regards, Jacob

LVL 16

Expert Comment

ID: 17097800
I'm glad to hear you found a solution to your problem.  I regret that your experience with MS tech support wasn't as enlightening as it perhaps should have been to say nothing of the lack of cohesion between different tech's and their explanations and/or opinions regarding what to do.  I also regret that I’m not the least bit surprised.  I’ve found that most MS Tech’s aren’t as competent with Replication as they should be as most of them seem to know what they know from study vs. real work in the trenches and there’s nothing like doing it for real to really understand the subject matter.  Even so, there is one individual at Microsoft who knows the subject very well and I’ve been very fortunate to have his direct number, so my way of saying thank you to Microsoft is to help others when I can to help pass on the knowledge Microsoft has made available to me.

It stands to reason that Microsoft’s dedication to Replication as a technology in Access is lacking but despite that it doesn’t change the fact that they built a great technology that does work (albeit it hiccups now and then).  

All and all it sounds like they helped you achieve a permanent solution that should work for you and it pleases me to see that you found my comments helpful as well.

To clarify some of the information they gave you, (that falls dangerously close to misinformation), I have a few things to say.  While it is true that Replication Manager IS NOT available in any CURRENT Microsoft Product IT IS AVAIALABLE in on the Microsoft Office 2000 Developer's CD (that is the one for Jet 4.0 which is in my opinion the only one worth having).  If one is considering Replication in Jet 3.5 (which does exist) I’d suggest one seriously consider upgrading to Jet 4.0 (Access 2000).  

If you have aspirations to do more with Replication in the future I’d suggest acquiring a copy of the Developer’s CD thru eBay or some other 2nd hand source while copies are still available.  If you’re not in any hurry you can probably even get one for a good price.

As you've clearly discovered, you don't need Replication Manager to make your solution work.  By creating a Hub copy on one of your servers and synchronizing directly with it you should be good to go.

By spawning Global Replicas you'll avail copies of your database to your users that could at any time take the place of your Hub Master and/or Design Master should either become corrupt.  In this way it is very much like having lots of backup copies of your database spread all over the place making the likelihood of you loosing everything extremely remote.  

Even so, don't get complacent.  At the very least back up your own copy periodically.  The approach I find that works best for me is to make backup of my copy (which is usually the design master but if all it is a global replica it is as good as being the design master) before I synchronize and immediately after I synchronize.  While this may seem extreme to some, it is so easy to do it's hard to imagine why one would not take that minor step for peace of mind if nothing else.  

I'd also encourage your users to make a habit of the following.  BEFORE they BEGIN WORK, SYCHRONIZE FIRST.  As soon as they are DONE WORKING, SYCHRONIZE AGAIN.  If they are willing to make a backup but are stingy about doing so I suggest they backup once their work is done and before they synchronize.  If something goes wrong it is possible to identify what they did and bring that work into the fold.  How important this is really boils down to how much work they have done and are willing to put on the line.  In truth, it’s unlikely that anything will go wrong but it never fails that when things do fail it will most certainly happen when you have a lot to loose.  As an alternative to backups they could instead synchronize periodically thru the day.  In truth the net effect of this is better than backing up as it avails the changes they make to others more quickly and reduces the chance of conflicts.  Think of it as being a little like saving a word document every hour or so just to be sure you don’t loose anything if the lights go out.  

The idea behind synchronizing before and after their work is to start them off with the most current data and then avail any changes they have made to others as soon as they are finished.  Again, theirs is nothing wrong with synchronizing a time or two during the course of a day if they are working for several hours.  As a bonus, more frequent synchronizations can reduce the amount of time required to synchronize since there will be less information to be sent and received from the Hub each time they do.  Even better, these more frequent synchronizations will significantly reduce the possibility of conflicts but don’t take these frequent synchs to the point of absurdity.  If the user has done enough work that it would pain them to loose it then it’s probably a good time to synchronize.  Regardless, they should always synchronize when their workday is done.

To that end, enjoy replication.  It is a good technology even if the number of people who use it isn’t that great and the number of people who really understand it is even less.  With any luck people like you will help motivate Microsoft to keep the technology alive, something I think they would do if the demand for it were great enough.  This of course begins with people like you, end users who use it and would subsequently be reluctant to upgrade if it were to go away, (hint, hint, to Microsoft).

Best of luck to you,


Ps.  Some users may be a little confused by all the talk about Access 2000 Replication when the topic started off regarding Access 2003.  While the versions of Access are different, the version of JET is not.  They both use Jet 4.0 so when we talk about Replication for Access 2000 we could just as well be referring to Access XP (2002) and/or Access 2003, (it's all Jet 4.0 - at least the database engine itself is, so at the end of the day Access is really the fluff, GUI if you will, around JET 4.0, hence we probably should say Replication for Jet 4.0 instead of Access 2000 but then again if we did that, it's likely few would know what we are talking about so hopefully this explanation will clear up the fog).  Simply put, Access 2000 Replication is (in theory) good to go for Access 2002 and Access 2003 because all three versions rely on Jet 4.0.  As for the next version of Access... all bets are off until it comes out but if replication is important to you I'd get one of those Developer CD's for Access 2000 while there still avail.  You should also embark on Replication knowing that Microsoft’s support for it is lacking, (something I'm sure Jacob discovered first hand), but then that's why we have forums like EE, there is someone out there who has the answer.

Author Comment

by:Chi Is Current
ID: 17101967
Rick ~

Thank you for your extensive comments!  They are most helpful.  I appreciate your support, encouragement and insight.  I DID acquire a copy of Office Developer 2000 v1.5 (on eBay) and have only begun to work w/ Replication Manager.  

A couple of things:  My clients are running Small Business Server 2003.  It has seemed to work OK for the direct synchronization tests I have done.  It is MOST heartening to hear of your successful testing AND your thinking that the technology is good.  It’s easy to see backing up as the key.

Secondly, one replica will be in China, synchronizing (over a s-l-o-w connection) to a hub in California, so the hours of operation will be different, which should help.  Because of the connection, the indirect synchronization wouls seem to offer greater reliability, though from what you said, it sounds as though the BLOBS theory you mention, regarding DIRECT synchronization (>>Replicas store all their changes, design and data alike in one great big BLOB.  If the BLOB doesn't arrive in tact access regards it as a failed transmission and proceeds as though it were never sent.) sounds very similar to the INDIRECT scheme (?) – providing another layer?  As I mentioned in an earlier post, the synchronization will involve only the back end, containing just tables, which should be better.

As this is an existing database, I'm finding there's some significant modification I need to do to get it ready for this process ie a way to work with database-generated part numbers (next in a sequence).  Right now its conceivable that two databases running independently could wind up creating duplicate part numbers.  Perhaps different prefixes for different sites (?).  Is this generally how you resolve something like this?

I plan to incorporate the synchronization process into a VBA routine, to make it easy for the designated "synchronization person", and write a batch file to do the backing up. I think it needs to be e-a-s-y to do for clients who all work with computers but are not technically oriented.

 - I'm surprised to think that MS would consider phasing out synchronization technology in Access -> possibly a corporate plan to encourage development for SQL Server as backend instead of Access.  Attempting to ratchet up the technology and $$$.

I certainly appreciate your thoughts on this.

Best Regards, Jacob
LVL 16

Expert Comment

ID: 17116963
I regret that my schedule has not allowed me to respond more quickly but I figure a slow response is better than none at all.

Direct synchronization with Replication Manager on Small Business Server 2003 will work mostly because Replication Manager has very little to do with the process itself.  It will avail the ability to control certain things like how long a replica remains active before it's failure to synch beyond a certain point has expired.  Although there are other ways to manage this Replication Manager makes it really easy (under the right circumstances).

First off, I suspect you'll have many more questions about Replication than the ones articulated here.  There is a Word Document that goes into many details hard to find anywhere else.  The document is available for download at the following KB....

Second, Indirect Synchronization (Synchronization that is done via the internet) requires the use of IIS and so far as I know is only supported under Windows 2000 Server.  While it is true that Replication Manager can run under Server 2003 I don't believe you'll encounter much success in getting it to work with Indirect (internet) synchronization.  I know, bad news so far as I know it’s the sad truth.  It's one of the things that could cause one to believe that Microsoft isn't very serious about continued support of the technology.  In truth I can't speak to their intentions, in part because I know what they are and in part because I don't.  In other words by NDA I'm bound not to say what I know and they have no obligation to say what their marketing intentions really are so even if I could speak to the subject I couldn't say for certain that what I've been told is in fact what will be.  That said I suggest we all do what everyone else does and move forward with caution given that they could have provided support for Indirect Replication in Server 2003 and it appears that they chose not to.  What that says is best left for each individual to decide but in the meantime I continue to hope that by continuing to support others who appreciate the value of the technology that Microsoft created, may help them appreciate and support it a little more.  The worst that could happen is that some may leverage a tool that would have otherwise been neglected and I figure since Microsoft Built it why not use it, (just be aware that Microsoft has an established record of building technologies that they latter abandon).  Doesn't mean you can't benefit from it, but capitalize on a great technology while exercising a certain amount of cautious observation and judicious evaluation.  Of this I'm sure, Microsoft love to sell software and if enough people want it and it becomes the reason to upgrade or not it gives Microsoft a reason to think carefully about abandoning a good thing (even if it is a significant undertaking to keep it).

Anyway enough said on that...

If you're interested in Internet (Indirect Synchronization) the following white paper is a must read...

Another good article (I know they are long but they are worth reading is...

Both of the above articles are referenced in the overview offered at....

Beyond that there are many more KB's regarding replication than one might at first realize.  To that end I'll dispense with the dialog and simply offer the articles and brief summary regarding them.  May you find them helpful and if nothing else encouraging, Microsoft did publish a lot of information about replication even if they didn't make it all that easy to find.  Perhaps this will make it a bit easier.  If I've missed any good articles regarding replication I extend my apologies but given the time I have this is the most I could pull up quickly and anyone who notices anything missing is welcome to offer their added insight.

URL                                                                   Description
http://support.microsoft.com/kb/138442/en-us      ACC95: How Replication Increases Size of Database
http://support.microsoft.com/kb/138443/en-us      ACC95: How Replication Affects AutoNumber Fields
http://support.microsoft.com/kb/138828/en-us      ACC95: Microsoft Jet Replication White Paper Available in Download Center
http://support.microsoft.com/kb/142188/en-us      ACC95: Replication Does Not Start Until You Click OK
http://support.microsoft.com/kb/149086/en-us      ACC95: Duplicate AutoNumber Values Prevent Replication
http://support.microsoft.com/kb/158930/en-us      ACC: How to Use the ReplicationConflictFunction Property
http://support.microsoft.com/kb/160753/en-us      "ACC: Query Error ""The Replication ID You Entered Is Invalid"""
http://support.microsoft.com/kb/164553/en-us      ACC97: Jet 3.5 Replication White Paper Available in Download Center
http://support.microsoft.com/kb/169955/en-us      ACC: Parameter Query Returns No Records with Replication ID Field
http://support.microsoft.com/kb/172974/en-us      Access Violation When Canceling Replication in Webadmin
http://support.microsoft.com/kb/173002/en-us      ACC: How Replication Manager Determines Base Replica
http://support.microsoft.com/kb/173005/en-us      ACC: Replication Fails with More Than 128 Shares on Computer
http://support.microsoft.com/kb/173044/en-us      ACC95: Replication/Synchronization Fails, Error in Msjtrclr
http://support.microsoft.com/kb/181371/en-us    ACC97: Internet Replication White Paper Available in Download Center
http://support.microsoft.com/kb/183710/en-us      ACC97: Replication Sample Code Available in Download Center
http://support.microsoft.com/kb/198638/en-us      ACC2000: How Replication Manager Determines Base Replica
http://support.microsoft.com/kb/208222/en-us      "ACC2000: ""The Replication ID You Entered Is invalid"" Error in Query"
http://support.microsoft.com/kb/210612/en-us      ACC2000: Parameter Query Returns No Records with Replication ID Field
http://support.microsoft.com/kb/224551/en-us      ACC2000: Security/Replication Tools Do Not Operate Against SQL 6.5
http://support.microsoft.com/kb/225907/en-us      ACC2000: AutoNumber Field with FieldSize of ReplicationID Can Be Manually Edited in a Data Access Page
http://support.microsoft.com/kb/253757/en-us    ACC2000: Upsizing Error and No Data Is Upsized with Table That Contains Replication IDs
http://support.microsoft.com/kb/282977/en-us      Access 2000 Replication FAQ is available in Microsoft Download Center
http://support.microsoft.com/kb/291958/en-us      ACC2002: Parameter Query Returns No Records with Replication ID Field
http://support.microsoft.com/kb/292771/en-us      Access Violation in Inetinfo During NNTP Replication Between Newsfeed and Public Folder
http://support.microsoft.com/kb/303380/en-us      ACC2000: Error Message Appears When You Try to Use Access 2000/Jet 4.0 Internet Replication
http://support.microsoft.com/kb/304026/en-us      ACC2000: Troubleshooting Common Replication Errors
LVL 16

Expert Comment

ID: 17116968
Oh one last thing, I wish Jacob my best regards and sincerely hope that my posts have been helpful to him and anyone else who may stumble across this question.

Best of luck Jacob!!!


Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question