Why should I split my database?

Splitting an Access application is essential whether or not you are using replication. Tony Toews explains it quite well for non-replicated applications:

http://www.granite.ab.ca/access/splitapp/why.htm

The Table of Contents of his detailed discussion of how to do it is here:

http://www.granite.ab.ca/access/splitapp/index.htm

Replication adds an even more important reason for splitting, and that is:

Replication of front-end objects DOES NOT WORK.

Now, you may think that Microsoft has suggested in its documentation that it works fine, and you'd be right. Indeed, in the article Things not to believe in the MS documentation I've highlighted where MS is wrong, and has corrected its documentation.

Why is replication not a good way to distribute changes to a front end application?

Why can't I email replicas/dropbox files for synchronization?

I can never connect my computer to a network. Can I still use Jet Replication?

What is a "dead replica?"

This Usenet post has a lengthy explanation:

http://groups.google.com/group/comp.databases.ms-access/msg/9f5a84194fa6c653

Here is the text from that message:

Path: g2news1.google.com!news3.google.com!news.glorb.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!cyclone1.gnilink.net!gnilink.net!cyclone.rdc-nyc.rr.com!news-out.nyc.rr.com!twister.nyc.rr.com.POSTED!not-for-mail
Newsgroups: comp.databases.ms-access
Subject: Re: Replication
From: "David W. Fenton" 
References: <0CZEd.13247$zy6.4535@bignews5.bellsouth.net>  
Message-ID: 
User-Agent: Xnews/5.04.25
Lines: 133
Date: Thu, 13 Jan 2005 00:07:36 GMT
NNTP-Posting-Host: 24.90.68.74
X-Complaints-To: abuse@rr.com
X-Trace: twister.nyc.rr.com 1105574856 24.90.68.74 (Wed, 12 Jan 2005 19:07:36 EST)
NNTP-Posting-Date: Wed, 12 Jan 2005 19:07:36 EST
Organization: Road Runner - NYC

"Andi Plotsky"  wrote in news:mf%Ed.14148$zy6.4813@bignews5.bellsouth.net:  

> Thanks for your speedy reply -
> 
> Maybe I should have explained the scenario better.  What I'm
> considering to be the 2 replicas are actually residing on laptops
> where the users will be gathering information from a variety of
> medical records in various cities. Periodically (daily?), they
> will want to send their databases back "home" - to update the
> Master.  Then the next morning, the new replicas (updated with the
> data from any user who has sent in data the previous day) will be 
> available for download from the server (or FTP site). 

Nope. It's not a valid replication scenario. It's wrong, it's not
supported, it will lead to loss of data.  

Read up on the website and newsgroup I cited, but here's the reason
why it's such a horrendous mistake to do it the way you're
describing: 

When a replica is created, it is given a unique GUID that is its
ReplicaID. That ReplicaID is associated with global location of that
replica, which would be its UNC path, or the machine name plus the
drive path. Say you have a Design Master, ReplicaID 1 on your home
server. You connect your two laptops to the network and via the
Access UI use direct replication to create a replica on each laptop.
The replica on Laptop1 in the folder C:\Databases is ReplicaID 2,
and the replica on Laptop2 in the folder C:\Databases is ReplicaID
3. 

If you rename or move a replica, the next time it is opened, Jet
checks if it still has the same name and is in the same location as
it was in the last time it was opened. If it's *not*, then it
generates a new ReplicaID and changes that replica's ReplicaID. From
your point of view as a human being, it's exactly the same data
file, but from the point of view of Jet replication, it's now a
completely fresh, previously unknown replica. 

So, when you email ReplicaID 2 from Laptop1 back to home base, as
soon as its opened, it becomes ReplicaID 3. When you email it or FTP
it back to Laptop1, it becomes ReplicaID 4. Each time you do this,
you change the ReplicaIDs, thus creating new replicas. 

Why is this a problem?

