Access + VPN

[Sparky]

Ok, I have a database which needs to work over VPN. The time delays using a
normal mdb file are ridiculous so this isn't practical. I have read a few
options a) design a web interface and b) use SQL. I'm running SBS 2003 on my
server so b should be a possibility but I have no experience of SQL at all.
Have been looking at access a bit more and am wondering if splitting the
database will have the same effect? Any suggestions or comments most welcome.

 

[Sylvain Lafontaine]

Chances are high that it won't be good either with SQL-Server.

If your frontend is really simple, it's possible that it will work correctly
against a SQL-Server over the VPN but your GUI really need to be simple and
it won't take to much of complication before the speed become ridiculous
again.

A well designed frontend against SQL-Server will work correctly over the
Internet (WAN) or a VPN but this will require that you use unbound forms and
make a lot of VBA code and will probably require more work than building a
web interface.

Splitting the database will change practically nothing. You will save time
on loading the forms and modules over the wire but access to the data will
remain exactly the same.

Unless you're an advanced programmer and have a lot of time, your only good
shot at using Access over the internet - without reworking a lot - would be
to use Terminal Server or a similar product like Thinsoft
(www.thinsoftinc.com).

 

[David W. Fenton]

a) design a web interface and b) use SQL.

c. set up Terminal Server on a server on one end of the VPN and have
the users run the app on Terminal Server across the VPN.

This would be the simplest and most cost-effective solution. Your
app would run unchanged and the only costs would be setup and
administration of the Terminal Server, along with the minor
licensing costs. In the long run, it also cuts down on adminstration
because your whole app is being run from one machine, rather than
with front ends spread across a bunch of workstations.

 

[Armen Stein]

Chances are high that it won't be good either with SQL-Server.


A well designed frontend against SQL-Server will work correctly over the
Internet (WAN) or a VPN but this will require that you use unbound forms and
make a lot of VBA code and will probably require more work than building a
web interface.

We develop a lot of systems using local Access applications and SQL
Server that perform adequately over VPN or other broadband
connections. It actually is possible to use bound forms, but for
updating they must be limited to just one record, or very few records.
For forms with read-only lists of records, they can be bound to
passthrough queries. There are a lot of other optimization
techniques and VBA code to use, and they're not for an Access
beginner.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com