MS Access database support - Philly-To-London via Intranet?

Author

Philly-To-London via Intranet?

(PeteCresswell)

2006-04-06, 8:29 pm

My Philadelphia, PA (USA) client has a branch office in London that wants to use
one of the applications I've developed for them.

Staying with an MS Access front end,
I see 4 possibilities:
--------------------------------------------------
1) Have London VPN into a PC in Philly. Virtually
a sure thing at minimal cost - just a few extra
PCs on our end.

2) Leave everything as-is and see if the front end
can connect over the intranet without hopelessly
bogging down. I'm not real hopeful, but the cost
to try it seems near zero: I just VPN to one of
their boxes and give it a shot from London.

3) Move the back end to SQL Server, but still ODBC
to the tables. I don't know enough to judge
whether this is even worth trying. Anybody?

4) Move the back end to SQL Server and replace all
the application's JET queries/ODBC connections
with ADO calls to stored procedures. This seems
like the Good-Right-And-Holy Path - but probably
more expensive than the clients will want.
---------------------------------------------------
--
PeteCresswell

(PeteCresswell)

2006-04-06, 8:29 pm

Per (PeteCresswell):[col
or=darkred]
>I see 4 possibilities:[/color]

Oops... make it 5:
-------------------------------------------------
5) Ship the whole system (front end, back end,
execution .BAT file...) to London and let
them run it independently there - but synchronize
the DB's nightly with a one-way copy (Philly => London).

This was the client's off-the-cuff request.

I don't like it because
- Long-term it seems like an ongoing daily task
- Somebody in London is sure to add their own data
to their copy of the back end only to have it go
"poof!" when the DB is replaced overnight - and
then request changes to the app that will let them
synchronize the DB both ways.
-------------------------------------------------
--
PeteCresswell

MGFoster

2006-04-06, 8:29 pm

I'd try the VPN.

I had a client who wanted to have tighter security on an Access db 'cuz
they were going to VPN the branch offices into the main office. I
changed the back-end to SQL Server and all the queries to SQL pass-thru
queries (used DAO instead of ADO & an .adp). It took about 4 weeks to
convert all the queries to Views and Stored Procedures and to re-write
some set up routines, and then to transfer the data into SQL. Then it
took the Network people about 2 months to set up the VPN!!

I realized it would have been more efficient, money-wise, to just keep
the Access user security & let them VPN into the front-ends. So far the
VPN has been acceptable (at least I've not heard any complaints) - this
is a very low use DB - only sporadic heavy use at beginning of month.

You do know that each sign-in has to have their own copy of the
front-end (in a separate folder) on the server they are VPNing into?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

(PeteCresswell) wrote:
> My Philadelphia, PA (USA) client has a branch office in London that wants to use
> one of the applications I've developed for them.
>
> Staying with an MS Access front end,
> I see 4 possibilities:
> --------------------------------------------------
> 1) Have London VPN into a PC in Philly. Virtually
> a sure thing at minimal cost - just a few extra
> PCs on our end.
>
> 2) Leave everything as-is and see if the front end
> can connect over the intranet without hopelessly
> bogging down. I'm not real hopeful, but the cost
> to try it seems near zero: I just VPN to one of
> their boxes and give it a shot from London.
>
> 3) Move the back end to SQL Server, but still ODBC
> to the tables. I don't know enough to judge
> whether this is even worth trying. Anybody?
>
> 4) Move the back end to SQL Server and replace all
> the application's JET queries/ODBC connections
> with ADO calls to stored procedures. This seems
> like the Good-Right-And-Holy Path - but probably
> more expensive than the clients will want.
> ---------------------------------------------------

(PeteCresswell)

2006-04-06, 8:29 pm

Per MGFoster:
>You do know that each sign-in has to have their own copy of the
>front-end (in a separate folder) on the server they are VPNing into?

I wasn't even thinking of something as sophisticated as a server - just an extra
dedicated PC for each user.

The server thing sounds like it would break my little deployment/work table
scheme.

All work tables are in C:\Temp - as is the local copy of the front end, which
gets downloaded/updated automagically by a .BAT file.

OTOH, nothing's impossible.... and maybe it would be beneficial to me to bite
the bullet, come to understand how the server environment works, and re-code
the execution stuff to deal with multiple identities on a single server.

Or... is there maybe some way for the server to define a virtual C: drive for
each user?
--
PeteCresswell

MGFoster

2006-04-06, 8:29 pm

(PeteCresswell) wrote:
> Per MGFoster:
>
>
>
> I wasn't even thinking of something as sophisticated as a server - just an extra
> dedicated PC for each user.
>
> The server thing sounds like it would break my little deployment/work table
> scheme.
>
> All work tables are in C:\Temp - as is the local copy of the front end, which
> gets downloaded/updated automagically by a .BAT file.
>
> OTOH, nothing's impossible.... and maybe it would be beneficial to me to bite
> the bullet, come to understand how the server environment works, and re-code
> the execution stuff to deal with multiple identities on a single server.
>
> Or... is there maybe some way for the server to define a virtual C: drive for
> each user?

