473,837 Members | 1,543 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Seeking advice on replication

I need advice about my decision to go with Replication in general.
This post was placed on the Microsoft Replication newsgroup, but
I really value the feedback that comes from this group as well.
I have a new client who want their Excel data moved to Access.
They have only 4-6 users of this data and a couple of them want
to work disconnected, with their laptops and synchronize when
they come into the office. The database will be small, around 20 mb,
and synchronization will only take place while connected to their LAN.

Does this sound like a good candidate for Replication?

If so, I'm assuming that the data and client would be combined into
one mdb file and the whole thing would be replicated, correct?

When a user creates a query or report for his own use, will it by
default be replicated to other users or remain local to him only?

If there is only a small likelyhood that two users will edit the same
record at the same time, how often should synchronization be done
and how would it be scheduled? Is the Rep Mgr the best route or
should this be done with VBA code?

Thanks in advance for any insights you have to contribute.

Danny J. Lesandrini
dlesandrini at hotmail

Nov 13 '05 #1
3 2420
dl*********@gma il.com wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:
I need advice about my decision to go with Replication in general.
This post was placed on the Microsoft Replication newsgroup, but
I really value the feedback that comes from this group as well.

I have a new client who want their Excel data moved to Access.
They have only 4-6 users of this data and a couple of them want
to work disconnected, with their laptops and synchronize when
they come into the office. The database will be small, around 20
mb, and synchronization will only take place while connected to
their LAN.

Does this sound like a good candidate for Replication?
Well, all you've told us is that it would be safely doable with
direct replication on the LAN, just using the Access UI, or
programmed through DAO (which can only do direct replication; for
indirect replication, you need the TSI Synchronizer from
http://www.trigeminal.com, and the synchronizer that comes as a part
of Replication Manager in the Developer's toolkit).

Whether or not a particular application is a good candidate depends
for Jet replication on a number of factors:

1. design of the existing data schema: certain kinds of RI setups
can break when replicated, such as a required self-referential join.

2. degree of overlap of data edits between different users -- if
most of the records are not going to be edited by more than one
person, it's not going to be an issue. But if they are, you'll have
major replication conflicts, and it will be very hard to resolve
them successfully.

The best apps for replication to multiple sites are apps where each
site/user works on a subset of data that needs to be shared with
other users read-only, but that other users won't be editing very
much. Also, if most of the changes will be added records, that works
better than lots of edits to existing records.

Of course, if you have any generated primary keys or numbering
systems that are exposed to the users, replication adds a set of
problems, as there is no centralized authority for handing out the
next value when the users are working independently.
If so, I'm assuming that the data and client would be combined
into one mdb file and the whole thing would be replicated,
correct?
Well, you'd have the standard split architecture, with a front end
with the forms/reports/queries and a back end with the data tables.
Only the back end would be replicated, because Jet replication
really only works in the long run with pure Jet data
(tables/queries) -- forms and reports and modules are all Access
objects (i.e., Jet objects with lots of special custom properties
known only to Access), and especially in Access 2K and beyond, with
the new monolithic save model (i.e., the Access project is stored in
a single record of a Jet system table, rather than in different
records for each object), replicating Access objects is very
dangerous. The same corruption problems that occur when you try to
have multiple people using the same front end are multiplied in a
replicated front end, and very often, after a very short period of
time (i.e., after a handful of synchronization s), the whole project
ends up irretrievably corrupt.

For data tables, though, Jet replication is wonderfully reliable,
assuming you don't have a workflow that is going to produce lots of
data editing conflicts.
When a user creates a query or report for his own use, will it by
default be replicated to other users or remain local to him only?
No, because that would be in the front end, which should not be
replicated.

The issues of distributing updates to a front end are precisely the
same in a replicated application as in a non-replicated application.
If there is only a small likelyhood that two users will edit the
same record at the same time, how often should synchronization be
done and how would it be scheduled? . . .
That all depends on how often people need the data from the other
members of the replica set (or from whatever functions as the main
data file in the office).
. . . Is the Rep Mgr the best route
or should this be done with VBA code?


Since they will only be replicating over the LAN, you don't need to
worry about ReplMan. You only need that when you need to do Indirect
or Internet replication. As long as you're on a WIRED LAN (not on
wireless -- wireless is completely unacceptable for any Access
application), you can use direct replication.

