473,597 Members | 2,829 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Database Replication A97 vs A2003

I developed 3 different replicated database applications in MS Access for 3
different corporate clients in Access 97 several years ago to enable data to
be shared across wide area networks. I observed 2 issues that plagued all 3
systems.

1. the database files continually became corrupt during synchronization .
Most times this was easily fixed via a repair and compact, but the frequency
with which this occurred was of concern, and occasionally the file would not
respond to a repair and would have to be rebuilt.

2. the conflict logs continually swell with bogus conflict records.

Based on this I tended to avoid recommending that any other clients develop
a system using replicated access databases. However I have recently been
approached by a client interested in converting an existing application to a
replicated system. This system is in Access 2003. Does anyone have any
experience in replicating A2003 databases? Has MS improved this at all
between 97 and 03?


Nov 13 '05 #1
1 1821
"Andrew Chanter" <he****@radsolu tions.com.au> wrote in
news:O%******** ***********@new s-server.bigpond. net.au:
I developed 3 different replicated database applications in MS
Access for 3 different corporate clients in Access 97 several
years ago to enable data to be shared across wide area networks.
I observed 2 issues that plagued all 3 systems.

1. the database files continually became corrupt during
synchronization . Most times this was easily fixed via a repair and
compact, but the frequency with which this occurred was of
concern, and occasionally the file would not respond to a repair
and would have to be rebuilt.

2. the conflict logs continually swell with bogus conflict
records.
Bogus? In what sense? They may look identical to *you*, but to Jet,
they aren't. Remember, Jet replication uses most-edited record (in
Jet 3.5, which is what was available in A97) to determine which
record wins when there is a tie between the records. In Jet 4 (A2K
and higher), this operates at the field level, but is still based on
most changes.

To put it another way, say this is the history:

Generation Replica 1 Replica 2
1 Value1 Value1
2 Value 1
3 Value1

Now, from a human point of view, the final state of the two records
is identical, as "Value1" = "Value1". But that's not the way it
works -- because Replica 1 has a higher generation number, it wins.

Now, assume you had an equal number of changes in both replicas
(which is what is required to have a conflict show up), and both
ended up with "Value1" as the end data value. To *you* there's
nothing to resolve, as the data in the fields is identical, but Jet
can't know that. To it, there have been 3 changes on both sides, and
it needs to ask a human being to fix it.

