Solved

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

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

810 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