It's very easy to program.

The other rookie mistake is to have the design master used for
editing. The design master should be squirrelled away and never used
for data editing. It should be synched with the other replicas
occasionally, because replicas eventually do expire (default is 1000
days, I think).

So, in the office, on the LAN, there'd be your main data editing
replica that all the front ends on the workstations connect to, and
hidden away where only an administrator will work with it, the
design master. On each laptop, there will be a replica which is
synched with the central office replica.

You might consider using ReplMan to set up a replica farm, which is
basically a collection of identical replicas that are synched in the
background on a schedule. The value of that is that if your main
data editing replica gets corrupted, you can easily replace it with
a new replica created from one of the replicas in the replica farm.
You'd only lose the data in the editing replica since the last
synch.

The shortest synch period that can be set with ReplMan is 15
minutes, so, that's not a big deal. Of course, you have to be
careful with Memo fields -- they should always be unbound in any
replicated application, because if a synch happens while a memo is
being edited, this often corrupts the memo pointer.

For details about replica farms, see Michael Kaplan's website,
http://www.trigeminal.com.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #2
Thank's David ... you're brilliant and articulate. That was just what I
needed though I can see I've got more research to do before I launch
into development.

I had really wanted to replicate forms and reports but I'll head your
warning. I also like the idea of performing the synch programmaticall y
and appreciate your comment that it's easy.

The information about keeping memo fields as unbound is also very, very
helpful. If you think of any other caveats, I'd love to be forewarned.

--
Danny J. Lesandrini
dl*********@hot mail.com
www.amazecreations.com/datafast/
"David W. Fenton" <dX********@bwa y.net.invalid> wrote ...
I need advice about my decision to go with Replication in general.
This post was placed on the Microsoft Replication newsgroup, but
I really value the feedback that comes from this group as well.

I have a new client who want their Excel data moved to Access.
They have only 4-6 users of this data and a couple of them want
to work disconnected, with their laptops and synchronize when
they come into the office. The database will be small, around 20
mb, and synchronization will only take place while connected to
their LAN.

Does this sound like a good candidate for Replication?


Well, all you've told us is that it would be safely doable with
direct replication on the LAN, just using the Access UI, or
programmed through DAO (which can only do direct replication; for
indirect replication, you need the TSI Synchronizer from
http://www.trigeminal.com, and the synchronizer that comes as a part
of Replication Manager in the Developer's toolkit).

Whether or not a particular application is a good candidate depends
for Jet replication on a number of factors:

1. design of the existing data schema: certain kinds of RI setups
can break when replicated, such as a required self-referential join.

2. degree of overlap of data edits between different users -- if
most of the records are not going to be edited by more than one
person, it's not going to be an issue. But if they are, you'll have
major replication conflicts, and it will be very hard to resolve
them successfully.

The best apps for replication to multiple sites are apps where each
site/user works on a subset of data that needs to be shared with
other users read-only, but that other users won't be editing very
much. Also, if most of the changes will be added records, that works
better than lots of edits to existing records.

Of course, if you have any generated primary keys or numbering
systems that are exposed to the users, replication adds a set of
problems, as there is no centralized authority for handing out the
next value when the users are working independently.
If so, I'm assuming that the data and client would be combined
into one mdb file and the whole thing would be replicated,
correct?


Well, you'd have the standard split architecture, with a front end
with the forms/reports/queries and a back end with the data tables.
Only the back end would be replicated, because Jet replication
really only works in the long run with pure Jet data
(tables/queries) -- forms and reports and modules are all Access
objects (i.e., Jet objects with lots of special custom properties
known only to Access), and especially in Access 2K and beyond, with
the new monolithic save model (i.e., the Access project is stored in
a single record of a Jet system table, rather than in different
records for each object), replicating Access objects is very
dangerous. The same corruption problems that occur when you try to
have multiple people using the same front end are multiplied in a
replicated front end, and very often, after a very short period of
time (i.e., after a handful of synchronization s), the whole project
ends up irretrievably corrupt.

For data tables, though, Jet replication is wonderfully reliable,
assuming you don't have a workflow that is going to produce lots of
data editing conflicts.
When a user creates a query or report for his own use, will it by
default be replicated to other users or remain local to him only?


No, because that would be in the front end, which should not be
replicated.

The issues of distributing updates to a front end are precisely the
same in a replicated application as in a non-replicated application.
If there is only a small likelyhood that two users will edit the
same record at the same time, how often should synchronization be
done and how would it be scheduled? . . .


That all depends on how often people need the data from the other
members of the replica set (or from whatever functions as the main
data file in the office).
. . . Is the Rep Mgr the best route
or should this be done with VBA code?


Since they will only be replicating over the LAN, you don't need to
worry about ReplMan. You only need that when you need to do Indirect
or Internet replication. As long as you're on a WIRED LAN (not on
wireless -- wireless is completely unacceptable for any Access
application), you can use direct replication.

