Geek Answers Handbook

Updating related tables in an MS Access database using C # programmatically

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

Two questions:

  • How to update table contents (related tables in fooDb should be synchronized with table contents in barDb )
  • How to link table with another barDb using ADO.NET

I googled but did not get useful results. I found out how to do this in VB (6) and DAO, but I need a solution for C #.

+4
source share
2 answers

If you are coding in C #, then Access is not involved, but only Jet. Thus, you can use any method that you want to access the data to, and then encode updates.

I have encoded such things in Access many times, and my approach for each table:

  • run a query that removes from fooDB that no longer exists in barDB.

  • run a query that inserts into fooDB entries that are in barDB that do not yet exist in fooDB.

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

The third is heavy. I look at a collection of fields in a DBA and write SQL on the fly, which 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 will need to use the available SQL dialect function to convert Null to zero. Running Jet SQL, I would use Nz (), of course, but this does not work through ODBC. Not sure if it will work with OLEDB.

In any case, the task is to release a bunch of columns along the columns of SQL queries, rather than trying to do this row by row, which will be much less efficient.

+2
source

Here is my solution for repeated DAO tables using C #.

My application uses a central MS Access database and 8 actual databases that are referenced. The central database is stored locally in my C # application, but the application allows you to host 8 databases elsewhere. On launch, my C # application overwrites the DAO tables in the central database based on the app.config options.

In addition, this database structure is the result of the fact that my application was originally an MS Access App, which I ported to VB6. I am currently converting my application to C #. I could refuse MS Access in VB6 or C #, but this is a very simple solution for desktop computers.

In a central database, I created a table called linkedtables with three columns, TableName, LinkedTableName, and DatabaseName.

At the beginning of the application, I call this procedure

  Common.RelinkDAOTables(Properties.Settings.Default.DRC_Data , Properties.Settings.Default.DRC_LinkedTables , "SELECT * FROM LinkedTables");

Default.DRC_Data - current central access folder DB Default.DRC_LinkedTables - Current folder of 8 databases

Here is the code, is it possible to repeat DAO tables in C #

  public static void RelinkDAOTables(string MDBfile, string filepath, string sql) { DataTable linkedTables = TableFromMDB(MDBfile, sql); dao.DBEngine DBE = new dao.DBEngine(); dao.Database DB = DBE.OpenDatabase(MDBfile, false, false, ""); foreach (DataRow row in linkedTables.Rows) { dao.TableDef table = DB.TableDefs[row["Name"].ToString()]; table.Connect = string.Format(";DATABASE={0}{1} ;TABLE={2}", filepath, row["database"], row["LinkedName"]); table.RefreshLink(); } }

Extra code written to retrieve data from an access database and return it as a DataTable

  public static DataTable TableFromOleDB(string Connectstring, string Sql) { try { OleDbConnection conn = new OleDbConnection(Connectstring); conn.Open(); OleDbCommand cmd = new OleDbCommand(Sql, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); return table; } catch (OleDbException) { return null; } } public static DataTable TableFromMDB(string MDBfile, string Sql) { return TableFromOleDB(string.Format(sConnectionString, MDBfile), Sql); }
+5
source

More articles:


All Articles