Where to go from here?

[Guest]

Dear all
I have developed projects in Access and VBA.
I now need to develop a big one. Do I change to VB and SQL? Do I use
Expess editions? Can I link to either Access or SQL?
I'm not looking for answers to these questions (although answers would be
good - but I haven't provided nearly enough information)
I simply need to know WHERE to find information on what these different
products / versions / cut-downs etc do.
Where can I look to find out what I should use as a development environment
for my next project.
Any pointers would be greatly appreciated - thank you in advance!
Kind regards
PJB

 

[Arvin Meyer [MVP]]

How big is big? I have an application that exceeds a hundred MB of data and
has 53 users written originally in Access 97 and 2000 and now running in
Access 2003. I'd feel comfortable with another 10 users and double the data
size.

To make a long story short, there is no definitive information. Everything
will be anecdotal. Anyone who claims to have absolute knowledge (beyond the
specifications) is just guessing. Every consultant and company has something
to sell, and will claim to have the solution. Everyone will be right ... and
wrong. Post your specifications and we'll all take a stab at it.

 

[Guest]

OK, thanks for that, point taken.
I'm writing a package solution for the UK mortgage industry.
It will be used by one man bands who will not want to buy expensive user
licenses and who will only have small numbers of records (e.g. thousands)
right through (hopefully!) to major corporates with up to 50 users in a
central location and access required for hundreds of users via the web and
independently (i.e. on notebooks / PC's and transmitted to central).
Processing hundreds of thousands of records. The small databases need to
seamlessly link / work with the major database as and when appropriate.
I need a development environment that satisfies single users right through
to major corporates. My development is made up of 'Front-end' (i.e. the
'Sale', sales people working independently or as part of a group) and
'Back-end' (i.e. Administration - for independents, could be intergrated with
the front-end, for bigger companies, multi-user environments with 'n' users)
The front and back ends need to 'talk' to each other. Screens available on
the net would be an advantage.
My initial question was not for a source to provide 'the answer' to my
particular issue - more a source of information of exactly WHAT VB, Access,
SQL, Express versions etc etc are supposed to provide. I.e. what do they do,
what dont they do, where are they strong, weak etc.
Anyway - many thanks for taking the time to respond. All help appreciated.

 

[(PeteCresswell)]

Per PJB:

I now need to develop a big one.

Big in terms of application/objects, number of users, and/or size of tables?

 

[(PeteCresswell)]

Per (PeteCresswell):

Big in terms of

Oops... your followup post spelled it out pretty clearly.

 

[Arvin Meyer [MVP]]

OK, thanks for that, point taken.
I'm writing a package solution for the UK mortgage industry.
It will be used by one man bands who will not want to buy expensive user
licenses and who will only have small numbers of records (e.g. thousands)

This is definitely the realm of Access.

right through (hopefully!) to major corporates with up to 50 users in a
central location

This can be an Access application or Access front-end with a SQL-Server
backend. For that many users, used need a full SQL-Server license.

and access required for hundreds of users via the web and
independently (i.e. on notebooks / PC's and transmitted to central).
Processing hundreds of thousands of records.

Now you need either a web front-end or if there are not more than 30
concurrent users connected at any one time, a Terminal Server will work just
fine.

The small databases need to
seamlessly link / work with the major database as and when appropriate.

Access databases will link to almost anything. Your SQL-Server database
engines are limited to a single platform at a time (as are most server based
engines)

I need a development environment that satisfies single users right through
to major corporates. My development is made up of 'Front-end' (i.e. the
'Sale', sales people working independently or as part of a group) and
'Back-end' (i.e. Administration - for independents, could be intergrated
with
the front-end, for bigger companies, multi-user environments with 'n'
users)
The front and back ends need to 'talk' to each other. Screens available
on
the net would be an advantage.
My initial question was not for a source to provide 'the answer' to my
particular issue - more a source of information of exactly WHAT VB,
Access,
SQL, Express versions etc etc are supposed to provide. I.e. what do they
do,
what dont they do, where are they strong, weak etc.
Anyway - many thanks for taking the time to respond. All help
appreciated.

Specifications:

Concurrent users: 255 on an Access (JET) engine. While I've heard of a few
with that many users, most sources use 20 concurrent users as a guideline. I
have 53 concurrent users without any problems and I'd feel comfortable with
at least 60 and maybe as many as 75.

Size: 2 GB. I have a client with a chained back-end who had grown to 30 GB
about 3 years ago.

 

[(PeteCresswell)]

Per Arvin Meyer [MVP]:

chained back-end

?

 

[Arvin Meyer [MVP]]

The have an Access 2.0 database, with 1 table in each of about 80
backend-end databases, all linked to a single front-end. They have one table
in the front-end which is essentially and index and search source. Once the
find the single record they are looking for, they change the Recordsource of
the form to the correct table for that record. It's been working for about
10 or 11 years now.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David W. Fenton]

if there are not more than 30
concurrent users connected at any one time, a Terminal Server will
work just fine.

Why would you say this? Why not an ADP front end to SQL Server for a
large user population? That could be run over Terminal Server. The
30 limit seems to me to have more to do with Jet as the back end
than it does with Windows Terminal Server, which is scalable via
adding memory and additional Terminal Servers. That is, there are no
limits to how many users you can support in WTS if you're willing to
invest in the necessary hardware.

 

[Arvin Meyer [MVP]]

I use a dual Xeon 3.0 GHz Terminal Server with4 MB of RAM and 15K RPM SCSI
drives. It cost just under $7K. Pretty fast machine. It was designed to
handle up to 50 users. With 30 it works beautifully. I agree that you can
support more users with more hardware, but at some point there is a
cross-over and it's cheaper to do a web front-end and a SQL-Server back-end
than to buy more servers.

 

[David W. Fenton]

I use a dual Xeon 3.0 GHz Terminal Server with4 MB of RAM and 15K
RPM SCSI drives. It cost just under $7K. Pretty fast machine. It
was designed to handle up to 50 users. With 30 it works
beautifully. I agree that you can support more users with more
hardware, but at some point there is a cross-over and it's cheaper
to do a web front-end and a SQL-Server back-end than to buy more
servers.

Interesting numbers. A couple of years ago a client of mine spent
just under $2,500 provisioning a Compaq blade server with 2GB of RAM
and minimal disk space as a Terminal Server to service 10 users (it
was not a dual CPU box, and was probably 2.5GHz clock speed). I
thought it was incredibly cheap. It didn't need much disk space, as
there was plenty of other storage capacity local to the network that
server lived on, and it has worked so well they've now got up to 20
users on it (they started with one Access app and QuickBooks, and
then added other uses).

So, I think it rather depends on what your users are doing.

I can't see why a Terminal Server needs the same disk space as your
file server. Why wouldn't the TS use the file server for storing
most of its application-related data, just as would happen on the
LAN?

MS recommends 128MBs of RAM per user, but I like to spec out 256MBs
to be on the safe side, but that's probably outdated nowadays
because the CPUs are so much faster, and the with dual-core
processors, you've got so much more processing power that RAM is not
quite so big an issue.

And, of course, the RAM requirements of the users will depend on
what apps they are using. I actually think apps like Quickbooks and
custom apps built in Access are the most likely candidates for
rolling out over Terminal Server. I can't think of any other apps
that it would make sense to roll out over TS, myself, but maybe I'm
just completely unimaginative!

 

[Arvin Meyer [MVP]]

I think part of the price difference comes from the licensing costs for the
30 CALs. All our users already had Office Professional, so they didn't need
the Office licenses. We didn't get a lot of disk space but do use a 6 disk
Raid 5 stripe set to ensure good speed. Usable disk space is 90 GB, but we
are only actively using about 3 GB. Another 20 GB is used as a secondary
backup for the file server. The front-end folders are on the WTS but the
back-end is shared with the rest of the company on a file server LAN.

 

[David W. Fenton]

I think part of the price difference comes from the licensing
costs for the 30 CALs. All our users already had Office
Professional, so they didn't need the Office licenses. We didn't
get a lot of disk space but do use a 6 disk Raid 5 stripe set to
ensure good speed. Usable disk space is 90 GB, but we are only
actively using about 3 GB. Another 20 GB is used as a secondary
backup for the file server. The front-end folders are on the WTS
but the back-end is shared with the rest of the company on a file
server LAN.

My clients using TS already had all their desktops with Office
installed. I do know that there aren't strict requirements to have
the exact same version of Office as you're running on the server.
That is, I can run Office 2K3 on a Terminal Server from my home
computer that has only Office 2K and XP installed. What I don't know
for certain is if a user without Access installed but with full
Office otherwise could run an Access app on Terminal Server. I have
a strong suspicion that you could, as the rules seem quite loose.
But that may depend on what version of Office is installed on the
Terminal Server. That is, if it's an Open license installation, it
may be looser than if it's a more limited version (both my clients
have open license packages).