It's very easy to program.

The other rookie mistake is to have the design master used for
editing. The design master should be squirrelled away and never used
for data editing. It should be synched with the other replicas
occasionally, because replicas eventually do expire (default is 1000
days, I think).

So, in the office, on the LAN, there'd be your main data editing
replica that all the front ends on the workstations connect to, and
hidden away where only an administrator will work with it, the
design master. On each laptop, there will be a replica which is
synched with the central office replica.

You might consider using ReplMan to set up a replica farm, which is
basically a collection of identical replicas that are synched in the
background on a schedule. The value of that is that if your main
data editing replica gets corrupted, you can easily replace it with
a new replica created from one of the replicas in the replica farm.
You'd only lose the data in the editing replica since the last
synch.

The shortest synch period that can be set with ReplMan is 15
minutes, so, that's not a big deal. Of course, you have to be
careful with Memo fields -- they should always be unbound in any
replicated application, because if a synch happens while a memo is
being edited, this often corrupts the memo pointer.

For details about replica farms, see Michael Kaplan's website,
http://www.trigeminal.com.

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

Nov 13 '05 #3
"Danny J. Lesandrini" <dl*********@ho tmail.com> wrote in
news:7t******** ************@co mcast.com:
Thank's David ... you're brilliant and articulate. That was just
what I needed though I can see I've got more research to do before
I launch into development.
Replication is *very* complicated. I'd suggest that you memorize
everything about replication on Michael Kaplan's website,
http://www.trigeminal.com, because he was the authority on the
subject, and no one has emerged who is ever going to know as much
about it as he.

I'd also suggest that you browse the Google archive of
microsoft.publi c.access.replic ation. You're already posting there,
so you know it exists, but everything that ever could happen with
regard to replication has been discussed there, and for any topic,
MichKa has given a definitive answer at one time or the other.
I had really wanted to replicate forms and reports but I'll head
your warning. . . .
It simply doesn't work. If you understand how replication works and
how the Access 2K+ project save model works, you'd quickly realize
it would be skating on thin ice to even consider it.
. . . I also like the idea of performing the synch
programmaticall y and appreciate your comment that it's easy.
It's actually this simple:

Dim strLocal As String ' the local replica
Dim strRemote As String ' the remote replica
Dim db As DAO.Database

Set dbSynch = DBEngine(0).Ope nDatabase(strLo cal)
db.Synchronize strRemote

db.Close
Set db = Nothing

That's all there is to it.

Of course, you have to figure out the local replica name and the
remote replica name, but that's pretty easy (the local one could be
gotten from any linked table, while you would probably be using the
same hub replica for all the remote synchs).

