Save SQL DataSet to Local MDB File

Context

My appliction uses an SQL database from which it reads my datatables at start of my application. If the application would fail to connect to the SQL DB, I have a local Ms Access .MDB file. I have a separate thread that checks if the local database is outdated.

I have a DataTable which I obtain from my SQL connection --> Verified and working I can connect to my Access database locally and read from it --> Verified and working

Issue/Question

I'm trying to update my local database by updating it with the DataTable I obtained from my SQL Connection.

public static void UpdateLocalDatabase(string strTableName, OleDbConnection MyConnection, DataTable MyTable)
{
    try
    {
        if (CreateDatabaseConnection() != null)
        {
            string strQuery = "SELECT * FROM " + strTableName;
            OleDbDataAdapter MyAdapter = new OleDbDataAdapter();
            OleDbCommandBuilder MyCommandBuilder = new OleDbCommandBuilder(MyAdapter);
            MyAdapter.SelectCommand = new OleDbCommand(strQuery, odcConnection);
            MyAdapter.UpdateCommand = MyCommandBuilder.GetUpdateCommand();
            MyConn.Open();
            MyAdapter.Update(MyTable);
            MyConn.Close();
        }
    }
    catch { }
}

If I debug this snippet, all variables are what they should be:

strTableName = the correct name for my table
MyConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyLocation;Persist Security Info=True;JET OLEDB:Database Password=MyPassword;"
MyTable = is the correct table that is also used further on by my application

This process runs through without an error and without using the catch but it does not touch my database, it just doesn't do a thing.

Am I dropping the ball here or just missing the obvious, I have no idea but I browsed many articles and apart for showing the MyAdapter.Update(), there doesn't seem to be much more to it.

Any help is welcome.

Thanks,

Kevin

Solution

Does your backup database have to be in access? because if you used SQL Compact Edition it'd be much easier to copy between the two?

Yes, it would either mean attaching it with your installer or just ensuring that all client machines have it pre-installed, it is free however.

if this is an issue then all you need to do (I think, not done it myself) would be to go to your installer projects properties, click prerequisites and then tick SQL compact so that it will be installed before your application can be used, iv done this before with other frameworks and it just pops up a box with the install shield asking whether they want to download the necessary software and its just one click then it should be done for them.

Do you need a hand on using the compact database also?

One negative by the way is it does lack some higher end features but shouldn't affect average database work

EDIT

if you will be using sql CE you can easily make the databse in VS by clicking data and new data source then following the steps making sure to put sql CE when asked

if it works, you'll end up with an .sdf database