Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Trying to get rid of MS Access

I have this small personal organizer application that helps me keeping track of where my hard earned money is going. There's nothing special about this application other that it was designed to be used only by myself and it works exactly the way I think it should.

This application has been a trusty companion for the last 10 years and it needs its well deserved retirement. This is the last piece of VB6 that I have installed on my system. Since I stopped installing Visual Studio 6 years ago, this means I have been dealing with a couple of known bugs. I also have not added any new feature in a long time (maybe since the year 2000).

This year I decided to finally rewrite this app in .Net and I have an interesting choice to make. The old app uses MS Access for its database and, while I know I could very well keep using Access, I just don't want to deal with Access anymore. It's a technology from the last century and I think there must be something better out there.

A few things I need the database to support:

  • I need to be able to carry the app in a thumb drive and run in any system that only has .Net installed
  • Must be in-process (no service, sorry SQL Express, mySQL)
  • Must be as much compatible with SQL-92 as possible
  • Even better if it's supported by the popular ORM tools

After a brief research I chose a few candidates that seemed convenient: SQLce (because of my familiarity with SQL Server) and SQLite (because it's everywhere, comes on the mac, trivial to install in Linux, it's the new Rails 2.0 default database).

As I'm increasingly living in a multi-platform environment, I think I'm leaning towards SQLite, but I'll welcome other suggestions that fit in the requirements.

I'll return to this topic with my findings and overall development experience with the chosen database in the near future. For now I'll leave these useful SQLite links.


Posted 04-09-2008 10:37 PM by sergiopereira
Filed under: ,

[Advertisement]

Comments

a wrote re: Trying to get rid of MS Access
on 04-10-2008 1:14 AM

VistaDB? They offer free version for non-comercial use.

skwr wrote re: Trying to get rid of MS Access
on 04-10-2008 1:29 AM

you might want to have a quick look at VistaDB www.vistadb.net/compare_sql_compact.asp

Zdeslav Vojkovic wrote re: Trying to get rid of MS Access
on 04-10-2008 4:13 AM

SQLite is nice but it lacks some important features, see www.sqlite.org/omitted.html.

I would suggest Firebird: it is open-source, supports almost everything from SQL92 standard, can be used in process or as a service, has an OleDb provider, nice free/OSS management tools, is supported in NHibernate. I used it successfully on many applications, and it is my database of choice applications which are not hosted by ISPs. You can find more details here: http://www.firebirdsql.org

sergiopereira wrote re: Trying to get rid of MS Access
on 04-10-2008 8:39 AM

@ skwr, thanks but no, thanks :) I'm willing to put up with the Windows-onlyness of SQLce because it's something with an easy transition for me. VistaDB seems a little too esoteric for me at the moment.

sergiopereira wrote re: Trying to get rid of MS Access
on 04-10-2008 8:50 AM

@Zdeslav

I thought about Firebird too. I might be wrong, but it seemed to me that the tool support for Firebird is still not at the same level as SQLite (I mean admin tools, query builders, etc).

You know what? I might just add this third alternative after all. If I end up using an ORM that supports all three engines, this may be even easier.

SQLite's limitations are not an issue for this application but can be a serious issue for future projects. So it's good that you shared that link.

Tom Opgenorth wrote re: Trying to get rid of MS Access
on 04-10-2008 9:51 AM

Have you considered db4o?  It's GPL, with commerical licences available.

Chris Wuestefeld wrote re: Trying to get rid of MS Access
on 04-10-2008 9:57 AM

Regarding VistaDB: there is no Windows-onlyness. It's entirely managed code, and I believe that it will work in Mono. So if you're writing your app in .Net, then VistaDB will work anywhere that your app will.

Since it's almost completely compatible with SQL Server 2005, including T-SQL (much more so than SQLce), and weighs in at under 1MB, it meets your stated requirements in spades.

On the other hand, its $300 price tag puts it into a different category than your other options.

skwr wrote re: Trying to get rid of MS Access
on 04-10-2008 10:11 AM

There is a free express version of VistaDb. There is also an NHibernate dialect avail from codeplex for it.

Zdeslav Vojkovic wrote re: Trying to get rid of MS Access
on 04-10-2008 10:15 AM

As far as admin tools are considered there's free FlameRobin (which leaves a lot to be desired), but there is also the excellent personal edition of IbExpert (www.ibexpert.info/.../17387.html)

Peter Ritchie wrote re: Trying to get rid of MS Access
on 04-10-2008 10:16 AM

I'd second Tom's post; if you're greenfield, why not go object-oriented?

Wojciech Gebczyk wrote re: Trying to get rid of MS Access
on 04-10-2008 10:32 AM

If I remember SQL Express CE can be deployed by copying some native dlls.

Seems that you will be using on "desktop" computer so Entity Framework and Sync Services work even now. (Install SP1 Beta)

It has support of VS, etc.

sergiopereira wrote re: Trying to get rid of MS Access
on 04-10-2008 10:57 AM

@Tom and @Peter, I completely forgot to consider oo databases. That would indeed be a nice opportunity to try them out. There's even a LINQ provider.

On the other hand, this may slow down my plans of creating a Cocoa version of the app some day.

sergiopereira wrote re: Trying to get rid of MS Access
on 04-10-2008 11:02 AM

@ Wojciech, aren't you mixing up two different products? SQL CE can be deployed right in the application's directory. SQL Express needs a supporting windows service.

Wojciech Gebczyk wrote re: Trying to get rid of MS Access
on 04-14-2008 5:22 AM

You are right. I mean SQL Server CE 3.5. My mistake.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)