Unexpected problem with synchronisation

The unexpected problem is this - say, 2 pcs called A and B, both
running my Access program, both arranged in a FE/BE arrangement. There
is an unbound form in the program. When the form opens all the fields
are populated with the required data from the required record. When the
form is closed every field is saved back to the relevant record,
whether it changed or not.

I now realise this is wrong - eg on machine A a name field changes from
null to Mr Smith. Some time later the same record is edited on machine
B but the name field is left as null. Remember I use unbound forms and
all fields are saved on exit from the record. Now network the 2
machines and synchronize the BE, and the null value as saved on machine
B wins and so both machines then show a null. And Mr Smith is gone !!

The answer I presume is to change my save routine so that only changed
fields are saved.

As there are hundreds of fields here can anyone help with a smart way
to do this - some way of checking each field, seeing if it has changed
and saving it if it has. The good news is that every field name on the
form matches every field name in the table.

I hope I have explained this well enough. Any help here would be much
appreciated.

Thanks
Dave

re: Unexpected problem with synchronisation

"Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
news:1148335949.188523.315120@y43g2000cwc.googlegr oups.com:
[color=blue]
> I now realise this is wrong - eg on machine A a name field changes
> from null to Mr Smith. Some time later the same record is edited
> on machine B but the name field is left as null. Remember I use
> unbound forms and all fields are saved on exit from the record.
> Now network the 2 machines and synchronize the BE, and the null
> value as saved on machine B wins and so both machines then show a
> null. And Mr Smith is gone !!
>
> The answer I presume is to change my save routine so that only
> changed fields are saved.
>
> As there are hundreds of fields here can anyone help with a smart
> way to do this - some way of checking each field, seeing if it has
> changed and saving it if it has. The good news is that every field
> name on the form matches every field name in the table.[/color]

What happens if the user *wants* to change a field to be blank? You
can't ignore that, since it's a real data change.

However, why in the world are you using an unbound form in the first
place?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unexpected problem with synchronisation

David

Firstly, the reason I used an unbound form in this particular
application was - well I can't actually remember now. But I do remember
that I went through the pain of converting from bound to unbound, and I
was glad I did because it solved all my problems, whatever they were at
the time. Generally I feel that I am in better control with unbound
forms despite the extra effort.

I think my example of null was a red herring. Please ignore. Here's the
problem again as reported by the client:

1) Synchronise replicas
2) In the replica (ie on a laptop) make a change to a field in the
carer table. e.g. change the surname of a carer.
3) Close the record.
4) On the base station open up that carer record and close it without
making any changes.
5) Synchronise replicas

The data on both the replica and the base station will now be without
the change made to the replica. It seems that the opening and closing
of the record without changes wins the synchronisation conflict with
the previously changed record.

Now, I'm assuming that this problem occurs because when I close the
unbound form I save all fields back to the table. If I'm right so far,
then I'm also assuming that the way to solve it is to only save fields
that have changed. So that's what I'm looking to do, but I'm trying to
find a neat way of doing it rather than some horribly long winded way.

Dave

re: Unexpected problem with synchronisation

"Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
news:1148393708.623609.39690@i39g2000cwa.googlegro ups.com:
[color=blue]
> David
>
> Firstly, the reason I used an unbound form in this particular
> application was - well I can't actually remember now. But I do
> remember that I went through the pain of converting from bound to
> unbound, and I was glad I did because it solved all my problems,
> whatever they were at the time. Generally I feel that I am in
> better control with unbound forms despite the extra effort.
>
> I think my example of null was a red herring. Please ignore.
> Here's the problem again as reported by the client:
>
> 1) Synchronise replicas[/color]

Are you talking about replicated Jet databases?
[color=blue]
> 2) In the replica (ie on a laptop) make a change to a field in
> the carer table. e.g. change the surname of a carer.
> 3) Close the record.
> 4) On the base station open up that carer record and close it
> without making any changes.
> 5) Synchronise replicas
>
> The data on both the replica and the base station will now be
> without the change made to the replica. It seems that the opening
> and closing of the record without changes wins the synchronisation
> conflict with the previously changed record.[/color]

You're now talking about something else entirely.

The resolution of replication conflicts follows strict rules. In Jet
3.5, it was "least decisive user wins" (i.e., the record with the
most changes in it), while in Jet 4, it's substantially more complex
(because conflicts are resolved at field level, rather than at
record level), and involves replica priority. There's an easily
found Knowledge Base article that explains how replica priorities
are set. Replicas with higher priorities win. The design master has
higher priority than all replicas, and any replica made direct from
the design master will have the same priority. Replicas made from a
replica of the DM will have a lower priority (and so forth).

You can also create custom conflict resolution rules that treat the
records according to your own rules.
[color=blue]
> Now, I'm assuming that this problem occurs because when I close
> the unbound form I save all fields back to the table. If I'm right
> so far, then I'm also assuming that the way to solve it is to only
> save fields that have changed. So that's what I'm looking to do,
> but I'm trying to find a neat way of doing it rather than some
> horribly long winded way.[/color]

