Database Replication - An Idiots Guide

Access replicas can be very useful for both data security, and for keeping up to date with mobile users.

For security purposes, if you have a copy of your main database on another drive/networked workstation, you can synchronise the two at regular intervals, so in the event of a disaster, the replica can be used to recover the data.

For mobile users with laptops, they take an up to date copy of the data out with them, and on return to base, synchronise with the main database to update their day's work.

To create a replica set, the database has to be converted to a Design Master. Extra tables are added, and extra fields added to existing tables. This is a one-way process. From the design master, additional replicas can be created, either on the same computer, or others connected to it. Changes to the structure of the database can only be carried out on the design master, except for addition of new tables. New tables can be added to any replica, but they remain local to the replica, and cannot be synchronised with other replicas. Only new tables added to the design master can be replicated to others, if required.

******WARNING TO ADO USERS*****

If you install this project and use the JRO (Jet and Replication Objects) as part of the ADO/ADOX/JRO library set, and install the project more than 7 folders deep you will get the following message whenever the JRO.Synchronise method is called:

Well Done Micro$oft! Plug your fantastic new ADO technology, then make it as full of bugs as all your other products. From experiments:

\\Laptop\SharedDocs\Folder001\Folder002\Sample Replica Database\Replica Of SampleReplicaDatabase.mdb THIS WORKS
\\Laptop\SharedDocs\Folder001\Folder002\Folder003\Sample Replica Database\Replica Of SampleReplicaDatabase.mdb THIS WORKS
\\Laptop\SharedDocs\Folder001\Folder002\Folder003\Folder004\Sample Replica Database\Replica Of SampleReplicaDatabase.mdb MESSAGE AS ABOVE

I beleive it is the location of the database(s) that matters, not the location of the application itself, but as by default the database is created in the App.Path, then this is where the error occurs. It DOES NOT happen with DAO. I tried fewer folders and longer pathnames, and that still works, so it is not the length of the string describing the path at fault. If anybody finds any information on this error I would be very grateful.

My advice? Use DAO!

***** END WARNING *****

This project is an extension of the Sample Database on this site, to include the creation of a Design Master database and one replica. You can then create further replicas as you wish, and synchronise them. The project can be downloaded Here. When starting the project, the following screen gives you a choice:

I have included both DAO and ADO/ADOX/JRO code into one project. You choose the method here, which sets a public boolean variable fDAO to True if you choose to use DAO. This is then used for the rest of the project. The reason for doing it this way is so you can make a direct comparison between the two technology code methods.

The code module modDatabase is used for this and other variables:

Option Explicit

'declare database variables as Public in a code module
'Then any form can use them.
Public db As DAO.Database
Public rs As DAO.Recordset

Public cat As New ADOX.Catalog
Public cnn As New ADODB.Connection
Public rst As New ADODB.Recordset
Public repSrc As New JRO.Replica

Public fDAO As Boolean

'Function to get \System Directory on different OS. Needed for accessing System.mdw file
'to allow reading of Access system tables
Private Declare Function apiSysDir Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Public Function SysDir() As String
   'function using api to get \System directory
   Dim Bufstr As String
   
   Bufstr = Space$(255)
   If apiSysDir(Bufstr, Len(Bufstr)) > 0 Then
      SysDir = Left$(Bufstr, InStr(Bufstr, Chr$(0)) - 1)
      If Right$(SysDir, 1) <> "\" Then
         SysDir = SysDir + "\"
      End If
   Else
      SysDir = ""
   End If
End Function