Split DB over the internet with SQL backend?

[Mark 123]

I just read at

http://groups.google.com/group/microsoft.public.access.forms/browse_frm/thread/766ba7b493eacb63/8a7d5504ee848c21?lnk=st&q=migrate+access+97+to+access+2007&rnum=1#8a7d5504ee848c21

that if you have a spilt db using SQL server rather than jet, the
front-end's can connect to the backend SQL server over the internet.

Has anyone had experience with this?

We are thinking of migrating an commercially sold Access97 runtime app to
Access 2007 and if this is true it may well pay to redo the backend in SQL
Server so users can use over the net if needed. What other upsides/downsides
are there to this?

Do 'little' end-users have to be rocket scientists to install and maintain
SQL server? $? Currently they only have to place one MDB backend file on a
shared fileserver. Some of them only have 5 PC's networked in their
business. Others have 500.

TIA

[Baz]

Re: Split DB over the internet with SQL backend?


"Mark 123" <none@none.co m> wrote in message
news:TVd2g.1575 6$JZ1.605448@ne ws.xtra.co.nz.. .[color=blue]
> I just read at
>[/color]
http://groups.google.com/group/micro...7d5504ee848c21[color=blue]
> that if you have a spilt db using SQL server rather than jet, the
> front-end's can connect to the backend SQL server over the internet.
>
> Has anyone had experience with this?
>
> We are thinking of migrating an commercially sold Access97 runtime app to
> Access 2007 and if this is true it may well pay to redo the backend in SQL
> Server so users can use over the net if needed. What other[/color]
upsides/downsides[color=blue]
> are there to this?
>
> Do 'little' end-users have to be rocket scientists to install and maintain
> SQL server? $? Currently they only have to place one MDB backend file on a
> shared fileserver. Some of them only have 5 PC's networked in their
> business. Others have 500.
>
> TIA
>
>[/color]

Yes, that is quite possible. However, it will not be super-fast. How
practical it is depends on what the system is actually doing and what kind
of bandwidth is available. Also, anytime you use the internet, you are at
the mercy of a lot of widely-distributed technology and a lot of 3rd-party
companies, so you need to accept that the link may sometimes be down for
reasons completely out of your control.

For security resons, this should really only be done using a VPN.

Migrating an mdb to SQL Server is not a trivial exercise, a complete reveiw
of your design is necessary to get the best out of the very different
architecture, particularly if you are contemplating the use of a slow
connection i.e. the internet.

It is perfectly possible to deliver SQL Server to end users in such a way
that they can set it up and use it without any DBA know-how.

[Lyle Fairfield]

Re: Split DB over the internet with SQL backend?

How fast is super fast?

I use an SQL db that lives on a Server in California (I am 4200 km
away), with front ends as HTA and Access ADPs on laptops and as ASP on
my web server. These are used in Canada, and from time to time in
England, France, Germany, The Czech Republic and Bulgaria.
I can recall only once that the connections have failed, and that for a
period of about 5 minutes. It's not noticeably slower than a mdb on my
own workstation.
One can get an idea of speed (and the power of ADO) by opening
http://www.ffdba.com/columns.aspchoosing some columns to show and
clicking submit, or not choosing any columns and clicking submit.

This is a very simple support db for my and relatives business account
keeping. You will find nothing complex here, and perhaps you will
realize that the whole thing has grown without much direction.

Regardless, for me in Ontario, listing the basic Table Schema for 27
tables takes less than one tenth of one second (that is the server work
takes less than one tenth of one second; the time for the html to be
sent to and displayed by my workstation is additional, but it seems to
be almost nothing). As more columns are added, more time is required,
but even with all columns selected the time is less than one-half a
second.

I have no difficulty matching or beating these results with a desktop
application using the same db.

[Lyle Fairfield]

Re: Split DB over the internet with SQL backend?

The link should be

http://www.ffdba.com/columns.asp

[Anthony England]

Re: Split DB over the internet with SQL backend?

