For so many years I have been forced to work with Microsoft Access and application using it as a local and even shared network data storage, resulting in endless problems with corrupted and bloated database files.
I was very pleased when I found out that Microsoft had finally introduced a good (and free) replacement for the light weight Access databases called SQL Server Compact Edition. As Access it is a file based database but with many of the features of it’s big brother SQL Server.
Finally testing the new database I quite quickly got more and more dissapointed, not at the database engine but the tools. When building on my first compact database using Visual Studio 2008 I almost immediately started getting errors like “The parameter is incorrect” and “The path is not of a legal form” for no particular reason. After googling for help I found out that the only solution is to restart Visual Studio, every time! Also the useability leaves much to hope for as now simple stuff like setting up relations and indexes requires switching between numerous dialogs.
Hoping the SQL Server Management Studio would provide better tools I found out the old 2005 version does not support the latest 3.5 CE database. After installing the new 2008 version, I was not surprised to see the same tools and dialogs as in Visual Studio. I guess a visual tool for defining the schema is out of the question.
I’m planning to use SQL Server Compact Edition in my next projects, hoping the engine will prove to compensate for the weak tools. You can be sure to read more about this subject in my later posts!