New Technoligy with Jet engine (terminal service and MDB)

[cityrock]

Hello friends.
It has come time for me to face a dilema i have been thinking about for

a long time, but actually doing nothing. Now its time to act.
The situation is "simple", and i have just spent hours reading other
posts regarding the advantages and disadvantages upgrading my mdb (jet
engine) to an sql BE.
I am the system engineer, administrator and programmer of our company's

database. Our mdb FE is very complex, using hundreds of forms, queries
and reports. I can truely say my DB architacture is good, and having
hundreds of relashionships, between tables is holding good.
All my users connect first to our server with Terminal Services, and
then open their own FE . This gives me the ability to save network
traffice to minimum, as the BE and FE are on the same machine. (By the
way I have multipule back ends as well, some FE's connect to all BE's,
and some to specfic).My server is a x64 AMD Opteron, with 2gb of ram. I

currently have around 20 users logged in during working hours. The
preformance at most times is good, even though alot of updates on
records are being performed by all users (not even alot of record
locks). I can say I am quite happy with these settings, and would have
left it like that if we were not expending.
But we are expending, and at least 40 more users will be joining our
system soon.
Again, i have multipule FE's ,and some users will have more access to
our BE's with alot of updates, and some with only limited (even read
only) access.

So now to my dilema. I have read, and understood why sql is better. It
is mostly because of network performance (which i do not have with my
scenerio), and multi user support (i have read where jet engine MDB's
work with 100+ users though). Also I know the benefits of recovery, and

backup (but again, after working hours the server is locking out all
users, and i have time to maintain my mdbs, as well as back them up. I
also have acceptance from my boss, taking 15 minutes to shut down
users for middle of the day backup if needed).
I also read sql might give me slower performance, but more data
integrity. Dilema Dilema....
So I am at a point, before I upgrade my system, have decided to spend
the next half year rewritting from scratch my back end and front ends.
This i will do, no matter which way i go. As you can probably see my
from post, i prefare using jet MDB. I know it by heart, i have good
programming skills with vba, and like the easiness of queries, forms
and reports in ms access (they fullfill all our companies needs). I
have thousends of hours of code, queiries,forms written, which i would
like to take with me to my knew system, most of them will not be able
to work if i upgrade to sql, without converting all the queires to
views/sql commands, and unbounding forms and controls....
I honestly say i have not worked much with sql server in the past, and
although understand the logic of ADP's , never before worked with an
ADP project.
I want to stay with MDB and jet, and just rewrite my application, with
efficiant code and db structure. is it possible?
I know its not recommanded, but i am even thinking of buying another
strong server, link the two servers togather, and that way take off
some work load used by terminal services on each server (even though TS

is supposed to manage 100+ users, but dont know how it will hold with a

big Ms Access proccess open on each session).
So to finilize my long post.
The new technoligies of terminal services, the good speeds of internet
in our days, the "not so expensive" strong servers these days, can keep

me working with MDB and JET on a complex database with 70 users? Is it
stupid of me not to upgrade to sql? Can terminal service (i know this
is an access group) hold 70 users with ms access, even just as FE
users?
What would be my alternatives, if any?
Thank you in advance,
Eli

[Lyle Fairfield]

cityrock wrote:

I also read sql might give me slower performance

Where?

[David W. Fenton]

"cityrock" <il*****@gmail.comwrote in
news:11*********************@75g2000cwc.googlegrou ps.com:

So I am at a point, before I upgrade my system, have decided to
spend the next half year rewritting from scratch my back end and
front ends.

That decision sounds phenomenally stupid to me. See:

Things You Should Never Do, Part I
http://www.joelonsoftware.com/articl...000000069.html

Netscape 6.0 is finally going into its first public beta. There
never was a version 5.0. The last major release, version 4.0,
was released almost three years ago. Three years is an awfully
long time in the Internet world. During this time, Netscape sat
by, helplessly, as their market share plummeted.

It's a bit smarmy of me to criticize them for waiting so long
between releases. They didn't do it on purpose, now, did they?

Well, yes. They did. They did it by making the single worst
strategic mistake that any software company can make:

They decided to rewrite the code from scratch.

Read the whole article -- makes a *very* compelling case for never
starting over entirely.

I work on the principle of "if it ain't broke, don't fix it." So,
I'd never touch working code or a working schema.

That's not to say I don't revise old apps. I sometimes will refactor
the code or replace old components with new versions that work
exactly the same way in order to lay the groundwork for future
enhancements. But it's only when the old version can't support the
enhancement, or when there is something broken in the old that I'd
do any of these things.

Moving from 20 to 60 simultaneous users is exactly the kind of thing
that is a valid reason for moving to a server back end. But the
first thing you should do is try doing the upsizing and see how many
things just work correctly without change. Then alter the individual
areas that *don't* work. If you had a well-designed Jet app, it's
likely to work pretty darned well with SQL Server, too.

My advice is to make only the changes that are necessitated by the
upsizing to a server back end. Then, for any parts of the app you're
dissatisfied with (for whatever reason), first refactor them to
bring them up to your current code standards, and only then
implement a new design that supports the new features. The principle
here is that any stage of the process, after the refactoring, after
the implementation of the initial redesign, you still have a working
application.

Trying to do it all at once will result in a Netscape-type situation
where you are 90% finished, but always 90% away from a working
application.

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

[Tom van Stiphout]

On 2 Jul 2006 09:46:38 -0700, "cityrock" <il*****@gmail.comwrote:

Perhaps you could organize a "Performance Party" to check what would
happen if you put more users on the system. Even if that means renting
some additional computers and testers. That would be A LOT cheaper
than rewriting because you FEAR that it may not work.