Of course, when comparing Terminal Server to the other options, the
CAL costs are going to be a lot less than the cost of redeveloping
as a web-based app, and a complete wash if you're comparing it to
replication, where you'd need Access installed on all workstations,
anyway.

 

[Arvin Meyer [MVP]]

You can run any part of Office from a Terminal Server without anything but
Windows installed locally, just not legally if you haven't paid for a
license to run it. The fact that you have a local license allows you to
legally run it from the Terminal Server. Otherwise, you must buy a license.

 

[David W. Fenton]

You can run any part of Office from a Terminal Server without
anything but Windows installed locally, just not legally if you
haven't paid for a license to run it. The fact that you have a
local license allows you to legally run it from the Terminal
Server. Otherwise, you must buy a license.

Are you saying there is no compliance checking at all? I assumed
that if you didn't have the appropriate local licenses, you wouldn't
be able to run the apps on the Terminal Server. Am I wrong? If not,
what's the point? While I've never been in a situation where I"d
even consider attempting to do things illegally, I didn't think
there was anything wrong with the scenario I was running with (or
that my clients were running with, which always involved some
version of Access installed on the local workstation).

 

[Arvin Meyer [MVP]]

Are you saying there is no compliance checking at all? I assumed
that if you didn't have the appropriate local licenses, you wouldn't
be able to run the apps on the Terminal Server. Am I wrong? If not,
what's the point? While I've never been in a situation where I"d
even consider attempting to do things illegally, I didn't think
there was anything wrong with the scenario I was running with (or
that my clients were running with, which always involved some
version of Access installed on the local workstation).

