keep 2 copies of a DB in synch with Access 2007

K

KumbiaKid

I have written a small DB app in Access 2000 and have used replication to
keep two copies on two computers (different users) in synch for over a year.
Both users need to be able to make changes independently and have those
changes synched to the other DB copy. Now, both users have moved to Office
2007. What is the best way to keep the two copies in synch with Access 2007?
Note that these are two computers in the user's homes. Both have internet
access, but the owner of the DB insists on keeping the Master copy but isn't
willing to allow a connection to the DB via the net.

I need some easy way to make sure changes by either user are synchronized
with the copy of the DB on the other user's computer. Can anyone help?

Thanks,
KumbiaKid
 
B

Brendan Reynolds

KumbiaKid said:
I have written a small DB app in Access 2000 and have used replication to
keep two copies on two computers (different users) in synch for over a
year.
Both users need to be able to make changes independently and have those
changes synched to the other DB copy. Now, both users have moved to Office
2007. What is the best way to keep the two copies in synch with Access
2007?
Note that these are two computers in the user's homes. Both have internet
access, but the owner of the DB insists on keeping the Master copy but
isn't
willing to allow a connection to the DB via the net.

I need some easy way to make sure changes by either user are synchronized
with the copy of the DB on the other user's computer. Can anyone help?

Thanks,
KumbiaKid

My understanding is that replication will continue to work as long as you
don't convert to the new ACCDB format. But you might get a more definitive
answer in the replication newsgroup (microsoft.public.access.replication).
 
G

GBA

don't understand how replication was working if the master refuses to connect
to internet - - - - but maybe that is a misunderstanding....

if you are saying that the Master does want to consolidate all the data; but
does NOT want the junior to see the total data - then have junior's db create
an exportable excel sheet of the new/changed records...and have junior email
it to Master so that Master can import it.

if Master does want junior to have all data - then they both must send to
each other an email with the updated info and both must import.

this would the poor man's manual replication method....the creative part is
of course to flag only the new & changed records so one doesn't resend the
same data over and over.
 
G

GBA

well I'm not sure that manual replication is even great in theory......

the description was a "small db" and there are only 2 pcs involved.... so it
may be viable that they just zip and email each other their stuff and stay in
sync...but someone posted that replication still works so may be not even
needed at all....

expanding the topic somewhat; one presumes that MS has dropped replication
in 07 and beyond in the belief that the better solution/trend is a browser
based db...which I would have to agree. but moving to a web server solution
is a pretty darn big step in a case like this user. I am very interested
to see a solution for the geographically spread small Access requirement.
 
D

David W. Fenton

My understanding is that replication will continue to work as long
as you don't convert to the new ACCDB format.

This is correct. Jet replication is fully supported by the ACE for
Jet 4 format MDBs. The A2007 UI includes the same functionality for
replication as was in previous versions of Access, but the
replication menus will be available only if the file is an MDB.

The front end could still be an ACCDB and the back end could be a
replicated MDB.
 
D

David W. Fenton

the description was a "small db" and there are only 2 pcs
involved.... so it may be viable that they just zip and email each
other their stuff and stay in sync...

If you're not using replication, yes, emailing is fine, but if
you're using replication, you can't email files back and forth, as
this completely breaks replication.
but someone posted that replication still works so may be not even
needed at all....

Yes, replication is still supported in A2007. It is only the ACCDB
file *format* that lacks support for replication, not the ACE or
Access itself.
expanding the topic somewhat; one presumes that MS has dropped
replication in 07

It has done no such thing.
and beyond in the belief that the better solution/trend is a
browser based db...which I would have to agree.

That only works for always-connected users. For disconnected laptop
users who need to edit data and merge their changes back, a
web-based solution is not viable.

This is, of course, why Sharepoint supports offline mode, because
they have to be able to support disconnected users.

So, no, I don't think youre surmise about the reason for MS choosing
to not support replication in the ACCDB file form is plausible.
but moving to a web server solution
is a pretty darn big step in a case like this user. I am very
interested to see a solution for the geographically spread small
Access requirement.

Jet replication works just fine. MDB is a native format for A2007
and is likely to remain so for a very long time.
 
D

David W. Fenton

Replication between 2 dbs on 2 separate pcs requires a network
connection for synching. The refusal of the owner of the master
copy to connect over the Internet prevents replication unless they
get some network connection, either over the Internet or
physically connected by an Ethernet cable, usb cable, etc. Ok, a
wireless network could connect the 2 pcs but I wouldn't recommend
it.

