How to fill empty cells of one Microsoft Access database using C#

I have a Microsoft Access database with columns MTRS, Lat_Y, and Lon_X filled with data all cells, 680,000 rows. I have a second similar Access database with data for MTRS but empty cells for Lat_Y, and Lon_X, 1600 rows. How do I fill the Lat_Y and Lon_X cells in the second smaller database for matching values of MTRS? I typically use OleDbConnection in C# to manipulate Access databases. I am weak on Access queries. I also have ArcGIS and QGIS but have limited experience with these.


Tim Sprout
Who is Participating?
COACHMAN99Connect With a Mentor Commented:
Unless I am misunderstanding your request, it sound like this is not an 'MS Access' query issue, but a SQL one?
Either way, the solution is to write an SQL Update query where you join the two tables on a suitable unique key (MTRS), and update the fields in the second table where the keys match.

UPDATE table1 INNER JOIN table2 ON table1.MTRS = table2.MTRS SET table2.Lat_Y = [table1].[Lat_Y], table2.Lat_X = [table1].[Lat_X]
WHERE (((table2.Lat_Y)="" Or (table2.Lat_Y) Is Null) AND ((table2.Lat_X)="" Or (table2.Lat_X) Is Null));

This query should be executed by the ADO command.execute method (disregarding the returned recordset)

e.g. Set recordset = command.Execute( yourquery, Parameters, Options )
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Using C# you'd have to open a connection to the "filled" database and retrieve the data (perhaps into a Datatable), and then open a second connection to the "empty" database and update the records in that database, based on the data you retrieved from the "filled" database.

I don't use C# so cannot provide you with exact code, but that's the basic process.
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
There are two databases, however, so a single Command object couldn't do that.
COACHMAN99Connect With a Mentor Commented:
Got it (and missed that) :-)
you are correct. clear then fill a temp table in the destination database then run the UPDATE command on those 2 tables.
tman43Author Commented:
Thanks for showing me the query logic. That will prove useful in the future. I ended up using a two ValuePair dictionary.

//create ValuePair structure
        struct ValuePair
            public string Lat_Y;
            public string Lon_X;

//create dictionary using ValuePair structure
        Dictionary<string, ValuePair> dictionary = new Dictionary<string, ValuePair>();

//read large parent database that has all cells filled and load dictionary
        private void btnDictionaryTest_Click(object sender, EventArgs e)
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "D:" + "AccessDatabase.mdb";
            using (OleDbConnection connection = new OleDbConnection(strConn))
                string strOleDb = "Select MTRS, Latitude, Longitude from tblRowMap";
                OleDbCommand command = new OleDbCommand(strOleDb, connection);

                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                    string mTRS = reader.GetString(0);
                    string lat_Y = reader.GetValue(1).ToString();
                    string lon_X = reader.GetValue(2).ToString();
                    dictionary.Add(mTRS, new ValuePair { Lat_Y = lat_Y, Lon_X = lon_X });
//read smaller child database that has empty cells that need filling (I used a DataGridView) and
//retrieve values for these keys
              if (dictionary.ContainsKey(mTRS) == true)
                  string latGrid2 = dictionary[mtrsGrid2].Lat_Y.ToString();
                  string lonGrid2 = dictionary[mtrsGrid2].Lon_X.ToString();                
//INSERT these and other required fields into third empty database
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.