Fog Creek Software
Discussion Board

Access vs. MSDE

"Ever try to build an installer that (1) checks to see if SQL Server or MSDE is already installed, and if it's not (2) installs MSDE, and (3) doesn't install MSDE with "sa" and no password (which is the default) leaving you open to a known security vulnerability, and works on Windows 98, Windows ME, Windows XP, and Windows 2000?"

"Now, if a dev wants to ship an Access database with his new .NET application then it's, uh let me see, just copy the .mdb file.  In other words, Access supports "Xcopy Deployment" as long as the "Access runtime" is installed (how .NET of it, and it's not even a .NET server)."

Good points!

Just me (Sir to you)
Thursday, May 15, 2003

Access - rope for the masse.

From the article "Also, why isn't MSDE as ubiquitous as IE"

Well after the little slammer fiasco, we better be damn glad it wasn't.  IIS was more ubiquitous and look how many idiots still have nimda laden systems and don't know it.

Every thing does not need to be a wizard.  Man up and learn to develop or get out of the business.  Way to many amatuer desktop app people in this industry.  I for one will be glad when we move beyond the quagmire of the desktop.  A "desktop" should consist of a monitor, mouse, keyboard and a tiny box of some sort with a network connection and NO HARDDRIVE, NO FLOPPY, NO CDROM.

Thursday, May 15, 2003

young grasshopper, you'll just be changing the quagmire of the desktop for the quagmire of the network

Thursday, May 15, 2003

It is interesting, but the question of where does one put some data on a computer constantly comes up.

If we have a good nice central data processing center with all the bells and whistles then we don’t need local data storage.

My oh my, how times have changed right back to the good old days of the mainframe!. If we have a good support team, good sql-server management, good everything, then I also agree that the desktop should not have any local data storage.

The problem is now lets send out our 15 salesman on a road trip. If the sales force can always assume that they ALWAYS will be able to connect to the company server, then we really don’t need a local database on each notebook, do we? It even means that for Excel and other parts of office to store data, we again don’t need some local data engine either (and that engine is usually JET, and not the MSDE).

Just for sake of clarity here, “JET” is the most common data engine used with products like ms-access, but you can also choose to use the MDSE engine, which is a stripped down version of sql-server). JET is a file based engine, and is NOT client-server. Both do support ODBC however! Often a large number of us choose JET, since it does support “xcopy” development, where deploying of the data is simply a file copy. With MDSE, this is generally not so simple.

The problem here is that we can really can NOT make the assumption that the sales force will always have a live connection to the company data center.

I am actually dealing with a possible job right now with the above exact issue. We can easily integrate much of the office suite into the companies data base and server structure that they have. It is a d3 database (, and we need to automate some of the sales force stuff. The fact of it not being sql-server is no big deal here.

The problem is how do we allow local, non net connected sales people to take and process orders on their notebooks? It is very possible that simple paper forms will be a superior technology to the notebooks unless we can solve this problem. They have the paper now, and each sales person also has the notebook.

So, do we allow local data storage on the each of the notebooks to take orders?

The ideal view is that the orders are entered into the computer, and WHEN the salesman has a chance, they connect, and up load the data. However, if we allow this, then we need a data engine and data on the each PC. Further, or worse, if we allow notebooks to take orders in this fashion, we also now need a running application on each notebook. (and that REALLY is a big deal). Now, what data engine do we use here?

If we can assume that the sales force can use paper forms, and then connect to the central system to enter that order, then we don’t need an application, or any local storage on the notebooks. Further, most if not all of the orders taken WILL initially be done on paper. This process will occur EVEN when a notebook is close by anyway. Given this, I am leaning towards NO data on the notebooks. We are looking at Terminal Services right now.

However, the REAL ideal here is that while at your desk connected, you get all your current clients that you have to visit, and get all the previous history information on that client. You then load up the notebook and hit the road. You take orders, and then eventually connect, and the data is synchronized. (using database replication comes to mind here real quick).

Hence, this problem is VERY simple if we can assume that everyone is always connected. Until that day comes, then we still have needs for a data engine on each PC.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Thursday, May 15, 2003

Lotus Notes? :-)

Frederic Faure
Thursday, May 15, 2003

MSDE is harder to install than a jet database, but the installation of a named instance is very easy with Install Shield 8.  (And yes, you can set the SA password).

JET is fine for small, simple applications.  But, if you application needs to scale from between 1 to 100 users, MSDE is a much better choice.  For the clients with 1-10 users, MSDE is fine.  For clients with many users, simply install the database on SQL Server.  No muss, no fuss, ONE codebase.

Also, JET on a network with multiple users can do very bad things depending on the Microsoft Server settings or the Novell client software in use.  MSDE databases just run.

