Remote Database and Office Database

[Thomas Kroljic]

Hello,

My boss would like to have several of his field employees (9) have an
Access application
on there laptop/netbook device so when they visit customers, they can
input data
into the database. They will not have access to the internet or any type
of
Remote connection. This will be a stand-alone Access application on their
laptop. ( Field person will meet with 10 customer a day.)

My two questions have to do with when the field employees come back into
the office.
What will be the best way to a) get data from their version of the
database on their
laptop into the Office database?
a) Should I user Replication Manager
or
b) would it be easier in the long run to write (vba)
code to move the data between the
laptop database and the Office database? If this is
the route to take, how
would I connect (using vba code) from the Field
version of the database
to the Office version?
or
c) export the appropriate data to a separate file?

For some reason, I'm thinking if I can steer clear of the Replication
Manager, I'll have fewer headaches. Is this true?

And my second question would have to do with any changes that I would need
to make to the data structure. How do I update the Field databases? Again,
would it be better to write the vba code to modify the data sturctures
instead of using Replication Manager?

I envision very few changes to the database sturcture. But I can see
forms, reports, queries... being added or updated.

Thank you,
Timothy

 

[Thomas Kroljic]

Is this the right forum to post this message?

 

[John W. Vinson]

Hello,

My boss would like to have several of his field employees (9) have an
Access application
on there laptop/netbook device so when they visit customers, they can
input data
into the database. They will not have access to the internet or any type
of
Remote connection. This will be a stand-alone Access application on their
laptop. ( Field person will meet with 10 customer a day.)

My two questions have to do with when the field employees come back into
the office.
What will be the best way to a) get data from their version of the
database on their
laptop into the Office database?
a) Should I user Replication Manager
or
b) would it be easier in the long run to write (vba)
code to move the data between the
laptop database and the Office database? If this is
the route to take, how
would I connect (using vba code) from the Field
version of the database
to the Office version?
or
c) export the appropriate data to a separate file?

For some reason, I'm thinking if I can steer clear of the Replication
Manager, I'll have fewer headaches. Is this true?

And my second question would have to do with any changes that I would need
to make to the data structure. How do I update the Field databases? Again,
would it be better to write the vba code to modify the data sturctures
instead of using Replication Manager?

I would VERY strongly urge that you a) split the database into a frontend and
backend (backend containing only tables), and give each employee their own
copy of the frontend; and b) use the Replication Manager to replicate the
backend (NOT the frontend!!!), and give each remote employee a member of the
replica set of the backend. The replica master must remain on the central
office server (and should not be used by anyone except the administrator).
Central office users would share a common backend, itself a member of the
replica set; when remote users come in they can connect to the network and
synchronize their replicas with this "sub-master" (and thereby with the
master).

David Fenton is one of the current gurus on replication, try a
http://groups.google.com advanced search of these groups using "replication"
and "fenton" as search terms.

 

[Thomas Kroljic]

John and Pieter,

Thanks for the tip. Is Replication stable? The last time attemtpted to
use it was 8 years ago. At that time it seem like a pain-in-the-butt...in
the end, we ended up using Terminal Services. Unfortunately, I don't have
that option at the moment.
I'll check on David Fenton in the group you listed below.

Thank you.

 

[John W. Vinson]

John and Pieter,

Thanks for the tip. Is Replication stable? The last time attemtpted to
use it was 8 years ago. At that time it seem like a pain-in-the-butt...in
the end, we ended up using Terminal Services. Unfortunately, I don't have
that option at the moment.
I'll check on David Fenton in the group you listed below.

It's stable, it does the job, it's a pain in the butt, and it's complicated to
set it up correctly. I've used it with two clients and they both ended up
using Citrix (terminal services basically).

It is possible to "roll your own" replication as you suggest, but it's a VERY
complicated and difficult process; you wouldn't be reinventing the wheel, more
like reinventing an automatic transmission!

 

[David W. Fenton]

Is this the right forum to post this message?

How is this inappropriate?

 

