I have an Access 2003 application that works well in a LAN environment with
a Jet based back end. However, I would like to move this back end to a SQL
Server database hosted on a website so that my users could run the
application from any Internet-connected PC, using a modified version of my
Access front end.

I know that it is technically possible to do this, but is it a perfectly
normal way of using SQL Server? Is response time likely to be an issue or
can careful design make performance similar to that with a LAN based back
end?

David

"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
news:edUj6pauIHA.4952@TK2MSFTNGP05.phx.gbl...
>I have an Access 2003 application that works well in a LAN environment with
>a Jet based back end. However, I would like to move this back end to a SQL
>Server database hosted on a website so that my users could run the
>application from any Internet-connected PC, using a modified version of my
>Access front end.
>
> I know that it is technically possible to do this, but is it a perfectly
> normal way of using SQL Server? Is response time likely to be an issue or
> can careful design make performance similar to that with a LAN based back
> end?
>
> David
Hi

If you make the posts used SQL server open to the internet, then anyone can
access your database and try to hack into it. If you use a VPN then things
may be more secure. If you have your SQL Server behind a firewall and a web
front end, then your database should be safe (if implemented correctly!).

As far as performance goes, a connection over the internet (including using
a VPN) can quite often be too slow or less reliable than is necessary to
allow a user satifactory access to the application, therefore you may get
instances where it works perfectly well for one user and impossible for
others.

John

Hi John,
Thanks for your reply. Are your performance and reliability warnings a way
of saying that very few people implement solutions of this type? Would
running local copies of a Jet back end and then using Replication to
synchronise these copies via the Internet be a better approach? Does SQL
Server actually have anything to offer me in this situation?

David


"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:8469BE8D-653E-47EC-97C7-A980BAB68B5A@microsoft.com...
>
> Hi
>
> If you make the posts used SQL server open to the internet, then anyone
> can access your database and try to hack into it. If you use a VPN then
> things may be more secure. If you have your SQL Server behind a firewall
> and a web front end, then your database should be safe (if implemented
> correctly!).
>
> As far as performance goes, a connection over the internet (including
> using a VPN) can quite often be too slow or less reliable than is
> necessary to allow a user satifactory access to the application, therefore
> you may get instances where it works perfectly well for one user and
> impossible for others.
>
> John

"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
news:uwsr0TbuIHA.1220@TK2MSFTNGP04.phx.gbl...
> Hi John,
> Thanks for your reply. Are your performance and reliability warnings a way
> of saying that very few people implement solutions of this type? Would
> running local copies of a Jet back end and then using Replication to
> synchronise these copies via the Internet be a better approach? Does SQL
> Server actually have anything to offer me in this situation?
>
> David
>

Hi David

Since the slammer worm most people have been very wary of exposing their SQL
Server databases via the internet. If you are happy with replication, then I
assume you can cope with a certain latency in your data, therefore having
them connected to one main database is not a requirement anyhow?
You can certainly replicate to access but if you want a more homogenous
environment you may want to use SQL Express or Compact Edition (although
then you wouldn't want to use an Access front end). Your users will also be
able to use the system in a disconnected state which may be beneficial.
What you would need to consider is any potential conflict resolution issues.

A different approach would be to change the Access Front end into a web
system (which I believe you can do!) which may give you the potential to use
different types of hardware and potentially easier upgrades and maintenance.

HTH

John

John,
I'm guessing that using a VPN would be a way to address the security issues
(yet another new thing for me to learn!). As you surmise, I can tolerate a
degree of latency. It's not vital that all the users are looking at an
identical version of the data tables at all times, though I would be
concerned if the replication process was too longwinded and complex to
operate on a daily basis.

> You can certainly replicate to access but if you want a more homogenous
> environment you may want to use SQL Express or Compact Edition (although
> then you wouldn't want to use an Access front end).

I didn't understand the above comments. In what way would I be achieving
greater homogeneity by using any variety of SQL Server? Surely the peak of
homogeneity is achieved by using Access for both the front and back end of
my app, as at present. Please clarify.

> A different approach would be to change the Access Front end into a web
> system (which I believe you can do!) which may give you the potential to
> use different types of hardware and potentially easier upgrades and
> maintenance.

If I was to rewrite the entire application so that it runs on a website,
backed up by a web hosted SQL Server database, then I would certainly enable
my users to run it from an Internet cafe in Kabul or Baghdad or wherever
thay choose to spend their vacation but I think I would struggle to achieve
it within a reasonable time. Trying to recreate the sophistication of an
Access front end via an Internet browser would be a major challenge for me.

David


"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:7695A70C-17A3-434A-852B-116982F17AFE@microsoft.com...
>
> Hi David
>
> Since the slammer worm most people have been very wary of exposing their
> SQL Server databases via the internet. If you are happy with replication,
> then I assume you can cope with a certain latency in your data, therefore
> having them connected to one main database is not a requirement anyhow?
> You can certainly replicate to access but if you want a more homogenous
> environment you may want to use SQL Express or Compact Edition (although
> then you wouldn't want to use an Access front end). Your users will also
> be able to use the system in a disconnected state which may be beneficial.
> What you would need to consider is any potential conflict resolution
> issues.
>
> A different approach would be to change the Access Front end into a web
> system (which I believe you can do!) which may give you the potential to
> use different types of hardware and potentially easier upgrades and
> maintenance.
>
> HTH
>
> John

See inline:
"David Anderson" <PLSdcanderson88@REMOVEhotmail.FIRSTcom> wrote in message
news:O2WQyAcuIHA.2188@TK2MSFTNGP04.phx.gbl...
> John,
> I'm guessing that using a VPN would be a way to address the security
> issues (yet another new thing for me to learn!). As you surmise, I can
> tolerate a degree of latency. It's not vital that all the users are
> looking at an identical version of the data tables at all times, though I
> would be concerned if the replication process was too longwinded and
> complex to operate on a daily basis.
>
>> You can certainly replicate to access but if you want a more homogenous
>> environment you may want to use SQL Express or Compact Edition (although
>> then you wouldn't want to use an Access front end).
>
> I didn't understand the above comments. In what way would I be achieving
> greater homogeneity by using any variety of SQL Server? Surely the peak of
> homogeneity is achieved by using Access for both the front and back end of
> my app, as at present. Please clarify.

I assumed that you would have SQL Server as the backend and using SQL Server
replication to either SQL Server or Access.
I don't know how well Access can handle replication

>
>> A different approach would be to change the Access Front end into a web
>> system (which I believe you can do!) which may give you the potential to
>> use different types of hardware and potentially easier upgrades and
>> maintenance.
>
> If I was to rewrite the entire application so that it runs on a website,
> backed up by a web hosted SQL Server database, then I would certainly
> enable my users to run it from an Internet cafe in Kabul or Baghdad or
> wherever thay choose to spend their vacation but I think I would struggle
> to achieve it within a reasonable time. Trying to recreate the
> sophistication of an Access front end via an Internet browser would be a
> major challenge for me.

Not being that up to date with access! but I seem to remember a posting
that talked about something in MS Access that ed to convert it into a
web app! Maybe that's one for the Access news group?

>
> David
>

John