A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Remote Database and Office Database



 
 
Thread Tools Display Modes
  #11  
Old May 28th, 2010, 05:55 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Remote Database and Office Database

On Thu, 27 May 2010 20:04:33 -0400, "Thomas Kroljic"
wrote:

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!)
--

John W. Vinson [MVP]
  #12  
Old May 28th, 2010, 03:13 PM posted to microsoft.public.access
Thomas Kroljic
external usenet poster
 
Posts: 21
Default Remote Database and Office Database

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

"John W. Vinson" wrote in message
...
On Thu, 27 May 2010 20:04:33 -0400, "Thomas Kroljic"
wrote:

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!)
--

John W. Vinson [MVP]



  #13  
Old May 29th, 2010, 12:36 AM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Remote Database and Office Database

"Thomas Kroljic" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #14  
Old May 29th, 2010, 12:39 AM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Remote Database and Office Database

"Thomas Kroljic" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old May 29th, 2010, 02:55 PM posted to microsoft.public.access
Thomas Kroljic
external usenet poster
 
Posts: 21
Default Remote Database and Office Database

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.


"David W. Fenton" wrote in message
36.90...
"Thomas Kroljic" wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



  #16  
Old May 30th, 2010, 06:54 PM posted to microsoft.public.access
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Remote Database and Office Database

"Thomas Kroljic" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #17  
Old May 31st, 2010, 03:50 PM posted to microsoft.public.access
Thomas Kroljic
external usenet poster
 
Posts: 21
Default Remote Database and Office Database

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

Thomas

"David W. Fenton" wrote in message
36.99...
"Thomas Kroljic" wrote in
:

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.

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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.