Synchronisation between MSDE database, UNIX and Access

From: AAVF IT (itdevelopment_at_aavf.co.uk)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 13:04:15 +0100

We are trying to build a database system, as follows.

Primary data source: UNIX server running CS3 (SageLine 500) in ISAM format,
connecting to PC users running MS Access via Easysoft ODBC.

We want a number of shop floor (factory) users to have wireless PCs with
barcode readers attached. These will be running an Access database
front-end, which links to an MSDE database table on a hard-wired, networked
PC. We have happily got this bit working. The problem we are having is
keeping the MSDE database synchronised with the CS3 ISAM tables on the UNIX
system. We need the MSDE and UNIX databases to stay in sync as far as data
records are concerned. The wireless PC users will not be changing data on
CS3, just viewing it. The other UNIX users in the plant will be altering the
CS3 data (adding/deleting/modifying records).

I have been advised that even if we manage to create an ODBC link between
CS3 and the MSDE database, the linked tables will not be visible to the
wireless users as this is a 3rd-party link (ie, their Access databases did
not create the link). We cannot have these wireless PCs talking to CS3 as
the response time will be too slow because of the amount of data in the
source tables.

My only thought so far is for the MSDE database server to also have Access
on it and for a routine to run at specified intervals that will update the
MSDE tables from CS3. The Access database would create a local copy of the
ISAM table, and would then synchronise this into the MSDE table. If this is
the only option, how can this be achieved without re-creating the MSDE
table, as we cannot do this in case a wireless user is in a record at the
time the table is rebuilt?

All (polite) suggestions welcomed.

Regards

Tom

Relevant Pages