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

ms-accesssql-server-ce

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 Answer

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 Solutions

Sql – Best way of upgrading SQL Server Compact Edition 3.5 database

Try Wizardby: it allows you to specify database changes incrementally and it will apply these changes in a very controlled manner. You'll have to write an MDL file and distribute it with your application along with Wizardby binaries, and at application startup it will check whether database version is up to date and if not it will apply all necessary changes in a transaction (luckily, SQL Server CE supports DDL transactions).

Sql – iBatis.Net SQL Server Compact Edition 3.5

Well I spent a day or so working on this for a pet project. I was able to get this working with Sql Server Compact 3.5 and iBatis.Net 1.6.2 on .Net Framework 4.0. If you wanted it to run on a different version you'll need to update the Version and PublicKeyToken of the parameterDbTypeClass attribute.

You just need to add the following to your provider.config file.

  <provider
      name="sqlServerCompact3.5"
       enabled="true"
       description="Microsoft SQL Server Compact 3.5, provider V3.5.1.0 in framework .NET V2.0"
       assemblyName="System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
       connectionClass="System.Data.SqlServerCe.SqlCeConnection"
       commandClass="System.Data.SqlServerCe.SqlCeCommand"
       parameterClass="System.Data.SqlServerCe.SqlCeParameter"
       parameterDbTypeClass="System.Data.SqlDbType, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
       parameterDbTypeProperty="SqlDbType"
       dataAdapterClass="System.Data.SqlServerCe.SqlCeDataAdapter"
       commandBuilderClass=" System.Data.SqlServerCe.SqlCeCommandBuilder"
       usePositionalParameters = "false"
       useParameterPrefixInSql = "true"
       useParameterPrefixInParameter = "true"
       parameterPrefix="@"
       allowMARS="false"
  />

Best Answer

Related Solutions

Sql – Best way of upgrading SQL Server Compact Edition 3.5 database

Sql – iBatis.Net SQL Server Compact Edition 3.5

Related Topic