Well, never save a record that hasn't changed.

I always populate an unbound form from a recordset. I then retain
that recordset and compare the values at SAVE time to that original
recordset (make it a snapshot so it won't refresh when other users
edit the data).

It's important in replicated databases to not force saves when no
data has actually changed because, as you seem to have discovered,
this can screw up conflict resolution.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unexpected problem with synchronisation

David

Would you mind telling me in a little more detail about how you do
this. I have a function called ShowRecord(ID) which uses FindFirst to
fill a recordset with the required record, which is then used to fill
the form. I have another function called SaveRecord(ID) which more or
less does the opposite. How and where do you create and save a
snapshot, and then how do you compare them at save time - sorry, I just
can't see how to do this even though I like the idea.

Thanks
Dave

David W. Fenton wrote:[color=blue]
> "Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
> news:1148393708.623609.39690@i39g2000cwa.googlegro ups.com:[/color]

<snip>
[color=blue]
>
> Well, never save a record that hasn't changed.
>
> I always populate an unbound form from a recordset. I then retain
> that recordset and compare the values at SAVE time to that original
> recordset (make it a snapshot so it won't refresh when other users
> edit the data).
>
> It's important in replicated databases to not force saves when no
> data has actually changed because, as you seem to have discovered,
> this can screw up conflict resolution.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

re: Unexpected problem with synchronisation

"Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
news:1150313564.166992.315250@c74g2000cwc.googlegr oups.com:
[color=blue]
> David W. Fenton wrote:[color=green]
>> "Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
>> news:1148393708.623609.39690@i39g2000cwa.googlegro ups.com:[/color]
>
><snip>
>[color=green]
>> Well, never save a record that hasn't changed.
>>
>> I always populate an unbound form from a recordset. I then retain
>> that recordset and compare the values at SAVE time to that
>> original recordset (make it a snapshot so it won't refresh when
>> other users edit the data).
>>
>> It's important in replicated databases to not force saves when no
>> data has actually changed because, as you seem to have
>> discovered, this can screw up conflict resolution.[/color]
>
> Would you mind telling me in a little more detail about how you do
> this. I have a function called ShowRecord(ID) which uses FindFirst
> to fill a recordset with the required record, . . .[/color]

Why use a FindFirst? Why not use a WHERE clause that returns the
single record you want? If you use a FindFirst, you have to
constantly update the recordset to be sure you're getting the most
current data.
[color=blue]
> . . . which is then used to fill
> the form. I have another function called SaveRecord(ID) which more
> or less does the opposite. How and where do you create and save a
> snapshot, and then how do you compare them at save time - sorry, I
> just can't see how to do this even though I like the idea.[/color]

When you load a record, you open a recordset of Snapshot type with
the data for the one record you want. You then leave that open in
memory. When you save, you open a new dynaset recordset filtered to
the single record, and loop through its fields. You compare the data
in the controls on the forms to the data in the corresponding fields
in the snapshot. If it's changed, you then check it against the data
in the dynaset (which should be current values). If it's the same as
the snapshot, you're safe to change it. If it's different from the
snapshot, you may or may not choose to tell the user that the data
changed since they started editing it.

There are a number of UI issues with latter, but I in some
circumstances it's preferable to overwriting the changes silently.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unexpected problem with synchronisation

David

Firstly, thanks very much for your help. It is much appreciated. But
now I have a couple more questions - hope you don't mind.

1. I know how to use the where clause for a bound form, but how do I
for an unbound one - like here. And I don't understand the bit about
constantly updating. Surely in the case of an unbound form you find the
record you require, fill the form with data from that record, and
that's it until it's time to close the form, when you either save the
changes or throw them away. Or am I missing something that I should
know about.

2. Does synchronisation work down to the field level? In other words,
if the synch process sees that a record has changed, does it only
'deal' with the fields that have changed within that record, and leave
the rest alone?

Dave


David W. Fenton wrote:[color=blue]
> "Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
> news:1150313564.166992.315250@c74g2000cwc.googlegr oups.com:[/color]
[color=blue][color=green]
> >
> > Would you mind telling me in a little more detail about how you do
> > this. I have a function called ShowRecord(ID) which uses FindFirst
> > to fill a recordset with the required record, . . .[/color]
>
> Why use a FindFirst? Why not use a WHERE clause that returns the
> single record you want? If you use a FindFirst, you have to
> constantly update the recordset to be sure you're getting the most
> current data.
>[color=green]
> > . . . which is then used to fill
> > the form. I have another function called SaveRecord(ID) which more
> > or less does the opposite. How and where do you create and save a
> > snapshot, and then how do you compare them at save time - sorry, I
> > just can't see how to do this even though I like the idea.[/color]
>
> When you load a record, you open a recordset of Snapshot type with
> the data for the one record you want. You then leave that open in
> memory. When you save, you open a new dynaset recordset filtered to
> the single record, and loop through its fields. You compare the data
> in the controls on the forms to the data in the corresponding fields
> in the snapshot. If it's changed, you then check it against the data
> in the dynaset (which should be current values). If it's the same as
> the snapshot, you're safe to change it. If it's different from the
> snapshot, you may or may not choose to tell the user that the data
> changed since they started editing it.
>
> There are a number of UI issues with latter, but I in some
> circumstances it's preferable to overwriting the changes silently.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

