Replicate backend database

[TimPittaway]

I've created a frontend/backend system and want to be able to replicate local versions of the backend to the network version. Is there an easy way to enable replication of the backend through the frontend application, or does the backend have to be manually replicated?

 

[mrf1xa]

I'm a bit confused here. The 'normal' way of running a split system is to have a frontend on each users PC and just one backend sitting on the werver, with all the frontends linked to it. This means that no replication should be needed, other than for backup purposes.

What am I missing?

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[PaulG111]

Tim,

I have exactly the same sort of set up which you are describing. In that I have a split mde front-end with all the forms and queries etc. I also have a backend with a master on a file server and replicated copies on the PCs and therefore local to each front end. I have done this in order to circumvent a performance problem where 4 of my users are at the end of a slow ISDN connection. Hitting a local replicated backend is a lot faster for them than hitting the master directly on the remote fileserver over the ISDN line. As each user logs into the frontend it links with the local replicated back end and synchronises with the master at the end of the ISDN line under frontend program control. Since only changed record data is exchanged over the ISDN line it only takes 4 or 5 minutes each day to complete the synch. Effectively each instance of your front end code can be manually or programatically linked with a different copy of your replicated backend database. As far as I am aware the code to do this is dicussed in various threads/faqs on this Tek-Tips forum. But I am a newbie here and I am not sure how you search for it. Perhaps a more experianced tek-tips user can point you in the right direction. I had to discover the code for this from various MS articles and trial and error before finding out this great tek-tips resource. If you like I can share what I have coded to do the above. It just might not be as slick as the stuff other tek-tips gurus have put together! My only problem relates to synchronising more than one replica with the backend master at once - in that it seems you can't. I need to detect if a sync is already in progress before allowing another one to start or find a way to do concurrent synchs with one master. If anyone has any ideas I would be very grateful. Please see my previous thread called Synchronisation. Regards, PaulG

 

[TimPittaway]

Thanks Paul, that does sound like what I need. The reason I have local backends is for users to work on laptops without network access, then replicate to the master backend when they return to the office and login to the network. Does yours replicate to the server each time a user logs in to their local frontend, or does it have to be manually initiated? I need a system where the user can manually initiate replication only once they have returned to the office.

Thanks again for any more help. Tim.

 

[PaulG111]

Hi Tim,

Yes, there is also a sync button that the user can click at any time in case there are updated store or contractor details which need to be pulled down. So you could have the user initiate a synch each time they log onto the network. However, there is an alternate approach. For my users some are working on a fast connection to the fileserver and therefore can hit the master backend directly without performance problems for others they are remote and they have to connect to the local replicated front end. My front end checks for a local replicated back end and if it finds one it links to that, refreshes the links and than carries out a back up and a sync. If a local replicated backend is not found it looks for the master and just connects directly without any sync operation. In this way I can have the same version of front end code without worrying about whether the user is going to be working remotely or local to the master back end Db. You probably want the reverse of that. i.e. Check for the presence of the network and the master backend, if found do a backup of the local replicated copy and then carry out a synch operation. In this way you can have one set of code for local and remote users and also have the code automatically initiate a synch whenever the front end is started up when the users laptop is connected to the network. All without the need for the user to consciously decide to carry out a sync themselves. Does this sound like what you are looking for? If so, let me know and I will look out the appropriate code segments from my front end and post them to this thread. It is a bit late here now so it will probably be tomorrow before I can get the code formatted and posted up for you. In the meantime consider whether my description above fits your requirement and come back with any questions you have and I will sort any code you would like for tomorrow. Regards, PaulG

 

[TimPittaway]

Paul

That sounds exactly like what I'm after. Would be ideal if the frontend was able to check for the network and, if not present, use the local backend, otherwise connect to the network backend and synch the local backend. I might run into problems in specifying which backend to connect to thouogh, as I have the same database for a number of different clients. But any code you are able to provide when you get a chance for the above would be greatly appreciated for a start. Thanks!

 

[PaulG111]

Tim,

Ok here is the code which I mentioned. Some of this code I have taken from various points on the net and other sections I have actually coded myself. There are probably better alternative ways of doing all of this and I am sure others might want to advise you as well. However, all of this code works ok on Access 2000 without problems.

My application does the following things in this order:
1. Load the startup form and call the process which links the frontend to the local replicated backend or the remote master backend. My code checks for a local replicated copy first before looking for the remote master. You will probably want your code to do it the other way round. – See the procedure below in Sub Link

2. Once the connect is complete we can do the usual login and user validation stuff

3. Then we check for whether a slave back end is on the C: drive and if so we open frmSync which does a compact/backup and then calls a process which does the sync itself. This forms times the sync operation so we can get an idea if performance is degrading over time as the dbs grow. See the from load code and the Sync process code below.

And that should be that. Hope this helps – let me know if you have any questions.
Regards,
PaulG

Sub Link()
‘This process checks for the presence of  a slave backend and links to it accordingly.
‘If a slave is not found it then links the frontend to the master backend Db
Dim strTarget As String
Dim tdf As TableDef, db As Database

'Point an object at the current db.
Set db = CurrentDb

'Refresh the object to reflect the current db schema
'N.B. I have commented this out as users cannot change the db schema anyway.
'Use of the refresh could slow the relink process when the frontend communicates
'with the backend database on the remote G drive.
'db.TableDefs.Refresh

