By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,326 Members | 1,932 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,326 IT Pros & Developers. It's quick & easy.

Auto Syncing Replicas

P: 9
We are lucky and get to run an 11 year old version of Access (97). I have set up a master with 5 replicas. These are located on various network drives in our organization. Is there any macro/code I can use to have the master auto sync each of the replicas when opened? the Microsoft website sucks and didn't help me at all. I would love any advice or help.
Sep 26 '08 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,661
Can you be clearer as to what, EXACTLY, you want to have happen.
Sep 26 '08 #2

P: 9
Can you be clearer as to what, EXACTLY, you want to have happen.

I would like to have a button on the master that automatically (without pop up and choices) syncs all of the replicas to the master.
Sep 26 '08 #3

NeoPa
Expert Mod 15k+
P: 31,661
I'm afraid if that's as clear as you can be then I'm unable to help.
Sep 26 '08 #4

P: 13
Look in Access Help for more info on this. I got in to this a bit a couple of years ago. Unless you're a programmer-extraordinare, it's a lot easier just to open the master, then do the Tools...Replication...Synchronize Now.
Sep 26 '08 #5

P: 9
I'm afraid if that's as clear as you can be then I'm unable to help.

Sorry about the confusion. I am looking for "Replication Code" that I can put into a macro that will run the sync procedure out of sight of the user. The only thing I found was from the Microsoft website http://support.microsoft.com/kb/183710/en-us

This is kind of what I need, but an actual working same so I can change the BD names and paths.
Sep 26 '08 #6

ADezii
Expert 5K+
P: 8,669
!!!WARNING - this code exists only in the crevices of my mind! (LOL)! It has not been tested, since I do not have the means to, and I make no guarantee whatsoever as to how well it will work, or if it will even work at all. DO NOT USE THIS CODE ON THE LIVE MASTER AND REPLICAS! All that being said:
  1. Create a Table named tblReplicas, and within this Table create a single Field name [Replica_Path].
  2. Create 5 New Records in tblReplicas consisting of the 'Absolute' Network Path of the 5 Replicas, including the Database Name, such as:
    K:\Server4\Databases\Replicas\Replica_1.mdb
  3. Depending on how you want the Replication Process to perform, namely: MASTER ==> Replica, Replica ==> MASTER, MASTER <==> Replica, select 1 of the 3 With..End With code segments below.
    Expand|Select|Wrap|Line Numbers
    1. 'To Send changes from the MASTER to the Replica
    2. With rstReplicas
    3. --Do While Not .EOF
    4. ----MyDB.Synchronize ![Replica_Path], dbRepExportChanges
    5. ----.MoveNext       '.MoveNext
    6. --Loop
    7. End With
    8. ---
    9. 'To have the MASTER receive changes from the Replica
    10. With rstReplicas
    11. --Do While Not .EOF
    12. ----MyDB.Synchronize ![Replica_Path], dbRepImportChanges
    13. ----.MoveNext
    14. --Loop
    15. End With
    16. ---
    17. 'Changes from both the MASTER and Replica are exchanged
    18. '(Default - Bi-directional exchange)
    19. With rstReplicas
    20. --Do While Not .EOF
    21. ----MyDB.Synchronize ![Replica_Path], dbRepImpExpChanges
    22. ----.MoveNext
    23. --Loop
    24. End With
  4. Execute the following code from 'within the MASTER'.
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database
    2. Dim rstReplicas As DAO.Recordset
    3. ---
    4. Set MyDB = CurrentDb()
    5. ---
    6. Set rstReplicas = MyDB.OpenRecordset("tblReplicas", dbOpenForwardOnly)
    7. ---
    8. With rstReplicas
    9. --Do While Not .EOF
    10. ----'Use only [1] of 3 Synchronize Options listed below
    11. ----MyDB.Synchronize ![Replica_Path], dbRepExportChanges
    12. --- ------------------------ OR
    13. ----MyDB.Synchronize ![Replica_Path], dbRepImportChanges
    14. --- ------------------------ OR
    15. ----MyDB.Synchronize ![Replica_Path], dbRepImpExpChanges
    16. --- ------------------------ OR
    17. ----.MoveNext
    18. --Loop
    19. End With
    20. ---
    21. rstReplicas.Close
    22. Set rstReplicas = Nothing
  5. If you need any explanation on the code, either muself or one of the other Members will assist you.
  6. Say a Prayer, and let me know how you make out.
Sep 27 '08 #7

P: 9
Will be trying it today. Thank you for the help.
Sep 29 '08 #8

ADezii
Expert 5K+
P: 8,669
Will be trying it today. Thank you for the help.
Kindly let me know the outcome, I'm interested myself.
Sep 29 '08 #9

P: 9
Kindly let me know the outcome, I'm interested myself.
Sorry for the delay but I had to go to Ny for work and this wasn't part of that work. I tried it today but I can get it to run. I am not very good with VB so I keep getting complier error when debuging. This is what I used.

Expand|Select|Wrap|Line Numbers
  1.  Dim MyDB As DAO.Database
  2. Dim rstReplicas As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5.  
  6. Set rstReplicas = MyDB.OpenRecordset("tblReplicas", dbOpenForwardOnly)
  7.  
  8. With rstReplicas
  9. Do While Not .EOF
  10.     MyDB.Synchronize ![Replica_Path], dbRepImpExpChanges
  11. .MoveNext
  12. Loop
  13. End With
  14.  
  15. rstReplicas.Close
  16. Set rstReplicas = Nothing
  17.  
For some reason it does not like "Set MyDB" Not sure why.
Oct 10 '08 #10

ADezii
Expert 5K+
P: 8,669
Sorry for the delay but I had to go to Ny for work and this wasn't part of that work. I tried it today but I can get it to run. I am not very good with VB so I keep getting complier error when debuging. This is what I used.

Expand|Select|Wrap|Line Numbers
  1.  Dim MyDB As DAO.Database
  2. Dim rstReplicas As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5.  
  6. Set rstReplicas = MyDB.OpenRecordset("tblReplicas", dbOpenForwardOnly)
  7.  
  8. With rstReplicas
  9. Do While Not .EOF
  10.     MyDB.Synchronize ![Replica_Path], dbRepImpExpChanges
  11. .MoveNext
  12. Loop
  13. End With
  14.  
  15. rstReplicas.Close
  16. Set rstReplicas = Nothing
  17.  
For some reason it does not like "Set MyDB" Not sure why.
Make sure you have a Reference set to the Microsoft DAO X.X Object Library
Oct 10 '08 #11

NeoPa
Expert Mod 15k+
P: 31,661
For some reason it does not like "Set MyDB" Not sure why.
Do you have a reference (Tools / References...) set to Microsoft DAO 3.6 Object Library (or similar version)?

PS. Possibly needed to refresh the page before replying - but I was doing some testing. I did find that this reference is not set automatically (Would have thought it would be).
Oct 10 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.