I don't know if it HAS to be on a server; that's just the way the
Network people set it up.

Larry Linson

2006-04-07, 3:44 am

"(PeteCresswell)" <x@y.Invalid> wrote

Others can advise you better than I about VPNing, but it seems a likely
"winner".

> 3) Move the back end to SQL Server, but still ODBC
> to the tables. I don't know enough to judge
> whether this is even worth trying. Anybody?

I've worked with Access clients using DAO/ODBC to various server databases
over WANs, since back in Access 2.0 days. When the users were connected via
a 56KB leased line, with multiple users sharing the line, performance was
lousy. When the primary users (the ones who did most of the data
entry/update were put on T-1 lines shared between multiple users, they were
"happy campers"). Another large user contingent, almost entirely just
reading and reporting were on whatever kind of WAN the client corporation
had set up, but not all their locations had T-1 lines.

Larry Linson
Microsoft Access MVP

> 4) Move the back end to SQL Server and replace all
> the application's JET queries/ODBC connections
> with ADO calls to stored procedures. This seems
> like the Good-Right-And-Holy Path - but probably
> more expensive than the clients will want.
> ---------------------------------------------------
> --
> PeteCresswell

David W. Fenton

2006-04-07, 9:37 am

"(PeteCresswell)" <x@y.Invalid> wrote in
news:fk9b32p8b76t97b
9t89qqrlipjup2gas6u@
4ax.com:

> 1) Have London VPN into a PC in Philly. Virtually
> a sure thing at minimal cost - just a few extra
> PCs on our end.

With Windows Terminal Server this is by far the easiest thing to do.
That is the only way I'd implement branch office support of an app
with shared data these days.

Back in 1998, I had a client with offices in NYC and London (and two
outside consultants working at two separate locations in
Connecticutt). We did it with replication. We rejected
Citrix/Terminal Server as too expensive (it was coming in at
$900/remote user for software alone, not including
telecommunications costs).

Today, the NYC office would host a Terminal Server and remote
offices/users would VPN into the NYC office and run the app on the
Terminal Server. Given that the NYC office had a T1 by the year
2000, this would have worked extremely well.

I wouldn't consider any other option at all. Terminal Server is just
way too easy.

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

David W. Fenton

2006-04-07, 9:37 am

"(PeteCresswell)" <x@y.Invalid> wrote in
news:55ab32lsvpjduhu
avb7jrpalscq7m0kt39@
4ax.com:

> 5) Ship the whole system (front end, back end,
> execution .BAT file...) to London and let
> them run it independently there - but synchronize
> the DB's nightly with a one-way copy (Philly => London).

Indirect replication is also an option, but Terminal Server would be
far easier to implement.

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

David W. Fenton

2006-04-07, 9:37 am

"(PeteCresswell)" <x@y.Invalid> wrote in
news:grbb32pkr7e92j7
hohti7l9c7im7blgn1r@
4ax.com:

> The server thing sounds like it would break my little
> deployment/work table scheme.
>
> All work tables are in C:\Temp - as is the local copy of the front
> end, which gets downloaded/updated automagically by a .BAT file.

In a Terminal Server environment, you could just put it in the
location referred to by the %TEMP% variable, which will be in the
profile of each user.

Since Win2K, no one should be placing any files anywhere but in the
approved locations. For data, that's in the user profile. For users,
that's in the PROGRAMS folder. It would seem that an Access app is a
program so that you'd put it in the PROGRAMS folder, but that is not
a good approach, as the MDB needs to be read/write, and by default
from Win2K on, the PROGRAMS folder is READ-ONLY for
non-administrative users. By putting your app in the PROGRAMS
folder, you're forcing your users to run as administrative users
(which is very bad from a security standpoint) or forcing an
administrator to change the default permissions on the folder where
your app is installed. It's better to put the MDB in user space,
instead.

This does make relinking somewhat more diffucult in that you have to
do it for each user installation (and multiple users of a single PC
will have multiple copies of the front end), but that is the proper
way to insure that your app will work for people logging in as users
and not as administrators. It's the proper way to engineer
applications for Windows.

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

(PeteCresswell)

2006-04-07, 8:27 pm

Per David W. Fenton:
>Indirect replication is also an option, but Terminal Server would be
>far easier to implement.

Not sure of the distinction between "indirect" and just "replication"... but
what I heard from one of our illuminati was that when replication goes bad, it
*really* goes bad... dunno exactly what that meant, but it kind of scared me
off.
--
PeteCresswell

