Access on VPN -- seeking solutions

[evancater]

My client wants to make their Access 2007 database available to
offices around the country with multi-user permissions set to control
access to the tables and forms, etc. The easiest thing would be a
client/server app, but they are concerned that accessing the backend
on their VPN would be too slow.

We've discussed the possibility of publishing the forms to the
intranet with ASP, but I'm concerned that web development with Access
is a complex and costly bucket of worms, especially given the multi-
user settings and the fact that I don't have any experience with web
development. I've also heard web development might be easier with SQL
server.

I've heard there may be ways to go the client/server route that would
avoid the speed issues with VPN, possibly involving RDP?

I would welcome any thoughts about how best to attack this project,
especially thoughts on how best to make the client/server work without
performance problems.

[Albert D. Kallal]

I share my thoughts and solutiosn on ths issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Since ms-access really has little, if anything to do with web based stuff,
then a good bet is as you mentioned RDP (terminal services).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[evancater]

Thanks, the article is very helpful. Now I just need some specifics on
how to set up an Access application to be accessed via Terminal
Services:

Some specific questions I'm hoping to have answered by Albert or
anyone who can answer them:
1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this way? I
need detailed instructions for an absolute TS beginner.
2. When using this method, is the front-end usually accessed via RDP
or is it possible/preferable to have a front-end on each local machine
with tables linked to a backend that is in a central location accessed
remotely?
3. Are there multi-user conflict issues using this method? Or does it
work just like a standard LAN based Access application, where the data
can be viewed and updated by multiple users at once? I would guess the
MDW file would be placed in the same location as the backend?
4. Does this method make it difficult for Access to automate other
programs like Outlook, Excel and Word?

Thanks so much for your help.
On Apr 28, 7:37 am, "Albert D. Kallal" <[e-mail]>
wrote:

I share my thoughts and solutiosn on ths issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Since ms-access really has little, if anything to do with web based stuff,
then a good bet is as you mentioned RDP (terminal services).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

[evancater]

Thanks, the article is very helpful. Now I just need some specifics on
how to set up an Access application to be accessed via Terminal
Services:

Some specific questions I'm hoping to have answered by Albert or
anyone who can answer them:
1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this way? I
need detailed instructions for an absolute TS beginner.
2. When using this method, is the front-end usually accessed via RDP
or is it possible/preferable to have a front-end on each local machine
with tables linked to a backend that is in a central location accessed
remotely?
3. Are there multi-user conflict issues using this method? Or does it
work just like a standard LAN based Access application, where the data
can be viewed and updated by multiple users at once? I would guess the
MDW file would be placed in the same location as the backend?
4. Does this method make it difficult for Access to automate other
programs like Outlook, Excel and Word?

Thanks so much for your help.
On Apr 28, 7:37 am, "Albert D. Kallal" <[e-mail]>
wrote:

I share my thoughts and solutiosn on ths issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Since ms-access really has little, if anything to do with web based stuff,
then a good bet is as you mentioned RDP (terminal services).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

On Apr 28, 7:37 am, "Albert D. Kallal" <[e-mail]>
wrote:

I share my thoughts and solutiosn on ths issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Since ms-access really has little, if anything to do with web based stuff,
then a good bet is as you mentioned RDP (terminal services).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[e-mail]

[DawnTreader]

On Apr 28, 1:30*pm, [e-mail] wrote:

Thanks, the article is very helpful. Now I just need some specifics on
how to set up an Access application to be accessed via Terminal
Services:

Some specific questions I'm hoping to have answered by Albert or
anyone who can answer them:
1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this way? I
need detailed instructions for an absolute TS beginner.
2. When using this method, is the front-end usually accessed via RDP
or is it possible/preferable to have a front-end on each local machine
with tables linked to a backend that is in a central location accessed
remotely?
3. Are there multi-user conflict issues using this method? Or does it
work just like a standard LAN based Access application, where the data
can be viewed and updated by multiple users at once? I would guess the
MDW file would be placed in the same location as the backend?
4. Does this method make it difficult for Access to automate other
programs like Outlook, Excel and Word?

Thanks so much for your help.

On Apr 28, 7:37 am, "Albert D. Kallal" <[e-mail]>
wrote:

I share my thoughts and solutiosn on ths issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Since ms-access really has little, if anything to do with web based stuff,
then a good bet is as you mentioned RDP (terminal services).

--
Albert D. Kallal * *(Access MVP)
Edmonton, Alberta Canada
[e-mail]

