Tip of the Month (March 2011)

Synchronizing Tables with Foreign Key (One-to-Many) Relationships

The Visual CE synchronizer synchronizes one table at a time. It completely synchronizes one table before synchronizing the next. If you have foreign key (one-to-many) relationships this may cause a problem.

What is that problem? If you have insertions, the the parent records have to be inserted before the child records are inserted. If you have deletions, the child records have to be deleted before the parent records are deleted. So there is no sequence of table synchronization that will work in all cases.

But, there are some work arounds:

• If you are only going to be inserting records (and not going to be deleting records), then synchronize the parent table before you synchronize the child table.
• If you are only going to be deleting records (and not going to be inserting records), then synchronize the child table before you synchronize the parent table.
• If you are going to be doing both record insertions and record deletions, your desktop/server database may have a "cascade delete" feature you can take advantage of. When enabled, deleting a record from the parent table will automatically delete the corresponding records from the child table. So, if you turn "cascade delete" on, the synchronizer will work for both insertions and deletions if you synchronize the parent table first and the child table second.

The only question that remains is how to control the order the tables are synchronized. In the windows folder of the desktop/server machine there is a file called WCEODBC.INI. Open this file with a text editor such as notepad. You will see a section called [VICESYNC] and a section called [VICEMNGR]. Ignore those two sections. The rest of the file contains one section for each table. The tables are synchronized in the same order that their sections appear in the WCEODBC.INI file. Carefully rearrange these sections to the desired order (we suggest that you make a backup copy of the WCEODBC.INI file before making any changes to it).