Solved

automatically synchronising replica

Posted on 2013-01-15
11
245 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
  • 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 500 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now