[David W. Fenton]

And my second question would have to do with any changes that I
would need to make to the data structure. How do I update the
Field databases? Again, would it be better to write the vba code
to modify the data sturctures instead of using Replication
Manager?

I envision very few changes to the database sturcture. But I
can see
forms, reports, queries... being added or updated.

Why you mention Replication Manager, I don't know. It's an
out-of-date tool that doesn't provide much in the way of
functionality that you can't get via other metods.

See the Jet Replication Wiki for somewhat comprehensive info:

http://dfenton.com/DFA/Replication/

Now, to some issues:

1. as others have said, your app needs to be split, front end
(forms/reports, etc.) and back end (data tables). Only the back end
should be replicated, as:

a. replication is not needed to push out changes to a front end.

b. replication doesn't work reliably with anything but pure Jet
objects (tables/queries).

Your app should be split in this form even if you're *not* using
replication.

2. if the users are always connected to the Internet when away from
the central office, you'd be much better off avoiding replication
entirely and using Windows Terminal Server as your remote host for
the app.

3. if your users need to work outside the office disconnected from
the Internet, then using a local replica is a great way to manage
that. If you limit synchs to when they are back in the office
connected to the wired LAN, then it's very easy to implement with
plain old direct replication. If they want to synch in the field
(with an occasional Internet connection) or they need to synch
across a WiFi or WAN connection, then you would have to use indirect
replication which is an order of magnitude or more harder to
implement and much more difficult to keep running reliably (because
there are so many more "moving parts"). If you need remote synchs,
then I'd recommend upgrading to Access 2010 and using Sharepoint
2010 instead. That allows both offline editing and easy synching
when connected.

Basically, even though I'm one of the few people who has a lot of
knowledge of Jet replication, I recommend against using it except in
a very narrow range of circumstances. A2010 and Sharepoint 2010
remove the last of those except for those who can't afford the
dependency on a Sharepoint server.

 

[Thomas Kroljic]

John,
<<and it's complicated to set it up correctly.>>
Even as an experience programmer, I still think it a complicated setup for
basically transferring data from one database to another.
Unfortunatetly, we will not have access to any internet connections while
in the field. So, all field data (computers) will return to the office by
the end of the day.

 

[Thomas Kroljic]

David,
Not sure that I fully understand what you are trying to tell me. Can you
either list or point me in the direction for "other methods"? The laptop
that goes out to the field will not have the ability to connect back to the
office via the internet. The Field computer will return back to the office
at the end of the day. If replication Manager isn't the way to go, then what
would you suggest I use when the laptop comes back to the office? Do I
create VBA code that does the exchange of data from the Field laptop
database to the main Office database (both will be Access 2010) or do you
have something else in mind.

As for splitting the database, I always do this when more than one person
wants access to the application. I'm very familiar with this setup.

Terminal Service, now called Remote Desktop by MS, would be great but they
will not have access to an internet connect while in the field.

<<If you limit synchs to when they are back in the office connected to the
wired LAN, then it's very easy to implement with plain old direct
replication.>>

if I decide to use this replication process when they come back to the
office, are you saying that it is easy to not only implement but maintain?
Do I need to have a tech person available every time a field laptop comes
into the office and attempts to sync? Once the process is setup and tested,
is it pretty user friendly?

I'm very comfortable with VBA coding, if coding a good procedure to move
data from one database to another is the way the go, then I would rather
take that approach. What do you recommend?

Thank you,

 

[John W. Vinson]

ohn,
<<and it's complicated to set it up correctly.>>
Even as an experience programmer, I still think it a complicated setup for
basically transferring data from one database to another.
Unfortunatetly, we will not have access to any internet connections while
in the field. So, all field data (computers) will return to the office by
the end of the day.

From your response to David, I'd suggest that your requirement is actually a
pretty good fit for replication, and that you should be able to do it easily.
The replication FAQ is pretty good - just follow it carefully. You won't need
to worry about internet replication. Just be sure that you keep the Replica
Master unused and safe, used only when you're synchronizing (it's a REAL pain
if the master gets lost or corrupted... BTDT!)

 

