Is Access a practical solution for internet access to a database?

[David Anderson]

In the past, I have done a fair bit of Access development, both creating
standalone apps and network-based apps with all data stored on a Windows
server - though my skills are now rather rusty. A new requirement is emerging
that looks like it would best be done by end users accessing a central
database via the Internet (using broadband connections). This is not
something I have ever done before or even begun to investigate in depth.

My question is simply, can it be done? Are there any caveats or
restrictions? Could this be implemented without the users having Access
licences, i.e. just using Internet Explorer? Is response time likely to be an
issue (the biggest table would probably contain well under 10,000 records)?
Are there Access version dependencies? I will soon receive a copy of Access
2007, though I've heard that it is significantly different from previous
versions (I might prefer to use Access 2003 for this project if the 2007
learning curve looks a little steep).

I'm just looking for positive vibes before investing time in this project.
Any guidance would be much appreciated.

David

 

[Rick Brandt]

In the past, I have done a fair bit of Access development, both
creating standalone apps and network-based apps with all data stored
on a Windows server - though my skills are now rather rusty. A new
requirement is emerging that looks like it would best be done by end
users accessing a central database via the Internet (using broadband
connections). This is not something I have ever done before or even
begun to investigate in depth.

My question is simply, can it be done? Are there any caveats or
restrictions? Could this be implemented without the users having
Access licences, i.e. just using Internet Explorer? Is response time
likely to be an issue (the biggest table would probably contain well
under 10,000 records)? Are there Access version dependencies? I will
soon receive a copy of Access 2007, though I've heard that it is
significantly different from previous versions (I might prefer to use
Access 2003 for this project if the 2007 learning curve looks a
little steep).

I'm just looking for positive vibes before investing time in this
project. Any guidance would be much appreciated.

David

If you build a completely new web app with ASP, ASP.Net, JSP, etc., and only use
your MDB file for the database then users will not need any Access license or to
have Access installed.

If you use remote control software like Terminal Server that they run over a VPN
connection then they would be using your app as-is with no changes required by
you at all. However in that case they WILL need licenses to connect and also
licenses for Access unless the PC that they are remoting into is using the
Access runtime. In that case all they need are licenses to connect to the
Terminal Server. Using Terminal Server would be somewhat dependent on how many
concurrent users you need to support.

 

[Albert D. Kallal]

It been my view that ms-access is not a web based system.

However, you can use ms-access remotely. I give some options here:

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

 

[David W. Fenton]

If you use remote control software like Terminal Server that they
run over a VPN connection then they would be using your app as-is
with no changes required by you at all.

Is there not a web-browser-based method of doing the same thing
(i.e., running an app on Terminal Server)? I assume the same
licensing applies, but it's launched through your web browser so
that there's no need to install the Remote Desktop Client (of
course, it's installed by default on WinXP and Vista, so that's not
much of an issue any more).

 

[Rick Brandt]

Is there not a web-browser-based method of doing the same thing
(i.e., running an app on Terminal Server)? I assume the same
licensing applies, but it's launched through your web browser so
that there's no need to install the Remote Desktop Client (of
course, it's installed by default on WinXP and Vista, so that's not
much of an issue any more).

Yeah, I've used it for testing a few of our Citrix apps, but prefer the client
app version myself.

 

[David Anderson]

Thanks for the pointers, guys. A Terminal Server based solution seems to be
getting a lot of votes, but I know nothing about this technology at present.
What little reading I have done suggests that it would require me to install
an internet-connected server PC running Microsoft Windows Server 2003. That
might not be an affordable solution for the organisation I am trying to help,
though I'm ruling nothing out yet.

To put this discussion in perspective, I am aiming to develop a database app
for a camera club to automate the admin associated with running a big
international competition. To my horror, almost everything is done manually
at present - only the digital image competition makes use of computers. I'm
also a member of this club, so my time is being provided at no charge.

