Is it possible to connect to Access Database using VPN?


P

Paul

An Access application is splitted into front-end and back-end and it was set
up to work as a client/server application for three users within the office
LAN. However a user need to access it from a remote location and he
suggested that I should set up the VPN and it will work from a remote
location. Is it possible to connect it to an Access DB using VPN connection?
Thanks.
 
Ad

Advertisements

A

Albert D. Kallal

Yes, that VPN should simply allow you to "see" that shared folder on your
network, and thus presumably when you go to link the tables via the linked
table manager, you simply browse to that shared folder. With a VPN, that
shared folder can be on any computer anywhere in the world..and as long as
it part of your network...then you can link to that file.

So, remember, as always, it don't matter if the back end file is on your
luck hard drive, or a computer halfway around the world..you STILL MUST BE
ABLE TO BROWSE to that folder on that remote computer like any other windows
folder. If you have full access to that folder, then you can link to it
using the linked table manager.

However, there is many problems doing what you want, and really the BEST
solution is to use remote desktop...

I explain the problems of using a VPN and why you can't really use it here:

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

Albert D. Kallal

So how does one go about replacing Jet with SQL?
Well, first, in that link, one suggestion was to use remote desktop...and
thus you don't need to convert to sql.

However, assuming you been running a split database for some time, then you
only are moving the data from a back end mdb file to sql server, and then in
place of linking the tables to a back end mdb file, you link them to sql
server.

It is assumed that you been using linked talbes for some time now....
If linked tables are new to you, you can read the follwing:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

It is somewhat easier to migrate your application to sql server if you
already been linking tables as a above.

However, regardless of a split application now, if you do link those tables
to sql server, then about 90% (or even more) of your application should
function. In fact, if you don't have any code, then often zero things need
be changed on the ms-access side.

Really, however, you need to learn to become familiar and comfortable using
sql server to setup the tables etc. However, for the most part, your front
end part (your existing ms-access forms, code etc) should not have to be
changed very much....


ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

http://support.microsoft.com/?id=128384

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321
 
T

Tom Lake

It is assumed that you been using linked talbes for some time now....
If linked tables are new to you, you can read the follwing:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

It is somewhat easier to migrate your application to sql server if you
already been linking tables as a above.

However, regardless of a split application now, if you do link those tables
to sql server, then about 90% (or even more) of your application should
function. In fact, if you don't have any code, then often zero things need
be changed on the ms-access side.

Really, however, you need to learn to become familiar and comfortable using
sql server to setup the tables etc. However, for the most part, your front
end part (your existing ms-access forms, code etc) should not have to be
changed very much....

Thanks for the quick and informative reply! I've been using Access since 1.0
so I definitely know the Access side (including splitting). I've never used SQL
Server or anything like it, however, so that's the part I need to learn.

Tom Lake
 
Ad

Advertisements

J

Jeff Boyce

Paul

A minor technicality, aside from the excellent advise you've already
received...

An Access front-end/back-end combination is not a client/server application,
it is a file/server application.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
P

Paul

Hi albert:

Thank you for your excellent advise. I do not think using VPN is a "sound"
option to deploy the Access "file/server" application over the WAN. I
believe that the "Remote Desktop" is a better solution to open the Access
application remotely. I can set up different folders in the RD server and
place the Access front-end in it for each of the user to access the
application remotely to eliminate more than one user to open the Access
front-end at the same time or should I worry if more than one remote user
open the same Access front-end at the same time. However the RD server can
only have 2 remote connections unless I set the RD server on different
machine with a differnt RD port other than the default 3389 port to map it
on the router so that it can forward it to different RD server. I do not
like the idea "replication" I set one up before, it is difficult to set up
and user "on the road" does not have the uptoday data often if users are
away from the office for a long period of time (I am talking about salesamn
travel oversea).

Once again thank you for your advise.

Cheers

Paul
 
A

Albert D. Kallal

Thank you for your excellent advise. I do not think using VPN is a "sound"
option to deploy the Access "file/server" application over the WAN.
correct, and that is my major point of the article...
I believe that the "Remote Desktop" is a better solution to open the
Access application remotely. I can set up different folders in the RD
server and place the Access front-end in it for each of the user to access
the application remotely to eliminate more than one user to open the
Access front-end at the same time
Yes, that is exactly what you do (so, the RD server will have a separate mde
in a folder for each user that logs on).
or should I worry if more than one remote user open the same Access
front-end at the same time.
Yes, do worry...and don't allow multiple users into the same front end
However the RD server can only have 2 remote connections
Actually, it only allows two "free" users running as remote admin users. If
you purchase "client access licenses" (cals), then you can have as many
users logged on as your machine can handle. This does mean that you will
have to run "terminal services", and I also believe a "license" server kicks
in also. However, the behaviour, and how TS works is really identical to
remote desktop (it is the same technology).

So, terminal services is really what you use if you need more then two
remote users at the same time.
 
D

David W. Fenton

Actually, it only allows two "free" users running as remote admin
users. If you purchase "client access licenses" (cals), then you
can have as many users logged on as your machine can handle.
Except if it's SBS, which doesn't allow the addition of any
additional TS client CALs (a good reason to avoid SBS).
 
J

Jeff Boyce

.... and that should have been "advice" ... (clearly insufficient caffeine
<g>)

Jeff Boyce
 
Ad

Advertisements

D

David W. Fenton

I am having good success with WinConnect Server
http://www.thinsoftinc.com/product_thin_client_winconnect_server_xp
.aspx Allows up to 21 simultaneous remote connections to Small
Business Server or Windows XP machines, at a considerable cost
advantage compared with TS.
How do you figure? You have to pay for both the clients and the
server, whereas WTS comes on all servers and RDP comes on all
workstations. It's only about $40/seat to add the CALs. I looked at
Winconnect and it seemed to me to cost more than that, and then has
to be installed, which vastly increases the administrative costs.
 
Ad

Advertisements

P

Paul

Can you install TS on a Windows XP Professional edition? I checked the
Microsoft Web site for the pricing of the Windows 2003 Server R2 and the 5
Cals License for the Terminal Services, as follows:

Product Offering U.S. Price* Description

Windows Server 2003 R2 Standard Edition


$999


Available in 32-bit and 64-bit versions.



Windows Server 2003 R2 Standard Edition


$1,199


Available in 32-bit and 64-bit versions. Includes 10 CALs (User or
Device, chosen after purchase)



Windows Server 2003, TS Client Access License 5-pack


$749

5 additional Windows Server 2003 Terminal Server (TS) CALs (User or
Device, chosen at time of purchase)


If I can get away with just the cost of the additional TS Client Access
License for a Windows XP Pro machine would be wonderful. I guess not....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top