(PeteCresswell)

2006-04-07, 8:27 pm

Per David W. Fenton:
>Today, the NYC office would host a Terminal Server and remote
>offices/users would VPN into the NYC office and run the app on the
>Terminal Server.

Is there a reason for using a Terminal Server instead of "N" PCs in a closet
somewhere - besides unit cost of the PCs and maybe added electrical consumption?
Seems like with a Terminal Server (whatever that is.... but I'm guessing it's
some sort of PC running Windows Server...) the users would be at the mercy of
whoever configs the server and/or what applications/users are also hitting on it
besides them. Also if the server goes down, everybody's dead in the water
until somebody brings it back up or builds a new one.

My guys value independence highly. I tried to talk them into a SQL Server back
end for this thing on Day 1 and independence from IT and/or the LAN folks was
the reason they gave for rejecting it. I suspect that they'd react similarly
to a Terminal Server unless there was some reason for using it over individual
PCs.
--
PeteCresswell

David W. Fenton

2006-04-08, 9:27 am

"(PeteCresswell)" <x@y.Invalid> wrote in
news:u63e32t31vgtmal
pa4mm57b6c2f18laqde@
4ax.com:

> Per David W. Fenton:
>
> Not sure of the distinction between "indirect" and just
> "replication"... but what I heard from one of our illuminati was
> that when replication goes bad, it *really* goes bad... dunno
> exactly what that meant, but it kind of scared me off.

Well, I don't know who that might have been, other than someone who
is truly ignorant of Jet replication.

It only goes bad if you do things wrong. This can include,
unfortunately, following recommendations in Microsoft's own
documentation (they still recommend replicating front ends as a
method of pushing out changes, even though that never works in the
long run -- replication works well only for data tables and queries,
i.e., the pure Jet objects in Access).

Replication does require knowledge to manage and care in
administration. But with that, it runs quite reliably.

As to "indirect" replication, it is a form of replication
synchronization distinct from DIRECT replication. Direct is what you
get when you synchronize from the Access user interface, and it
opens the full remote database across your network connection. This
works fine on a LAN, but on a WAN or a dialup connection, is fatally
dangerous (if the connection is dropped, at the very least, the
remote replica will lose replicability, which means you'll have to
manually recover the data changes in it and then replace it with a
new replica).

