Fog Creek Software
Discussion Board




MS Access or MSDE for a new development?

I'm about to start a new development using VB6 and I can't decide between using MS Access or MSDE (SQLServer Personal Edition) for the database. As a programmer, I would love to use MSDE, but I'm doing research before making a decision, mostly for deploying issues.

I have used Access for desktop apps and SQLServer for enterprise app development but I have much more experience with SQLServer than with Access. I have never used MSDE as database is a desktop app, though.

About the application: It's a semi shrink wrapped monolitic application designed to run on user desktops. The users are clients of my client, so the app will be distributed and installed exclusively by my client on the user's machines. User desktops' specifications range between Win98/ME, Win2K and WinXP.

The application allows data input for very specific surveys, does very simple calculations on data entered, and prints out reports. User activities are data-input oriented and the work is often done by several people, so there is the need for several computers to have the app installed and accessing a single database on a central machine over the LAN.

The setup program would ask if installation type is client or 'server' and the only difference should be that the server installation will host the database and all other machines should be installed as clients. When installing a client, the setup program will ask for the name of the server machine to setup an ODBC entry (or building a connection string).

The databases are small, typically from 100 to 600 three-page surveys, making something like less than 10 MB if stored in Access.

So, what do you think?, Is Access enough and reliable (I'm mostly concerned by the 'distributed' side of the application having the database in one machine and accesing from several machines, via ODBC I supposse) or I should go with MSDE? (where I have to worry about the user's machine having minimum requirements to install the SQLServer service).

To make things a bit clearer, I don't need the MS-Access front-end, I'm talking about the database driver only to operate on a .MDB file.

Also, not as important as the thechnical issues, Is there any special considerations I should watch concerning licensing of the MSDE engine?.

Thanks a lot for your time and comments

Sergio
Tuesday, March 11, 2003

From MSDE site:

http://www.microsoft.com/sql/techinfo/development/2000/msde2000.asp

" ... MSDE 2000 is designed to run on Microsoft Windows® 98, Windows Millennium Edition (Windows Me), Microsoft Windows NT® Workstation version 4.0 (with Service Pack 5 or later), and Windows 2000 Professional ... "

So, operating system seems not to be a problem. But,

" ... MSDE 2000 can be built into applications and redistributed royalty-free with Microsoft development tools, such as Microsoft Visual Studio® .NET and Microsoft Office XP Developer Edition ... "

Does that mean that I cannot use it with a VisualStudio 6 application ?

Sergio
Tuesday, March 11, 2003


>(I'm mostly concerned by the 'distributed' side
>of the application having the database in one
>machine and accesing from several machines,
>via ODBC I supposse)

My experience has been that access scales up to a -few- users.  Once you get seven people banging away at the database, forget it. :-)

If you're using ODBC, why not start out with access.  If it becomes a performance problem, swap up to SQL Server.  (Can you Ship MSDE to people for $$?  I dunno.)

Using ODBC means that your app doesn't -care- about the database back-end.  All you have to change is the install script. 

So, in conclusion, don't assume that access has performance issues.  Fix the problem when and if you have one. :-)

regards,

Matt H.
Tuesday, March 11, 2003

The product I'm working on is very similar to yours.  We decided to use MSDE (with a VB.NET front-end).

This was informative for me when making this decision, especially Table 1.

http://msdn.microsoft.com/library/default.asp?URL=/library/backgrnd/html/msdeforvs.htm

Joe Paradise
Tuesday, March 11, 2003

It sounds like Access would be a better candidate for the needs that you've expressed. The Access Jet engine of course only runs when instantiated through ODBC/OLEDB, versus the MSDE engine that runs as a service, often 24/7 without explicit control from your application, and the Jet engine is dramatically lighter on resources (as the MSDE really is SQL Server, just with a couple of registry tweaks hardcoding some arbitrary limitations). In many ways the query syntax of Access is actually more powerful than SQL Server, though of course you'll want to remain as close to "pure SQL" as possible if and when you decide to upgrade to SQL Server. As a sidenote, note that a large percentage of the systems infected by the Slammer virus were actually running MSDE...often without the user realizing it.

