Jet or MSDE

[NB]

Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB

[Tom van Stiphout]

On 10 Dec 2003 19:18:19 -0800, ni******@lycos. com (NB) wrote:

If it ain't broke, don't fix it.
-Tom.

Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB

[Tony Toews]

ni******@lycos. com (NB) wrote:

The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.
Puny. <smile> A client has 150 tables, 1200 queries, 450 forms, 350 reports last
time I looked. Backend is about 300-350 Mb in size. 15-25 users including five via
Terminal Server. Some running A97 others A2000.
The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.
Like Tom said "Ain't broke, don't fix"
I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?

It's SQL Server but restricted to five processes. It'll be a lot more work to get
it going. Especially if you have functions in queries.

However if your client has mission critical data where they can't go to last nights
backup, ie telephone call sales, hotel reservations, whatever, then that is how you
should go.

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

[deko]

I maintain an mdb with a large number of objects myself and was asking the
same question - to MSDE or not to MSDE? Because the size limitation is the
same (2Gb) there's not much to gain. From what I've read, MSDE gives you
better security and is better at accommodating a larger number of users.
But you might as well go to a full SQL server backend if those are critical
needs.
"NB" <ni******@lycos .com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB

[Van T. Dinh]

For "a larger number of users", you may need the full MS-SQL Server (license
required) rather than the MSDE. MSDE is restricted to 5 processes so it
won't be suitable for a large number of users.

--
HTH
Van T. Dinh

"deko" <dj****@hotmail .com> wrote in message
news:4o******** ***********@new ssvr25.news.pro digy.com...

I maintain an mdb with a large number of objects myself and was asking the
same question - to MSDE or not to MSDE? Because the size limitation is the same (2Gb) there's not much to gain. From what I've read, MSDE gives you
better security and is better at accommodating a larger number of users.
But you might as well go to a full SQL server backend if those are critical needs.
"NB" <ni******@lycos .com> wrote in message
news:5c******** *************** ***@posting.goo gle.com...

Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB

[Tony Toews]

"Van T. Dinh" <Va***********@ PlseUseNewsGrou p.bigpond.com> wrote:

For "a larger number of users", you may need the full MS-SQL Server (license
required) rather than the MSDE. MSDE is restricted to 5 processes so it
won't be suitable for a large number of users.

But there are reports of up to 100 users using an MSDE backend.

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

[Van T. Dinh]

Yes, it a varying "larger number of users" depending on the how dynamic the
data is.

Personally, I would never recommend MSDE for even 25-30 users.

--
Cheers
Van
"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:ur******** *************** *********@4ax.c om...

But there are reports of up to 100 users using an MSDE backend.

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

[Ted Theodoropoulos]

users should not even enter the equation except possibly as a
coefficient. the number of concurrent processes are what's important.
concurrent users doesn't necessarily mean concurrent connections and
it certainly doesn't mean concurrent processes. i've yet to see solid
testing data around MSDE but i have used in successfully in several
situations including one or two production environments but admittedly
they were small.

having the front end sql tools is manditory, however. don't even
think about trying to create, maintain, and benchmark your db using
sql statements. u need QA, EM and Profiler. it takes a lot of work
(and somewhat defeats the purpose) to create a completely disconnected
front-end in access. i'm of the opinion that if you're going to put
in that kind of effort you should think about .NET where u have much
more power at your disposal.

u lose the reporting in access which is great. let me stop right
there. u didn't ask about the front end and i don't want to start
another VB vs Access war. to answer your question, YES, msde is worth
investigating but don't underestimate the work involved. if you're
not having problems, i'd wait a bit.

"Van T. Dinh" <Va***********@ discussions.mic rosoft.com> wrote in message news:<s8******* **********@news-server.bigpond. net.au>...

Yes, it a varying "larger number of users" depending on the how dynamic the
data is.

Personally, I would never recommend MSDE for even 25-30 users.

--
Cheers
Van
"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:ur******** *************** *********@4ax.c om...

But there are reports of up to 100 users using an MSDE backend.

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

[Larry Linson]

Ted,

And, why should the number of users _not_ be considered, since it is almost
impossible to determine or predict the number of concurrent processes? The
largest number of concurrent users I've seen reliably reported was in early
releases of MSDE when it was just _limited_ to 5 concurrent processes, not,
as now, just had delays inserted after 5 -- that was 25 users, reported by
Roger Jennings in one of his "Special Edition - Using Access..." books.

I have never seen anyone else, whethere I'd consider them a reliable source
or not, claim any more MSDE users. I have seen reliable reports (Michael
Kaplan, Stephen Forte, Mike Groh, and others) of 100+ concurrent users with
Access - Jet multiuser, and have routinely seen reports of 30 - 70
concurrent users.

And, without grabbing the box for my Developer Edition, I believe it
includes the _Developer_ Edition of SQL Server, which does include all the
excellent adminstrative tools. You can develop with that, then deploy with
the Desktop Edition of SQL Server (aka MSDE). And, if the audience grows,
it's a slam-dunk to move to full SQL Server.

But, frankly, all my clients who understood the need to have a server
database weren't put off by the cost of MS SQL Server for moderate size
audiences (or other "true" server databases; some, like the old version of
Sybase SQL Anywhere, were modestly priced and easily handled 25 - 50 users).

If I understand correctly, .NET is overkill for a client-server database
configuration; certainly, it is marketed not for the "small stuff" but for
enterprise-level distributed applications. Looks, from reports on the beta
of the next release that Microsoft's "third time's the charm" history is
repeating itself. That one is, reportedly, much easier for the developer and
better implemented, too, for response and stability.

Larry Linson
Microsoft Access MVP

"Ted Theodoropoulos" <te********@yah oo.com> wrote in message
news:f5******** *************** ***@posting.goo gle.com...

users should not even enter the equation except possibly as a
coefficient. the number of concurrent processes are what's important.
concurrent users doesn't necessarily mean concurrent connections and
it certainly doesn't mean concurrent processes. i've yet to see solid
testing data around MSDE but i have used in successfully in several
situations including one or two production environments but admittedly
they were small.

having the front end sql tools is manditory, however. don't even
think about trying to create, maintain, and benchmark your db using
sql statements. u need QA, EM and Profiler. it takes a lot of work
(and somewhat defeats the purpose) to create a completely disconnected
front-end in access. i'm of the opinion that if you're going to put
in that kind of effort you should think about .NET where u have much
more power at your disposal.

u lose the reporting in access which is great. let me stop right
there. u didn't ask about the front end and i don't want to start
another VB vs Access war. to answer your question, YES, msde is worth
investigating but don't underestimate the work involved. if you're
not having problems, i'd wait a bit.

"Van T. Dinh" <Va***********@ discussions.mic rosoft.com> wrote in message

news:<s8******* **********@news-server.bigpond. net.au>...

Yes, it a varying "larger number of users" depending on the how dynamic the data is.

Personally, I would never recommend MSDE for even 25-30 users.

--
Cheers
Van
"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:ur******** *************** *********@4ax.c om...

But there are reports of up to 100 users using an MSDE backend.

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

[(Pete Cresswell)]

RE/

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Others have said that they've developed apps with C/S back ends successfully
using 100% DAO. Certainly thats the fastest and least-expensive way to get an
app up and running.

I've only done one major app with a SQL Server back end. For that one, I bit
the bullet and tried to push as much processing as I could back to the server
via stored procedures. I've still got some DAO in the "System"/"Table
Maintainence" screens because it just didn't seem right to burn up a lot of
billable hours on something that only one or two people would ever use...but the
part of the app that's exposed to Joe User is 100% stored procedure driven.

So far, I've found development of stored procedures to be a real time sink. My
take is that the development manhours increase by a good 30-50% when stored
procedures are used instead of DAO QueryDefs. Maybe I'll discover some tool
to speed it up and certainly my expertise will improve....but developing one of
those things will always take much, much, much longer than an MS Access
QueryDef.

I'm hoping for another opportunity to so an SQL Server or MSDE-based app just
for the practice. If I keep on doing it like I did with the first one - making
the client as thin as possible i.e. pushing as much processing as I can back to
the server - it seems like I'm positioning myself for a transition to N-tier
development if/when that time comes. I'm already looking forward to my current
contract expiring sometime this spring so I can spend a couple months rewriting
that first SQL Server app in .NET just to get a feel for it.

To stop rambling and address your question, I can see a few reasons to develop
with an MSDE back end:

1) IT Prejudice. A lot of IT orgs despise "Access Databases" - not realizing
the distinction between MS-Access-The-Front-End-Development-Tool and JET, the
file-based DB engine that is often used with it. Having an MSDE back end that
can be migrated to SQL Server probably makes some of them feel a little better.

2) Concurrent Users. Once something is developed in the MSDE, it can be
migrated transparently to SQL Server. People can argue about how many users
JET and/or MSDE can support - but SQL Server can support a *lot* of users while
I've never heard anybody claim that JET or MSDE can support over 50.
Personally, I think more like 10 for JET...although there are people who say
they run 30 or so...probably depends on what those users are doing.

3) Control-Type Security. By restricting access to stored procedures/views,
you can butten up a SQL Server DB really tight.

4) Security Administration: You can base SQL Server security on LAN UserIDs.
Much simpler to administer from the LAN guy's perspective.

5) Data-Safety Security. With JET, LAN and local hardware problems are always
hanging over your head. I've had one app run over 7 years, 6-10 concurrent
users every day and never had a corrupted back end. In fact the *only* time it
went down was when somebody yanked on the wrong cable in a LAN closet - and then
it was back up and running within the hour. OTOH I've had other apps that went
through episodes of repeated corruption. On one it went away when we finally
got The Powers That Be to let us move it to another server. A lot of smart
people tried to find the problem, but never did.

6) Recovery. With SQL Server you can have point-in-time recovery. OTOH, it's
not trivial compared to restoring a JET DB from last nite's backup. OTOOH, if
your JET DB goes down at 4:27 in the afternoon you've probably lost a day's
work.

7) Dialup. I've never tried it, but I wouldn't even *think* about running a
JET backend over a phone line. Citrix aside, it seems to me like - in spite of
what the users say today - that there's a pretty good chance they'll want some
applications available over a phone line sometime in the future.

8) Performance as load increases. JET is fast when things are light and then
bogs down as traffic increases. MSDE or SQL Server, while maybe not as fast
with a light load, can handle heavier loads without degrading. Also, since
it's all happening on the server, you have the option of upgrading the box to
improve performance.

9) Parochial Issues. Getting one's feet wet with MSDE or SQL Server can't hurt
your technical development....
--
PeteCresswell