Not viable for a direct synch as a wireless connection is much too
unreliable. A direct synch opens the remote replica across the wire
and if the connection drops, the remote replica 9 times out of 10
will be corrupted, and when compacted you may not lose any data, but
99 times out of 100 the remote replica will lose replicability and
no longer be synchronizable. Then you have to manually recover the
data from it and create a new replica to replace it.

This is not worth it.
Msft *hasn't* dropped replication from Access 2007. I keep
hearing this lie.

I am still trying to figure out why people feel the need to repeat
this lie.

The key distinction:

Replication was dropped from the ACCDB file format.

It is still fully supported by the ACE and by Access 2007 as long as
you use the file format for which it is supported, i.e., MDB.
 
G

GBA

It is my understanding that there is no replication in the 07 accmd format.
No attempt to spread false rumors. I am very pro Access. It's what my 07
text book says.

One can keep in going with mdb format in 07 - very true.

But still pre 07 in time can only fade away. So looking forward - one has
no replication. But again, I believe it is because the trend is inevitable
to browser based as the solution to geographically distributed requirements.
But nothing is out there -yet- that compares to Access' affordability &
completeness for the small business. A web server based solution is a
gigantic step in complexity & cost today compared to Access.
 
K

KumbiaKid

Thanks to all for your suggestions.

It looks like replication will continue to be the best solution for this
application. I was hoping there might be something new that would be easier
to manage. David says emailing "completely breaks replication". What my users
have been doing is the following. The owner keeps the Design Master and a
replica on her machine and the other users keeps a replica. The replica from
the second user gets created on a USB stick connected to the owners PC, then
copied onto the second user's PC. When it's time for a synch, the second
user's replica gets copied back onto the USB stick which is then moved to the
owner's PC and the synch is performed. Access does complain because the DB ID
gets changed when the DB's location changes, but the synch still does the
right thing. Once both replicas are synched with the Design Master, the old
replicas are deleted and two new replicas are created and the process starts
over. I am fully aware that this is not how replication is supposed to work
and that moving replicas to different directories on different PCs is
technically a no-no, but it has worked for us and I don't know any better
way. If there's a way to create the replica directly on the second user's PC
and do the synch via the net without any enterprise level tools, I'd love to
hear how. As I suggested in my original post, the owner is dubious about
letting anyone connect to her PC via the net, so I would need to be able to
provide her with serious assurances that the connection would be secure and
that she would be able to control it absolutely. Specialized VPN software,
Sharepoint, etc. are out of the question financially for this application, so
we need either built-in Windows solutions or very inexpensive add-ons. By the
way, both of these PCs are using Win XP Pro and both connect to the internet
via routers (one is satellite broadband and the other is 3G wireless
broadband).

Thanks again,
KumbiaKid
 
K

KumbiaKid

Thanks Chris,

I did not know, when I started this project, about the limitations on jet
replication. It seemed, on the face of it, to be a workable, if not even good
solution. And, it has worked. When the project started, neither user had
broadband (only dial-up internet) and the circumstances still do not lend
themselves to sharing a single copy of the DB. Neither user has a static IP
address. These are just two people who need to share some data that happens
to be best managed via a database application. Both live in rural Australia,
one, the database owner, uses purely solar power and cannot leave her
computer on all the time so it's not practical for the other user to be able
to work whenever she needs to. This is not in a commercial environment, so
hiring a trained employee or consultant are not options. I appreciate your
references and I will certainly look into them. What I need is a way for two
users to each be able, independently, to update data in their respective
copies of a database, then at specified times, synchronize the two databases.
That is exactly, as I understand it, what jet replication is designed to do.
If we can, using one of the tools you have suggested, connect the databases
to synchronize them in situ so we can avoid copying or moving the replicas,
it still seems to me a viable option. If one of your alternative solutions
can function in this manner, please tell me how instead of chastising me for
using the solution that appeared to me, on the surface, to be appropriate.

Thanks again,
KumbiaKid
 
Y

Ynot

GBA said:
I am very pro Access. It's what my 07
text book says.

We call that brainwashing. Would you like some cookies to go with that
Kool-Aid while you study? :)o)
 
D

David W. Fenton