I am a huge fan of SQL Server, but for a small client application it's just a gross overkill. The one scenario where I could see it is if you planned on actively using the replication facilities of SQL Server (i.e. many clients that gather and replicate information to a central database), but you could do that fairly easily "by hand" anyways.

Jimmy Chonga
Tuesday, March 11, 2003

While I've never used MSDE for development, everything I've read about it gives me the distinct impression that its fundamental purpose is to let you produce an application which works with SQL Server without having to have SQL Server on your development machine.  In other words, it seems to be a "practice" SQL Server which you use in development and then replace with the real thing when you deploy.

One good place to search is Google Groups; go to comp.databases.ms-access and search on MSDE and you'll find a number of professional Access developers' views on it.

Kyralessa
Tuesday, March 11, 2003

Matt, Thank you for your comments.

You made an interesting point. I really hadn't thought that I could design my app database independent and use ODBC to switch later if needed.

The main reason I wanted MSDE was to use stored procedures instead of hardcoding the SQL statements in VB code (an approach I really like and use all the time when using SQLServer as database).

The second reason is to eliminate ODBC administration. I like the way you can connect to SQLServer using just the connection string without having to install ODBC DSN entries.

I remember having used DSN-less connection to a .MDB file, but I don't know if you can connect like that to a remote machine.

I suposse It will better if I quit complaining about ODBC DSN and learn to do that stuff    =)

Sergio
Tuesday, March 11, 2003

Is the application gioing to run separately on each of your client client's site?

How many simultaneous users?

How much processing is involved in each query. If there's a lot then Jet is faster because the processing is done on the desktop machine and not all on the server. If it's just a question of adding records then client/server seems superior.

Stephen Jones
Tuesday, March 11, 2003

There is no doubt that stored procedure like programming is superior, and Access gives you that via "Queries" (they can even take paramters). Additionally there is an Access OleDB driver that lets you provide the path, and if the database is on another machine accessible via a share then you most certainly could access it in such a manner.

If you have multiple PCs all connecting to the same database instance, then the MSDE may be an option. As another user mentioned, the MSDE really is a choice where SQL Server would be appropriate, but people don't want to pony up the dough for a SQL Server license : Microsoft really hopes that the user will become entrenched in SQL Server, will grow into the MSDE limits, and will then upgrade to SQL Server. The other instance where MSDE is appropriate is where you're using the replication facilities. Cheers!

Jimmy Chonga
Tuesday, March 11, 2003

In our case, for CityDesk, we chose Jet (access) because the installation is easier and people understand the idea of one database = one file. Trying to explain to end users that they need to be running a server to use the application is way too complicated. For SETUP, it's much simpler just to make sure they have a bunch of DLLs in place rather than also making sure that they are running a server with all the complexity that brings in.

Joel Spolsky
Tuesday, March 11, 2003

Number of users here is a big issue:

The decision breaker for this is going to be based on several issues:

What is the average number of users (multi-user). If on average, you will have  a LOT of single user systems, and the occasional 2-3 user system, then I would go with a JET/mdb file share. Your support costs will be less. You might throw in some code to make a daily “copy” of the data in case of file damage.

The jet file share is thus a lot better for simple load and go type software. Many commercial programs out there do still use JET and a file share. Joels CityDesk does. So does the very popular simply Accounting software.

As side note, you don’t use ODBC to share a JET/mdb file on the network, you simply share the file in a folder. You will wind up using a ADO connecton string to JET (that occurs only ON the locak pc). So, in therory, ODBC is never actually occuring across the netwrok.

You still wind up using a standard file share via JET/mdb over the network.  However, if you stick to straight sql, then can write as if using odbc.

Hence, the only real down side to a JET file share is the possibility of a damaged file on a network. With a good backup policy, then maximum exposure is one day, and most of the time that is an acceptable risk. It is with CityDesk, and it is with Simply Accounting.

Thus, #1 issue is average number of users. If you users counts are below 5 users, then I would go with JET.

Other issues are:

Connectivity. Do you need the data eventually for a web site? How about access to the data via a wan? If you need a variety of connection options, then the MSDE is the better choice. It is a socked based connection, and NOT a file share which is useless across a wan.

Security:
    You can’t prevent employees walking away with a file share. With the MDSE, then you can lock up the actual data files.

