I need a database that could be stored network drive and would allow multiple users (up to 20) to use it without any server software.
I'm considering MS Access or Berkeley DB.
Can you share your experience with file databases?
Which one did you use, did you have any problems with it?
Access can be a bitch. Ive been in the position where i had to go around and tell 20-50 people to close access so I could go to "design mode" to change the design of the forms and maybe a column. No fun at all. (Old access, and it might just be a bad setup)
I would suggest SQLite because the entire database is stored in a single file, and it quite safely handles multiple users accessing it at the same time. There are several different libraries that you can use for your client application and there is no server software needed.
One of the strengths is that it mimics SQL servers so closely that if you need to convert from using a database file to a full-fledged SQL Server, most of your queries in your client won't need to change. You'll just need to migrate the data over to the new server database (which I wouldn't be surprised if there are programs to convert SQLite databases to MySQL databases, for example.)
I really don't think that file-based databases can scale past half a dozen users. The last time I had an Access database (admittedly this was quite a while ago) I had to work really hard to get it to work for 8-9 people.
It is really much easier to install Ubuntu on an old junk computer with PostgreSQL or MySQL. That's what I had to do even when I kept my Access front-end.
Ayende was recently trying to make a similar decision, and tried a bunch of so-called embedded databases. Hopefully his observations can help you.
I have been using Access for some time and in a variety of situations, including on-line. I have found that Access works well if it is properly set up according to the guidelines. One advantage of Access is that it includes everything in one package: Forms, Query Building, Reports, Database Management, and VBA. In addition, it works well with all other Office applications. The Access 2007 runtime can be obtained free from here, which makes distribution less expensive. Access is certainly unsuitable for large operations, but it should be quite suitable for twenty users. EDIT: Microsoft puts the number of concurrent users at 255.
Beware of any file based database, they are all likely to have the same problems. Your situation really calls for a Client/Server solution.
From SQLite FAQ
A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.
The original question makes no sense to me, in that the options don't belong together. BerkeleyDB is a database engine only, while Access is an application development tool that ships with a default file-based (i.e., non-server) database engine (Jet). By virtue of putting Access with Berkeley, it seems obvious that what is needed is only a database engine, and no application at all, but how end users use Berkeley DB without a front end, I don't know (I've only used it from the command line).
Those who cannot run a Jet MDB with 20 simultaneous users are simply not competent to be giving advice on using Jet as a data store. It is completely doable as long as best practices are followed. I would recommend in addition to Microsoft's Best Practices web page, Tony Toews's Best Practices, and Tony's Corruption FAQ (i.e., things you want to avoid doing in order to have a stable application).
I strongly doubt that the original questioner is building no front end application, but since he doesn't indicate what kind of front end is involved, it's hard to recommend a back end that will go with it. Access has the advantage of giving you both parts of the equation, and when used properly, is perfectly reliable for multiple users.
--
David W. Fenton
http://dfenton.com/DFA/
Can Access be set up to support 10-20 users? Yes. It, as well as all file-based databases use the file system for locking and concurrency control, however. And, Access data files are more susceptible to database corruption than are database servers. And, while you can set it up for this, you MUST, as David Fenton mentions above, follow best practices, if you want to end up with a reliable system.
Personally, I find that, given the hoops that you need to jump through to ensure that an Access solution is reasonably trouble-free, it is much less trouble to implement an instance of MSDE/SQL Server Express, or postgreSql.
Berkeley DB supports a high degree of concurrency (far more then 20), but it does so primarily by utilizing shared memory and mutexes (possibly even replication) - facilities that do not work well when BDB is deployed as a file stored on a network drive.
In order to take advantage of DBD concurrency capabilities you will have to build an application around it.