On Apr 28, 7:37 am, "Albert D. Kallal" <[e-mail]>
wrote:

I share my thoughts and solutiosn on ths issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Since ms-access really has little, if anything to do with web based stuff,
then a good bet is as you mentioned RDP (terminal services).

--
Albert D. Kallal * *(Access MVP)
Edmonton, Alberta Canada
[e-mail]- Hide quoted text -

- Show quoted text -

here is what we have done at my work place to allow our service
representatives access to our access. :) pun intended.

1 install hamachi on the server machine that you want to terminal
into. use it to create a network. make the password very strong, a
combination of upper and lower case letters numbers and symbols. if
you have multiple areas that need access then make multiple networks.
we have 7. each network is for a different representative in a
different country or region. its not necessary unless you have more
than 16 people accessing through hamachi. each hamachi network is
allowed upto 16 connections, unless you pay for more. whether you get
a hamachi license for your server is up to you. the only thing is if
you dont you have to leave someone logged into the server computer
forever. if you pay for a license then you can run hamachi as a
service and the connection stays on no matter what.

2 give the hamachi network, address and password for the network to
the people that you intend to allow access.

3 each client must install hamachi and connect to the appropriate
network.

4 use the terminal server to create users for each person that you
intend to use the access app. one thing this does is allow you to use
the windows log in as thier access log in. my company has a table that
stores all the employees that we allow access and we use this table to
grant thier access to functions and forms and information. we didnt
use the built in access login security system.

5 each user should be given a password for thier user that you create
and again make it strong.

6 deploy your frontend and have each users profile point to that file
being the only file that the user can use, and have it be thier start
up program. this secures the terminal and your internal network. there
are things you might want to take away from the users on the access
menus. addtionally you would want to set up the terminal server to
quit thier session when the user quits the access app.

7 give your users thier passwords for their terminal server log in.

this is a quick run down, but the essential bits of what my company
does for around 30 to 50 users some internal, some external to our
network. there are things that we do in access to limit or filter the
information they are allowed to see depending on thier need and it is
based on the users login to the terminal server and a combo box on the
main form that starts when the users session opens the app. if you
need some step by step help let me know here and i will walk you
through what my company did.

additionally more information on how you have already set up the app
would be of beneifit. have you already set up the access workgroup and
user system? how many users, what kind of access to the information
and features should they have?

read you soon. :)

[David W. Fenton]

ev*******@gmail.com wrote in
news:51**********************************@d45g2000 hsc.googlegroups.co
m:

1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this
way? I need detailed instructions for an absolute TS beginner.

There's really not much to it. The most complicated part of it is
the licensing. With A2K7 you have to use the Enterprise version of
Windows Server and the Enterprise version of Access.

The only other issue is that many people think you can share a front
end on Terminal Server, but really, there's no difference between
running on TS and running on individual workstations -- ever user
has to have an individual front end.

2. When using this method, is the front-end usually accessed via
RDP or is it possible/preferable to have a front-end on each local
machine with tables linked to a backend that is in a central
location accessed remotely?

You get no benefit from doing it with the front end on the local
machines, and that just doesn't work over a VPN (except in very
carefully designed apps). The whole point of TS is that you're not
pulling anything across the wire except the video data. This makes
it very efficient and snappy.

3. Are there multi-user conflict issues using this method? Or does
it work just like a standard LAN based Access application, where
the data can be viewed and updated by multiple users at once? I
would guess the MDW file would be placed in the same location as
the backend?

The back end is the only MDB that will have multi-user issues, and
it's no different from having a shared back end on a server.

4. Does this method make it difficult for Access to automate other
programs like Outlook, Excel and Word?

That can be an issue, as automating those apps greatly increases the
RAM and CPU usage. Outlook has some gotchas working under terminal
server, if I'm remembering correctly, but I've never used Office
2007, so maybe the problems are different now.

I honestly don't believe in automating Outlook, in any event, but
frequently automate Word and Excel. But I've never done either
running on Terminal Server.

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

[David W. Fenton]

DawnTreader <al*******@gmail.comwrote in
news:c6**********************************@i36g2000 prf.googlegroups.co
m:

install hamachi on the server

Windows Terminal Server requires no installation. Why would you
bother with a 3rd-party product?

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

[evenlater]

That's very helpful. I may take you up on your offer of a walk-through
once I get more information from my client. In the mean time I have a
couple questions:

On Apr 28, 9:38 pm, DawnTreader <[e-mail]:

