Developer Forum

I have a client running Access 2000 on a desktop (win 98) acting as
the 'server' and a Sony Viaio notebook (win 2000). They are
synchronising and
the data on the laptop is continually getting corrupted. The symptoms
are either relatively minor but very irritating such as a) not finding
records based on the primary key, so all records being displayed or b)
queries showing duplicate records (doubled up) for no reason
whatsoever, when all the joins are on unique indexes or, more
seriously and worst of all, all new records entered since the last
synchronisation suddenly disappearing from both sets of data after
synchronisation. When this happens, the records are usually to be
found in the 'conflicts' tables, although there can't be any
conflicts. After synchronising, the user is told there are conflicts
to resolve but when opening the conflict resolution tool no table
conflicts are shown.


Is it possible that the Office installation could be at fault? They
have a mixture of OEM versions, and not SP3. Or could it be because
the laptop
replication library file references are via the network on the Win 98
machine, due to the fact that the library files are on different
locations on the two machines. I think the only way to resolve that
is to upgrade the servier to W2000 or xp but client not keen as they
will probably need to upgrade the PC


It is driving both me and my client crazy - any suggestions very
welcome!


PS: already posted this in general access newsgroup

I know how you feel, I've been through that tour of duty. It lasted 6 weeks;
re-create replica set, try this, try that, re-create replica set, ... I
still somewhat blame Office as it worked perfectly on the dodgy network in
Access 97 but hello started when they upgraded to Access 2003. Access
Replication 2003 must be a whole lot more picky and less network friendly.

The problem likely is the network between the two computers. The data is
getting messed up and corrupting the databases.

Your solution is likely two parts, 1) get the physical network checked and
fixed. 2) Until then change to indirect replication, it was made for this
sort of environment.



I have a client running Access 2000 on a desktop (win 98) acting as
the 'server' and a Sony Viaio notebook (win 2000). They are
synchronising and
the data on the laptop is continually getting corrupted. The symptoms
are either relatively minor but very irritating such as a) not finding
records based on the primary key, so all records being displayed or b)
queries showing duplicate records (doubled up) for no reason
whatsoever, when all the joins are on unique indexes or, more
seriously and worst of all, all new records entered since the last
synchronisation suddenly disappearing from both sets of data after
synchronisation. When this happens, the records are usually to be
found in the 'conflicts' tables, although there can't be any
conflicts. After synchronising, the user is told there are conflicts
to resolve but when opening the conflict resolution tool no table
conflicts are shown.


Is it possible that the Office installation could be at fault? They
have a mixture of OEM versions, and not SP3. Or could it be because
the laptop
replication library file references are via the network on the Win 98
machine, due to the fact that the library files are on different
locations on the two machines. I think the only way to resolve that
is to upgrade the servier to W2000 or xp but client not keen as they
will probably need to upgrade the PC


It is driving both me and my client crazy - any suggestions very
welcome!


PS: already posted this in general access newsgroup

