Applies To:
  • CitectSCADA 5.xx

I have an application where I think MS Access Database replicas might be a solution. I have written a small query but when I query a replicated table via an SQLExec() call, I get a "SQL Error 4149 Program Type Out of Range" error returned to SQLErrMSG(). The same query runs fine on the original table. Do you know what is causing this? And do you think this MS Access Replication feature is robust enough to be using on my system? 

In MS Access 95 and higher it is possible to replicate databases. Database replication is a technique you can use to support multiple users of an application. Replication is the process of creating multiple copies of an application and its data, to be used at locations that are not always connected to each other. Collectively, the copies are called a replica set. One member of the replica set must be designated as the Design Master; any other copy is a replica. Each replica contains a common set of replicated objects. Any single replica can also contain local objects that exist only in that replica. 

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


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 to have a useful collection of articles.