None tier, or 3 tried design? Do you plan to design a 3 tried system with business objects between the database, then the GUI? If yes, then again, MDSE is the better choice. Are you existing software designs that way now? If you have a first rate team, and they do the 3 tiered dance, then you might also consider this design approach to your application.

Will a number of clients have large user seat counts? If you expect a large number of clients to have more then say 10 users, then you will want to offer sql-server as a option. For little guys, you run MSDE, and for the large ones, you run Sql-server. Hence, design for your average user seat count.

Does your designs assume stored procedures. If you are a real sql-server guy, then loosing stored procedures is going to hurt, and again you want to use the MSDE.

Potential market:
Selling a product that requites sql-server can actually give your product respect. If this is not a issue, then this is not a issue!

In addition, you can also consider something like open source MySql, as that also is a good possibility if your application is quite simple, but needs lots of users (I say simple, since MySql does not have stored procedures, but is very good for appcltions to share data, and this is especially so when stored procedures and triggers are not needed).

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Tuesday, March 11, 2003

Thanks evryone for your comments.

Jimmy, thanks, that was very helpful. I think I should have better asked for reasons to NOT using MSDE, as I'm already positive that Access is good for the job.

I also believethat system resources consumed by MSDE are not justified by the size of the application and its relevance to the user, neither do the security vulnerabilities exposed.

What I think I'm going to do is start with Access, but being careful on isolating SQL code in data access classes.

I'm even thinking on builing a VB class called 'StoredProcedures' that will have a method for every SQL statement and isolating it in an independent DLL. That way I will be able to change data access statements the same way I do when using real stored procedures.

Sergio
Tuesday, March 11, 2003

I realy like MSDE. It is not a toy at all. It is the full SQL Server engine with some performance caps andf excluding some of the nice perifery. Very capable in a wide range of situations.
My only beef with it is is that it is not maintained trough Windows Update. This is a very big deal which in my mind should disqualify the use of this engine in "desktop" style applications that is intended to run without the supervision of a true administrator.
Think about it: do you think your clients will be able to handle SQL Server SP and hotfix installations? Do you even want to ask them?
If Jet can handle the load, I would go with it for this reason alone.

Just me (Sir to you)
Tuesday, March 11, 2003

I think that using either ODBC or ADO are your best bets here, and don't worry so much about the backend.  As somebody else mentioned, so long as you stick to standard SQL, everything should be fine. For now, stick which whatever backend will be easiest to deploy and support.

Clay Dowling
Tuesday, March 11, 2003


Yes, the multiuser scenario is less frequent than the single user scenario. And I'm talking about half dozen people working at the same time at most. Typically it will be 2 or 3 people.

Albert, that is an interesting security observation, I will take that into consideration because the data collected is in fact sensitive info about the user's company. Also, the file share approach means also a little more of setup administration (sharing the folder) but the advantage of seeng the .mdb as local is worth the extra configuration.
BTW, you surely type fast ;)

Thanks again everybody.

As a side note, it is really amazing how can one get so many top quality comments  in such a short time. You helped me in 20 minutes to make a decision I have been thinking for days.

I am a frequent reader of JOS forum, but I will start to post more often also.

long life to the formum.  =)

Sergio
Tuesday, March 11, 2003

Sergio said, "The main reason I wanted MSDE was to use stored procedures instead of hardcoding the SQL statements in VB code (an approach I really like and use all the time when using SQLServer as database)."

Sergio -- As someone else said, Access/Jet actually does do stored procedures.  Actually sort of a very limited stored procedure that is basically just a stored query that accepts parameters.  But that can cover quite a few scenarios; that's all a lot of the stored procedures  I use in SQL Server are.

You have to use ADO to get access to these stored procedures.  Nice thing is that a stored procedure with no parameters is really the same thing as a view.  Having this available really adds to the usefulness and usability of Jet, in my opinion.  And makes it easier to start out with Jet and later migrate to MSDE.

The Jet/ADO stored procedures are not usable from within an Access application.  And I don't think many Access or non-Access programmers are even aware of them.  But they do exist and they are cool.  Here are a couple links to more information about how to create and use them from within VB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;201493

http://support.microsoft.com/default.aspx?scid=KB;en-us;q202116

