c# - SQL Server CE two way sync with remote Access database -

i'm working on pretty special, legacy project need build app pda devices under windows mobile 6.5. devices have local database (sql server ce) supposed to sync remote database (microsoft access) whenever docked , have network access.

so local database using sql server ce works fine, can’t figure out way sync access database properly.

i read odbc , oledb unsupported under windows mobile 6.5, ressources find obsolete or have empty links, , the way found export local database relevant tables in xml in hope build vba component access import them properly. (and figure out backwards sync).

update on project , new questions

first of all, provided useful answer, , @josef saved me lot of time auto path on this thread.

so remote sql server no go security reasons (client paranoid security , won't provide me server). i'm tied sql server ce on pda , access on computer.

as sync:

• the exportation fine: i'm using multiple dataadapters , writexml method generate xml files transmitted ftp when device plugged in. files automatically imported access database. (see code @ end).

• my problem on importation: can acquire data through xml readers access-generated file. data inserted in dataset (in fact, can print data on pda screen) i can't figure out way "upsert" on pda's database. need creative way update/insert data tables if contains data same id.

i tried 2 methods, sql errors (from understood it's sql server ce doesn't handle stored procedures or t-sql). example simple query supposed update "available" flag of storage spots:

try {     sqlcedataadapter dataadapter = new sqlcedataadapter();     dataset xmldataset = new dataset();     xmldataset.readxml(localpath +@"\import.xml");      datagrid1.datasource = xmldataset.tables[1];      _conn.open();     int = 0;     (i = 0; <= xmldataset.tables[1].rows.count - 1; i++)     {         spot = xmldataset.tables[1].rows[i].itemarray[0].tostring();         is_available = convert.toboolean(xmldataset.tables[1].rows[i].itemarray[1]);          sqlcecommand importspotcmd = new sqlcecommand(@"                     if exists (select spot spots spot=@spot)                      begin                         update spots set available=@available                     end                     else                     begin                     insert spots(spot, available)                          values(@spot, @available)                     end", _conn);         importspotcmd.parameters.add("@spot", spot);         importspotcmd.parameters.add("@available", is_available);          dataadapter.insertcommand = importspotcmd;         dataadapter.insertcommand.executenonquery();     }     _conn.close(); } catch (sqlceexception sql_ex) {     messagebox.show("sql database error: " + sql_ex.message); } 

i tried query, same problem sql server ce apparently don't handle on duplicate key (i think it's mysql specific).

insert spots (spot, available)      values(@spot, @available) on duplicate key update spots set available=@available 

the code of the export method, fixed works fine still relevant wants know:

    private void exportbtn_click(object sender, eventargs e)     {          const string sqlquery = "select * storage";         const string sqlquery2 = "select * spots";         string autopath = system.io.path.getdirectoryname(system.reflection.assembly.getexecutingassembly().getname().codebase);  //get current execution directory          using (sqlceconnection _conn = new sqlceconnection(_connstring))         {             try             {                 sqlcedataadapter dataadapter1 = new sqlcedataadapter(sqlquery, _conn);                 sqlcedataadapter dataadapter2 = new sqlcedataadapter(sqlquery2, _conn);                 _conn.open();                 dataset ds = new dataset("sqlexport");                 dataadapter1.fill(ds, "stock");                 dataadapter2.fill(ds, "spots");                  ds.writexml(autopath + @"\export.xml");             }             catch (sqlceexception sql_ex)             {                 messagebox.show("sql database error: " + sql_ex.message);             }          }     } 

as access more or less stand-alone db solution recommend go full flavored sql server plus iis setup merge replication synchronisation between sql ce data , sql server data.

this described full sample code , setup in book "programming .net compact framework" paul yao , david durant (chapter 8, synchronizing mobile data).

for working sync, changes defined tables , data on server , ce device must tracked (done via guids, unique numbers) there timestamps , conflict handling has defined.

if data never changed other means on server, may track device side changes , push them access database. done app buld updates described here.

if not want go expensive way sql server, there cheaper solutions free sqlite (available ce , compact framework too) , commercial sync tool sqlite msaccess dbsync.

if experienced, may create own sqlite ms access sync tool.