Advice on an expanding Access project

[user451]

As the most Access-savvy person in my office, I have been handed the
task of proposing a nationwide expansion of a project that I have
developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the results of
certain law enforcement activities. Pretty low volume for new
transactions, with about 10 users per day accessing or editing
existing records. The problem is, that the program is so successful
that is has been requested nationwide. The plan is to add 1 new city
per month, with each city contibuting approx. 10 new transactions per
day. The total number of cities using the software will be about 60.
As I am fairly new to this, what can you recommend ? Should I stick
with Access, or is it unsuitable for my needs ? Network issues are
not a concern, as it will reside on a server in Maryland, and the
nationwide WAN is already in place. Any hints or ideas would be
appreciated. Thank you.

[Steve Jorgensen]

At those transaction volumes, I'm guessing that off-line replication would be
a good way to go. If D.F. sees this, he can offer much more advice on the
replication approach than I can.

On Mon, 17 May 2004 00:04:41 GMT, us*****@anon.co m wrote:

As the most Access-savvy person in my office, I have been handed the
task of proposing a nationwide expansion of a project that I have
developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the results of
certain law enforcement activities. Pretty low volume for new
transactions , with about 10 users per day accessing or editing
existing records. The problem is, that the program is so successful
that is has been requested nationwide. The plan is to add 1 new city
per month, with each city contibuting approx. 10 new transactions per
day. The total number of cities using the software will be about 60.
As I am fairly new to this, what can you recommend ? Should I stick
with Access, or is it unsuitable for my needs ? Network issues are
not a concern, as it will reside on a server in Maryland, and the
nationwide WAN is already in place. Any hints or ideas would be
appreciated. Thank you.

[MacDermott]

It does not look as if volume or number of concurrent users would be a major
problem in this expansion.
However, I've never heard of an Access project successfully using a WAN as a
simple network.
One option, already suggested, is replication.
Another would be to provide a terminal server solution.
Yet a third option would be to develop a web-based interface.
If, as your description suggests, the data is not mission-critical and
could be re-entered without much trouble in the event of a crash, you could
continue to use your Access back-end, but you'd need a different (probably
HTML-based) front end.

HTH
- Turtle

<us*****@anon.c om> wrote in message news:40a9016d.4 301062@news-server...

As the most Access-savvy person in my office, I have been handed the
task of proposing a nationwide expansion of a project that I have
developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the results of
certain law enforcement activities. Pretty low volume for new
transactions, with about 10 users per day accessing or editing
existing records. The problem is, that the program is so successful
that is has been requested nationwide. The plan is to add 1 new city
per month, with each city contibuting approx. 10 new transactions per
day. The total number of cities using the software will be about 60.
As I am fairly new to this, what can you recommend ? Should I stick
with Access, or is it unsuitable for my needs ? Network issues are
not a concern, as it will reside on a server in Maryland, and the
nationwide WAN is already in place. Any hints or ideas would be
appreciated. Thank you.

[Tony Toews]

us*****@anon.co m wrote:

Should I stick
with Access, or is it unsuitable for my needs ? Network issues are
not a concern, as it will reside on a server in Maryland, and the
nationwide WAN is already in place. Any hints or ideas would be
appreciated. Thank you.

Access is suitable but not for the WAN part. You could also have a lot of
configuration issues for 60 cities especially when it comes to updates. I also
wouldn't do replication because it could end up being more trouble than it's worth
especially for non technical users who would never remember instructions from one
month to another.

Thus, as others have suggested, I'd go with Terminal Server or a web based front end.
If you have a lot of VBA logic that could be ported to VB.Net although obviously with
some work. Terminal Server "just" requires an outlay for the Terminal Server system
and CALs (Client Access Licenses) with no changes to your app required.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[David W. Fenton]

us*****@anon.co m wrote in news:40a9016d.4 301062@news-server:

As the most Access-savvy person in my office, I have been handed
the task of proposing a nationwide expansion of a project that I
have developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per
day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the results
of certain law enforcement activities. Pretty low volume for new
transactions, with about 10 users per day accessing or editing
existing records. The problem is, that the program is so
successful that is has been requested nationwide. The plan is to
add 1 new city per month, with each city contibuting approx. 10
new transactions per day. The total number of cities using the
software will be about 60.
As I am fairly new to this, what can you recommend ? Should I
stick
with Access, or is it unsuitable for my needs ? Network issues
are not a concern, as it will reside on a server in Maryland, and
the nationwide WAN is already in place. Any hints or ideas would
be appreciated. Thank you.

In regard to the three alternatives, as someone who has done all
three, here are my comments:

1. replication: I wouldn't say it's worth it because:

a. it requires Access installed everywhere the app is used
b. for synchronizing across a WAN it requires Replication
Manager, which requires setup on servers at both ends of
the connection.

2. Terminal Server: this would work well, but:

a. you have to have the server infrastructure in place, or it's a
great deal of work
b. you may have licensing expenses of some heft, because the
users of Access on the Terminal Server have to have the
license to use Access on their workstations.

3. browser-based interface: this is what I'd go with because:

a. it sounds like what is being done is very simple
b. it requires no setup on the other end to add a new user
c. it's fully controllable/administrable from a central location,
to an even greater degree than Terminal Server

The only downside to 3) is figuring out where it can be hosted. If
you've got a WAN, then you can probably run an HTTP server in your
local office and have them connect to that across the LAN. However,
there are security implications to that, of course. I certainly
wouldn't recommend using IIS, as it's just a big batch of security
weaknesses waiting to be exploited.

If I were setting it up, I'd do Apache + PHP.

