Replicating and synchronizing a database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ThomasK - 12 Oct 2006 18:03 GMT

Hi
I have a database that is split, FE and BE. The BE is located on the server.
The FE (about 15) are located on laptops that are out of the office durring
the day and then connected to the LAN in the office at night. We would like
to have it set up so that the laptops can be plugged in via the USB at the
end of the work day and synchronized to the master on the server. We also
need a very easy way for the users to complete this synchronization. Maybe a
button on the screen after they plug into the USB. The users may also be on
trying to synchronize at the same time.

Can the database be set up for this? Would there be any problems?

Thanks,
Tom

Reply to this Message

David W. Fenton - 12 Oct 2006 22:02 GMT

> I have a database that is split, FE and BE. The BE is located on
> the server. The FE (about 15) are located on laptops that are out
[quoted text clipped - 7 lines]
>
> Can the database be set up for this? Would there be any problems?

I'm not sure what USB has to do with it. Do you mean a USB network
card to connect to the network? Or USB wireless card? If the latter,
then it's not good, as wireless is too unreliable for use with
Access.

It's quite simple to set up a direct synch as you describe. The DAO
code for that is this simple:

Dim dbLocal As DAO.Database

Set dbLocal = DBEngine.OpenDatase("C:\Path\LocalReplica.mdb")
dbLocal.Synchronize "\\Server\Share\RemoteReplica.mdb"

dbLocal.Close
Set dbLocal = Nothing

You could have a button or menu choice to run this code somewhere in
your Access front end.

It could be a problem to have multiple people synching
simultaneously. I have never had that kind of scenario before so
can't say for sure.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

ThomasK - 12 Oct 2006 23:05 GMT

Thanks for your responce,
The scenario would be that people would take their laptops away from their
desks during the day and gather information and then return at the end of the
day and connect to the lan (by a direct network connection using the USB port)
and synchronize with the BE on the server. People may come back to the office
at the same time and try to update at the same time which I think may cause
problems. Also, Im wondering if there would be a problem if new records were
made on two or more separate laptops and the Primary key number for those
records were the same. Then when the laptops were synchronized the tables
would have different records with the same primary key.

Sorry for my ignorance, I just got to the point where I can build a simple
database and I know very little about setting it up for multiple users.

Thanks,
Tom

Reply to this Message

David W. Fenton - 13 Oct 2006 22:41 GMT

> The scenario would be that people would take their laptops away
> from their desks during the day and gather information and then
> return at the end of the day and connect to the lan (by a direct
> network connection using the USB port) and synchronize with the BE
> on the server.

Well, if you have people working on the LAN editing the back end on
the server, I wouldn't have your laptop users synch with the back
end the LAN users are editing. That can cause replication errors
because people are editing records/tables that wouldn't be able to
be synched (depends on the circumstances). Most vulnerable are memo
fields, so those should only be edited unbound.

The best thing is to have a hub replica that the offsite users synch
with, and have that on a synch schedule with the production back
end.

This is most easily implemented with the synchronizer to run
scheduled synchs, but you could code it yourself if you wanted to.

> People may come back to the office
> at the same time and try to update at the same time which I think
[quoted text clipped - 3 lines]
> laptops were synchronized the tables would have different records
> with the same primary key.

Yes, of course, if there are PK collisions, that would be a problem.
But if you're using Autonumbers as PK, that isn't a pragmatic
consideration, as in replicated databases, the Autonumbers are
randomized. In 10 years or so of replication work, I've never seen a
random Autonumber collision.

If you're assigning your own PK sequence, then you've got a major
problem. You have to come up with a way to prevent collissions. The
easiest way is to make each replica's sequence unique to it, either
by pre-allocating ranges of numbers, or by using a 2-column
sequence, one column of which encodes the replica.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message