Just my $.02 (but based on moving a large application (install base 500+ customers) from JET to MSDE.

Eric Budd
Thursday, May 15, 2003

I've never done it, but I seriously doubt whether MSDE could scale to anything close to 100 users, given the built in performance-degradation that occurs as the number of connections rises above 5.

If you have to scale with MSDE, at some point -- and before you have many users -- you're going to have to move to SQL Server.  That move is simple, since MSDE is essentially a crippled version of SQL Server.  The only difficult thing will be paying for the SQL Server licenses.  And that's exactly how Microsoft wants it.

As far as I'm concerned, the main advantage MSDE has over Jet is not scalability.  It's reliability.  And possibly speed, depending on how the database app is constructed.  (But in most cases I think you'd be able to build a similar app that uses Jet and is just as fast.)

Herbert Sitz
Thursday, May 15, 2003

Eric -- OK, I can see now that you weren't suggesting that MSDE could be used with 100 users.  So my previous post can be taken as just a clarification that when you start with MSDE you're going to need to purchase SQL Server licenses if you want to scale up beyond a small user base.

Herbert Sitz
Thursday, May 15, 2003

I didn't say that MSDE could handle 100+ users, only that the same code base with SQL Server could. 

Another thing we have found is that using ADO.Net which uses a disconnected model, MSDE works fine with 10+ users depending on what they are doing (and the machine MSDE is running on).  In most client/server based applications users spend most of their time looking at the data on the screen and only a small fraction of the time interacting with the database.

Any yes, MSDE is MUCH more stable than JET and provides better backup options.

Eric Budd
Thursday, May 15, 2003

I believe the MSDE Deployment Toolkit comes with a VB 6.0 project called 'SqlAppSetupWiz.vbp' that illustrates how to install SQL Server or MSDE solutions.  I have never used it, but it might help some folks who are looking to deploy an application that uses these DBMS's.  There is also an article entitled 'Deploying Database Solutions' in the SQL Server 7 technical Articles - help, which is a good read if you need to create a setup program.

Dave B.
Thursday, May 15, 2003

Hmm, is using MSDE with disconnected recordsets not in violation of the MSDE license?  I know they prohibit use of  MSDE on webservers for a similar reason (viz., that stateless apps can scale remarkably well while using very few connections). 

If MS hasn't prohibited use of MSDE with disconnected recordsets yet, I wouldn't be suprised if they modified the license in the future.  Of course that might not matter, because I don't think many people care about complying with the MSDE license and I don't think Microsoft has been very active in publicizing or enforcing the terms.

Herbert Sitz
Thursday, May 15, 2003


Wouldn't MSMQ be the solution you're looking for in this case?

Thursday, May 15, 2003

It would be very hard for Microsoft to say we couldn't use their data engine (MSDE) with their latest and greatest data access method (ADO.Net) in a client/server environment.  You could also use disconnected ADO recordsets or event (yuck) RDO.

Eric Budd
Thursday, May 15, 2003

Eric -- You're right, of course.  And rechecking the latest info on MSDE 2k, I see that you can even use it for web apps.

I'm not sure how I got idea that you couldn't use it for web apps.  I'm sure I read that at one point while checking out licensing for the original MSDE. 

In any case, you're absolutely right that MSDE should scale very well when using stateless connections.  I bet you could server close to 100 users in many circumstances that way (even though I realize you weren't suggesting that in your original message).

Herbert Sitz
Thursday, May 15, 2003

MSDE is actually a great option for an ASP-model business that's in startup phase: run it until you have enough clients that you get contention, then you have revenue so you can seamlessly upgrade to the full (and very expensive) version of SQL Server.

Brad Wilson (
Thursday, May 15, 2003

TO Jake,
Server Centric has been done for some time as proven by and
No harddrive, no floppy, Not many Users.  I will leave it to the read to figure out why...

A Software Build Guy
Friday, May 16, 2003

The phrase 'seamlessly upgrade' gives me the heeby geebies.

Simon Lucy
Friday, May 16, 2003


>Wouldn't MSMQ be the solution you're looking for in this case?

A message based system is possible, but on the notebook side we still need some type of data application to run. You can’t un-leash a sales force with a data entry screen that does verify the types of product ordered. Stuff like combo boxes that look up pricing also do double duty since users then can’t enter the wrong product info. All of the process of designing a good application means that the data entry system needs a database behind those forms. In fact, I don’t know of any business application that can function without some data store during data entry. Hence, we thus still need a local data engine.

So, while we could consider a messaging system for the transfer, the real problem is that we need a application to run on the notebooks to create that order before it is sent. So, the sending of the order via MSMQ, or some other means is not the problem. The software to create the order on the notebook is the problem.

Simple put, if we can assume a connection to the server, then we don’t need any software to be written for, or installed on the local pc.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Friday, May 16, 2003

          Ask the salesmen what they want.

          You may find that they copy the data from the paper forms in their lost moments in the car park or coffee shop or whatever. They won't be able to do that if they need a connection.

          Also find out why they still enter on paper when they have a notebook. It might be to do with the size of the notebook, and they would want to enter the data directly when they find themselves with a tablet PC.

Stephen Jones
Friday, May 16, 2003

"The phrase 'seamlessly upgrade' gives me the heeby geebies. "

This is not a problem.  Backup the database from MSDE.  Restore it on SQL Server.  Configure user counts with the server and the database.  Easy as 1,2,3....

Eric Budd
Friday, May 16, 2003

Heck, you don't even have to do that. The file format is the same. Detach from MSDE, uninstall MSDE, install SQL Server, attach.

Brad Wilson (
Friday, May 16, 2003

Ahhh yes.

The squirrelly mess that can lie behind the words 'Install'. 


You've shipped your software,  you got the installation right for the MSDE, they use it happily for some time, perhaps some considerable time.  Now they need to scale up and want to use your SQL Server version.

You're nice to them, you don't even charge for this extra scaling except maybe in per seat licences.  But they have to install SQL Server, oh actually first they have to install Win2K Server, then SQL Server (and not leave sa with a blank password).  Then they get this icon on the taskbar, oh and all these new utilities, look what does this do?

And they manage to connect the old MSDE file to the SQL Server and then they can really play.

Once you step beyond the MSDE application you've also stepped outside the shrink wrap boundary which lets you isolate yourself from the shenanigans that users can get themelves into quite blamelessly.

That's what gives me the heeby jeebies.  I know its easy to do, for me; making it easy for an unknown user in a far away land is a differently shaped egg.

Simon Lucy
Friday, May 16, 2003

Actually, if you were talking about my points, I did mention I was speaking specifically about "ASP-model businesses" (i.e., you host the service, and a monthly service fee is paid). I was not talking about shrink wrap software.

Of course, I think it's naive to call ANY server software "shrink wrap".

Brad Wilson (
Friday, May 16, 2003

On MSDE limitations:

Something to be aware of: MSDE's limit is concurrent queries, not connections. You can have as many connections as you want to the system. The only performance govenor is in the query engine. Once you hit five concurrent queries, the system throttles back performance.

On MSDE installation:

I use MSDE for one of the products my company makes. Initially, it was a headache, until I understood the options involved. It's very easy to launch the MSI from a command line (as part of an install script, for example) and pass across options including the instance name, SA password etc.

Using any standard windows installer creation tool (ORCA, Wise, Installshield etc) you can even add a custom action that embeds the installation into your applications install, progress bar and all. It looks very slick.

Geoff Bennett
Sunday, May 18, 2003

No one has heard of XML, huh?

I've written software that already gets around this entire problem and avoids a local data engine entirely.  In our case, we're still using desktops, but the remote desktops are connected across a WAN and are designed to run even if the WAN drops for several days.

Why not write the TabletPC/NotebookPC app and use XML files for your local data store.  With .NET you can edit XML files as if it were a local data store, do queries and even do data-bound control, complex grids, etc etc.

When your sales force creates orders, place the order XML files to be uploaded in a folder on the filesystem.  When they connect, draw up a progress window dialog and push the new orders up to a web service to process the orders.  Send a result XML document back to the sales force worker so they can review what happened.

Here's an example synchronization session:
  1. User clicks "Synchronize"
  2. You create a new synchronization thread, THEN..
  3. Take your order files and contact the corporate web service, push each order through.
  4. Take any business errors that may have occured and write them back on to the local cache.
  5. Delete the sales orders that were accepted by the web service.
  And finally...

  6. Contact an updating web service to redownload all your list datasets (like product, serial numbers, statuses, employee lists, office files, etc).

The sales guy can then open any orders that were rejected, make changes, then send them up again.  if you were a good little coder, you would have put good comments in the order file like "PART X11BF IS OUT OF STOCK - PLS EXCHANGE WITH PART X118G".

Queries of disconnected data is also easily accomplished with XML, but most remote machines can't handle giant datastores.  I've seen some really crappy 400MB Access databases out there that people try to send synchronous updates to... it's pathetic.

Do your research up front and find out exactly how much information the sales people really need and what to send down and back up from the devices.  If the sales guy needs to lookup customers in his area, then maybe when he synchs up you can send him a partitioned customer XML document that only contains the contacts in his region and send only changes down to the device (similar to synching your USENET newsgroups with a newsreader).

It's better to deploy a 90% solution that works well than a 100% solution that breaks down, is pathetically slow, or takes an army of contractors to maintain.

Christopher Sawyer
Sunday, February 1, 2004

I would just use and forget about MSDE/Access -- especially for mid-market apps < a few hundred users.  I'm fairly sure there are tablet-based apps using it as the 'temp' database as well -- healthcare app methinks.

Monday, March 15, 2004

Here's a new MSDN article that describes the ASP.NET and MSDE workload governor well, and offers a solution to getting increased scalability from it:

Stephen Peters
Thursday, April 8, 2004

*  Recent Topics

*  Fog Creek Home