Sql – Connect SQL Server Compact 3.5 Database to MS Access 2003 using ADO


Is it possible to write connect and open a SQL Compact 3.5 database from within MS Access 2003? I want to be able to use MS Access 2003 to manipulate data in a SQL Compact 3.5 database. If it is possible, then what statements would be used to open the database?

Best Solution

This is just an idea and I can't confirm that it will work, but given that SQL Compact lacks an ODBC driver and you can't have linked tables, perhaps you can use an OLEDB connect string for SQL Compact as the Source Connect String of a saved QueryDef in Access. If you can get that to work you may be able to create a saved QueryDef for each table, and then utilize them as though the queries were linked tables.

I can't test it on my machine because the only OLEDB provider I have installed is Jet, and Access doesn't seem to like that.

But it might be worth a try. Possibly it's not going to work, as I can't find anywhere that anyone has done this in Access. But I don't really see why it shouldn't work.

Again, I could simply be wrong, though.

Related Question