Not like SQL Server, which comes with ready-to-use tool (SQL Server Management Studio/Express) for importing/exporting data from/to other type of databases, SQL Server Compact is a file based database without management tool coming with it.
I did a google search that yield very few links to tools to move data in/out SQL Server Compact DB. There are a few third party tools do what you want.
However, if the said MDB database is not too big/complicated (say hundreds of tables), I'd simply manually re-create the tables in the SQL Server Compact (you can do it with SQL Server Management Studio), and then write a small EXE in VB/VB.NET/C# (or VBA in Access) to move the data in tables from MDB to the *.sdf.
The code to retrieve data from a table and update into another table would be the same except for corresponding SQL statement "SELECT..." and "INSERT INTO...". So, just write 2 subs that take a "SELECT..."/"INSERT..." as parameter, and run against each table, it'll be fairly easy.
Pseudo-code:
static main()
{
DataTable tbl=null;
using ([connection=connection to MBD])
{
tbl=GetData(connection,"Select...From tbl1...");
}
Using ([connection=Connection to SDF])
{
UpdateData(connection,tbl,"INSERT INTO tbl1...");
}
}
private DataTable GetData(OldDbConnection connection,string selectStatement)
{
DataTable tbl=DataTable();
//Create OldDbCommand with the "Select..."statement
//Create OldDbDataAdapter with the OleDbCommand
OldDbDataAdapter.Fill(tbl);
return tbl
}
private void UpdateData(OldDbConnection connection,DataTable tbl,string insertStatement)
{
//Create OldDbCommand with the Insert...statement
//Note, each inserting value in the INSERT... startement should be made as parameter
//like: insert into tbl1 (field1,field2...) VALUES (@param1,@param2...)
for each DataRow r in tbl.Rows)
{
for each DataColumn col in tbl.Columns
{
SqlCommand1.Parameters.AddWithValue("@Param1",r[col.Name]);
}
SqlCommand1.ExecuteNonQuery();
}
}
If you have quite a few MBD files to transter data from, it is worth to take a few hours to polish your code for such a tool