Herbert Sitz
Tuesday, March 11, 2003

"If it's a lot of processing then Jet is faster."

Stephen, are you sure of this?  The whole concept of client/server is to offload processing from the workstation.

If you have five workstations each who have to execute a query for a table containing 100,000 records, then you're downloading 500,000 records over your network just to determine which records to include in a query result.  This is horribly inefficient.

The real question in my mind is, do you have a sufficiently powerful server?  If you're using an old P65 with 32MB of RAM that's one thing.  A PIII/500MHz with 512MB of RAM will outperform any file-based database, IMHO.

Karl Perry
Tuesday, March 11, 2003

Access is only fast if the database is located on the local machine running the application.  If you are sharing the file over a lan, performance goes way down.

We have just moved out application (payroll) to MSDE\SQL from Access.  Some of our cliients have 3000+ employees and databases over 500 megs.  Performance of Jet under those circumstances (with multiple users) is not good.

Also, Jet files can get get very flakey.  We had too many files get hosed in multiuser senarios over a lan.  Novell client patches, Win Server 2000 SP3, ect.  I just wouldn't trust Jet with anything too critical.  Just my .02.

Eric Budd
Tuesday, March 11, 2003

Herbert, wow. That was.. enlightening. One more reason to go for the Jet alternative.

To be honest, I thouht that jet was already becoming 'old' and wanted to make the leap to the next cool tool... Well, I was wrong and it seems that using a jet/mdb database is the right tool for this time.

Sergio
Tuesday, March 11, 2003

Well, Jet is getting old.  And MS is trying to push people away from it. 

It can still be the right tool for some jobs, though.  I wouldn't want to use it for apps expecting to get more than 100 MB or so of data, or that will be used by more than 15 or 20 concurrent users. 

But if you program the front end the right way, you can minimize most of the speed problems caused by it being a fileserving database.  And if you do regular backups you won't have too worry much about the fact that it's not as industrial stength as other solutions.  No fileserving database is ever going to be able to absolutely corruption-free.  From what I can tell, Jet has improved in this regard from the problems it had several years ago and it seems to be fairly reliable, so long as client machines aren't crashing and taking the .mdb down with them.

Herbert Sitz
Tuesday, March 11, 2003

Just me (Sir to you): "Think about it: do you think your clients will be able to handle SQL Server SP and hotfix installations? Do you even want to ask them?
If Jet can handle the load, I would go with it for this reason alone."

The is actually a big consideration. The latest SQL Server 'virus' also infected machines running MSDE that wern't patched with the fix.

How many users of MSDE:
1) Knew that there was a patch available?
2) Knew that they should patch their machine?
3) Even knew that their app used MSDE?

RocketJeff
Tuesday, March 11, 2003

>>>If you have five workstations each who have to execute a query for a table containing 100,000 records, then you're downloading 500,000 records over your network just to determine which records to include in a query result. This is horribly inefficient.

That does not happen. Lets assume we have a single user pc, with NO network involved. We fire up ms-access and then do a query on that 500,000 reocrd table. Lets assume that they query will return one records. Does JET load all 500,000 records to find that one records? No, it is does not. It reads in the index file, and only part of that. It does a binary tree search into the index, and then finds out where the record is. At most, a few “pages” of data gets loaded.

Only that one record + some of the index will be read from the disk.

Now, take the above example, and share the file on a network. The exact same thing happens (nothing changes, except the location of the disk drive). The whole file is NOT sent down the network. Well, ok it is if there is not index on the field we are searching.

Eric’s comments about JET slowing down with large files and lot of users is correct. In those types of environment, JET is not workable at all.

However, for small files in the 50, to 150,000 record range, and only a few users, it is a very good solution.

The horse for the right course is the key here. I have a lot of JET applications running in 2-6 users in a file share. With 5 users, and 55 related tables, some in the 50,000 record range, response is instant. There is not hint of a slow down yet with such small files.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Tuesday, March 11, 2003

Karl, I expressed myself badly here.

The scenario where Jet will win is where multiple users are each doing simultaneous adhoc queries on the database.

You lose the time taken to download the recordset but will gain in the fact that the queries will execute a lot faster, not least because you have multiple processors and memory to do them with, instead of just the one server.