"Mark 123" <none@none.co m> wrote in message
news:TVd2g.1575 6$JZ1.605448@ne ws.xtra.co.nz.. .[color=blue]
>I just read at
> http://groups.google.com/group/micro...7d5504ee848c21
> that if you have a spilt db using SQL server rather than jet, the
> front-end's can connect to the backend SQL server over the internet.
>
> Has anyone had experience with this?
>
> We are thinking of migrating an commercially sold Access97 runtime app to
> Access 2007 and if this is true it may well pay to redo the backend in SQL
> Server so users can use over the net if needed. What other
> upsides/downsides
> are there to this?
>
> Do 'little' end-users have to be rocket scientists to install and maintain
> SQL server? $? Currently they only have to place one MDB backend file on a
> shared fileserver. Some of them only have 5 PC's networked in their
> business. Others have 500.
>
> TIA[/color]


I have done this without any problems with speed or reliability. I went to
a web host (http://www.fasthosts.co.uk) to provide space on a SQL Server
database which gave me some control over the setup (eg able to add logins)
but not total control of the machine.
It was set up with port 1433 open and no vpn, so provided you knew an ip
address and username/password you could sit down anywhere in the world and
access the data. This configuration is not the most secure (the data was
not overly precious) and although I can't prove the data was not looked at
by unauthorised people, I doubt it very much and I am sure it was not
tampered with in any way.

If the sole purpose of the exercise is to enable the database over the web,
then moving to SQL Server may not be the best option. There are other ways
such as Terminal Services or PC Anywhere where users could remote control
another machine to access the database. This has the advantage that you do
not need to modify your coding in any way at all, and any questions of
setup/security are now someone else's problem.

If, however, you think you may need to build up some experience with SQL
Server in any case, then you might want to install it on your local network
and get the db up and running and tuned for SQL Server (you could
potentially do a lot of re-coding for this). Once the app is up and
working, you could build a setup module which creates a new copy of the
database and any other initialisation.

I would guess that customers with 500 pcs might already have their own SQL
database together with a salaried db administrator who would be quite happy
to look after the database. For smaller customers, you could do the setup
and keep a watchful eye over it and charge for this service.

[Baz]

Re: Split DB over the internet with SQL backend?

"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:1145742631 .343833.311200@ i39g2000cwa.goo glegroups.com.. .[color=blue]
> How fast is super fast?
>[/color]

LAN = 100 Mbit. VPN over the internet = depends on the bandwidth at each
end, but much, much slower than 100 Mbit.
[color=blue]
> I use an SQL db that lives on a Server in California (I am 4200 km
> away), with front ends as HTA and Access ADPs on laptops and as ASP on
> my web server. These are used in Canada, and from time to time in
> England, France, Germany, The Czech Republic and Bulgaria.
> I can recall only once that the connections have failed, and that for a
> period of about 5 minutes. It's not noticeably slower than a mdb on my
> own workstation.
>[/color]

So what? That's an entirely subjective impression, you've given no
information about the nature of the application, the amount of traffic, the
available bandwidths and so on. The original poster simply talked about
connecting client and server over the internet, he could be talking about
dialup for all you know, and if you want to give him the impression that
he's gonna get the same response over a 56k dialup as he gets over a 100
Mbit LAN then you are not helping him at all.

FWIW, I'm currently connecting to a customer's database over a VPN where the
server is sitting on the end of a leased line in London, I have a 1 Mbit
ADSL connection, and the users in China, I'm not sure what they have.
However, it IS much slower than using the app locally on their LAN. Quite
usable, but definitely slower. Sure, the app isn't designed with slow
connections in mind, but nor is the original poster's: an app which is being
converted to client/server from an old mdb app is likely to be pretty
profligate as regards it's use of the database engine.

The point about reliability is that, when it does go wrong, it is entirely
out of your hands. Jeez, it might take an hour just to get an ISP to answer
the phone!
[color=blue]
> One can get an idea of speed (and the power of ADO) by opening
> http://www.ffdba.com/columns.aspchoosing some columns to show and
> clicking submit, or not choosing any columns and clicking submit.
>
> This is a very simple support db for my and relatives business account
> keeping. You will find nothing complex here, and perhaps you will
> realize that the whole thing has grown without much direction.
>
> Regardless, for me in Ontario, listing the basic Table Schema for 27
> tables takes less than one tenth of one second (that is the server work
> takes less than one tenth of one second; the time for the html to be
> sent to and displayed by my workstation is additional, but it seems to
> be almost nothing). As more columns are added, more time is required,
> but even with all columns selected the time is less than one-half a
> second.
>
> I have no difficulty matching or beating these results with a desktop
> application using the same db.
>[/color]

What the hell has that got to do with it? The OP is asking about splitting
Access client and SQL Server database over the internet, and you present an
ASP application as an example. It's completely irrelevant.

[Lyle Fairfield]

Re: Split DB over the internet with SQL backend?

The ADO technology used by the ASP application and the ADO technology
used by an Access ADP or an HTA are identical. It is ADO that connects
to and communicates with the SQL-Server.
It is true that the ASP/ADO connection is not following the same route
across the internet as an ADP would; on the other hand, as I pointed
out, to accommodate different needs I have created almost identical ASP
and HTA applications. The HTA applications run solely on the
workstation and there is no noticeable difference in their performance.
(A subjective opinion is that they are faster.) As I have pointed out,
they have been used across Europe without any troubles with speed. I
have sat at night and discussed transactions we were both looking at
(in the HTA) with colleagues on Yahoo Server. When she updated a record
and saved the changes were immediately visible to me upon a refresh.
Speed was never a problem. It would be quite tedious to provide an ADP
or HTA example as one would have to do considerable work to protect
logons and passwords; I felt the columns ASP was an example that showed
the potential for MS-SQL over internet.
BTW, the Columns ASP is simply a port of a reference page from an
application that accommodates, among many other things, execution of
dynamically created SQL strings and/or the display of recordsets from
same. I mention this to point out that MS-SQL over the net is entirely
manageable.
Of course, I do not know the internet connection speed of the OP and
his clients. If he is using or testing a 56k dial up, then he'll be
aware that everything is dead slow. But I doubt that he is. I have
created, sold and delivered ADP applications that connected to MS-SQL
over the internet. Both were programmed to use an internet connection
to the Server when the local connection was not available. Managers
used these applications when they travelled, perhaps with different
speeds and qualities of connections. Again, there were no problems.
Clearly, you and I live in different worlds. One does not wait for an
hour for an ISP response to a problem here.
You have mentioned the design of the application as a factor in speed
and reliability. Some of the examples I have given are ADPs with bound
forms no different from MDBs using local tables. ADP connections are
surely not efficient as there are often multiple connections from one
ADP.
I believe the OP wanted to know if Access-MS-SQL over the net
applications are feasible and from experience, at least five years of
experience, I can say, "Yes!"

[Lyle Fairfield]

Re: Split DB over the internet with SQL backend?

BTW I emulated the columns.asp in both an ADP and an HTA. The ADP took
about twice as long, nearly a second for all the columns. The HTA took
about half as long, about a quarter of a second. I think I could live
with a second, but I suppose this might make one think ADPs are slow.
IMO, they are.

[(PeteCresswell)]

Re: Split DB over the internet with SQL backend?

Per Anthony England:[color=blue]
> This has the advantage that you do
>not need to modify your coding in any way at all,[/color]

A small nit: If you are using local work tables and are not adhering to the MS
standard that says those tables should be under C:\Documents and
Settings\[UserName], you will have to rewrite whatever code you use to create
work tables and also do some kind of workaround for MakeTable queries that write
to a temp DB.
--
PeteCresswell

[David W. Fenton]

Re: Split DB over the internet with SQL backend?

"(PeteCresswell )" <x@y.Invalid> wrote in
news:s6vm429dm5 qt91g62o7h45m98 iob3aeks3@4ax.c om:
[color=blue]
> Per Anthony England:[color=green]
>> This has the advantage that you do
>>not need to modify your coding in any way at all,[/color]
>
> A small nit: If you are using local work tables and are not
> adhering to the MS standard that says those tables should be under
> C:\Documents and Settings\[UserName], you will have to rewrite
> whatever code you use to create work tables and also do some kind
> of workaround for MakeTable queries that write to a temp DB.[/color]

First off, it's only necessary to do that if you can't count on your
users running as administrators on the local PC. This will likely
not be the case in situations where they are authenticating against
a domain controller, but for peer-to-peer networks of standalone PCs
configured according to MS's default configuration, they will be
running as admin (which is bad, of course).

Secondly, the issue is really quite irrelevant if you've configured
your Access app sensibly. The temp MDB should just reside in the
same folder as the application front end. It doesn't need to be
recreated each time it's used. If the user has write access to the
location where the front end is stored, it will also have write
access to the temp database.

The only wrinkle is in the first time the front end is installed the
links for the temp tables (assuming you're using links) will have to
be updated to point to the same folder that the front end resides
in. This is a one-time operation and can be done transparently
without the user ever noticing.

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

[David W. Fenton]

Re: Split DB over the internet with SQL backend?

"(PeteCresswell )" <x@y.Invalid> wrote in
news:s6vm429dm5 qt91g62o7h45m98 iob3aeks3@4ax.c om:
[color=blue]
> If you are using local work tables and are not adhering to the MS
> standard that says those tables should be under C:\Documents and
> Settings\[UserName], you will have to rewrite whatever code you
> use to create work tables and also do some kind of workaround for
> MakeTable queries that write to a temp DB.[/color]

Another note on this:

The MS standard has nothing to do with "those tables" and where they
should be stored. It's simply a result of locking down the system to
prevent modification of system and application files by
non-administrative user processes.

Under the default locked-down scenario (in place since Win2K, i.e.,
since 1999!), any user data that has to be writable (which includes
an Access front end MDB) must be stored under the user's profile,
which is globally writable to user-level logons (which is what
should be in use, rather than admin-level logons).

Another advantage of this is that in a domain where roaming profiles
are in use, the user's same front-end MDB will be available at any
workstation they sit down at. If you're storing user settings in
that front end (either in tables or database properties), those will
follow a user with roaming profiles *if* the front end is stored in
a folder under the user's pofile folder.

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

[(PeteCresswell)]

Re: Split DB over the internet with SQL backend?

Per David W. Fenton:[color=blue]
>Under the default locked-down scenario (in place since Win2K, i.e.,
>since 1999!), any user data that has to be writable (which includes
>an Access front end MDB) must be stored under the user's profile,
>which is globally writable to user-level logons (which is what
>should be in use, rather than admin-level logons).[/color]

You can probably settle this then: Would it be legit to stash those temp tables
under Documents and Settings/All Users?

I say "no" - that it would be an end run around the spirit of the standard....
I'm hoping that's the answer because it's something I wasn't bright enough to
think of before I started re-writing all my code to work under Documents and
Settings\[UserName\....
--
PeteCresswell

[david epsom dot com dot au]

Re: Split DB over the internet with SQL backend?

You don't have to be a rocket scientist to use SQL
Server, but if you haven't done it before, you can't
maintain, backup, or restore. And when your server
goes down because you didn't truncate the logs, you
won't know what to do about it.

It's no more difficult than using Windows. It's no
less difficult than using Windows for the first time
(if anyone else here is old enough to remember that).

DAO transactions are broken in Access 2K+/Jet 4.
If you have transactions in your code, you need to
remove them or re-write the whole transaction as
a stored procedure.

(david)



"Mark 123" <none@none.co m> wrote in message
news:TVd2g.1575 6$JZ1.605448@ne ws.xtra.co.nz.. .[color=blue]
>I just read at
> http://groups.google.com/group/micro...7d5504ee848c21
> that if you have a spilt db using SQL server rather than jet, the
> front-end's can connect to the backend SQL server over the internet.
>
> Has anyone had experience with this?
>
> We are thinking of migrating an commercially sold Access97 runtime app to
> Access 2007 and if this is true it may well pay to redo the backend in SQL
> Server so users can use over the net if needed. What other
> upsides/downsides
> are there to this?
>
> Do 'little' end-users have to be rocket scientists to install and maintain
> SQL server? $? Currently they only have to place one MDB backend file on a
> shared fileserver. Some of them only have 5 PC's networked in their
> business. Others have 500.
>
> TIA
>
>[/color]

[Baz]

Re: Split DB over the internet with SQL backend?

"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:1145779270 .550346.268590@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> The ADO technology used by the ASP application and the ADO technology
> used by an Access ADP or an HTA are identical. It is ADO that connects
> to and communicates with the SQL-Server.
> It is true that the ASP/ADO connection is not following the same route
> across the internet as an ADP would; on the other hand, as I pointed
> out, to accommodate different needs I have created almost identical ASP
> and HTA applications. The HTA applications run solely on the
> workstation and there is no noticeable difference in their performance.
> (A subjective opinion is that they are faster.) As I have pointed out,
> they have been used across Europe without any troubles with speed. I
> have sat at night and discussed transactions we were both looking at
> (in the HTA) with colleagues on Yahoo Server. When she updated a record
> and saved the changes were immediately visible to me upon a refresh.
> Speed was never a problem. It would be quite tedious to provide an ADP
> or HTA example as one would have to do considerable work to protect
> logons and passwords; I felt the columns ASP was an example that showed
> the potential for MS-SQL over internet.
> BTW, the Columns ASP is simply a port of a reference page from an
> application that accommodates, among many other things, execution of
> dynamically created SQL strings and/or the display of recordsets from
> same. I mention this to point out that MS-SQL over the net is entirely
> manageable.
> Of course, I do not know the internet connection speed of the OP and
> his clients. If he is using or testing a 56k dial up, then he'll be
> aware that everything is dead slow. But I doubt that he is. I have
> created, sold and delivered ADP applications that connected to MS-SQL
> over the internet. Both were programmed to use an internet connection
> to the Server when the local connection was not available. Managers
> used these applications when they travelled, perhaps with different
> speeds and qualities of connections. Again, there were no problems.
> Clearly, you and I live in different worlds. One does not wait for an
> hour for an ISP response to a problem here.
> You have mentioned the design of the application as a factor in speed
> and reliability. Some of the examples I have given are ADPs with bound
> forms no different from MDBs using local tables. ADP connections are
> surely not efficient as there are often multiple connections from one
> ADP.
> I believe the OP wanted to know if Access-MS-SQL over the net
> applications are feasible and from experience, at least five years of
> experience, I can say, "Yes!"
>[/color]

I never said they weren't feasible, in fact I said completely the opposite.
However, the OP needs to think about all the ins-and-outs of it, rather than
just be told "yeah, that'll work". If you truly believe that an Access app,
with probably lots of combos and list boxes, and record sources potentially
consisting of thousands of rows, is gonna be as fast over a VPN as it is on
a LAN then you are living in la-la land. It's got nothing to do with ADO,
it's all about how the app is designed, how many queries it generates, and
the volume of data they return. And that's not even to begin thinking about
factors like the sub-optimal speed of many DSL connections - many of them do
not achieve their nominal bandwidth, and that's BEFORE you take contention
into consideration.

Is it feasible? Yes, of course it is. Is it slower? Without a shadow of a
doubt. Would the OP notice, or care? I have no idea, he needs to do his
homework, do some testing, and draw his own conclusions.

In terms of application design, there are all sorts of things you can do in
an Access app if you know it is going to be used over a slow connection.
Just as one simple example, you could use ODBC rather than ADP and create
local copies of all the "static" tables which populate the combo boxes and
such like. Bit slower to launch the app, 'cos the local copies have to be
refreshed, but more efficient thereafter.

[Baz]

Re: Split DB over the internet with SQL backend?


"Lyle Fairfield" <lylefairfield@ aim.com> wrote in message
news:1145780152 .148103.321010@ t31g2000cwb.goo glegroups.com.. .[color=blue]
> BTW I emulated the columns.asp in both an ADP and an HTA. The ADP took
> about twice as long, nearly a second for all the columns. The HTA took
> about half as long, about a quarter of a second. I think I could live
> with a second, but I suppose this might make one think ADPs are slow.
> IMO, they are.
>[/color]

An SQL Profiler trace for each scenario would most likely have been
illuminating. Whenever I have found an ADP to be slow, it's always because
it's doing something dumb query-wise, which can be fixed once you are aware
of it.