I can't say for sure whether there is compliance checking or not. There's no
real way for me to check it either because we have licenses for everything..
I do know that Terminal Server does check for CALs, because you must type in
the registration of every Windows machine that is allowed to connect. I
don't know whether there is a check for Office licenses because it doesn't
ask for registrations. You do not need to have Access installed locally to
run Citrix, and I assume it's the same for Terminal Server. In our case, we
had local licenses for everything, so the Terminal Server connections had to
be legal.

 

[David W. Fenton]

I can't say for sure whether there is compliance checking or not.
There's no real way for me to check it either because we have
licenses for everything.. I do know that Terminal Server does
check for CALs, because you must type in the registration of every
Windows machine that is allowed to connect.

Huh? I've never had to do that for the Terminal Servers I've set up.
If you use device licenses (the default), the license is just handed
out when the machine connects to the Terminal Server. It works
differently for user-level licenses (which the license manager can't
track and display in it's UI -- idiotic Microsoft).

I
don't know whether there is a check for Office licenses because it
doesn't ask for registrations. You do not need to have Access
installed locally to run Citrix, and I assume it's the same for
Terminal Server. In our case, we had local licenses for
everything, so the Terminal Server connections had to be legal.

There are two CALs involved, the Terminal Server connection CAL,
which is handled by the licensing server running on the Terminal
Server (or somewhere local to the TS's network), and the Office CAL.
If you have Office installed, you don't need an Office CAL. It's
also the case that certain versions of Windows workstation don't
need a CAL to connect to TS (it's a backward compatibility thing --
any version of Windows that postdates the Windows server version
doesn't need its own CAL).

My question is really only about the Office CALs.

The documentation for Terminal Server licensing is extremely
confusing. I have to believe that MS wants it that way.

 

[Arvin Meyer [MVP]]

The documentation for Terminal Server licensing is extremely
confusing. I have to believe that MS wants it that way.

The fare of lawyers. The more confusing something is, the more money they
make.

 

[David W. Fenton]

The fare of lawyers. The more confusing something is, the more
money they make.

It's really silly, though. It's one of the best things MS has done
in the last few years, i.e., incorporating TS into the standard
Windows Server distribution.

I'm a huge Terminal Server fan.