It looks like replication will continue to be the best solution
for this application.

If there's not network, then, no, replication is not at all
suitable. You will eventually encounter an unfixable data error and
once that happens, you don't know if your replicas have identical
data. That means your data is CORRUPT because you can't trust that
the data in all your replicas is what you think it is.

So, you need to stop using replication. There is no way that you can
depend on it over the long run.
 
D

David W. Fenton

What I need is a way for two
users to each be able, independently, to update data in their
respective copies of a database, then at specified times,
synchronize the two databases. That is exactly, as I understand
it, what jet replication is designed to do.

That's what it's designed to do. But it's also designed for a
networked environment. Copying replicas around willy-nilly breaks
replication as you quickly end up with "dead replicas":

http://groups.google.com/group/comp.databases.ms-access/msg/9f5a84194
fa6c653

Or you can read the full thing on the Jet Replication Wiki FAQ,
question #5:

http://dfenton.com/DFA/Replication/index.php?title=FAQ
If we can, using one of the tools you have suggested, connect the
databases to synchronize them in situ so we can avoid copying or
moving the replicas, it still seems to me a viable option.

You could have done it over dialup, ferchrissakes. I had users
synchronizing indirect over dialup back in 1998. It works fine and
doesn't even take that long (because indirect replication only sends
the changes to the data).
If one of your alternative solutions
can function in this manner, please tell me how instead of
chastising me for using the solution that appeared to me, on the
surface, to be appropriate.

You didn't do your homework. You proposed a solution that has put
the data in danger of being corrupted. At this point, you really
don't even know if the data in all the replicas is identical, which
is the major problem that dead replicas cause (assuming the dead
replicas don't produce errors that prevent synchronization
entirely).

The longer you keep doing what you're doing, the less reliable is
the data.
 
D

David W. Fenton

It is my understanding that there is no replication in the 07
accmd format.

That is correct.
No attempt to spread false rumors. I am very pro Access. It's
what my 07 text book says.

Does your textbook say what you said, which was:

MS has dropped replication in 07

If so, get a new textbook, as the textbook is wrong.
One can keep in going with mdb format in 07 - very true.

Yes, and that's what the statement you made is FALSE.
But still pre 07 in time can only fade away.

MDB is a native format for Access and for the ACE. As long as the
ACE supports Jet 4 MDBs without conversion, there will be support
for replication in future versions of Access. If MS decides to
create a new version of the ACE that requires conversion of Jet 4
MDBs, then that will be the point at which the ACE will no longer
support replication.

I see no indication by MS that they would do such a thing, and it
would certainly be throwing away all the good will they've had for
the last 3 versions of Access, which have no difficulties working
with the previous versions' files. This is because the Jet versions
didn't change, of course (A2000, A2002 and A2003 all use Jet 4) and
they went the extra mile with their new version of Jet (ACE) to
continue to support the MDB files from those three previous
versions. Why would they suddenly change that?
So looking forward - one has
no replication.

You didn't say "looking forward." You said RIGHT NOW.
But again, I believe it is because the trend is inevitable
to browser based as the solution to geographically distributed
requirements.

Sharepoint is browser-based, yet it is designed to store offline
data because MS understands perfectly well that people have to work
unconnected to the Internet or any other network. Jet Replication is
an excellent way to support disconnected users -- much easier than
Sharepoint and also with a much smaller footprint.
But nothing is out there -yet- that compares to Access'
affordability & completeness for the small business. A web server
based solution is a gigantic step in complexity & cost today
compared to Access.

And that's why I think you're completely wrong.
 
T

Tony Toews [MVP]

Chris O'C via AccessMonster.com said:
You knew it was a really, really bad idea
abusing the replicated db the way you are, yet you went ahead with it anyway.

Now how did KumbiaKid know replication was a really, really bad idea
without asking here? As far as they were concerned it worked.
You have so many free or nearly free alternatives for securely networking
this app for 2 users, yet you believe Jet replication is the way to go?
Sheesh.

Oh wait, solar panels at one place? Duhhh, now that's an excellent
reason to avoid all those fancy solutions we in the world of 24x7
power grids and high speed Internet take for granted.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
T

Tony Toews [MVP]

KumbiaKid said:
I did not know, when I started this project, about the limitations on jet
replication. It seemed, on the face of it, to be a workable, if not even good
solution.

