SQL Compact and MS-Access

So I saw SQL Compact 4.0 was released and I was excited for a whole hour. A data source is as useful as the tools that can connect to it. Just about everything can connect to an MS-Access database or to a SQL Server database, via Oledb, ODBC,  MS-Access linked tables, you name it.  You can even get MS-Access to connect to an XML file or a HTML table.  But HTML isn’t a real file based database and MS-Access’s JET-SQL is just different enough from TSQL to be annoying. So it would be nice to be able to have MS-Access use SQL  Compact as a datasource, esp as a linked table and not as an ADO datasource in VBA scripts.

But as far as I can tell, this just isn’t possible to do *without writing code*. MS-Access used ODBC to talk to unfamiliar datasources and AFAIK, there isn’t an ODBC driver to SQL Compact.  Googling turned up MSDASQL, but that solves the converse problem of getting an Oledb consumer to talk to ODBC. And MS-Access is an ODBC consumer.

So that leaves either importing all my data from SQL Compact into MS-Access (not too exciting) or writing VBA code, which might enable some scenarios.  This is one possibility I haven’t completely explored- MS-Access can bind to ADO recordsets, maybe including SQL Compact. It’s a possibility.


Sql Compact, it appears to be ready for use

In 3.5 it support AES encryption, which is required for use in US Federal government software development.

It can run run in ASP.NET, even though it isn’t a “supported” use.  I read this as the team that is writing it doesn’t have the resources to rigorously check and see how safe or dangerous it is to use Sql Compact in an ASP.NET environment.  Given the success of SQLite (or XML for that matter, e.g. Das Blog) I think file based DBs for small websites are a proven concept, and heck, it’s better than the alternative, given the known failures of MS-Access.

It uses SQL Sever Management Studio as the primary tool for interaction.

It supports Linq, especially if you use SQL Metal.

Some ETL tools exist.

On the otherhand, it lacks JDBC, ODBC access.  It doesn’t have the ease of import/export that MS-Access has.