Another idea is a pilot project. Take one functional area that can be
rewritten in about a week or two max, and use it to gauge pros and
cons.

-Tom.
<clip>

>

So to finilize my long post.
The new technoligies of terminal services, the good speeds of internet
in our days, the "not so expensive" strong servers these days, can keep

me working with MDB and JET on a complex database with 70 users? Is it
stupid of me not to upgrade to sql? Can terminal service (i know this
is an access group) hold 70 users with ms access, even just as FE
users?
What would be my alternatives, if any?
Thank you in advance,
Eli

[Larry Linson]

You've had answers from two intelligent and experienced database developers
(who don't always agree, however). I'll answer another question that didn't
seem to be answered:

Yes, you can continue to use an MDB, by adding ODBC, and linking the tables.

The current upsizing tool will not do this, but if you decide to go with a
server back end, you are going to need to know the server, anway (there are
none, to my knowledge, that don't require some "database administration",
though not necessarily a full-time Data Base Administrator, DBA). So
re-create your tables in the server, get the ODBC drivers for it, and
populate those tables from your Access MDB by using Append Queries. Once you
link the tables, you can and continue to use the current Access front-end
(but, do be prepared to make some mods to it... the formula for good
performance in client-server is to move as little data across the network as
possible; that's not always the way that MDB - Jet multiuser databases are
done).

If all the users will be on a LAN, or even a fast LAN, I would consider
putting the Access client (front-end) on each user's machine, instead of
paying the overhead for Terminal Server and/or Citrix.

Larry Linson
Microsoft Access MVP

"cityrock" <il*****@gmail.comwrote in message
news:11*********************@75g2000cwc.googlegrou ps.com...

Hello friends.
It has come time for me to face a dilema i have been thinking about for

a long time, but actually doing nothing. Now its time to act.
The situation is "simple", and i have just spent hours reading other
posts regarding the advantages and disadvantages upgrading my mdb (jet
engine) to an sql BE.
I am the system engineer, administrator and programmer of our company's

database. Our mdb FE is very complex, using hundreds of forms, queries
and reports. I can truely say my DB architacture is good, and having
hundreds of relashionships, between tables is holding good.
All my users connect first to our server with Terminal Services, and
then open their own FE . This gives me the ability to save network
traffice to minimum, as the BE and FE are on the same machine. (By the
way I have multipule back ends as well, some FE's connect to all BE's,
and some to specfic).My server is a x64 AMD Opteron, with 2gb of ram. I

currently have around 20 users logged in during working hours. The
preformance at most times is good, even though alot of updates on
records are being performed by all users (not even alot of record
locks). I can say I am quite happy with these settings, and would have
left it like that if we were not expending.
But we are expending, and at least 40 more users will be joining our
system soon.
Again, i have multipule FE's ,and some users will have more access to
our BE's with alot of updates, and some with only limited (even read
only) access.

So now to my dilema. I have read, and understood why sql is better. It
is mostly because of network performance (which i do not have with my
scenerio), and multi user support (i have read where jet engine MDB's
work with 100+ users though). Also I know the benefits of recovery, and

backup (but again, after working hours the server is locking out all
users, and i have time to maintain my mdbs, as well as back them up. I
also have acceptance from my boss, taking 15 minutes to shut down
users for middle of the day backup if needed).
I also read sql might give me slower performance, but more data
integrity. Dilema Dilema....
So I am at a point, before I upgrade my system, have decided to spend
the next half year rewritting from scratch my back end and front ends.
This i will do, no matter which way i go. As you can probably see my
from post, i prefare using jet MDB. I know it by heart, i have good
programming skills with vba, and like the easiness of queries, forms
and reports in ms access (they fullfill all our companies needs). I
have thousends of hours of code, queiries,forms written, which i would
like to take with me to my knew system, most of them will not be able
to work if i upgrade to sql, without converting all the queires to
views/sql commands, and unbounding forms and controls....
I honestly say i have not worked much with sql server in the past, and
although understand the logic of ADP's , never before worked with an
ADP project.
I want to stay with MDB and jet, and just rewrite my application, with
efficiant code and db structure. is it possible?
I know its not recommanded, but i am even thinking of buying another
strong server, link the two servers togather, and that way take off
some work load used by terminal services on each server (even though TS

is supposed to manage 100+ users, but dont know how it will hold with a

big Ms Access proccess open on each session).
So to finilize my long post.
The new technoligies of terminal services, the good speeds of internet
in our days, the "not so expensive" strong servers these days, can keep

me working with MDB and JET on a complex database with 70 users? Is it
stupid of me not to upgrade to sql? Can terminal service (i know this
is an access group) hold 70 users with ms access, even just as FE
users?
What would be my alternatives, if any?
Thank you in advance,
Eli

[cityrock]

Hello my good people,
It is amazing how much I learned from one post. I want to thank you all
for your "eye opening" replies.
I have taken the "netscape" example to my mind, and now understand its
not the way to go.
My problem is, rewriting the code came to my mind, not just because of
more traffic, but also because my database is not fit to work with new
requirments these users will have. I am sure I need to change structore
of at least 2 of my main tables (these tables connect to alot of forms,
queries, reports). So these will need to berewritten in any case. Now I
know, I am going to work on my exsisting code, and not start flat from
zero.
Doing a pilot project is a perfect solution. I will take one category
of my application (a category that is actually written and structored
bad anyways), and will see how it works , linked to an sql server.
Thank you for sharing with me your expirence, and sorry for my bad
english...
Eli