[Thomas Kroljic]

John,
thanks for the follow-up. I'll read through the FAQ on replication and
give it a try in a small test environment.

 

[David W. Fenton]

Not sure that I fully understand what you are trying to tell me.
Can you
either list or point me in the direction for "other methods"? The
laptop that goes out to the field will not have the ability to
connect back to the office via the internet. The Field computer
will return back to the office at the end of the day. If
replication Manager isn't the way to go, then what would you
suggest I use when the laptop comes back to the office?

About 5 lines of code behind a command button:

Dim dbLaptop as DAO.Database

Set dbLaptop = DBEngine.OpenDatabase("[laptop replica]")
dbLaptop.Synchronize "[server replica]")
dbLaptop.Close
Set dbLaptop = Nothing

There are a lot of things that could be added to that:

1. check to see if the server replica is available (I use the File
System Object because it's more reliable than other methods)

2. you might want to check if there are any conflicts after the
synch and inform the user, or open the conflict resolver.

3. you probably don't want to hard-code the laptop replica filename,
so you can use the Connect property of one of your linked tables,
using Mid(CurrentDB.TableDefs("LinkedTableName").Connect, 11).

Do I
create VBA code that does the exchange of data from the Field
laptop database to the main Office database (both will be Access
2010) or do you have something else in mind.

I haven't actually tested replication in A2010 yet. There was, of
course, a show-stopping but in the initial release of A2007 that
caused the DAO synchronize command to fail! But they patched that
with a hotfix very quickly and then included the fix in the first
service pack, so it's worked OK since then. I doubt they'd make the
same mistake in A2010, but it certainly is the case that Jet
replication is not a priority for the Access development team.

As for splitting the database, I always do this when more than one
person wants access to the application. I'm very familiar with
this setup.

With replication, even if it's only one user working on two
different PCs, you must split to avoid corruption of the VBA
project.

Terminal Service, now called Remote Desktop by MS, would be great
but they will not have access to an internet connect while in the
field.

Remote Desktop and Terminal Services are not, strictly speaking, the
same thing. Remote Desktop is built on top of a subset of Terminal
Services, but Terminal Services include a lot more things than just
what RDP is built in top of.

<<If you limit synchs to when they are back in the office connected
to the
wired LAN, then it's very easy to implement with plain old direct
replication.>>

if I decide to use this replication process when they come back to
the office, are you saying that it is easy to not only implement
but maintain?

Comparatively speaking yes, i.e., in comparison to implementing and
maintaining a setup with indirect replication over the Internet (via
VPN).

Do I need to have a tech person available every time a field
laptop comes into the office and attempts to sync? Once the
process is setup and tested, is it pretty user friendly?

You will not need anyone for that. Once setup and tested, it should
work transparently. The main issue you will encounter is with
network connectivity, usually involving troubleshooting in the form
of the question "is it plugged in?"

I'm very comfortable with VBA coding, if coding a good procedure
to move data from one database to another is the way the go, then
I would rather take that approach. What do you recommend?

The code above is a starting point. The Replication Wiki has more
detail (you should definitely read the Replication Myths, the FAQ
and Best Practices). For the enhancements I mentioned above, just
ask if you don't know how it's done.

 

[David W. Fenton]

thanks for the follow-up. I'll read through the FAQ on
replication and
give it a try in a small test environment.

Be sure to read the Replication Wiki article on things not to
believe in the MS documentation, though.

 

[Thomas Kroljic]

David,
Thank you very much for taking the time to explain and give examples
related to my post.
The example you listed below is straightforward and looks pretty simple.
I'll follow up this
post by reading Replication Myths, the FAQ and Best Practices as you
suggested.

One final question for now, if the Field laptop is only used for
gathering New data and there is no
reason to upload data from the Office database to the Field database, is
it worth setting up a Replication procedure?

Again, thank you very much for your input.

Not sure that I fully understand what you are trying to tell me.
Can you
either list or point me in the direction for "other methods"? The
laptop that goes out to the field will not have the ability to
connect back to the office via the internet. The Field computer
will return back to the office at the end of the day. If
replication Manager isn't the way to go, then what would you
suggest I use when the laptop comes back to the office?

About 5 lines of code behind a command button:

Dim dbLaptop as DAO.Database

Set dbLaptop = DBEngine.OpenDatabase("[laptop replica]")
dbLaptop.Synchronize "[server replica]")
dbLaptop.Close
Set dbLaptop = Nothing

