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.

Why require ADO? If you eliminate that requirement it opens up other options, like SQLite. — afrazier, Mar 30 '12 at 00:48
Because we already have 16 years worth of code using ADO components — Jerry Dodge, Mar 30 '12 at 00:59
Here's a similar question: http://stackoverflow.com/questions/222699/which-embedded-database-to-use-in-a-delphi-application — Jerry Dodge, Mar 30 '12 at 02:25

kobik · Answer 1 · 2012-03-30T22:33:56.733

7

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.

edited Mar 30 '12 at 22:33

answered Mar 30 '12 at 00:18

kobik

21,001
4
61
121

2

If you want the users to also be able to open the database and link to it from Excel or mailmerge, Access is certainly recommended! – Birger Mar 30 '12 at 07:24
1

Access is great as long as you understand its [limitations](http://webcheatsheet.com/sql/access_specification.php) – Kenneth Cochran Mar 30 '12 at 19:24
I'm actually trying not to use Access, because 1) it has way more than I need, and 2) If anything I do not want anyone to open this database themselves to look at it. Using access makes this way too easy, I want to make it difficult for the end user to see the raw data. – Jerry Dodge Mar 30 '12 at 20:43
1

@JerryDodge, MDB files can be protected/encrypted with username + password. Having "more than I need" is better than having less ;) In the end it's your choice... I'm not working for MS. – kobik Mar 30 '12 at 21:10
To rephrase what I meant, Access is too easy to work with, it's designed for people who don't know or want to know how to write code. It's kinda like a beefed up excel. – Jerry Dodge Mar 30 '12 at 21:42
2

@JerryDodge, You are completely wrong. It is *nothing* like Excel, and has nothing to do with `Access.Application` (Building DB applications *with* Access) . You will use MS-Access to build your DB structure and DB design. Once you have done it, you have a single MDB file, and it's all with Delphi/ADO from now on... – kobik Mar 30 '12 at 22:25
^^ Metaphorically speaking, I wasn't serious – Jerry Dodge Apr 01 '12 at 17:21

score 5 · Accepted Answer · answered Mar 30 '12 at 00:26

5

You have several options for store your data in single database file.

All of them can be accesed via ADO using a ODBC or OLEDB driver. my personal recomendation is Firebird, because is free, fast, stable and had a Embedded version.

answered Mar 30 '12 at 00:26

RRUZ

134,889
20
356
483

Found this question, by the way... http://stackoverflow.com/questions/78621/what-do-i-need-to-start-using-embedded-firebird-with-delphi – Jerry Dodge Mar 30 '12 at 01:22

score 1 · Answer 3 · answered Mar 30 '12 at 19:18

This is a pretty useful comparison of a number of embedded databases. Of the ones tested these ones support (odbc), (oledb) or (both) and use a (single) file for the database:

Accuracer (odbc) (single)
NexusDB (odbc) (single v4 and newer)
Firebird (both) (single) - multiple odbc implementations and the commercial IBProvider supports three different ways to connect to the ADO components.
TurboDB (odbc) (single v4 and newer)

Note: Most of these also supply ADO.Net Providers as well.

The other's in the comparison (Advantage, ElevateDB, DBISAM and Apollo) use a file per table/index scheme.

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

3 Answers3