Conversion MSAccess -> SSCe

We have plan to migrate our database from Access to SqlCe.

Here our situation:

We will never use a Pocket PC applications, only Windows desktop application.

We want to migrate the .mdb file to a .sdf file (I've seen the ADS application, but since we don't use mobile devices, it's no use)

The .sdf will be use on local client machine only.

Here's my problems:

I've use a conversion to migrate from access (http://www.primeworks-mobile.com/Downloads/DPW.html) and seem to work fine.

I updated the source code to use the SqlServerCe dll instead of OleDB.

Some of my queries work fine, but others just won't work in VS2005 using C#.

To check if the query is really wrong, I take exactly the same query and put it Query Builder from the Server Explorer in VS2005.

And the query just work fines!!! It's bring back all the records asked for, and I've got an error for the same query from my source code.

I'm using a datatable, here's the code used for filling the DataTable. I've got an error on the line adapter.FillSchema()

string connectionString = @."Data Source=" + fic_parent.CheminFichierComplet;
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter();

connection.Open();

SqlCeCommand command = new SqlCeCommand(requete, connection);

command.CommandType = CommandType.TableDirect;
adapter.SelectCommand = command;

tableResultats = new DataTable();
adapter.FillSchema(tableResultats, SchemaType.Source);
adapter.Fill(tableResultats);

// Close connection
connection.Close();
}

Error is:

Message="The specified table does not exist. [ (...) ]"
Source="SQL Server 2005 Everywhere Edition ADO.NET Data Provider"
HResult=-2147217865
NativeError=0

Here my questions:

Is it possible to have an application that will convert my Access databases to an SSCe .sdf file ?

Why I can query in Server Explorer and can't in my source code using the same sql statement ?

At which level my code isn't good, because I just replace all OleDB* --> SqlCe* in my code.

I hope that I explain clearly my problem, do not hesitate to ask me questions about this problem.

You can write an application to convert an Access database to a SQL CE database (I actually wrote DPW, so I know it is possible). DPW is an OLE DB consumer but you should also be able to achieve the same effect in .NET by using the OleDb provider for Access (you can also use it for SQL CE on the desktop) and the SqlCe provider.

I have read some questions from other users complaining that some of the supported SQL will not run on Server Explorer but will run on the device's Query Analyzer. Although I have not tested this myself (and should it prove right) then there are differences in the supported SQL of the OLE DB provider and the .NET provider. I have never experienced such a situation because all the tools I write and use are OLE DB based and I have been able to execute all documented (and some undocumented...) SQL.

So I would use the OleDb provider, just in case.

|||

I've tried the OleDB and it's won't work. I still don't know what causes this error. I even build the SQL query with the query builder of the server explorer, put it back in code and throw me the same error.

I don't know if the SqlCe .Net provider causes the problem, but now it's we cannot use SqlCe instead of Access, that's a bad thing...

|||

This is the most stupid error I've made this year(or maybe this month...), I set the commandtype to TableDirect, which MSAccess ignore, but not SSCe, I have a query string, so I have to set-up to text!

string connectionString = @."Data Source=" + fic_parent.CheminFichierComplet;
using (SqlCeConnection connection = new SqlCeConnection(connectionString))
{
SqlCeDataAdapter adapter = new SqlCeDataAdapter();

connection.Open();

SqlCeCommand command = new SqlCeCommand(requete, connection);

command.CommandType = CommandType.Text;
adapter.SelectCommand = command;

tableResultats = new DataTable();
adapter.FillSchema(tableResultats, SchemaType.Source);
adapter.Fill(tableResultats);

// Close connection
connection.Close();
}

Sorry for this mistake....

|||

Hi,

By any chance do you have a solution for this with you.

I want to import data from access database to SQL 2005 compact Edition (.sdf file) and after that I want to use this .sdf file in my C# code.

Could you spend couple of minutes for me in providing steps.

Thanks in advance .

G Sreenaiah <gsreenaiah@.msn.com>

|||I use an conversion tool http://www.primeworks-mobile.com/Downloads/DPW.html to convert access db to an SQL Ce sdf file. You just have to use a SQLCe data objects to interact with the database (like in my previous code).|||

Hi,

I found the tool provided by http://www.primeworks-mobile.com/Downloads/DPW.html won't support for SQLCe 3.0.

Even if you provide a solution for copying data from Oracle to SQLCe that is okay for at this moment.

I have the required table structure in SQLCe database file, just I have to dump data from Oracle to SQLCe.

|||

The version that I'd use work fine with SQLCe 3.0, don't know which one it was, but I know that some latest version won't work whit SSCe 3.0.

And I don't know any tool that can transfer from Oracle to SSCe. Since we have been able to transfert our database to SSCe, we don't need anymore transfert tool. You should start a new thread for asking if somebody have a better answer for you.

You can lookup the MSDN SSCe Blog : http://blogs.msdn.com/sqlservercompact/ and look for the Access Database Synchronizer (ADS ) (http://blogs.msdn.com/sqlservercompact/archive/2007/02/19/microsoft-sql-server-2005-compact-edition-access-database-synchronizer-ads-rtw.aspx).