re: Unexpected problem with synchronisation

"Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
news:1150357011.473087.313260@p79g2000cwp.googlegr oups.com:
[color=blue]
> Firstly, thanks very much for your help. It is much appreciated.
> But now I have a couple more questions - hope you don't mind.
>
> 1. I know how to use the where clause for a bound form, but how do
> I for an unbound one - like here. . . .[/color]

You write your SQL for the recordset you're opening to be the same
as the SQL with WHERE clause that you'd use for the recordsource of
the form.
[color=blue]
> . . . And I don't understand the bit about
> constantly updating. Surely in the case of an unbound form you
> find the record you require, fill the form with data from that
> record, and that's it until it's time to close the form, when you
> either save the changes or throw them away. Or am I missing
> something that I should know about.[/color]

Well, since it's an unbound form, I'd assume it's not a single-user
database (otherwise, there's virtually no justification for using an
unbound form). What you're testing for is whether *other* users have
updated the data after the record was loaded and before it was
saved.
[color=blue]
> 2. Does synchronisation work down to the field level? In other
> words, if the synch process sees that a record has changed, does
> it only 'deal' with the fields that have changed within that
> record, and leave the rest alone?[/color]

What does "synchronisation" have to do with it? This is not
replication (I hope), so let's not use that term.

All you're doing is opening a copy of the record, keeping it in
memory in the state it was before the user got a chance to update
it, and when the user saves the changes, you check the unbound
controls on the form against the corresponding fields in the saved
recordset. That means you'd change only the fields that the user has
altered.

If none of this makes any sense to you, then I question your
competence to be writing an unbound application.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unexpected problem with synchronisation

David W. Fenton wrote:[color=blue]
> "Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in
> news:1150357011.473087.313260@p79g2000cwp.googlegr oups.com:
>[color=green]
> > Firstly, thanks very much for your help. It is much appreciated.
> > But now I have a couple more questions - hope you don't mind.
> >
> > 1. I know how to use the where clause for a bound form, but how do
> > I for an unbound one - like here. . . .[/color]
>
> You write your SQL for the recordset you're opening to be the same
> as the SQL with WHERE clause that you'd use for the recordsource of
> the form.
>[color=green]
> > . . . And I don't understand the bit about
> > constantly updating. Surely in the case of an unbound form you
> > find the record you require, fill the form with data from that
> > record, and that's it until it's time to close the form, when you
> > either save the changes or throw them away. Or am I missing
> > something that I should know about.[/color]
>
> Well, since it's an unbound form, I'd assume it's not a single-user
> database (otherwise, there's virtually no justification for using an
> unbound form). What you're testing for is whether *other* users have
> updated the data after the record was loaded and before it was
> saved.
>[color=green]
> > 2. Does synchronisation work down to the field level? In other
> > words, if the synch process sees that a record has changed, does
> > it only 'deal' with the fields that have changed within that
> > record, and leave the rest alone?[/color]
>
> What does "synchronisation" have to do with it? This is not
> replication (I hope), so let's not use that term.
>
> All you're doing is opening a copy of the record, keeping it in
> memory in the state it was before the user got a chance to update
> it, and when the user saves the changes, you check the unbound
> controls on the form against the corresponding fields in the saved
> recordset. That means you'd change only the fields that the user has
> altered.
>
> If none of this makes any sense to you, then I question your
> competence to be writing an unbound application.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/[/color]

Dave G,

It was probably too obvious for David to mention, but don't forget to
check whether or not the record timestamp has changed since getting the
data before doing anything else.

James A. Fortune
CDMAPoster@FortuneJames.com

re: Unexpected problem with synchronisation

CDMAPoster@FortuneJames.com wrote in
news:1150425726.285379.183080@i40g2000cwc.googlegr oups.com:
[color=blue]
> It was probably too obvious for David to mention, but don't forget
> to check whether or not the record timestamp has changed since
> getting the data before doing anything else.[/color]

That assumes that there's a timestamp in the record. I don't use
them myself, so did not mention it. But if I did, I don't think I'd
depend on it, as it assumes that all the workstations updating the
data have their clocks set properly. While it would be great if
every workstation synched with a time server every day, unless you
know for certain that every workstation does so, you can't rely on
the timestamp field being accurate. Thus, you have to check every
field value, which to me seems to make the timestamp field of little
value for this particular purpose.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/