Packages containing large prints and/or 35mm transparencies (slides) and/or
CDs with digital images are all sent to a single address (the home of another
club member). She then distributes these packages, unopened, to the print
secretary, the slide secretary or the digital secretary. The shape of the
package tends to provide a fairly reliable indication of the contents, though
some packages contain mixed media. The subsequent sorting, labelling and
judging processes, etc, take place in three parallel, asynchronous, streams.
Data on all three media types is shared at the end of the competition for the
generation of statistics, the creation of a single catalogue brochure and the
return of prints and slides to their owners.

From a database perspective, I need to ensure that each entrant is given a
unique key ID and has his or her address details, etc, entered only once into
the system. Each secretary would do the data entry for the entries passed to
him, but because entrants can submit work to any or all of the media
categories, all three secretaries need the ability to share a common Entrants
table (little if anything else needs to be shared until the end of the
competition). The entries arrive between October to January, peaking towards
the end of this period.

The competition is run on a break-even basis so I need to find an affordable
solution for sharing the Entrants table, which would only have about 1000
records. One rather crude solution would be for them each to email me their
version of the table. I could then merge the data, try to highlight any
duplicates and then email the result back to the three secretaries, but this
would be a cumbersome process that would probably break down during the peak
of entry processing activity. I would much prefer the Entrants table to be
shared live across the Internet.

Does this help?

David

 

[Rick Brandt]

Thanks for the pointers, guys. A Terminal Server based solution seems
to be getting a lot of votes, but I know nothing about this
technology at present. What little reading I have done suggests that
it would require me to install an internet-connected server PC
running Microsoft Windows Server 2003. That might not be an
affordable solution for the organisation I am trying to help, though
I'm ruling nothing out yet.

To put this discussion in perspective, I am aiming to develop a
database app for a camera club to automate the admin associated with
running a big international competition. To my horror, almost
everything is done manually at present - only the digital image
competition makes use of computers. I'm also a member of this club,
so my time is being provided at no charge.

Packages containing large prints and/or 35mm transparencies (slides)
and/or CDs with digital images are all sent to a single address (the
home of another club member). She then distributes these packages,
unopened, to the print secretary, the slide secretary or the digital
secretary. The shape of the package tends to provide a fairly
reliable indication of the contents, though some packages contain
mixed media. The subsequent sorting, labelling and judging processes,
etc, take place in three parallel, asynchronous, streams. Data on all
three media types is shared at the end of the competition for the
generation of statistics, the creation of a single catalogue brochure
and the return of prints and slides to their owners.

From a database perspective, I need to ensure that each entrant is
given a unique key ID and has his or her address details, etc,
entered only once into the system. Each secretary would do the data
entry for the entries passed to him, but because entrants can submit
work to any or all of the media categories, all three secretaries
need the ability to share a common Entrants table (little if anything
else needs to be shared until the end of the competition). The
entries arrive between October to January, peaking towards the end of
this period.

The competition is run on a break-even basis so I need to find an
affordable solution for sharing the Entrants table, which would only
have about 1000 records. One rather crude solution would be for them
each to email me their version of the table. I could then merge the
data, try to highlight any duplicates and then email the result back
to the three secretaries, but this would be a cumbersome process that
would probably break down during the peak of entry processing
activity. I would much prefer the Entrants table to be shared live
across the Internet.

Does this help?

David

The core issue is that an "Access Application", the forms, reports, etc., that
the user actually interacts with simply cannot be run from a web page. You
would have to build a web page that replaces all of the user interface stuff
that your Access app has (from scratch) using technologies that you are not
familiar with and which even for a seasoned web professional would take much
more time and effort than building the original Access application.

Or you can use terminal server.

Access is about the easiest and fastest way to build a nice thick client
database application. If it were easy to "webify" them you would see such
applications all over the internet. The reason you don't is because it can't be
done.

 

[David Anderson]

