?
Solved

automatically synchronising replica

Posted on 2013-01-15
11
Medium Priority
?
283 Views
Last Modified: 2013-01-19
hey guys,

i've got a replica access database. the design master is in the network drive and the replicas are on the local computers of my colleagues.

is there a way that i can trigger the synchronising? i've split the database into FE and BE so i'm only synchronising the BE.

1) me trigging the synchronising would be the best in my opinion ATM, 2) failing which can i schedule it, 3) failing which how can i set up an event that triggers this synchronisation?

i'm thinking when the staff closes the FE then it will look for the BE in the same folder and then start the synchronisation.

if possible can yall include some code? thanks guys!!!

feel free to suggest the best way i can do it. the network here is way way way too slow. replication was the only way i could make things work. it took 5 minutes to open a 30MB database. thanks guys!!
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38777769
<<
when the staff closes the FE then it will look for the BE in the same folder and then start the synchronisation.
>>

You can do this by creating a "splash" form in your database that opens when your database opens, and remains open in the background (it can be hidden) all the while your database is open.

In the close event of that form:


    Dim dbRep As Database
    
    Set dbRep = OpenDatabase("YourPath\YourBackEndDB.mdb")
    dbRep.Synchronize "YourPath\YourMasterDB", dbRepImpExpChanges

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38777808
A couple of additions/corrections:

    Dim dbRep As Database
    
    ' Look for the BackEnd:
     if Dir("YourPath\YourBackEndDB.mdb")  & "" = "" Then
         MsgBox "Back End not found"
         Exit Sub
     end if

    ' Look for the Master Design DB:
    If Dir("YourPath\YourMasterDB.mdb") & "" = "" then
        MsgBox "Master Design database not found"
        Exit Sub
    End If

    Set dbRep = OpenDatabase("YourPath\YourBackEndDB.mdb")
    dbRep.Synchronize "YourPath\YourMasterDB.mdb", dbRepImpExpChanges
 
    ' Remember to close the replica
    dbRep.Close
    Set dbRep = nothing

Open in new window

0
 

Author Comment

by:developingprogrammer
ID: 38779140
hi mbizup, thanks for your code. could you kindly explain this line to me?

If Dir("C:\Users\me\Desktop\Replica of WBR_be.mdb ") & "" = "" Then

it works - in the sense it find the backend database. but what does the ""="" mean in this if statement?

in the immediate window, i get this.
?Dir("C:\Users\me\Desktop\Replica of WBR_be.mdb ")
Replica of WBR_be.mdb

so the if statement is saying:

If Replica of WBR_be.mdb '=' then

(i changed the double quotes to single quotes for ease of reading)

but Replica of WBR_be.mdb = what? i don't really understand this logic.

thanks mbizup!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:developingprogrammer
ID: 38779154
and also, do you think we should synchronise to our design master or to a main replica?

cause i read this on another site
============================================
> And, yes, these are only data tables.  The "local" databases (on
> the laptops) will link to the tables in the replicas.  Isn't this
> a valid scenario?  It was my understanding that only the Design
> Master had to stay on the original computer forever.....
The split design is fine. The Design Master should, indeed stay on
the original computer, but it should not be the copy that is used as
the central point for synchronization with the replicas and as the
editable copy at the home site.

The Design Master should be kept in a safe place and synched once a
month, at most.
============================================================
i don't understand why we should not synchronise to the design master directly --> as per what you've done in your code. so we're in concurrence but it may be that we might have missed out something? i'm not too sure. thanks mbizup!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38780837
<<
If Dir("C:\Users\me\Desktop\Replica of WBR_be.mdb ") & "" = "" Then
>>

The Dir function locates the filename specified and returns the name of the file if it is found.

The Dir function will return a blank if the file is NOT found.

That statement looks for a blank return from the Dir() function before continuing with code that would require that file to be present.

<<
in the immediate window, i get this.
?Dir("C:\Users\me\Desktop\Replica of WBR_be.mdb ")
Replica of WBR_be.mdb
>>

That simply means that the file is indeed there as expected.  If it were not there for whatever reason, you ? Dir(etc) would return a blank/nothing.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38780851
<<
and also, do you think we should synchronise to our design master or to a main replica?
>>

I honestly can't advise you on this.  What you see here is the extent of my knowledge of replication/syncronization.

I personally have never had the need to use it, and if I am not mistaken, MS is gradually dropping support for it.  There are also very few others here who use/are familiar with it which explains the delay between your initial post and my response (response time is typically much quicker in the Access area).
0
 

Author Comment

by:developingprogrammer
ID: 38781129
i see, no problem mbizup, but once again i really appreciate you helping me out!

one more question - i see that for your = sign you put it as ""="", why do we need 2 quotation marks to open and two to close? i understand that if something is a string then we need to do that, for example in dlookup statements. but how come we need to do it here? so that computer will read the input as "=" cause the first quotation mark just tells the computer that --> hey i'm going to enter in something already! so how come we need 2 quotation marks to open and two to close? thanks mbizup!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38781181
The "" is an empty string on both sides of the =.

Concatenating an empty string to an expression, variable or object value is a way of handling possible nulls, to avoid errors.  A null concatenated with an empty string will result in an empty string (which can then be compared to other strings, wheras comparing a null would result in an error).

<< if Dir("YourPath\YourBackEndDB.mdb")  & "" = "" Then >>

The following is equivalent code, but it might be clearer:

dim strEmpty as string
strEmpty = ""
 if Dir("YourPath\YourBackEndDB.mdb")  & strEmpty  = strEmpty  Then
           ' null/empty return from Dir - your file was not found

Open in new window


It basically lets us check for a blank return from Dir, regardless of whether the return from Dir is NULL or an Empty String ("")
0
 

Author Comment

by:developingprogrammer
ID: 38785456
WHAO THAT IS SUPER COOL!!!!
0
 

Author Comment

by:developingprogrammer
ID: 38785457
i never thought of that haha, but yes that is really super cool that empty string concatenation thingy = )

ok implementing your code now and trying it out now = )
0
 

Author Closing Comment

by:developingprogrammer
ID: 38796425
fantastic stuff from mbizup once again = ))
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

764 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