Solved

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

Posted on 2016-11-12
5
40 Views
Last Modified: 2016-11-14
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.

Thanks,

Tim Sprout
0
Comment
Question by:tman43
  • 2
  • 2
5 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41885283
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.
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 250 total points
ID: 41886615
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.

e.g.
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 )
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 41886669
There are two databases, however, so a single Command object couldn't do that.
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 250 total points
ID: 41886672
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.
0
 

Author Comment

by:tman43
ID: 41887236
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);

                connection.Open();
                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 });
                }
                reader.Close();
            }
//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
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now