Rick,
Okay. I take your point, so let me pursue the TS option a little further.
Was I correct in my assumption that I would need to have a dedicated PC
running Windows Server 2003? The usual minimum of 5 Client Access Licenses
would meet my needs. Does TS come included with Server 2003 or is it an extra
cost option? Is it complicated to deploy TS? It's not vital, but can a TS
client be installed on a Mac?

You previously stated, "If you use remote control software like Terminal
Server that they run over a VPN connection then they would be using your app
as-is with no changes required by you at all. However in that case they WILL
need licenses to connect and also licenses for Access unless the PC that they
are remoting into is using the Access runtime. In that case all they need
are licenses to connect to the
Terminal Server". I guess you are saying here that it would be sensible to
create a production version of my app that is based on the runtime module.

The next issue concerns the need for a VPN. Once again, I have no practical
experience of this technology - though I am willing to learn. A quick browse
suggests that I could obtain such a service for a single PC (the central PC
holding the database app) for about $70 per year. That price was from
www.LogMeIn.com for their Pro product. I'm not asking for comments on this
particular ISP, but is this class of VPN service likely to meet my needs?

David

 

[Rick Brandt]

Rick,
Okay. I take your point, so let me pursue the TS option a little
further. Was I correct in my assumption that I would need to have a
dedicated PC running Windows Server 2003? The usual minimum of 5
Client Access Licenses would meet my needs. Does TS come included
with Server 2003 or is it an extra cost option? Is it complicated to
deploy TS? It's not vital, but can a TS client be installed on a Mac?

TS is included with server 2003, but you only automatically get two free TS
connections and those must both have administrator rights. For what you are
talking about you woudl need separate CALs per user.

The TS client *might* be available for a MAC. I have heard Linux people
discussing a client for it so if Linux has one I would expect MAC to as well.
If your users have Windows XP then they already have the client.

You previously stated, "If you use remote control software like
Terminal Server that they run over a VPN connection then they would
be using your app as-is with no changes required by you at all.
However in that case they WILL need licenses to connect and also
licenses for Access unless the PC that they are remoting into is
using the Access runtime. In that case all they need are licenses to
connect to the
Terminal Server". I guess you are saying here that it would be
sensible to create a production version of my app that is based on
the runtime module.

Unless the users already have Access in which case you are covered there.

The next issue concerns the need for a VPN. Once again, I have no
practical experience of this technology - though I am willing to
learn. A quick browse suggests that I could obtain such a service for
a single PC (the central PC holding the database app) for about $70
per year. That price was from www.LogMeIn.com for their Pro product.
I'm not asking for comments on this particular ISP, but is this class
of VPN service likely to meet my needs?

The VPN is only to make the whole thing secure. You "could" do this without
one, but I would not recommend it. I'm sorry, but I am not a good resource for
the specifics of VPNs. I use one from my house to work, but do not administer
anything about it.

 

[David Anderson]

Rick,
Thanks for the info. You appear to be saying that the 5 CALs that typically
come with MS Server 2003 Std Edition are for LAN access only and don't cover
WAN use via Terminal Services. Is that correct?

To extend the debate, would I be right in thinking that instead of using TS
as a very thin client, I could write front-end apps as an Access Project for
each user PC and have the data stored on a back-end SQL Server database? I'm
guessing that a somewhat 'thicker' client such as this would offer faster
response times. I seem to remember that there used to be a cut down form of
SQL Server called MSDE (permitting up to 5 users) that was supplied free with
some versions of Access? Is this still the case?

David

 

[David W. Fenton]

Access is about the easiest and fastest way to build a nice thick
client database application. If it were easy to "webify" them you
would see such applications all over the internet. The reason you
don't is because it can't be done.

Seems to me that if the app doesn't exist yet and it needs to be
delivered over the Internet, then develop it as a web app from
scratch and leave Access entirely out of the loop. I'm a PHP fan
myself, but that's not too popular around here (I'm a big believer
in keeping variety in the software ecosystem, so won't use MS web
solutions).

 