1 install hamachi on the server machine that you want to terminal
into. use it to create a network.

Hamachi is a tool used to create VPNs, right? My client already has
VPN networks set up, so I assume I can skip this step.

4 use the terminal server to create users for each person that you
intend to use the access app. one thing this does is allow you to use
the windows log in as thier access log in. my company has a table that
stores all the employees that we allow access and we use this table to
grant thier access to functions and forms and information. we didnt
use the built in access login security system.

Interesting way to avoid the whole MDW business.

6 deploy your frontend and have each users profile point to that file
being the only file that the user can use, and have it be thier start
up program.

Not sure how you point a profile at a file... what kind of profile are
we talking about?

additionally more information on how you have already set up the app
would be of beneifit. have you already set up the access workgroup and
user system? how many users, what kind of access to the information
and features should they have?

We're more or less starting from scratch, although the client does
have a very simple Access database that will be a starting point. The
current version has only one user. The client hasn't decided yet how
many users are required for the new app, but I think it's going to be
20 or so tops at the moment with the possibility of adding more as the
company grows. I do wonder whether we're better off going the ASP
route if the TS method is cumbersome or limiting in terms of their
ability to expand the application over time. But a client-server
Access application would be much easier from a development standpoint.

Thanks again.

[Larry Linson]

"evenlater" <ev*******@gmail.comwrote

This is probably a stupid question, but I'm unclear on how that would
work. Each user has to have his own front end, but the FE is stored on
the server machine that the user terminals into?

The only stupid question is one to which you don't know the answer but don't
ask because you think it might seem stupid. Yes, each user has their own
front end, stored on the server machine, because having multiple users
logged in to a single copy of the front-end (although it can be done)
significantly increases the chance of corruption of that single copy. You
may go for years without a problem, but a minor change to the database or
the environment may trigger frequent incidents of corruption.

(In answer to a likely next question: I don't know anyone who would claim to
know all the possible causes of that corruption to advise you how to avoid
it.)

Larry Linson
Microsoft Office Access MVP

[Tony Toews [MVP]]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote:

>1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this
way? I need detailed instructions for an absolute TS beginner.

There's really not much to it. The most complicated part of it is
the licensing. With A2K7 you have to use the Enterprise version of
Windows Server and the Enterprise version of Access.

I wonder if the A2007 runtime would install on a Terminal Server system?

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/

[Rick Brandt]

Tony Toews [MVP] wrote:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote:

>>1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this
way? I need detailed instructions for an absolute TS beginner.

There's really not much to it. The most complicated part of it is
the licensing. With A2K7 you have to use the Enterprise version of
Windows Server and the Enterprise version of Access.

I wonder if the A2007 runtime would install on a Terminal Server
system?

Tony

If not, that will kill using TS for Access apps for a lot of people. Just
one more license requirement to drive the costs up.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Tony Toews [MVP]]

"Rick Brandt" <ri*********@hotmail.comwrote:

>>>1. Do you know of a good written how-to source on the subject of
terminal services, preferably with a focus on using Access this
way? I need detailed instructions for an absolute TS beginner.

There's really not much to it. The most complicated part of it is
the licensing. With A2K7 you have to use the Enterprise version of
Windows Server and the Enterprise version of Access.

I wonder if the A2007 runtime would install on a Terminal Server
system?

If not, that will kill using TS for Access apps for a lot of people. Just
one more license requirement to drive the costs up.

Exactly what I was thinking.

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/

[DawnTreader]

Hello

Hamachi allows you to tunnel through any security setups in a secure
way. :)

seems kinda silly, but when you dont want to have to spend huge
dollars dealing with your firewall company because all they really
want is your cash, then using hamachi to circumvent that security is
easy and simple and secured with AES-256. whatever that is, all i know
is it is secure.

additionally you can set up the network that you use hamachi to create
with a good strong password that you give to those who need it. and
you can also kill the network if necessary or simply take the terminal
server offline from any networks without having to reset everthing
later. i actually use hamachi to communicate down times for the system
we have going at my work place so that the people who use it are aware
we are doing maintenance.

hamachi also tunnels right through windows firewalls, which is a pain
for an average person to understand and make exceptions for. put
simply hamachi is a great tool.

i use it in varying capacities, one being a terminal server
application using access.

[David W. Fenton]

DawnTreader <al*******@gmail.comwrote in
news:87**********************************@n1g2000p rb.googlegroups.com
:

Hamachi allows you to tunnel through any security setups in a
secure way. :)

Er, isn't that what a VPN does?

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