Solved

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

Posted on 2016-11-12
5
73 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio 2015 auto inserted code 12 72
Trying to figure out how to design a form 4 30
Access vs Access runtime 6 19
Email question 12 26
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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

697 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