Remotely accessing an Access 2000 database

[Paul Fenton]

We have a couple of Access 2000 on our office network, split into
Front End/Back End. At any given time there are 5-6 users in either
of those databases. We have very few problems.

We're in the process of upgrading our server hardware and moving to
Windows Server 2003. Still working on the details with Dell.

We have a need for some salesman to access the databases from home and
I'm looking for the best way to do this. Currently, there is a copy
of the front end .mde file on the server and one person can get into
the server using pcAnywhere and work. Also with pcAnywhere, a user
can log on to his office workstation and run the database that way.
The problem with that solution is that the salesmen don't have
workstations in the office to log on to.

VPN won't work because of the likelihood of lost data or a corrupted
file if service is lost.

I have heard about MS Terminal Services and Sharepoint Services which,
supposedly, will allow multiple remote connections to the front end of
the database. I have no idea if or how that can be done.

I would appreciate some advice on the best way to skin this cat.


Paul Fenton
PLG & Associates.

 

[Pieter Wijnen]

Terminal Services (With or without VPN) is the simplest solution by far
VPN alone is as you pointed out more prone to corruption - but is doable if
the front-end resides locally, and even better - the data is stored in a
"proper" database
(Oracle, SQLServer, MSDE)
You would want to turn of Oppurtunistic locking if you do go for VPN &
Access Backend (see http://support.microsoft.com/?id=296264 for howto)
There's a cost involved with all solutions, but I presume the cheapest will
be to invest in TS (5 Users).
A TS (5 User) cannot however be converted to a >5 user license AFAIK if the
need arises

Pieter

We have a couple of Access 2000 on our office network, split into
Front End/Back End. At any given time there are 5-6 users in either
of those databases. We have very few problems.

We're in the process of upgrading our server hardware and moving to
Windows Server 2003. Still working on the details with Dell.

We have a need for some salesman to access the databases from home and
I'm looking for the best way to do this. Currently, there is a copy
of the front end .mde file on the server and one person can get into
the server using pcAnywhere and work. Also with pcAnywhere, a user
can log on to his office workstation and run the database that way.
The problem with that solution is that the salesmen don't have
workstations in the office to log on to.

VPN won't work because of the likelihood of lost data or a corrupted
file if service is lost.

I have heard about MS Terminal Services and Sharepoint Services which,
supposedly, will allow multiple remote connections to the front end of
the database. I have no idea if or how that can be done.

I would appreciate some advice on the best way to skin this cat.


Paul Fenton
PLG & Associates.

--

 

[Tony Toews]

Terminal Services (With or without VPN) is the simplest solution by far
VPN alone is as you pointed out more prone to corruption - but is doable if
the front-end resides locally, and even better - the data is stored in a
"proper" database
(Oracle, SQLServer, MSDE)

VPN though really has nothing to do with the corruption issue. It's
more a matter of the speed of the connection and how vulnerable it is
to seemingly insignificant dropout when dealing with an Access back
end.

And yes using a database such as Oracle, SQL Server or MSDE will, if
properly designed, make much better use of a slow connection and much
less prone to corruptions.

You would want to turn of Oppurtunistic locking if you do go for VPN &
Access Backend (see http://support.microsoft.com/?id=296264 for howto)

This is the first I've heard of this being a solution for corruption
and VPNs for slow networks. Please explain this a bit more.

There's a cost involved with all solutions, but I presume the cheapest will
be to invest in TS (5 Users).

And likely the simplest solution. I have a number of clients happily
using TS.

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 Toews]

We're in the process of upgrading our server hardware and moving to
Windows Server 2003. Still working on the details with Dell.

We have a need for some salesman to access the databases from home and
I'm looking for the best way to do this. Currently, there is a copy
of the front end .mde file on the server and one person can get into
the server using pcAnywhere and work. Also with pcAnywhere, a user
can log on to his office workstation and run the database that way.
The problem with that solution is that the salesmen don't have
workstations in the office to log on to.

VPN won't work because of the likelihood of lost data or a corrupted
file if service is lost.

It would be more accurate to say that a slow connection whatever it
might be won't work with an Access/Jet based backend. VPN is the
solution for a secure connection.

I have heard about MS Terminal Services and Sharepoint Services which,
supposedly, will allow multiple remote connections to the front end of
the database.

Sharepoint Services has nothing to do with this problem so ignore it
in this context.

TS will work quite nicely. It is very similar in concept to the
pcAnywhere except that multiple sessions run on the same server/host
computer. Whereas in pcAnywhere you can only have one session on the
host computer.

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

 

[Pieter Wijnen]

I've been off Access for a few years (playing at beeing DBA & general
troubleshooter)
My former job included trying to troubleshoot Microsoft Navision XAL
(formerly Concorde)
Which in Native mode is a file based db (similar to Access).
Unfortunately it didn't support UNC paths.
the main problem was not in corruption but on Citrix/TS on Win 2000 tended
to disconnect mapped Network shares when the first logged in user logged
out,
causing other users to loose contact with the mapped drive. Using UNC &
disabling OpLock virtually resolved this issue (Microsoft Navision Axapta)
I did find a relevant KB at http://support.microsoft.com/kb/q300216/

Pieter

 

[Pieter Wijnen]

I've been off Access for a few years (playing at beeing DBA & general
troubleshooter)
My former job included trying to troubleshoot Microsoft Navision XAL
(formerly Concorde)
Which in Native mode is a file based db (similar to Access).
Unfortunately it didn't support UNC paths.
the main problem was not in corruption but on Citrix/TS on Win 2000 tended
to disconnect mapped Network shares when the first logged in user logged
out,
causing other users to loose contact with the mapped drive. Using UNC &
disabling OpLock virtually resolved this issue (Microsoft Navision Axapta)
I did find a relevant KB at http://support.microsoft.com/kb/q300216/

Pieter

VPN though really has nothing to do with the corruption issue. It's
more a matter of the speed of the connection and how vulnerable it is
to seemingly insignificant dropout when dealing with an Access back
end.

And yes using a database such as Oracle, SQL Server or MSDE will, if
properly designed, make much better use of a slow connection and much
less prone to corruptions.


This is the first I've heard of this being a solution for corruption
and VPNs for slow networks. Please explain this a bit more.


And likely the simplest solution. I have a number of clients happily
using TS.

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

--