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

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 2

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