[David W. Fenton]

TS is included with server 2003, but you only automatically get
two free TS connections and those must both have administrator
rights. For what you are talking about you woudl need separate
CALs per user.

And don't buy Small Business Server, as you can't add any TS
licenses to it at all -- you need Standard or Enterprise.

 

[David W. Fenton]

Thanks for the info. You appear to be saying that the 5 CALs that
typically come with MS Server 2003 Std Edition are for LAN access
only and don't cover WAN use via Terminal Services. Is that
correct?

Np, it's not correct.

The standard 5 CALs are for simple *network* access, not TS access.
You need to purchase additional TS CALs, one for each computer
connected to the TS (or one for each user, but the latter doesn't
work as well because the licensing server was designed for
computer-based CALs).

To extend the debate, would I be right in thinking that instead of
using TS as a very thin client, I could write front-end apps as an
Access Project for each user PC and have the data stored on a
back-end SQL Server database? I'm guessing that a somewhat
'thicker' client such as this would offer faster response times. I
seem to remember that there used to be a cut down form of SQL
Server called MSDE (permitting up to 5 users) that was supplied
free with some versions of Access? Is this still the case?

Since the app doesn't yet exist, skip Access entirely, and write it
as a browser-based app.

That seems to me to be what makes the most sense. It's certainly
what I'd do in your situation. TS only makes sense for this kind of
situation when you already have an existing Access app.

 

[David Anderson]

David,
As you say, nothing is written yet, so I'm always interested in hearing
about new ways to solve my problem. Access is in the frame simply because I
already have skills in exploiting its 'thick client' capabilities. However,
that does not address my need for database connectivity via the Internet.
Whatever I do, I will have to learn some new skills and spend some money.

So persuade me. Is PHP easy to learn for someone coming from a VBA
environment? Are there lots of third party tools that can speed up the
development process? Can you provide links to some impressive examples of PHP
based apps?

David

 

[Rick Brandt]

David W. Fenton wrote:
[snip] That seems to me to be what makes the most sense. It's certainly

what I'd do in your situation. TS only makes sense for this kind of
situation when you already have an existing Access app.

Agreed.

 

[David W. Fenton]

Is PHP easy to learn for someone coming from a VBA
environment?

No, it's not easy at all. Indeed, the documentation is terrible if
you're accustomed to the cushy world of Access help files and VB
code. Of course, PHP advocates think their documentation is
wonderful, but that's because they come from a world where there's
very little decent documentation at all.

 

[Steve Schapel]

David,

A couple of other ideas for you to explore...

You can run a terminal server type of solution on a Windows XP machine,
using technology such as
http://www.thinsoftinc.com/product_thin_client_winconnect_server_xp.aspx.
I have used this myself, and find it excellent. There are costs, of
course, but given the scope of what you are doing, I would expect this
to be much more manageable than a full-blown TS setup.

If we are looking at Access 2007, then this would also seem to be an
ideal candidate for putting your data on a SharePoint site, and linking
to it from your Access application on the secretatries' computers.
Offline data management, and very nice synchronisation, are supported.
A lot of web hosting services are now offering SharePoint bundled with
their hosting plans, at minimal cost. For example, you can get a WSSv3
site at www.webhost4life.com for $15. A bit of a learning curve, but
not as much as learning to develop with PHP or ASP.Net!

 

[SmartbizAustralia]

You might just use SharePoint if you have a windows 2003 server box.

You can even link access 2007 and the run-time is launched later this
year around july so this is a very simple and doesn't require you
learning .net which is the other microsoft alternative to webalise
applications.

Regards,
Tom Bizannes
Sydney,Australia
MsAccess and Asp.Net development

 

[Jeff Conrad [MSFT]]

David,

I'm interested to learn more about your scenario. Would you mind sending me
an offline message? Use the Contact form on my web site to reach me.

Thanks,
--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com