Of course, I have the expertise and experience to implement any one
of the three alternatives, and you may not. I think I'd still
recommend option 3), though, as it basically takes network
reliability out of the equation entirely.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Mon, 17 May 2004 16:36:43 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

us*****@anon.c om wrote in news:40a9016d.4 301062@news-server:

As the most Access-savvy person in my office, I have been handed
the task of proposing a nationwide expansion of a project that I
have developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per
day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the results
of certain law enforcement activities. Pretty low volume for new
transactions, with about 10 users per day accessing or editing
existing records. The problem is, that the program is so
successful that is has been requested nationwide. The plan is to
add 1 new city per month, with each city contibuting approx. 10
new transactions per day. The total number of cities using the
software will be about 60.
As I am fairly new to this, what can you recommend ? Should I
stick
with Access, or is it unsuitable for my needs ? Network issues
are not a concern, as it will reside on a server in Maryland, and
the nationwide WAN is already in place. Any hints or ideas would
be appreciated. Thank you.
In regard to the three alternatives, as someone who has done all
three, here are my comments:

1. replication: I wouldn't say it's worth it because:

a. it requires Access installed everywhere the app is used
b. for synchronizing across a WAN it requires Replication
Manager, which requires setup on servers at both ends of
the connection.

Then I stand corrected.
2. Terminal Server: this would work well, but:

a. you have to have the server infrastructure in place, or it's a
great deal of work
b. you may have licensing expenses of some heft, because the
users of Access on the Terminal Server have to have the
license to use Access on their workstations.
Regarding (b), this would not be an issue if only the Access Runtime was
installed on the TS, right?
3. browser-based interface: this is what I'd go with because:

a. it sounds like what is being done is very simple
b. it requires no setup on the other end to add a new user
c. it's fully controllable/administrable from a central location,
to an even greater degree than Terminal Server

The only downside to 3) is figuring out where it can be hosted. If
you've got a WAN, then you can probably run an HTTP server in your
local office and have them connect to that across the LAN. However,
there are security implications to that, of course. I certainly
wouldn't recommend using IIS, as it's just a big batch of security
weaknesses waiting to be exploited.

If I were setting it up, I'd do Apache + PHP.

I hear that's a great option, but some other good options I hear really good
things about are Cold Fusion and Zope.

[David W. Fenton]

Steve Jorgensen <no****@nospam. nospam> wrote in
news:c8******** *************** *********@4ax.c om:

On Mon, 17 May 2004 16:36:43 GMT, "David W. Fenton"
<dX********@bw ay.net.invalid> wrote:

us*****@anon. com wrote in news:40a9016d.4 301062@news-server:

As the most Access-savvy person in my office, I have been handed
the task of proposing a nationwide expansion of a project that I
have developed in Access. A brief overview:

Right now, about 25 people process about 5 transactions per
day.
(Total, not each) The back-end database resides on a server not
located in my office, but nearby. The transactions are the
results of certain law enforcement activities. Pretty low volume
for new transactions, with about 10 users per day accessing or
editing existing records. The problem is, that the program is so
successful that is has been requested nationwide. The plan is to
add 1 new city per month, with each city contibuting approx. 10
new transactions per day. The total number of cities using the
software will be about 60.
As I am fairly new to this, what can you recommend ? Should I
stick
with Access, or is it unsuitable for my needs ? Network issues
are not a concern, as it will reside on a server in Maryland,
and the nationwide WAN is already in place. Any hints or ideas
would be appreciated. Thank you.

In regard to the three alternatives, as someone who has done all
three, here are my comments:

1. replication: I wouldn't say it's worth it because:

a. it requires Access installed everywhere the app is used
b. for synchronizing across a WAN it requires Replication
Manager, which requires setup on servers at both ends of
the connection.

Then I stand corrected.

Synchronization can be done with just Access on both ends, but that
is a direct synchronization , and is the same as editing the the file
across that connection. So, if the connection is unsuitable for
direct editing of the back end MDB, it will be unacceptable for
direct synchronization .

Indirect synchronization requires Replication Manager, which you get
with the developer edition of Office. It's not trivial to configure,
nor trivial to keep running smoothly.

And you have to keep the process running on both ends (the two
processes communicate with each other at schedule intervals and
exchange message files that include the changes made; these are put
in a dropbox on each end and applied to the local replica by the
local replicator process), and it can't be run as a system service
-- it can only run in a logged on session.

2. Terminal Server: this would work well, but:

a. you have to have the server infrastructure in place, or it's
a
great deal of work
b. you may have licensing expenses of some heft, because the
users of Access on the Terminal Server have to have the
license to use Access on their workstations.

Regarding (b), this would not be an issue if only the Access
Runtime was installed on the TS, right?

Good question.

3. browser-based interface: this is what I'd go with because:

a. it sounds like what is being done is very simple
b. it requires no setup on the other end to add a new user
c. it's fully controllable/administrable from a central
location,
to an even greater degree than Terminal Server

The only downside to 3) is figuring out where it can be hosted. If
you've got a WAN, then you can probably run an HTTP server in your
local office and have them connect to that across the LAN.
However, there are security implications to that, of course. I
certainly wouldn't recommend using IIS, as it's just a big batch
of security weaknesses waiting to be exploited.

If I were setting it up, I'd do Apache + PHP.

I hear that's a great option, but some other good options I hear
really good things about are Cold Fusion and Zope.

Cold Fusion is beyond my comprehension nowadays, and costs money,
big money.

PHP is free, and just as great, though I must say I found learning
CF a lot easier than learning PHP.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc