473,840 Members | 1,447 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Nov 12 '05 #1
41 2466
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


Nov 12 '05 #2
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
Nov 12 '05 #3

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

Nov 12 '05 #4
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


Nov 12 '05 #5
"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
Nov 12 '05 #6
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

Nov 12 '05 #7
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

Nov 12 '05 #8
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

Nov 12 '05 #9
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
Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3107
by: izzy | last post by:
I was wondering if any of you guys can kindly help me in finding all the different versions of MSDE 2000 that came out since it's first release. I expected to find something similar like Sun's archive (http://java.sun.com/products/archive/index.html) for MSDE 2000 too but failed to find after a few hours of browsing at the Microsoft Download Center (http://www.microsoft.com/downloads/search.aspx?displaylang=en). They seem more into...
5
8443
by: Igor Solodovnikov | last post by:
Hi. I am trying to automatically backup transaction log when error 9002 happened. So i have created appropriate job and alert to catch this error. I have two instances of sql server under Windows 2000. One of them is full SQL Server, another is msde. When transaction log is full in full SQL Server error 9002 severity 17 state 2 is logged in sql server log and in Windows Application log. My alert firing my job. All is fine. But when...
10
3860
by: noname | last post by:
MSDE 2000 Release A installed under windows 2000 pro will not communicate with SQL Server Manager nor MS Access on peer computer. Can someone help? Have set DISABLENETWORKPROTOCOLS=0 at install time. Install log shows installation successful.. Control Panel> Admin Tools> Services section shows SQL server instance running.
3
6127
by: *no spam* | last post by:
I want to move my Access 2K database into MSDE. The Access Upsizing Wizard crashes (a known bug wi A2K), so I'm using the following suggested method: Access --> New --> Project (Existing Database) This asks for the name of the .adp file to create and then launches into the Data Link Properties dialog box (so far so good) I select my MSDE server from the drop-down, enter the sa account & passwd, attach a database file and try to...
5
3508
by: Robin Tucker | last post by:
I'm looking for a simple way of telling (inside a stored procedure) if I'm currently using MSDE or a full SQL server. Ideally, there is some pre-defined environment variable that won't cause me too much overhead. The reason I'm doing this is because my system "rolls over" databases when it reaches the 2Gb limit with MSDE, but obviously I want to avoid this overhead if the user installs onto a full SQL server instance. Thanks
7
1582
by: Diogo Alves - Software Developer | last post by:
hi there I am developing a software that needs a database to be shared, I've heard about MSDE, can someone tell me if * There is a newer version than MSDE 2000? * It's possible to use MSDE with C#.Net? * I would like a tutorial or something about deploying MSDE with my application.
2
5076
by: Rosy Moss | last post by:
I am in the process of cleaning up a database that our company uses to track jobs, time and expense, and customer information. We are running Windows 2000 Server with approximately 20 terminals (Each running 2000) logging in each day. Four of these terminals access the server via Citrix. Currently the database is about 60MB, but it grows to 150 and larger each week. I am constantly having to compact it to keep it running smoothly. ...
3
2538
by: Paul Aspinall | last post by:
Hi I want to package my C# winforms app, to be deployed with MSDE, as easily as possible for the end user. I want to create an MSI or Installshield (prefer MSI), to setup my C# app, together with MSDE (if not already installed), and the MSDE application DB. How can I detect if MSDE is already installed?? Has anyone does this?? How??
4
1574
by: Anthony P. Mancini | last post by:
Does anyone know how to make the MSDE do SQL authentication ? It appears to authenticate using Windows at all times. Thanks, Anthony
0
9698
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10657
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10299
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9436
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7836
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7022
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5684
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5872
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4071
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.