Indirect synchronization uses a process running on each end of the
synch to drop message files into a dropbox at the partner replica.
These message files detail the changes to data only, and are thus
very efficient. The process running on each machine reads and
applies the message files and then communicates with the other
machine by dropping its own messages in the remote dropbox. This
method is very safe and very efficient. However, it does require
care to keep running and has some drawbacks on servers (the
synchronizer process can't be run as a service, for instance).

Terminal Server is vastly preferable when you're dealing with fixed
offices that need to share data. Replication is very helpful for
roaming users who can't count on being able to connect reliably or
cheaply to the Internet to run the app on a Terminal Server. If the
remote users can wait to synch when they're physically in the office
and connected to the LAN, direct replication will suffice. If they
need to synch from the field, then they need indirect replication.

There's also a variety of synchronization called "Internet"
synchronization, which uses FTP rather than an SMB connection to
drop the message files in the remote dropboxes. But it is hard-wired
to depend on using IIS's FTP server on both ends of the synch, which
adds an additional dependency and a new layer of potential security
problems. Now that VPNs are pretty common, indirect replication over
a VPN running across the Internet is the easiest method for
accomplishing indirect replication, and Internet replication is
really not needed these days.

In the days when full-time Internet connections were rare and
Terminal Server cost a fortune, indirect replication was a great way
to have remote offices sharing data. But now it's main purpose is
supporting roaming users who need to edit data while disconnected.

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

David W. Fenton

2006-04-08, 9:27 am

"(PeteCresswell)" <x@y.Invalid> wrote in
news:cj3e32t5p3m9o3u
g30uio9dsslv1pd3nkk@
4ax.com:

> Per David W. Fenton:
>
> Is there a reason for using a Terminal Server instead of "N" PCs
> in a closet somewhere - besides unit cost of the PCs and maybe
> added electrical consumption?

Well, most clients who would be in this situation would already have
a file server. If it's Win2K Server or Win2K3 Server, Terminal
Server is a built-in feature, so why allocate a bunch of PCs for
this purpose?

> Seems like with a Terminal Server (whatever that is.... but I'm
> guessing it's some sort of PC running Windows Server...) the
> users would be at the mercy of whoever configs the server and/or
> what applications/users are also hitting on it besides them. . . .

Well, yes, I guess, but only in the same sense that users of a
website are at the mercy of the website's sysadmin, or users of a
file server are at the mercy of the administrator of the file
server.

As to performance, you have to allocate sufficient bandwidth and
RAM, but that's not a real issue until you're looking to support
dozens of simultaneous users. And if you compare the incremental
cost of adding a user to a terminal server to the cost of setting up
a dedicated PC for them, then the terminal server wins by a long
shot.

It also means that administration of the Access app is centralized,
on one machine, rather than spread over a bunch of workstations.

> . . . Also if the server goes down, everybody's dead in the water
> until somebody brings it back up or builds a new one.

That is no different than with a file server or a web server, so I
just don't see the point in bringing it up as an issue. A Terminal
Server is going to be as reliable as any Windows server.

I do all my work for several clients remotely on their Terminal
Server, and other than the client who has only the two default admin
terminal server sessions allowed, I never have problems connecting.
The servers are up 24/7, and there are no problems. The only issue
is when the TS licenses are already maxed out, and I run into that
only on the machines that have only the default two admin sessions
(i.e., they haven't bought additional licenses). If you're
supporting multiple users from a remote office, you'd simply buy
sufficient licenses to avoid that problem. At c. $40 per user for
that, it's really not very expensive at all.

> My guys value independence highly. I tried to talk them into a
> SQL Server back end for this thing on Day 1 and independence from
> IT and/or the LAN folks was the reason they gave for rejecting it.
> I suspect that they'd react similarly to a Terminal Server
> unless there was some reason for using it over individual PCs.

The SQL Server could go down.

There really is *no* difference in terms of server reliability
between a SQL Server solution and a Terminal Server solution. But
the TS solution makes administration substantially easier (the
remote users don't have to have your app installed on their
computer).

You really need to go to Albert Kallal's website and read his
thoughts on terminal server.

And you really need to read up about it. It is a very reliable
solution and extremely easy to set up and manage. It's also
remarkably inexpensive to implement, especially when you're
migrating remote users to it who already have Office installed on
their local PCs (which is required for running Office apps on the
terminal server).

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

(PeteCresswell)

2006-04-08, 9:27 am

Per David W. Fenton:
>That is no different than with a file server or a web server, so I
>just don't see the point in bringing it up as an issue. A Terminal
>Server is going to be as reliable as any Windows server.

My concern would be that on the troubleshooting groups radar, the terminal
server might look different: only a few people affected, maybe not covered by
whatever monitoring setup they have, possibly not understood by the
rank-and-file.
--
PeteCresswell

David W. Fenton

2006-04-08, 11:28 am

"(PeteCresswell)" <x@y.Invalid> wrote in
news:i0hf32hn5rm8m0q
ie69gs99uvft17j4e7j@
4ax.com:

> Per David W. Fenton:
>
> My concern would be that on the troubleshooting groups radar, the
> terminal server might look different: only a few people affected,
> maybe not covered by whatever monitoring setup they have, possibly
> not understood by the rank-and-file.

Huh?

If they have any Win2K or Win2K3 servers, they already have a
Terminal Server. The default configurations provide two licenses
that are only usable by administrators. If you add licenses, you can
have as many people using the Terminal Server as there are licenses
and resources on the server to support them.

Administering Terminal Server is trivial (though there are some
tricky gotchas with the Win2K3 license server).

I think you're assuming it's some kind of special box, whereas any
existing Win2K or Win2K3 is already a Terminal Server, by default.

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

(PeteCresswell)

2006-04-08, 1:28 pm

Per David W. Fenton:
>I think you're assuming it's some kind of special box, whereas any
>existing Win2K or Win2K3 is already a Terminal Server, by default.

I was - because it seemed like a multi-user terminal server would have to have a
lot of horsepower to handle multiple concurrent instances of MS Access. Seems
like that's a bigger load than just somebody having, say, 10 apps open on their
desktop because that person can only be exercising one app at a time - whereas
with the terminal server, it would have to be servicing everybody who is logged
on more-or-less simultaneously.
--
PeteCresswell

Rick Brandt

2006-04-08, 1:28 pm

"(PeteCresswell)" <x@y.Invalid> wrote in message
news:87rf32piqa2rhh7
qrngelnpbjbpo3tri00@
4ax.com...
> Per David W. Fenton:
>
> I was - because it seemed like a multi-user terminal server would have to have
> a
> lot of horsepower to handle multiple concurrent instances of MS Access. Seems
> like that's a bigger load than just somebody having, say, 10 apps open on
> their
> desktop because that person can only be exercising one app at a time - whereas
> with the terminal server, it would have to be servicing everybody who is
> logged
> on more-or-less simultaneously.
> --
> PeteCresswell

That's true, but most PCs these days are ridiculously more powerful than the
needs of the user so you would be surprised at the specs that will run 20 - 40
Terminal Server users. The box can't be a slouch, but it need not be deep blue
either.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Sponsored Links