If you're considering keeping two sets of data on the PC side, why not
have the "active" database and a set of tables that only keep records
that have changed since the last sync?  When you update the main tables,
also add the record to the update version of the table.  Then you just
do "SELECT * FROM tblCurrentTable WHERE uniqueId IN (SELECT uniqueId
FROM tblLastSyncTable).  Of course I realize this won't work if your
tables don't have some sort of unique indentifier.



Eric Pankoke

Founder

Point Of Light Software

http://www.polsoftware.com/



-----Original Message-----
From: %%email.bounce%% [mailto:%%email.bounce%%] On Behalf Of Jonathan
Carse
Sent: Monday, February 11, 2008 11:41 PM
To: Conduit Developer Forum
Subject: Re: Performing fast sync with Access database



Hi,

Yes, indeed that seems to be the most logical solution.

However -
Before I joined my company and wrote the conduit for them, they used
Appforge's Universal Conduit.
This is a conduit written by Appforge (I think they were purchased by
Oracle) that creates a mirror of an Access database (well, any database
that can be DSN'ed into the system, actually) onto a Palm device.
And that conduit worked fantastic -

1.      If no changes were made to either side, the synchronization will
not take any time at all

2.      If changes were made, only the affected records will be sync'ed.

3.      If changes were made to the same record on both sides, the
changes will be merged, so no data is lost.

All of these were accomplished excellently without manually embedding a
dirty bit.
The third bullet above indicates that there is more than just a dirty
bit set - it keeps track of every field, not just every record. Sort of
a dirty bit for each field, if you will.


So I'm assuming Appforge found a way to track after the data cleverly
enough to make synchronizations fast and accurate, without having to
modify the existing database, or rely on a stronger database engine than
Access.

My bet is that they make a copy of the database each time it is sync'ed.
Then, on the next sync, they have 3 versions for each record - the PC's,
the Palm's, and the record that was saved after the last sync. That way
they know which fields were changed on each side.
I tried to do the same thing -
I made a copy of my MDB file, and used the linked tables option to link
my two MDB files. So now I have two versions for each table - the
current table and the table that will be saved after each sync.
But now I'm stuck because I can't think of a fast enough SELECT clause
that will pull only the modified records out of the current table.
If Access supported the "EXCEPT" SQL operator
(http://msdn2.microsoft.com/en-us/library/ms188055.aspx), it could be
done using the following simple query:

SELECT * FROM tblCurrentTable
EXCEPT
SELECT * FROM tblLastSyncTable

Does anybody have any better ideas?
I really don't want to start adding dirty bits to all my tables, and I
want a much more elegant and generic solution to the problem.

If you're wondering why I'm not just using Appforge's solution, the
answer is that they only support creating a mirror between the Access
database and the palm database. I had to write a conduit that
synchronizes only part of the "big" Access database to the "small" palm
database.

Thanks

2008/2/12, epankoke@comc... <epankoke@comc...>:

I worked on a project once that used an Access Database, and what we did
was create our own "dirty" flag at the end of the record.  Basically, a
one byte flag that was 0 for unchanged and 1 for changed (we actually
had a more complex setup, but this would be the basic route to take).
Then all you have to look at on the Access side is the recordset where
DirtyFlag = 1.


--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/







--
For information on using the ACCESS Developer Forums, or to unsubscribe,
please see http://www.access-company.com/developers/forums/

--
For information on using the ACCESS Developer Forums, or to unsubscribe, please see http://www.access-company.com/developers/forums/