What are the simplest database types which I can use with ADO?

Question

I'm looking for a way that I can keep a database in one single file, no server hosting it, and with the ability to use ADO (In delphi, specifically TADOConnection and/or TADOQuery). Please pardon my lack of terminology on this one. I'm only familiar with SQL Server databases, and nothing about any others. In fact, the only other ways I know to read/write files are Plain Text, INI, and XML. As for any official "databases", I know nothing.

So what I would like to do is keep a single file as a database, similar to how QuickBooks has a single "Company File". I should not have anything to host the data, such as SQL Server. And it needs to be compatible with ADO, so I can use simple select, update, delete, etc. It doesn't need to be so complex as to have relations, security, etc. But it does need to have some same syntax rules as SQL Server, like commands such as join, alter, distinct, etc.

I'm looking for the lightest-weight method to do so. The files need to be flexible enough to be able to copy/paste (so long as the application isn't using it), similar to an excel file. In fact, my original idea was to use Excel, as I know I can use ADO, but I also don't want to require Microsoft's excel drivers (it would have to presume that MS Office / Excel is installed on user's computer). It's obviously going to need some drivers, but I need the most standard method which is compatible everywhere.

Answer 1

You can use MS-Access MDB files. It can be used via Microsoft OLEDB Jet 4 engine (Which is build in into Windows since at least Win XP) and is perfect for local desktop DB applications, with the ability to create Tables, PKs, Indexes, Queries/Views, Transactions, Multi-User, replication, compact/repair and much more with almost perfect compatibility to MS SQL-Server SQL syntax (since MS-Access is the ancestor of MS SQL-Server).
MS-Access product (i.e MS Office) dose not have to be installed on the client machine. No extra drivers or files to install, and completely integrable with existing MS-Office products.

---

Edit: MDB files could be also Protected/Encrypted.