?
Solved

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

Posted on 2016-11-12
5
Medium Priority
?
101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 1000 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

770 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