Help! Life or Death hangs in the balance...well, maybe not quite that severe, but this newbie needs some expert direction with a project, please.

I have a VB6 project I'm working on that will be the front end to a database. To date, I've been able to stick with MS Jet (& DAO) as my database of choice because the number of shared users and num. transactions were very small. This time, however, I'm looking at having up to 30-40 users sharing the database, and although the transaction volumes will not be high (2-3 per minute) I've been hearing that Jet will surely barf and cause much grief in this situation. I've also heard that Jet is far less stable than other db's when it comes to users losing their connection during a transaction. I'm guessing most of the guru's will suggest learning and using SQL server, but this appears to be a daunting and expensive task. I'll do it if necessary, but I would greatly appreciate any feedback regarding this issue, as well as:

- Does MSDE provide more stability than Jet and would it be suitable?
- What about MySQL? Good experiences with this one or is it simply evil?

Also, related to this project: would a database like Jet have a problem with 30+ users sharing it to simply retrieve recordsets (read only)?

Thanks a bunch...any help would be greatly appreciated!

-Peter

JET
In my experience when Jet loses network even for an instant it loses connection to the MDB file. At best the user gets a 'DISK OR NETWORK' error. at worst you get a corrupted MDB file.

I've read of success stories with up to 50 users on Jet, but I'm sceptical. Have a look at groups.google.com if you want to find them.

Remember that if you have a large number of rows in your database (it may have in the future) then your system is going to get slow. If you perform a query on Jet that retrieves 10 rows from 10,000, then all 10,000 rows are read by the client, so they have to travel across the network, whereas obviously MSDE or MS SQL Server will only kick back the 10 rows.

MSDE
MSDE apparently starts slowing down when asked to perform more than 5 concurrent batch processes. It sounds like it might be suited to your application
http://www.granite.ab.ca/accsmstr.htm has info.

We use MSDE at work, but only for a 10 user system. Not sure if you'd need SQL Server instead. MySQL is likely to be a steeper learning: the ODBC drivers were flakey when I tried them, but that was a couple of years ago.

MSDE might be quite hard work since you don't get the Enterprise Manager (GUI) for administration: we have it as a result of have one of the developer tools. You'll have to use the command-line I think.

My personal preference: ditch Jet, and go MSDE.

Dave

Thanks, Dave.

I was seriously considering MSDE until I realized that I don't have a license to distribute it with my application anyway! I have VB6, not visual studio 6, so that rules out using MSDE 1.0 (only distributable if you have VS) and I have Office XP Pro (which has MSDE 2000 on it) but I can't distribute it either since you need Office Developer ed.! I think Bill is too greedy.

Anyway, you're right about the steeper learning curve with MySQL, and the same is especially true for PostgreSQL, but I've heard great things about the latter and may give it a try. The app I'm programming will be a distribution, and I don't think it'll fly if I need to shell out for a license on every install.

Talk about a learning curve - I may even be installing PostgreSQL on a Linux box! (I have no idea what I'm doing)! Anyway, thanks for the post, I really appreciate all responses.

-Peter