If one of your alternative solutions
can function in this manner, please tell me how instead of chastising me for
using the solution that appeared to me, on the surface, to be appropriate.

KumbiaKid is correct. If all you do is read the Microsoft help
files and don't do a lot of digging on the Internet you will think
that replication is the answer to a lot of problems.

It's not easy digging out the information on the Internet about what
the best options are. There is so much garbage mixed in with the
nuggets now especially with those idiots at Google indexing online
forums which are mirrors of NNTP newsgroups so that Google can make ad
money. The only real practical method of determining the best
practices involve asking questions.

And you Chris and you David are chastising KumbiaKid for asking and
making them think they are a fool. That's not professional.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

David W. Fenton

Now how did KumbiaKid know replication was a really, really bad
idea without asking here?

Replication wasn't the bad idea -- shipping replicas around on USB
Flash drives was the bad idea.
As far as they were concerned it worked.

I can't find the exact article, but I think the original poster
actually said at some point in the discussion that s/he knew it was
not the right way to do it and went ahead and did it anyway.
 
D

David W. Fenton

KumbiaKid is correct. If all you do is read the Microsoft help
files and don't do a lot of digging on the Internet you will think
that replication is the answer to a lot of problems.

Really? It seems to me that Microsoft hides the existence of Jet
replication as much as it possibly can without actually withdrawing
the Knowledge Base articles referring to it. Indeed, a lot of the
existing documentation for it is very old, as MS hasn't done
anything with it since the introduction of Jet 4 in 1999.

What MS *does* do, I think, is try to hide the complexity of
replication. Perhaps the technology would have been better served in
the long run if it had only ever been available in code.
It's not easy digging out the information on the Internet about
what the best options are.

Google "Jet replication" and tell me you don't have a way to get to
the best resources on it quite easily.
There is so much garbage mixed in with the
nuggets now especially with those idiots at Google indexing online
forums which are mirrors of NNTP newsgroups so that Google can
make ad money. The only real practical method of determining
the best practices involve asking questions.

And you Chris and you David are chastising KumbiaKid for asking
and making them think they are a fool. That's not professional.

S/he said at one point that s/he already knew that the approach
being used was actually wrong, but insisted on doing it anyway.

Clearly, there's a very stupid pointy-haired boss involved (the one
who won't network). If that's non-negotiable, then Jet replication
is just not a viable option.

It's clear that a lot of people use technologies they don't really
understand, and yes, that's reality. But it shouldn't be that way.
If you're embarking on something as complicated as Jet replication,
you need to fully understand how it works, and once you understand
that, you won't even consider shipping replicas around on USB thumb
drives.

And I can guarantee you that MS *never* recommends anything of the
sort in any of its documentation.
 
K

KumbiaKid

How "exciting"! So, Chris, Tony, and/or David, what is my best solution given:
1. Two users in different locations need to update a database independently
and at unspecifiable times (sometimes simultaneously, sometimes not);
2. Neither user's computer can be left on all the time and neither user is
sure to be available to turn on her computer whenever the other wants to make
updates (so it's impractical for either PC to be employed as a server for
both users);
3. Every now and then, updates from each user need to be reconciled with
updates entered by the other user so that when the reconciliation has been
done, both users see the same picture of the database with both users'
changes incorporated.

By the way my earlier comment about solar power would have been obvious to
anyone using solar power -- you don't waste power by leaving any devices on
when they're not being used. It has nothing to do with how advanced the
technology is. Actually, good advice globally if you ask me.
 
D

David W. Fenton

How "exciting"! So, Chris, Tony, and/or David, what is my best
solution given: 1. Two users in different locations need to update
a database independently and at unspecifiable times (sometimes
simultaneously, sometimes not); 2. Neither user's computer can be
left on all the time and neither user is sure to be available to
turn on her computer whenever the other wants to make updates (so
it's impractical for either PC to be employed as a server for both
users); 3. Every now and then, updates from each user need to be
reconciled with updates entered by the other user so that when the
reconciliation has been done, both users see the same picture of
the database with both users' changes incorporated.

The only solution I see is programming it manually. This is not a
trivial task when there will be updates in both databases. But given
the ridiculous restrictions the pointy-haired-boss has put in place,
it is the only way.

It will cost many times more in time and effort and $$$ than
solutions involving always-on networking would. And it will be much
more prone to errors during the development process and those could
really badly screw up your data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top