'Now look for a slave copy of the database on the local drive.
'If it is not there the client must be on a machine in Crewe linked to the
'Master database and therefore no reconnect is required.
'Note the use of the "?" wildcard to make sure we pick up all variations of
'slave db names for the 03 trading year.  I use a different name for each slave
‘db on each PC but my frontend only needs to know the filename pattern
‘not the full name.  That way I can still use the same front end code for
‘every local and remote PC.
strTarget = Dir("C:\OTS_Db\Rep\OTS_Rep?03.mdb")

'Now only connect to the slave if we have found one.
If strTarget <> &quot;&quot; Then

    'Loop through all the tables in the current db.
    For Each tdf In db.TableDefs
    
        'Check each table for a connect string
        With tdf
            If Len(.Connect) > 0 Then
                'Once found update it to point to the slave db previously found.
                .Connect = &quot;;Database=C:\OTS_Db\Rep\&quot; & strTarget
                
                'Refresh the link to make it stick.
                .RefreshLink
            End If
        End With
    Next
Else

    'Otherwise connect to the Master database on the G: drive
    'Loop through all the tables in the current db.
    For Each tdf In db.TableDefs
    
        'Check each table for a connect string
        With tdf
            If Len(.Connect) > 0 Then
                'Once found update it to point to the master db
                .Connect = &quot;;Database=G:\ots_be\ots_be03.mdb&quot;
                
                'Refresh the link to make it stick.
                .RefreshLink
            End If
        End With
    Next
End If


'Tidy up defined objects now we are done.
Set tdf = Nothing
Set db = Nothing

End Sub



Private Sub Form_Load()
Dim strFile As String, strSpec As String, sngStart As Single, sngEnd As Single, sngElap As Single
Dim intMins As Integer, intSecs As Integer

'Get the slave database name.
strFile = Dir(&quot;C:\OTS_Db\Rep\OTS_Rep?03.mdb&quot;)

'Set up the complete filepath for the location of the slave database.
strSpec = &quot;C:\OTS_Db\Rep\&quot; & strFile

'Look for a database backup from any previous compact and if found kill it.
If Dir(&quot;C:\OTS_Db\Rep\RepBak03.mdb&quot;) <> &quot;&quot; Then
    Kill (&quot;C:\OTS_Db\Rep\RepBak03.mdb&quot;)
End If

'Before carrying out a sync, complete a database repair and a compact.

'First do the repair operation
'N.B. Commented out as no longer required or valid for Access 2000 dbs
'DBEngine.RepairDatabase strSpec

'Then Compact the Replicated database to the RepBak backup file
DBEngine.CompactDatabase strSpec, &quot;C:\OTS_Db\Rep\RepBak03.mdb&quot;

'Then Kill the original slave database file as described in strSpec
Kill (strSpec)

'Then compact the RepBak file back to the original slave database file
DBEngine.CompactDatabase &quot;C:\OTS_Db\Rep\RepBak03.mdb&quot;, strSpec

'Now start the sync process.
'Let the user know what is going on.
lblStatus.Caption = &quot;Please wait - synchronisation with Master Database in progress.&quot;

'Set the timer start variable before begining synchronisation.
sngStart = Timer

'Call the routine which does the sync., passing the filespec for the target replicated db, ‘the filespec for the master db and an integer representing the type of sync to be ‘carried out
Call SynchronizeDBs(strSpec, &quot;G:\OTS_BE\ots_be03.mdb&quot;, 1)

'Now that the sync is finished get the end time.
sngEnd = Timer

'Work out the total elapsed time.
sngElap = sngEnd - sngStart

'Work out the number of minutes elapsed. (not too many hopefully)
intMins = Int(sngElap / 60)

'Work out the number of remaining seconds too.
intSecs = sngElap Mod 60

'Set up the results caption with the appropriate message.
lblStatus.Caption = &quot;Synchronisation completed in &quot; & intMins

If intMins = 1 Then
    lblStatus.Caption = lblStatus.Caption & &quot; minute and &quot; & intSecs
Else
    lblStatus.Caption = lblStatus.Caption & &quot; minutes and &quot; & intSecs
End If

If intSecs = 1 Then
    lblStatus.Caption = lblStatus.Caption & &quot; second.&quot;
Else
    lblStatus.Caption = lblStatus.Caption & &quot; seconds.&quot;
End If

'Enable the form close button now the sync is complete.
cmdOK.Enabled = True

End Sub

Sub SynchronizeDBs(strDBName As String, strSyncTargetDB As String, intSync As Integer)
    
    'This process carries out the required database synchronisation between the local slave
    'and the database Master.
    Dim dbs As Database
    
    Set dbs = DBEngine(0).OpenDatabase(strDBName)
        
    Select Case intSync
    Case 1  'Synchronize replicas (bidirectional exchange).
      dbs.Synchronize strSyncTargetDB, dbRepImpExpChanges
    Case 2  'Synchronize replicas (Export changes).
      dbs.Synchronize strSyncTargetDB, dbRepExportChanges
    Case 3  'Synchronize replicas (Import changes).
      dbs.Synchronize strSyncTargetDB, dbRepImportChanges
    Case 4  'Synchronize replicas (Internet).
      dbs.Synchronize strSyncTargetDB, dbRepSyncInternet
    End Select
    
    dbs.Close
    Set dbs = Nothing
    
End Sub