There are a lot of things that could be added to that:

1. check to see if the server replica is available (I use the File
System Object because it's more reliable than other methods)

2. you might want to check if there are any conflicts after the
synch and inform the user, or open the conflict resolver.

3. you probably don't want to hard-code the laptop replica filename,
so you can use the Connect property of one of your linked tables,
using Mid(CurrentDB.TableDefs("LinkedTableName").Connect, 11).

Do I
create VBA code that does the exchange of data from the Field
laptop database to the main Office database (both will be Access
2010) or do you have something else in mind.

I haven't actually tested replication in A2010 yet. There was, of
course, a show-stopping but in the initial release of A2007 that
caused the DAO synchronize command to fail! But they patched that
with a hotfix very quickly and then included the fix in the first
service pack, so it's worked OK since then. I doubt they'd make the
same mistake in A2010, but it certainly is the case that Jet
replication is not a priority for the Access development team.

As for splitting the database, I always do this when more than one
person wants access to the application. I'm very familiar with
this setup.

With replication, even if it's only one user working on two
different PCs, you must split to avoid corruption of the VBA
project.

Terminal Service, now called Remote Desktop by MS, would be great
but they will not have access to an internet connect while in the
field.

Remote Desktop and Terminal Services are not, strictly speaking, the
same thing. Remote Desktop is built on top of a subset of Terminal
Services, but Terminal Services include a lot more things than just
what RDP is built in top of.

<<If you limit synchs to when they are back in the office connected
to the
wired LAN, then it's very easy to implement with plain old direct
replication.>>

if I decide to use this replication process when they come back to
the office, are you saying that it is easy to not only implement
but maintain?

Comparatively speaking yes, i.e., in comparison to implementing and
maintaining a setup with indirect replication over the Internet (via
VPN).

Do I need to have a tech person available every time a field
laptop comes into the office and attempts to sync? Once the
process is setup and tested, is it pretty user friendly?

You will not need anyone for that. Once setup and tested, it should
work transparently. The main issue you will encounter is with
network connectivity, usually involving troubleshooting in the form
of the question "is it plugged in?"

I'm very comfortable with VBA coding, if coding a good procedure
to move data from one database to another is the way the go, then
I would rather take that approach. What do you recommend?

The code above is a starting point. The Replication Wiki has more
detail (you should definitely read the Replication Myths, the FAQ
and Best Practices). For the enhancements I mentioned above, just
ask if you don't know how it's done.

 

[David W. Fenton]

One final question for now, if the Field laptop is only used
for
gathering New data and there is no
reason to upload data from the Office database to the Field
database, is
it worth setting up a Replication procedure?

I don't know. If you are sure that's all that ever happens, and the
table they are adding data to is completely independent of all
others (no relationships), then it might be simpler.

But, frankly, writing the code to do that takes a lot longer than
writing the replication synch code, and replication will work even
if the requirements change and you need more than just one-way
additions, while "manually" updating will break if the requirements
change.

I'm prejudiced on this, i.e., pro-Replication, because I've been
using it for apps just like this since 1997. It's trivial for me,
and I've got pre-written code I can borrow from other projects to do
all the conflict checking and so forth (though none of it is all
that complicated).

In terms of reliability, as long as the synchs are always across a
wired LAN connection, it will be rock-solid.

 

[Thomas Kroljic]

David,
Again, Thank You very much for your time and advice. I do appreciate.

Thomas