Synchronize Question

  • Thread starter Thread starter slonocode
  • Start date Start date
S

slonocode

I have a situation where I have read only access to an Access database.
I need to query the database and insert the rows retrieved into my own
database. However there is no gaurantee that the original query won't
return rows that I've already inserted into my own database.

So my question is what is the best way to handle the situation? Just
trap the errors when the insert fails? Query my own database for
primary keys and then discard those rows from the original query? Check
each row of the original query for existance in my own database before
attempting to insert?

This is a desktop application which is basically single user so
performance isn't of the utmost priority. I'm basically looking for a
best practice approach to this problem.

Thanks
Slonocode
 
Hi,

I guess you might create a stored procedure (Query in access) that checks
the existance before inserting data or check it with another OleDbCommand
before inserting.
I would really avoid generating errors.
 
Assuming that the primary key values are identical between Access and SQL
(i.e. Access.ID 1 = SQL.ID = 1, Access.ID 2 = SQL.ID 2, etc.), you can create
one dataset for each database and then merge the two values.

1. Fill two datasets (AccessDataSet and SqlDataSet) with the values from
your tables.
2. Set the Datatable's PrimaryKey to be the column containing your ID on
each DataTable.
3. Merge Access into Sql ... SqlDataSet.Merge(AccessDataSet)
4. Update the Sql Database using the DataAdapter.Update method.

The merge method should add any rows in access that aren't in sql to (with
RowState = Added) and modify any rows which are different between the two
(with RowState = Modified).

Paul
 
Back
Top