Update linked tables in MS Access Database with C# programatically

Question

I have two Access 2003 databases (fooDb and barDb). There are four tables in fooDb that are linked to tables in barDb.

Two questions:

• How do I update the table contents (linked tables in fooDb should be synchronized with the table contents in barDb)
• How do I re-link the table to a different barDb using ADO.NET

I googled but didn't get any helpful results. What I found out is how to accomplish this in VB(6) and DAO, but I need a solution for C#.

Answer 1

This post shows how to relink tables using ADO, which may help. With Access, you will need to run both update and append queries to synchronize the tables.

Answer 2

If you're coding in C#, then Access is not involved, only Jet. So, you can use whatever method you want to access the data and then code the updates.

I've coded this kind of thing in Access many times, and my approach for each table is:

1. run a query that deletes from fooDB that no longer exist in barDB.

2. run a query that inserts into fooDB records that are in barDB that do not yet exist in fooDB.

3. I always use code that writes on-the-fly SQL to update the fooDB table with the data from barDB.

The 3rd one is the hard one. I loop through the fields collection in DBA and write SQL on the fly that would be something like this:

UPDATE table2 INNER JOIN table1 ON table2.ID = table1.ID
SET table2.field1=table1.field1
WHERE (table2.field1 & "") <> (table1.field1 & "")

For numeric fields you'd have to use your available SQL dialect's function for converting Null to zero. Running Jet SQL, I'd use Nz(), of course, but that doesn't work via ODBC. Not sure if it will work with OLEDB, though.

In any event, the point is to issue a bunch of column-by-column SQL updates instead of trying to do it row by row, which will be much less efficient.