I have a lightweight app that will use some data. I am trying to decide if I should use XML based or just include a mdb file.
+3
A:
Access:
- More performant, particularly in regards to queries
- Better support of relationships beyond simple parent-child.
- Much easier to secure than XML files
- Straightforward upgrade path to SQL Server if there is a need.
XML:
- User editable outside of the application
- Easier integration with third-party tools
- Lightweight retrieval of data, no signficant extra libraries to load.
Really, I don't see a good reason to use XML files as the main data store of your application - you'll quickly run into roadblocks and gotchas. In that case Access is clearly the preferred method. For configuration information ABOUT your application, however, XML is probably an overall better solution.
Ryan Brunner
2010-02-09 14:30:37
It's a good answer. You missed out one important factor (which OP hasn't mentioned): Cost.
Mitch Wheat
2010-02-09 14:32:29
You don't need Access installed for an application to work with MDB files, Windows has the DB engine built-in. You only need Access to manually inspect the contents of the file.
John
2010-02-09 14:40:17
And if you have one copy of Access, you could use the free A2007 runtime to give others access to an application built in Access, if that's what you need to do. In other words, the db engine is free, and the application runtime is free.
David-W-Fenton
2010-02-09 22:26:56
John, Windows 2000 through Windows 7 come with Jet 4.0/DAO 3.6 as part of the OS. We don't know what will happen in Windows 8. Thus I'd suggest avoiding use of the ACCDB or Access 2007 formatted MDBs as the client system may not have A2007 installed on it. You can also use other tools to inspect the contents of the databaswe such as Excel.
Tony Toews
2010-02-11 19:56:42
@Tony: in my tests, I couldn't see that an MDB file created with A2007 was any different from one created with A2003. That is, there doesn't appear to be an A2007-specific MDB format. Of course, that's assuming data tables only -- if you've got VBA code, that likely has version-specific differences.
David-W-Fenton
2010-02-11 21:13:48
David, ah, I didn't realize that. I had assumed it was like A97, A2000 and A2002/3. And when I think about it that makes sense as MS put all the changes into the ACCDB format.
Tony Toews
2010-02-11 22:46:02
@Tony... MS like backwards compatibility. If they are making such big changes to the OS, your application itself may not work either.
John
2010-02-12 09:20:47
A:
If you have few items of data to store, and this is very unlikely to increase, XML is Ok... load all data into memory and save it afterwards.
I'd use XML (or a flat file more likely) for some very simple app with well-defined limits on data quite happily... but if it seemed likely that the number of data will get into the thousands I'd probably use MDB from the start.
If it was windows only, that is.
John
2010-02-09 14:43:55
A:
Don't use either, use Firebird instead.
You can run it as a fully embedded database or as a local server with basically zero administration for the user.
Then if needed you can always upgrade to a multiuser server version. It's free and it doesn't become corrupt like Access is prone to do during software or system crashes. You also have all the advantages of a full SQL database which will make some things a lot easier compared to XML if the data is non trivial.
Edit: I modifed the wording that said Access corrupts any time the computer crashes. That isn't true but it is prone to that problem and I've seen it happen many times.
Kevin Gale
2010-02-09 14:44:44
Along this vein, there are several light-weight database products that don't require a traditional server to operate - SQLite and db4o are two others that I've used successfully. Db4o in particular would probably be an ideal fit for your project, as you can avoid pretty much any ORM-type code.
Ryan Brunner
2010-02-09 15:23:49
I agree. Storing some simple data in XML is fine. But once it gets beyond simple, use some sort of database instead of rolling your own. And if you are wondering if you should use a database then the answer is probably yes.
Kevin Gale
2010-02-09 17:46:52
Access does not corrupt every time the computer crashes. I've been at clients several times with 20 users in to a database with over a hundred tables and the power went out. The server was on a UPS but the client systems were not. And no corruption.
Tony Toews
2010-02-11 19:54:31
Access is not a real database server, just a bunch of client Windows applications sharing a file on a shared device. A recipe for disaster, really.
Lluis Martinez
2010-02-11 20:05:00
A recipe for disaster? I've had dozens of clients using Access apps with Jet data files for almost 15 years, and all but one have not lost a single byte of data (and in that case, it was because of unmaintained backup routines, which can bite you with a server db engine, too). Stop the Access/Jet/ACE bashing, @Lluis Martinez -- it makes you look either ignorant or childish (or, perhaps, both).
David-W-Fenton
2010-02-11 21:15:41
And I've had more than dozens that have lost everything. We had hundreds of customers running a multiprocess application. We got lots of call with corrupt databases after software or system crashes. So your mileage may vary. With Firebird we just don't get these calls.
Kevin Gale
2010-02-11 21:36:41
I also modified to answer to not say it becomes corrupt every time the computer crashes.
Kevin Gale
2010-02-11 21:42:58
If you've lost data, then you didn't set things up properly in the first place, and it's your fault. It's really that simple.
David-W-Fenton
2010-02-12 17:53:00
A:
How about both?
I use a CMS that stores the main XML for the content as a string in a single db field. However I do prefer MSSQL DB's over access, but our access driven website run in exactly the same way.
e.g. sId, sDescription (XML string)
Allows me to quickly pull out ALL the data without long SQL statements, and very quickly translate using XSL to xHTML for output. Bonzer!
Will Hancock
2010-02-10 12:10:27