Now, this doesn't account for conflicts. You'd probably want to loop
throught the .TableDefs collection of both the local database to see
if there are any conflicts (you just check for any table names that
end in "_Conflict" , and then you have to decide how to handle it. If
you're not rolling your own conflict viewer, then you'll have to
prompt the user to open the data files and resolve conflicts.

However, you may not want this to be something that's done by
ordinary users, and leave it to an administrator. So, the conflict
check might advise the user to contact the administrator.

The great thing about Jet 4 conflicts is that you don't have to
resolve them on both sides, as in Jet 3.5 -- you resolve them in
your local replica and then synch again, and it propagates the
results to the remote replica.
The information about keeping memo fields as unbound is also very,
very helpful. If you think of any other caveats, I'd love to be
forewarned.


I can't think of anything else in particular. But as you encounter
issues, post here. I have all threads with "replicatio n" in the
subject flagged and try to reply to them here so that people don't
think there is no one around who knows how it works.

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

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

Similar topics

0
2303
by: Cherrish Vaidiyan | last post by:
Frank <fbortel@nescape.net> wrote in message news:<bqgb99$a04$1@news1.tilbu1.nb.home.nl>... > Cherrish Vaidiyan wrote: > > Hello, > > > > I have certain doubts regarding replication of Oracle 9i in Red Hat > > Linux 9. > > > > 1) I want to implement asynchronous/synchronous multimaster > > replication.I have heard about Replication Management Tool in OEM. > > What are the steps to be taken initailly for using Replication
6
1560
by: user451 | last post by:
As the most Access-savvy person in my office, I have been handed the task of proposing a nationwide expansion of a project that I have developed in Access. A brief overview: Right now, about 25 people process about 5 transactions per day. (Total, not each) The back-end database resides on a server not located in my office, but nearby. The transactions are the results of certain law enforcement activities. Pretty low volume for new...
6
3891
by: John | last post by:
Hi We have an access app (front-end+backend) running on the company network. I am trying to setup replication for laptop users who go into field and need the data synched between their laptops and the server upon return to the office. I am planning it this; Move all access tables to sql server and then link the tables to access front-end mdb app (using odbc?). Copy the same setup (access front end + sql backend) onto each laptop....
9
3941
by: David W. Fenton | last post by:
See: Updated version of the Microsoft Jet 4.0 Service Pack 8 replication files is available in the Download Center http://support.microsoft.com/?scid=kb;en-us;321076 This includes the Jet 4 synchronizer. This allows anyone to do indirect replication, even without
2
1200
by: Joseph Geretz | last post by:
I don't know if this is the right group for my question, but I'm seeking advice from knowledgable .NET developers. Hopefully I've come to the right place. I work with a document management application which is tightly integrated with the workstation Windows environment. Our software does not necessarily handle any specific document type. User's can import any document type into our repository. On the display side, we use WinAPI to...
2
1466
by: Jim | last post by:
I work in the environmental industry and we have large gcms instruments that aquire raw data to an attached computer. These computers are networked, and have shared folders so the data can be accessed from the network. I would like to create a fairly simple utility that copies data from the computers shared folders onto the network. It will also depending on if the file successfully copied, check if the file/folder(s) modified date is...
9
1545
by: laststubborn | last post by:
Dear All, We have a big concern in our Database system. We have 2000 transactions daily in our database. We need to replicate some how the database for our fail over setup. I tried transactional replication at midnight but our all systems locked and we had a lot of complaints from the customers and It was taking a lot of time to snapshot part and I had to abort it because of these reasons. I need an advice how I can create a replication...
3
4553
by: Gert van der Kooij | last post by:
Hi, Our SQL Replication is between DB2 databases on Windows servers. I'm searching for the document which tells me how to migrate our SQL Replication environment from V8 to V9 (we also need to migrate from V7 to V8 but that's fully described so that's no problem). The PDF 'Migrating to Replication Version 9' doesn't contain a description about migrating SQL Replication, only Q replication. I found some links to PDF manuals which should...
2
4268
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
10863
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10263
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
6987
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
5663
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...
0
5838
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4468
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
2
4034
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3120
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.