My guess is that you have an application that is saving records more
often than it needs to do so (each save is a generation, even if the
data hasn't been altered). The first thing to do is to check your
application to see if you have code that is saving records without
first checking if the record needs to be saved. The easiest way to
do this is to replace any code that saves records with this:

If Me.Dirty Then Me.Dirty = False

That will only save the record if it has been edited.

Now, if you have code that happens to be somehow writing data that
is unchanged (say, writing "Value1" into a field whose existing
value is already "Value1"), then you need to eliminate that as well.

I have never seen a properly designed replicated application that
was suitable for replication in the first place (i.e., a single
record under normal usage is only very rarely edited in more than
one replica) that produces very many conflicts. So my guess is that
your application has a design flaw that is producing these
conflicts, especially since from a human standpoint, the conflicting
records are identical.

(also, make sure your application is split, and don't under *any*
circumstances replicate the front end -- this can lead to all kinds
of conflicts and problems that very often lead to the loss of the
whole replicated project to corruption; additionally, make sure
you're not emailing or copying replicas around -- that's a
non-supported scenario destined to corrupt your replica set
eventually, or, at the very least, leave you with replicas that you
can't guarantee have the same data in them)
Based on this I tended to avoid recommending that any other
clients develop a system using replicated access databases.
However I have recently been approached by a client interested in
converting an existing application to a replicated system. This
system is in Access 2003. Does anyone have any experience in
replicating A2003 databases? Has MS improved this at all between
97 and 03?


I don't use replication any more except in one scenario, when there
are laptop users who have to work in the field and edit/add data
without a live Internet connection. For all remote users who can be
connected to the Internet, I host the application on Windows
Terminal Server.

In the laptop situation, if the users can wait to get back to the
office to synch with the mother ship, then they can do it with
simple direct replication (connected via LAN). If they need to synch
from their remote location, then you need to use dialup (for
indirect replication) or Internet (for Internet replication; though
if you can set up a VPN and connect to the home network via WAN, you
can use indirect replication).

Indirect replication is tricky to set up and keep running.

Internet replication is almost impossible to set up and keep running
(according to reports I've seen from people who've tried it), and I
would never consider it as it has a built-in dependency on IIS,
which I will not allow any my clients to run (at least, where I have
any say in the matter; I certainly will never recommend any
application that requires it!).

In terms of your question about improvements, replication was vastly
improved with the introduction of Jet 4 along with Access 2000. What
you got was two really major improvements:

1. field-level conflict resolution (instead of record-level)

2. elimination of data errors (they are treated as mere conflicts,
which makes them much easier to resolve).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2178
by: smahler | last post by:
Hi all, Here is my situation. I have an application that uses SQL 2000. The client has a program that uses MSDE. The client MUST have the MSDE version of the Database. What I have done is to create a SQL 2000 Database that is exactly like the MSDE version. What I wish to do, is at regularly scheduled intervals, update the SQL 2000 Data with the newest MSDE Data. On my clients server, they have both MSDE and SQL 2000 installed the...
0
1365
by: Markus Enders | last post by:
Hi everybody, we are using version 7 of db2 and need to replicate a database. We are using two web-based systems, which access two different databases. One system is used for import data (internal usage), the other system is used to show the imported data via web to the end-user (just reading the database). Both databases are running on different systems (both solaris). Want we want to do is to replicate the import-database (source)...
6
6296
by: tdmailbox | last post by:
I developed an access database in access 2003(keeping my database in the access 2000 format). It has been fully tested to be access 2000 compatible and works great. I am trying to package the final version however and when I tell it to create a shortcut it includeds the path to access 2003 in the shortcut C:\Program Files\Microsoft Office\OFFICE11\ my client is running access 2000. I could not figure any way to change the access...
2
1278
by: Terry Bell | last post by:
My client has an A97 application, been running for 7 years, split front/backend. They backup each night and sometimes half way through each day. If the backend corrupts, they restore the latest backup and re-apply the latest transactions. This is not very easy to do and they want a better way. What are the options? Upsize to A2003/Sql Server? What kind of licence do you need for 3-user SQL Server - do you need full SQL Server of MSDE?...
18
2691
by: Asif | last post by:
Hi all, I am working on portal which is using MS Access 2003 database. The problem that I am facing is that once I received data from vendors I have to upload whole Access Database file to server. Initially it is not an issue but after increasing the size of database file(nearly to 50 MB) it is now difficult to upload such file on server.
8
2696
by: rdemyan via AccessMonster.com | last post by:
I've converted my application from A2K format to A2003 format. I tried to follow Allen Browne's protocol in getting my app into A2003 (although I was unable to find informtion on the conversion process). Lots of decompiling and lots of compacting of the original application in A2000. Then the app was opened in A2003 and compacted, decompiled and compacted. Next I imported everything into a blank A2003 database. Then this db was...
3
4628
by: John Morgan | last post by:
I have my first small SQl Server 2005 database developed on my local server and I have also its equivalent as an online database. I wish to update the local database (using and asp.net interface) and then to upload the data (at least the amended data, but given the small size all data should be no trouble) to the online database. I think replication is the straight answer but I have no experience of this and I am wondering what else...
1
1710
by: aine_canby | last post by:
Hi, My company has an old access database which has been in use for many years now by a few employees. I need to make some design changes to one of the forms, but I'm not sure how I should go about doing this. I have designed databases with access before, but not when replication was used. So, should I first get everyone out of the database, then make a copy of the database to which I will add my changes to. Then finally,
2
4228
by: Query Builder | last post by:
Hi, I have transactional replication set up on on of our MS SQL 2000 (SP4) Std Edition database server Because of an unfortunate scenario, I had to restore one of the publication databases. I scripted the replication module and dropped the publication first. Then did a full restore. When I try to set up the replication thru the script, it created the
0
7967
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7885
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8258
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6687
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5428
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3882
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2403
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1493
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1233
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.