Well, it isn't necessarily unless there are any data errors in a
synchronization. Jet 4 eliminated data errors as separate from
conflicts, but they are a different kind of conflict. An example
would be if there were a collision on a primary key in two different
replicas -- you can't add the two new records from the two records,
since there can be only one record with a particular PK value. 

Now, say you generated this duplicate PK in ReplicaID 2 vs. a new
record inserted in ReplicaID 3. When you email those two replicas to
the home base and open them, you now have ReplicaID 4 (original #2)
and ReplicaID 5 (original #3). When you synch with the home base, it
records a data error (conflict) with records that came from
ReplicaID 4 and ReplicaID 5. Now, assume you are unable to resolve
the error, but decide it doesn't matter, and so you email the
replicas back to the laptops in the field. As soon as they are
opened, they are now ReplicaID 6 (original #2) and ReplicaID 7 (3).
You still have an unresolved data conflict. 

Now, you make more changes and email back to home base, which synchs
with what are now ReplicaID 8 (2) and ReplicaID 9 (3). Say that at
this point you notice the conflict. But the conflict was generated
between ReplicaID 2 and ReplicaID 3, but you *can't* resolve this
BECAUSE THOSE REPLICAS NO LONGER EXIST. 

The result is that:

1. the two replicas have a permanent data conflict that can only be
removed by someone who has the expertise to do so (and it can be
very time-consuming with large numbers of dead replicas). 

2. the two replicas HAVE DIFFERENT DATA IN THEM, a difference that
can never be resolved by synchronization. 

In other words, using the method of sending files back and forth,
you are creating a situation that is very likely to quickly produce
a situation where YOUR REPLICAS ARE NO LONGER IDENTICAL. 

You said you had lots of conflicts every day. This is no doubt
because of all these dead replicas (there should never be large
numbers of conflicts to resolve if they are only data). And these
errors multiply arithmetically. Each time you change the ReplicaIDs,
you multiple the number of data errors (since the replicas with the
new ReplicaIDs still have these records in them that have never been
successfully synched). With two such replicas, each time you synch,
you double the number of basic errors. 

And you don't know what state your data is actually in -- you have
no idea if it's the same in all three replicas or not. 

Are you beginning to see why it's a really bad idea to do what
you're doing? 

The only valid architecture for Jet replication is one in which the
PCs where the replicas live can be periodically connected via
network and synchronized across that network. There are three ways
for this to happen: 

1. direct replication: connect the laptop to the home base LAN, and
synchronize with the central replica. 

2. indirect replication: connect via network (LAN, WAN, dialup) and
synchronize via the Jet synchronizer (which sends and receives the
changes, rather than opening both data files across the network
connection as in indirect replication). 

3. Internet replication: using FTP for replicas that live on PCs
that have IIS installed, do indirect replication via FTP across the
Internet (instead of across an SMB LAN). 

ALL OTHER SCENARIOS WILL LEAD TO DATA CORRUPTION/LOSS.

> 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. 

-- 
David W. Fenton                        http://www.bway.net/~dfenton
dfenton at bway dot net                http://www.bway.net/~dfassoc

Can I use DAO to initiate an indirect synch in code?

No. In Jet 3.5 or 4.0, DAO can only do a Direct or Internet synch:Once you pass a constant for the second optional argument of the Synchronize method, the default, which is dbRepImpExpChanges, is no longer operative. The other two options are dbRepImportChanges and dbRepExportChanges.

  Dim dbLocal As DAO.Database

  Set dbLocal = DBEngine.OpenDatase("C:\PathToYourLocalReplica\LocalReplica.mdb")
  ' DIRECT SYNCH
  dbLocal.Synchronize "\\Server\Share\RemoteReplica.mdb"
  ' INTERNET SYNCH
  dbLocal.Synchronize "\\Server\Share\RemoteReplica.mdb", dbRepImpExpChanges + dbRepSyncInternet

  dbLocal.Close
  Set dbLocal = Nothing

To initiate an Indirect synch in code, you need the TSI Synchronizer (Jet 3.5 or 4.0), or in Jet 4.0, JRO.

  TSI SYNCHRONIZER CODE

  JRO CODE

Notes:

How can I get column-level conflict checking in a replica set converted from Jet 3.x to Jet 4.0?

When you convert from a Jet version before 4, the older default bahavior (row-level conflict tracking) is retained, so the ColumnLevelTracking property is set to FALSE. You cannot change this property in a replicated table. The only way to change the ColumnLevelTracking property is to unreplicate the table, uncheck the Row-Level Tracking checkbox in the table properties, then re-replicate it. This, of course, has to be done in the Design Master. Also, you can't unreplicate a table that participates in relationships, so you'll need to remove relationships, unreplicate, uncheck row-level tracking, and then re-replicate, and restore the relationships. The quick checklist:

1. using the database documenter, print out your existing relationships (for reference in making sure you restore all of them correctly after this process). This is found on the Tools menu under Analyze.
2. remove relationships to the first table you're going to change.
3. open the table properties sheet (the one you get by right clicking on the table in the database window, not the one you see in design view), and unreplicate the table.
4. open that same property sheet again and uncheck Row-Level Tracking (this will be set to TRUE because it's a converted replica set).
5. re-replicate the table.
6. examine the printout of relationships and restore the original relationships, OR, repeat the process for the tables that were originally participating in relationships with the table you've just changed.

For tables that have no relationships, you obviously skip steps 2 and 6. You might want to attempt step 3 first, because it will tell you if there are relationships, or just work from the printout created in step 1.

How can I tell if I have conflicts after a synchronization?

There are three different approaches to this. One looks for conflict tables in the TableDefs collection, one pokes into a system table to get a list of conflict tables, and the other looks at table properties of TableDefs.

1. Search TableDefs by name

  Dim dbReplica As DAO.Database
  Dim tdf As DAO.TableDef
  Dim strTableName As String
  Dim strConflictTables As String
  Dim strTablesWithConflicts As String
  
  Set dbReplica = DBEngine.Opendatabase([path/name of replica])
  For Each tdf In dbReplica
    strTableName = tdf.Name
    If Right(strName,9)="_Conflict" Then
       strConflictTables = strConflictTables _
          & ", " & strTableName
       strTablesWithConflicts = strTablesWithConflicts _
          & ", " & Left(strTableName,Len(strTableName)-9)
    End If
  Next tdf

  strConflictTables = Mid(strConflictTables, 3)
  strTablesWithConflicts = Mid(strTablesWithConflicts, 3)
  ' Do something with the two lists at this point

  Set tdf = Nothing
  dbReplica.Close
  Set dbReplica = Nothing

2. Use the MSysSideTables system table to get the list

  Dim dbReplica As DAO.Database
  Dim rs As DAO.Recordset
  Dim strSideTable As String
  Dim strConflictTables As String
  Dim strTablesWithConflicts As String
  
  Set dbReplica = DBEngine.Opendatabase([path/name of replica])
  strSQL = "SELECT MSysSideTables.SideTable FROM MSysSideTables"
  strSQL = strSQL & " ORDER BY MSysSideTables.SideTable;"
  Set rs = dbReplica.OpenRecordset(strSQL)
  With rs
    If Recordcount > 0 Then
       Do Until .EOF
         strSideTable = !SideTable
         strConflictTables = strConflictTables _
           & ", " & strSideTable
         strTablesWithConflicts = strTablesWithConflicts _
           & ", " & Left(strSideTable,Len(strSideTable)-9)
       Loop
    End If
  End With

  strConflictTables = Mid(strConflictTables, 3)
  strTablesWithConflicts = Mid(strTablesWithConflicts, 3)
  ' Do something with the two lists at this point

  rs.Close
  Set rs = Nothing
  dbReplica.Close
  Set dbReplica = Nothing

3. Search TableDefs for ConflictTable property

  Dim dbReplica As DAO.Database
  Dim tdf As DAO.TableDef
  Dim strConflictTables As String
  Dim strTablesWithConflicts As String
  
  Set dbReplica = DBEngine.Opendatabase([path/name of replica])
  For Each tdf In dbReplica
    if tdf.ConflictTable <> vbNullString Then
       strTablesWithConflicts = strTablesWithConflicts _
          & ", " & tdf.Name
       strConflictTables = strConflictTables _
          & ", " & tdf.ConflictTable
    End If
  Next tdf

  strConflictTables = Mid(strConflictTables, 3)
  strTablesWithConflicts = Mid(strTablesWithConflicts, 3)
  ' Do something with the two lists at this point

  Set tdf = Nothing
  dbReplica.Close
  Set dbReplica = Nothing

4. Use JRO to get the list of tables (code based on code in question 32 of the Jet 4.0 FAQ)

  Dim conn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim repRW As New JRO.Replica
  Dim strConflictTables As String
  Dim strTablesWithConflicts As String

  conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data source=C:\demo\NWindRW.mdb;"
  repRW.ActiveConnection = conn
  Set rs = repRW.ConflictTables
  rs.MoveFirst
  While NOT rs.EOF
    strTablesWithConflicts = strTablesWithConflicts _
       & ", " & rs(1)
    strConflictTables = strConflictTables _
       & ", " & rs(0)
    rs.MoveNext
  Wend

  strConflictTables = Mid(strConflictTables, 3)
  strTablesWithConflicts = Mid(strTablesWithConflicts, 3)
  ' Do something with the two lists at this point

  rs.Close
  Set rs = Nothing
  conn.Close 

Note that in Jet 4.0, conflicts are reported at all replicas, while in Jet 3.x, conflicts were reported only in the replica that had the losing record. Note also that before Jet 4.0, replication errors and design errors were reported in separate side tables, so if you're using the MSysSideTables method (#2), you will want to check which kind of side table is listed.

How does one set up indirect replication if you don't have Replication Manager?

See the article setting up indirect replication without Replication Manager.

I have a replicated MDB and I don't want it to be replicated any more. What do I do?

You need to unreplicate your MDB. Before doing so, you might want to synchronize with all the replicas to make sure you have all the data (though if you're contemplating unreplicating, you're unlikely to have been using replication recently). Microsoft provides instructions on how to do this manually:

• Access 2002 instructions (should work for A2003, too)

http://support.microsoft.com/kb/290052/

• Access 2000 instructions

http://support.microsoft.com/kb/208394/

• Access 97 instructions

http://support.microsoft.com/kb/153526/

The A97 article also has a download link for the Jet 3.5 unreplication wizard:

http://support.microsoft.com/kb/168398/

There are two other tools that also work for Jet 4 databases:

• Michael Kaplan's TSI Un-Replicator Add-in

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=7#7

Note that this utility works for all versions of Access that use Jet 4, even though it says it's for Access 2000. That means it works for Access 2000, 2002 (XP), 2003 and replicated MDBs created in Access 2007.

• Graham Search's Unreplicate Utility

http://www.pacificdb.com.au/MVP/Code/UnReplicate.htm

This utility has code, and you could perhaps learn something by reviewing the code, but I've never actually used it (I've hardly ever unreplicated anything). However, as of 2007/12/9, many people have reported problems with this utility, and I have been unable to make it work myself.

Can Jet Replication synchronize with database servers?

Yes! There are at least two platforms that Jet can participate with in "heterogeneous replication," one is MS SQL Server (before version 2005), and the other is IBM's DB2.

• For information on SQL Server heterogeneous replication see:

Replication (SQL Server 2000): Access Subscribers

(SQL Server 2005 discontinues support for heterogeneous replication, with MS now recommending just using SQL Server 2005 Express. Bloody idiots -- this is no doubt another aspect of their SharePoint promotion, as in the removal of Replication from the new ACCDB format in Access 2007. See Discontinued Functionality in SQL Server 2005 Replication)

• For DB2 see:

DB2 Information Center