Repairing Synchronized Databases

[Tara]

Hi,
When you are forced to repair a synchronized Access 97 database (due to the
famous "Unrecognized Database Format" error), whether it is the Design Master
or the Replica, the database is no longer replicable. I am getting this
error two or three times a day and being forced to rollback and loose updates
to records.

First, what are the most common causes for this error and how do I decrease
the occurance of it?

Second, is there anything I can do to keep a database replicable after being
forced to repair it?

Please Help...loosing production time....

Thank you.
Tara

[Ted McNeal]

Tara,

I’ll answer your questions then offer a general suggestion that is truly
worth the effort.

JET databases are inherently exposed to file system or network problems.
The most problematic tables are those with memo or blob fields. Writing
to these fields from a networked computer can span more than one packet.
The issue with replicable tables is that they all have a system field ‘s_Lineage’
which is a blob.

I have seen issues such as bad cat-5 cables, malfunctioning OS on either
the client or file server, and a bad hub cause the database to crash. Nearly
all of the time the problem occurs in a memo or blob field. You can determine
the culprit by reading the ldb file using the Microsoft utility ldbview.exe.
When a database is flagged as suspect no engine will remove the associated
ldb file. This allows you to perform such an inspection even though all
users are logged out of the database. The workstation that caused the database
to become corrupted will read yes under the column suspect where the workstation’s
name is the user name.

Sometimes, the database that you restore has issues, either structural or
data related, and will eventually be flagged as suspect.

The idea behind making a database not replicable upon repair is that the
database may still have issues that could pollute the design master and other
replicas. Hence, you cannot restore a database without it being flagged
as not-replicable, as this is an intentional action on the part of the engine’s
repair process.

One thing you can do is to setup a replica farm at each instance of a replica
and at the hub. A replica farm consists of two or more (usually two or three)
replicas that co-exist on a specific computer and are set to synchronize
with each other every 15 minutes. This schedule is set by using the Jet
replication manager, which is a component of the Office 97 ODE tools. All
users will be designated to use only one of the replicas. Whereas the others
act as warm backups in the event that the user’s database is flagged as suspect.

One thing you can do to hedge against data loss in the replica set, is to
implement a field that records the date and time when the record was last
updated and move the data to a warm backup. The problem with a Jet database
is that it does not support triggers, hence the aforementioned implementation
must occur in the application. Of course you have the option of implementing
this on only critical-frequently updates tables.

As for my suggestion, I would strongly advise you begin thinking about porting
your database to SQL Server and implement merge replication. You can get
white papers on setting up merge replication from Microsoft www.microsoft.com/sql
or from Mr. Hotek’s website www.mssqlserver.com.

I still use JET databases for moving data and for some specialized data transferring
that require the mobility and security of a jet database. I also use Jet
as part of my MS Access development in which I use ODBC Direct to communicate
with SQL Server. Yet, I truly enjoy working with SQL Server and merge replication.
The stability and functionality cannot begin to compare with Jet.

Ted McNeal
tmcneal@mhsinc.org





"Tara" <tarac@execpc.com> wrote:
>
>Hi,
>When you are forced to repair a synchronized Access 97 database (due to
the
>famous "Unrecognized Database Format" error), whether it is the Design Master
>or the Replica, the database is no longer replicable. I am getting this
>error two or three times a day and being forced to rollback and loose updates
>to records.
>
>First, what are the most common causes for this error and how do I decrease
>the occurance of it?
>
>Second, is there anything I can do to keep a database replicable after being
>forced to repair it?
>
>Please Help...loosing production time....
>
>Thank you.
>Tara

[Robert]

"Tara" <tarac@execpc.com> wrote:
>
>Hi,
>When you are forced to repair a synchronized Access 97 database (due to
the
>famous "Unrecognized Database Format" error), whether it is the Design Master
>or the Replica, the database is no longer replicable. I am getting this
>error two or three times a day and being forced to rollback and loose updates
>to records.
>
>First, what are the most common causes for this error and how do I decrease
>the occurance of it?
>
>Second, is there anything I can do to keep a database replicable after being
>forced to repair it?
>
>Please Help...loosing production time....
>
>Thank you.
>Tara


Tara,

I have had this same problem, the only thing I could track down was network
problems eventually I had to move my DB to SQL Server, this solved the problem
as SQL Server is much more reliable. However short of that you might try
the Jetcomp.exe utility (download from microsoft) Jetcomp.exe is able to
repair .mdb files more successfuly than native compact/repair.

Good luck