Thanks very much for your advice. When I checked the MSysConflicts
table, the reason for all the conflicts (and yes, the 'deleted' records
were on conflicts tables and the Conflict Viewer didn't pick them up)
was that the data was in a corrupted database. I had to manually merge
the data changes, no fun with more than 800 records in a dozen tables!

I will try indirect replication - am I right in that I have to use
Replication Manager?
The network guy was there today and claims no problems with the
network, but found damaged files on the notebook's hard drive, so they
will probably replace that machine...fingers crossed!

Cheval" < XXXX@XXXXX.COM > wrote in
news:LDT9e.19148$ XXXX@XXXXX.COM :


I would strongly disagree with this diagnosis.

1. first, make sure that both machines have Access 2K SR1 or later
(it may be called a service pack, I can't remember). Second, make
sure that you apply Jet 4 SP6 or later (the last I knew about was 8;
6 was stable, 7 was pulled quickly for bugs, and 8 added in some
additional security to avoid exposure to exploits that could execute
arbitrary code through the Jet expression service). Without that
scenario, you're guaranteed to have continued corruption issues, no
matter how stable your network.

2. I can't see how network issues could possibly cause corruption,
in any case, since if you're using the network to update the db on
the other computer, why in the world is replication needed in the
first place? Of course, you say you're using a Win98 workstation as
the peer-to-peer server, and a Win2K desktop connecting to it. I'm
pretty sure that's a problematic scenario. I don't remember the
details, but I think there were problems with Win2K connecting to
Win98 machines. And I always found the Win98 networking
infrastructure very hard to keep running well. So, you may need to
upgrade the peer-to-peer server to a real version of Windows (Win2K
would be just fine), or replace that workstation with a different
workstation running Win2K or WinXP (or NT 4, for that matter). On
the other hand, I had a client running with Win95 on the
peer-to-peer server and Win2K on their laptop, and it worked just
great. But, again, that just supports my experience that Win98 is an
outlier in terms of ease of networking.

3. I assume the basics, that you have a split front end/back end,
with forms, reports and so forth in the front end and only data
tables in the back end, and that you're only replicating the back
end. I also assume that you have some reason for replicating the
back end -- your explanation doesn't make clear to me why you'd have
any need for replication at all. Could it perhaps be that when the
laptop is in the office, it connects to the back end on the Win98
box, and when the laptop is on the road, it uses a replica on the
laptop's hard drive? That would justify replication and that's the
only replication scenario I use any longer with any of my clients
(all remote application sharing for my clients is now done via
hosting on Windows Terminal Server). If you have replicated the
front end project, that's likely to be your problem, as opening and
closing a form updates certain properties (especially if your users
are doing sorting and filtering, for instance, which are saved with
the form when it is closed), and these could automatically be
causing real conflicts in the data describing the structure of the
forms. To repeat: replication works only for pure Jet objects. That
means, data tables and queries only. All other objects are stored in
Jet, but are not Jet objects, but Access objects (forms, reports,
macros, modules), and should not be replicated.

4. more on conflicts: assuming that you are replicating only the
back end, let me address your assertions about the "unreal"
conflicts. Keep in mind that Jet replication uses not a
"human-sensical" definition of edits, but a mechanical count of
number of edits (i.e., generations of changes, where each generation
is an individual change). Here's a scenario that would produce a
conflict for the Jet replication

hanks to you all for your interest and help in this.

In response:

1. Upgrading to the latest Jet service pack I realise is essential and
also Office 2000 sp3.

2. The reason we are using replication is because the main user of the
system is constantly travelling around the globe and wants access to
the data. He probably enters and edits more data than those back in
the office, and I have thought of ditching the replication so that he
has the main data set and they have a read-only copy whilst he is away.
They are not too keen on that for now however. I have never been
particularly happy with using the Win98 machine as the server, although
I had a similar scenario at another client replicating too with a Win95
desktop and Win2000 laptop with no problems, as you too experienced.

3. The front and back end are split, the only objects being replicated
are the tables, one unbound form (which handles the process) AND one
module with the JRO code - perhaps that could be a problem? It has
caused difficulties because of the library files being in different
locations on these 2 pc's. Therefore, when synchronising the laptop
looks up the library files via the network on the win98 machine - maybe
that's not too clever?

4. As far as the conflicts are concerned, they were in all cases
records which had been added or updated on the laptop when away from
the network. I.e. all the new records he added appeared in the
conflict table, the reason in the MSysConflicts table given for all the
conflicts being to the effect that the 'record was found in a corrupted
database and could not be sychronised'. The thing is that although the
added/updated records appeared in the conflicts tables, they could not
be accessed by the user via the Conflict Viewer which stated that there
were no conflict tables. Almost all the 'conflict' records he had
edited had not been updated by the people in the office (the database
has an audit trail for all updates), it was only the generation number
which was different (and the changed data obviously). There are no
temp tables in the back end, they are all in the front end.

5. The conflict tables did not exist before this incident: I had
re-created the design master just before his trip because there was
another horrendous occurence: a networked WinXp machine was being used
to add items to a table (using a bar-code scanner) and I strongly
suspect there was a network interruption because all the entries in the
list box (which refreshed after each addition) apparantly showed
'#Error' and the data was then not available. When they attemtped to
repair the 'server' data, it proved too much of a task for Access and
it reverted to a 6-month old '.bak' file which had a fraction of their
data in it. At that point I went in and found that their data file had
been renamed 'Damaged.mdb' by Access. I removed the damaged fields and
had to recreate the design master and *manually* merge changed records
which were still on the laptop - something I had to do last week as
well, not much fun. So I do think they have network issues as well.
Their network person found a damaged and out of date version of Norton
Utilities on that machine, which he thought may have accounted for the
incident; I am not so sure.

I do stress to my client that they MUST compact/repair the replica both
before and after synchronising. They have a desktop shortcut to do it.
Whether they actually

"Cheval" < XXXX@XXXXX.COM > wrote in



But it's going to be the same kind of corruption you get with a
non-replicated MDB, not some special kind specific to replicated
DBs. The only exception for that is that replicas will often lose
replicability as the first sign of something flaky on a network,
while otherwise remaining completely uncorrupted (and not even
flagged as corrupt by Access).

If you can still synchronize, then it's not traditional corruption
of the kind caused by dropped connections.

--
David W. Fenton http://www.yqcomputer.com/ ~dfenton
dfenton at bway dot net http://www.yqcomputer.com/ ~dfassoc

Ondine" < XXXX@XXXXX.COM > wrote in
news: XXXX@XXXXX.COM :


It doesn't have to be SP3. There are important reasons to avoid SP3,
because that was the update where Microsoft introduced what is
called the Draconian Outlook security patch, which neutered your
ability to open most attachments (instead of actually addressing the
whole issue of attachment security in an adult manner). Many shops
that are still on Office 2K don't want that version of Outlook, and
to can't get SP3 of Access without getting the neutered version of
Outlook. That's why I keep SR1 handy for clients who use Outlook 2K
and don't want to give up their attachments.


In my experience, Win98 is a different animal, less reliably as P2P
server than the much older Win95 (any version).

I really hated Win98 and moved most of my client base from Win95 to
NT 4, with great success. It also meant that they were completely
ready for Win2K (no shocking experience dealing with real security
for the first time).


I would never use JRO. Instead, I'd use native DAO. I don't
understand why people muck around with ADO when they are working
with Jet data from an MDB. In that scenario, except for the tiny
handful of things ADO can do that DAO can't, there's no reason to
use ADO at all.

(of course, the rant above is based on my perhaps foggy understand
of exactly what JRO is; I assume that it's an extra library for Jet
Replication that is provided as an adjunct to ADO, since ADO can't
possibly know anything about Jet replication, any more than ODBC
would)


I don't know why you're using JRO. If you use DAO, you won't have
any issues, since Access does fine adjusting to different DAO
locations.


Unlike Jet 3.x replication, Jet 4 does not distinguish between a
data error and a data conflict. All data errors are treated just
like conflicts. This is what you're seeing, a data error showing up
as a conflict.

It seems quite obvious to me that you *must* resolve the corruption
issue before you can do anything else. You need to identify which
replica is corrupt and compact it. If you cannot get rid of the
corruption and you can't create a replica of the corrupted MDB, then
that MDB's data is lost to replication synchronization and the only
way to recover it is manually, using queries to copy data and update
records between the bad replica and the good one.

Again, you should go to http://www.trigeminal.com and read up on
Replica Farms. Properly implemented, a replica farm should mean that
you never have to worry about losing a replica (and its data) ever
again.


Conflict tables do not exist until there are conflicts. They persist
until all the conflicts in them are resolved, or until you manually
delete them (which is not a recommended solution).


I don't know that Access 2000 does any such thing. It sounds like
the application involved did this.


My experience with Norton is that, in general, it is much worse than
useless -- it tends to cause far more problems than it fixes.

It sounds to me like you have a very bad network setup, with
unreliable equipment. Replication is never going to be reliable if
your basic network is not reliable.


Can't you do it in code before you start the synchronization,
including making a backup beforehand? If you were using DAO it would
be quite easy.


They don't need the latest, only SR1 or higher.


Make sure you don't have duplicates of the replica

"David W. Fenton" wrote:

I take your point, although they don't use Outlook as the owner of this
business refuses to allow internet connections on pc's linked to his data,
which just makes my life even harder with this crew


I generally don't use ADO either, I'm wedded to DAO. As far as I can
remember when I set this up, there wasn't a DAO route to automating the sync
process (I probably need to check this)

It's funny that the Conflict Viewer didn't pick them up though.

That's exactly what I did, for 839 records. I have told them to work only
on the network data and not under any circumstances to synchronise until we
have upgraded their hardware/software. Interestingly, the corrupted mdb on
the laptop did open and function after the sync, as it did beforehand when it
was probably already corrupted in some way.


I have to inform myself about these farms.


No, it's not something I invented. I was very surprised to find it as I
haven't come across this before. It was either created after Access failed
to repair the file, or possibly, but less likely, when it because corrupted
in the first place.
This is definitely the case.


I'll look into that too.

I'd better look out for that.



Thanks again for your help.

Ondine.

Technically yes, but if it cannot replicate fully then I would also call it
a corruption.

In our case we couldn't replicate, due to a replication error. That I would
also call a corruption.



"Cheval" < XXXX@XXXXX.COM > wrote in



But it's going to be the same kind of corruption you get with a
non-replicated MDB, not some special kind specific to replicated
DBs. The only exception for that is that replicas will often lose
replicability as the first sign of something flaky on a network,
while otherwise remaining completely uncorrupted (and not even
flagged as corrupt by Access).

If you can still synchronize, then it's not traditional corruption
of the kind caused by dropped connections.

--
David W. Fenton http://www.yqcomputer.com/ ~dfenton
dfenton at bway dot net http://www.yqcomputer.com/ ~dfassoc

=?Utf-8?B?T25kaW5l?= < XXXX@XXXXX.COM > wrote in



There certainly are such commands in DAO, but only for direct
replication.

If you want to do indirect replication initiated in code, then you
need the TSI Synchronizer, from http://www.yqcomputer.com/ .

I wouldn't muck around with JRO for love or money.

--
David W. Fenton http://www.yqcomputer.com/ ~dfenton
dfenton at bway dot net http://www.yqcomputer.com/ ~dfassoc

=?Utf-8?B?T25kaW5l?= < XXXX@XXXXX.COM > wrote in



I'm just passing on help that's been given to me over the years,
back when I was struggling with the same issues.

If you haven't figure out, Michael Kaplan's website,
http://www.yqcomputer.com/ , is the essential website for Jet
replication, and the Google archives for this newsgroup cover just
about anything that has ever been attempted with Jet replication,
and every problem that has resulted.

--
David W. Fenton http://www.yqcomputer.com/ ~dfenton
dfenton at bway dot net http://www.yqcomputer.com/ ~dfassoc

...and over the years you have posted some of the best info on replication -
thanks.

"Vitaljus J. Karalius" < XXXX@XXXXX.COM > wrote in






I'm a piker in comparison to MichKa.

I've been doing replication on a regular basis again, and have also
noticed lots of replication questions in CDMA, so I've been trying
to make an effort to be sure that over there and in this newsgroup
questions about replication get answered now that MichKa has moved
on to other topics.

I am not the expert he is, but so far, not too much has come up that
I haven't already encountered.

I'm surprised at the number of people encountering replication for
the first time at this late date. My clients needed it way back in
1997, shortly after it was first introduced, and that's when I got
my feet wet.

And I made all the mistakes that everyone else makes, with the
exception of replicating the front end -- I'd never have thought of
that as I have always known to split front end/back end. And I
learned *that* from reading the Access 2 help file's articles on
running multi-user applications.

I guess I was lucky, since a lot of people miss that.

--
David W. Fenton http://www.yqcomputer.com/ ~dfenton
dfenton at bway dot net http://www.yqcomputer.com/ ~dfassoc