See http://msdn.microsoft.com/library/techart/dbrepjet.htm - Database Replication with the Microsoft Jet Database Engine: A Technical Overview, for an excellent description of this technology and how and when to implement it.

Microsoft Access for Windows 95 supports full replicas, which contain all of the data in the original database. In Microsoft Access 97 and 2000 you can use Data Access Objects (DAO) properties and methods to create a partial replica, which contains a subset of the data in a full replica. You can choose which tables, queries, forms, reports, macros, and modules you wish to replicate from the database. Partial replicas can have several advantages over full replicas including faster synchronization speeds, small disk space requirements and reduced network traffic.

One way to create a replica is simply to drag you Access mdb file to My Briefcase on the Desktop. The Replica and the Design Master Database both get modified in this process. Additional "system" fields are added to the tables which have been designated to be replicable. These include:

Field Description

s_GUID A globally unique identifier for each record.

s_Lineage A binary field that contains information about the history of changes to each record.

s_Generation A field that stores information regarding groups of changes.

s_ColLineage

You can view these additional fields in your table by opening the table in the Design Master or the Replica and going to Tools Menu- Options and on the View Tab tick the System Objects and Hidden Objects check boxes.

When you run a query such as:

SQLExec(hSQL, "SELECT * FROM tblMyTable WHERE milk = '" + sProduct + "'") on either the Design Master or the Replica database, the wild card means that these "system" fields are included in the search. When you call SQLNext(hSQL) to get the first record Citect returns the above SQL error 4149 (as trapped via SQLErrMSG( )).

The field Citect is having a problem with is s_GUID (this field may be renamed by the User) Citects SQLNext() function is having difficulty interpreting the data type associated with the s_GUID field. The s_GUID field has a unique identifier and it's Field Size is deemed "Replication ID". All the other fields have Field Sizes such as "Long Integer" for Autonumber fields or "50" for Text fields and so on. It is not possible to change a "Replication ID" to another datatype such as "Long Integer". Citect does not recognize Replication ID to be a valid data type.

This error can be eliminated by avoiding the use of wildcards in SELECT Statements. ie do not use:

SQLExec(hSQL, "SELECT * FROM tblMyTable WHERE milk = '" + sProduct + "'")

but use this instead:

SQLExec(hSQL, "SELECT tblMyTable.ID, tblMyTable.flour, tblMyTable.milk, tblMyTable.sugar FROM tblMyTable WHERE milk = '" + sProduct + "'")

Citect currently uses some ODBC libraries which do not support this new repliction functionality. We are researching this problem and will post new information here as it becomes available.

When Should I Use Replication?

Replication is well-suited to distributed systems that focus primarily on adding new records rather than on updating existing records. There are many tools and techniques for implementing replication. Some factors to consider when choosing a replication technique are:

· How quickly you need data synchronized across all sites.

· Budget for hardware, software, and communication services.

· Overall system-reliability requirements.

The best candidates for replication are applications that can tolerate some latency in data updates in exchange for a robust configuration that can allow updates from any replica and that supports users who are only occasionally connected. This flexibility means the system can work more effectively, potentially improving performance. When the application's users are connected, it might be through a direct connection on a local area network (LAN) or wide area network (WAN), or through the Internet or an intranet. Data can be exchanged on a LAN, a WAN, or the Internet.

Microsoft Jet replication is a good solution if you want to:

· Share data among users at multiple remote locations.

· Automate the distribution of new features and updates to multiple users.

· Use different machines for system queries and transaction processing (this can improve transaction-processing performance).

· Automatically back up data without disabling the system (each replica serves as a backup, so a separate backup procedure is not needed).

Note that at the time of writing, the Microsoft(r) Jet database engine version 4.0 which provides this database processing and replication functionality was used for testing along with MS Access 2000 . Microsoft tools/applications are subject to change and you should refer to the Microsoft website and documentation for the latest information. I have found http://www.microsoft.com/accessdev/a-a&sa.htm to have a useful collection of articles.