Pros and cons of the Access database engine. Life after SQLite.

Question

I asked a question a while ago about which local DB was right for my situation. I needed to access the DB from both .NET code and VB6. The overwhelming response was SQLite. However, I decided to pass on SQLite, because the only OLE DB provider for it charges royalties for every deployed copy of my software. It also requires an activation procedure to be run on every single PC.

After evaluating other options (SQL Server Compact edition - barely functional OLE DB provider, Firebird - don't want to have to pay for another driver, etc...), I've come to conclusion that the only viable choice is using .MDB files created by Microsoft Access (or the Jet engine).

I haven't used it since late 90s, so I have the following questions to those who have experience with it.

1. Have they resolved the problem where the database would corrupt every now and then.
2. Is access to the MDB from c# accomplished via the ADO.NET OLEDB Provider or is there a native solution (i can't seem to find it).
3. Is there a viable alternative to the really crappy SQL Editor in Access?

Thanks.

Answer 1

Have you considered SQL Server 2008 Express Edition (as oppose to SQL Server CE)?

1) Personally, I found that most times that Access DBs corrupted it was due to code that didn't clean up after it self, or there was a faulty Network card involved.

2)

string connectionString = @“Provider = Microsoft.Jet.OLEDB.4.0; " + 
                          @"Data Source = C:\data\northwind.mdb; " +
                          @"User Id = guest; Password = abc123”


using (OleDbConnection oleDbConnection = New OleDbConnection())
{
    oleDbConnection.ConnectionString = connectionString;

    oleDbConnection.Open();

    ...
}

3) SQL Server 2008 Express Edition

Answer 2

Rather then going "back" to Access, I'd stick with SQLite and use the System.Data.SQLite provider for SQLite data access within the .NET code.

Then I'd just create a simple COM interop .NET class for use by VB6 that wraps any required SQLite data access functionality. Finally, just reference and use it like a standard COM object from your VB6 projects.

My knowledge of Access is probably a bit dated and biased by bad experiences, but within reason I would try most other options before resorting to the Access route.

Answer 3

You could also try SQL Anywhere it runs on various OS and has a small footprint. Works for me :)

Answer 4

MDB corruption is largely due to failures that occur in client machines, file servers, and networks while the database is open. If you put the MDB on a file share this is always a risk, if on a local hard drive and used by one user the problems are much rarer.

I would not expect SQLite to be any different, and if anything worse.

Periodically running JetComp.exe (a Microsoft download) will fix many problems and compact index tables and such. Backups are important no matter what you use.

You don't need MS Access at all to use Jet MDBs. There are some 3rd party tools for designing the database schema and doing interactive queries, both command line and GUI.

Answer 5

AngryHacker asked:

Q1. Have they resolved the problem where the database would corrupt every now and then.

Er, what?

There was never any corruption problem in properly engineered apps properly deployed in properly maintained environments. I haven't seen a corrupted MDB in 3 or 4 years, and I have dozens of my apps in full-time production use by many clients in many different types of operating environments.

I think that most people who experience corruption are those who try to share an MDB file among many users (whether split or unsplit). Since you're not contemplating using Access, that's not really an issue.

Q2. Is access to the MDB from c# accomplished via the ADO.NET OLEDB Provider or is there a native solution (i can't seem to find it).

The native solution would be DAO, but that's COM, so you might not want to use that. From C#, I'd say OLEDB is your best bet, but that's not my area of expertise so take it with a grain of salt. I believe that Michael Kaplan reported that the Jet ADO/OLEDB provider was thread-safe, while DAO is not. This doesn't mean he recommended ADO/OLEDB over DAO, though, but his comments also came in an Access context, and not C#.

Q3. Is there a viable alternative to the really crappy SQL Editor in Access?

Why would you be using that when you're not actually using Access? You could use any SQL editor you like as long as you test that the SQL you write is compatible with Jet's SQL dialect.

I, for one, don't see what the issue is with Access's SQL editor (other than the inability to set the font size), but then, I write a lot of my SQL using the QBE and don't ever even look at the SQL view.

Answer 6

To answer your question regarding the really crappy SQL editor in Access - I wholeheartedly agree. The font stinks, MSAccess always badly reformats the query, it sometimes adds in metacharacters that break my SQL, and lastly but worstly, if it can't parse the SQL, it won't let you have access to it!

My solution is to use external code. I use DAO to instantiate MSAccess and can then directly edit the queries using the QueryDefs collection. It lets you do most things - create, rename, edit, etc. There are a couple of things you cannot do this way though - for example, you do not have access to the query metadata (description, hidden, etc).

External code is also great because you can build a suite of test cases, specifying expected return values, etc.