One factor to bear in mind is whether the application has the server to itself. If you have a MSSQL or Oracle application on a database server that has a load of other applications on it that also access the database engine then things could slow down.

In most cases though you will be right.

Stephen Jones
Tuesday, March 11, 2003

Joel,

one thing I never got was the reason why you ignore MSDE for FogBUGZ? I know this is not an oversight. You are far more clever than to just let something like that slip. So there must be some serious reason but I can not see directly in this case what it could be. Your insights might be very relevant for those of us considering MSDE for their own products.

Just me (Sir to you)
Wednesday, March 12, 2003

One more thought, will your clients respond differently to an Access vs SQL Server Application?

For some applications it may simply be easier to sell an IT manager (or worse a PHB) an application built on 'SQL Server Technology' than one built on Access.  Access is fine for many applications, but it doesn't get any respect  (probably since it is on everybodies desktop).  Also, it has no security to speak of that can't be broken in about 10 seconds.

Just my $.02.  Wait, now it is $.04.

Eric Budd
Wednesday, March 12, 2003

Eric, Albert has pointed out in another post that Access has the same security model as SQL server.

You can set up user-level seciurity, and every object can have varying levels of permission set. Frankly it's a pain to set up but it certainly can't be broken in a few seconds. In fact the main problem is people who forget to print out the report on the security settings and keep them in the safe.

You can also encrypt the data to prevent it from being read .

The only problem would be that somebody could copy and paste the entire .mdb file; I would think that there are ways using OS security to prevent that happening, and you'd still have to break the Access security to do anything with the file.

Stephen Jones
Wednesday, March 12, 2003

Yes, you can.

You can also buy a handy-dandy utility for less than $100 which will crack both the Access Encryption and the Access User security in the .mdw file.

Go to http://www.crackpassword.com/#office

We tried their demo and, sure enough, it works. 

Access user and group based security is fine for some things, but it is insufficient if you really need to keep the data safe.

Eric Budd
Wednesday, March 12, 2003

Thanks for the link; I've bookmarked it in case I ever need it.

You would presumably need access to the .mdw file in the first place.  It seems to me that you could stop that scenario by not granting sharing on the folder but allowing traverse folder rights so that the user would be able to connect to the file but not view it. You, or somebody actively involved in NT seciurity would have to answer this one, as time constraints mean checking it out has got to wait its turn on my todo list.

Stephen Jones
Wednesday, March 12, 2003

I may have mentioned that ms-access has the same security "model" as sql server. However, what I meant is that you have the same concept of users, and groups. That does not mean, or imply that ms-access has the same security “model” as sql server.

Hence, with ms-access you have groups, and security rights etc. In fact, it really is the  same “idea” as windows 2000 sever. While it is the same conceptually, there is no real security with ms-access.  Ms-access security is no different then most products (you have users,  groups, and assign users to groups and then assign those groups to tables etc). That part of ms-access is simple, and is no real big deal.

However, a employee can simply copy the both the mdb file, and the workgroup security file and take it home. So, we have to distinguish between what we mean by security here. Ms-access has some security in the sense of saying certain users (or groups) can run only certain reports. Or certain users can't read a particular table. However, if you don't want an employee walking out with the data, then don't use ms-access. It is not secure in this regards.

With sql-server, users DO NOT need physical access to the data file, where as with ms-access they do. There is NO WAY to prevent a user from walking out the door when using a jet file share. With a true data server, users do not need access to the actaull data files (the server only does).

For keeping employees from not running a sales report, ms-access is fine. To prevent employees walking out with your data, it is useless!

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Friday, March 14, 2003

Hello People,

It's actually quite simple: Access is evil.  MSDE 2000 rocks, and it's free.

I started with Access 1.0 in early 1993, and after failing me time and time again with it's various limitations I have finally moved to MSDE and have never looked back.  As a database engine MSDE eats Access for dinner in terms of Performance, Scalability, Security, Usability and Portability.

I am of course unashamedly bias and my comments are provided without specific examples but we're talking differences in record locking, database corruption, etc.
Also bear in mind that choice of database platform will not save you from poor application design.

Warren James Bullock
Monday, March 15, 2004

*  Recent Topics

*  Fog Creek Home