How do I copy all the tables from an MDB database to a ACCDB database programatically using C#?

GIVEN:
5 MDB Files (Same table, same column names, same datatypes)
MS Access 2013
Visual Studio 2013 C#
FIND:
Combine the data in the MDB files into a single ACCDB database programmatically.  

I have a script written in C# that I am working on that has multiple steps and there is a step (above) in the process that I am stuck. I have to move all the tables into single database so that I can run reports on a daily basis.  I have over 2000 databases in this format so doing this programmatically is the goal.  

I can connect to the database but I cannot figure out how to get all the tables transferred to another access database (accdb).

///Connection to MDB File in Code in Console Application
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;

namespace ConsoleConnectToAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string filepath = "C:\\Users\\name\\Desktop\\beta\\sample.MDB;";
                string sourcestring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString = (sourcestring + filepath + "Persist Security Info=False;");
                connection.Open();
                string checkConnection = "Connection Successful";
                Console.WriteLine(checkConnection);
                //Copy all tables to another accdb database
				connection.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error" + ex);
           
            }

        }
    }
}

Open in new window

AdrianMEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
The programmatic is not the difficult part, the issue is maintaining references/relationships.

The logic to determine whether the

If you are lucky and identity columns in the various tables are unique among the MDB files in all tables, and no duplicate entries exist.

how many tables does each MDB have?

Getting all the data into a single table with all columns and then check if reference exists, and use the ID from that to construct the sequential ........
AdrianMEAuthor Commented:
There are 15 tables (same layout) in each database.  

I have a workaround for the relationships but I don't know if it will work yet.   I have a blank database that has the relationships defined.  I am trying to get the table data from the mdb file and append it to the master DB(blank database).

Each database table has a column that identifies which database it came from.  It should have never been split into different databases which is why I am trying to combine them.  

Can this be done in C#?
PatHartmanCommented:
Are they going to stay combined?  If this is just something you are doing to run some reports but the independent databases are still "live", then you don't need RI in the combined database since you will not be changing data there.

Is there some reason you have to do this in C#?  It would be easier (for me at least) to do it with VBA.

The simplest way (I can't help at all with C# code) would be to create the target database with tables and relationships all defined but empty.  Then as you loop through each source database, run append queries that take from the source database and append to the target.  I would create a separate query for each table.  Then all you would have to change in the code loop is the name of the source database.  You wouldn't have to worry about column and table names.

I also don't have the syntax handy because I never use it but you can qualify a table name in a query with a specific database (full path) name.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

AdrianMEAuthor Commented:
The reason I am working in C# is because this is part of a larger code that I started in C#.  If you don't mind sharing a VBA syntax later, I can at least look at the syntax and see if there is a way I can google for a C# equivalent.
arnoldCommented:
IMHO, the user needs the logic to aggregate the desperate sets of information where entry a in database a has ID 1 while in database b has id 2
One option is to restructure the new database that will have yet another unique idcolumn in each table  while maintaining as the asker seems to be the old references
table1
table1_ID, original_referenceID,name..
table2
tabled2_id,foreign_table1_ID,foreignreference_original_referenceID,description

There will inevitably be duplicates possible in name, but all the data with the preserved referencing will be preserved
once all the data is in one table, consolidating the data will be simpler likely as a means to a final original structured DB/tables.

The other way is that when data is assembled from the current DB it has to be queried to identify the existing ID column value that must be used to normalize the data.  Starting with the largest MDB file with the most records.

i.e you pull from MDB1 234,mark,anycity,anycountry.
you do not want to use 234 unless it does not exist in the existing data or is also mark from anycity anycountry.
going iteratively row by row initially from the  secondary tables, while at  the same time depending on what the data is, creating an identity/translation map
i.e. in the new DB mark has id 12. you would create a 12,234,MDB_database in a table_map_user. that way when you pull data from a table that references/identifies mark 234, your script will translate the 234,mdb_database to 12 when it adds the entry/s to the central DB.

You have to know which tables have no external dependencies as they have to be processed first, then to the next least dependent table.

......
PatHartmanCommented:
I would use the curmudgeon method since there are only 15 files.  Create an append query for each file that copies the records from the source to the target.  The only variable you need is the source database name.  You will need to use the "IN" clause.  Here's a link to the query syntax:

https://msdn.microsoft.com/en-us/library/bb177907(v=office.12).aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AdrianMEAuthor Commented:
Thanks everyone for your help.  I spent the day coding this up using the suggestions above and I have a working code.  I don't think I used best practice's to get this done but what I ended up doing was having two database connections open at the same time (I was trying to avoid that).   I couldn't figure out the syntax to store a table in a temporary location while I inserted into the other database.  

string sqlcommand = "INSERT INTO tblname IN '" + filesqlpath2 + "' SELECT * FROM tblname in '" + filesqlpath + "'";
OleDbCommand command = new OleDbCommand();
command.CommandText = sqlcommand;
command.ExecuteNonQuery();

Open in new window


I am going to move all the database names into an array and use a foreach loop to insert each one of these into the MasterDB. which is filesqlpath2.    I know there is a more elegant way of doing this but I just need a quick and dirty way of getting 2000 databases times 15 tables into a single database.
PatHartmanCommented:
You could have exported the tables as csv files and then imported them to the other database.
I am going to move all the database names into an array and use a foreach loop to insert
Why would you bother?  Why not use FSO to simply read the directories where the databases are stored?
AdrianMEAuthor Commented:
Pat,

I think it what I did might be similar to what you are suggesting (FSO) but in C#.  The is code working and all my tables have been transferred over to the MasterDB.

string[] fileArrayForMDBFiles = Directory.GetFiles(@"C:\PathofMDBs\", "*.mdb");
//Transfers all the Tables in Each MDB File Over to the Master Database
foreach (string mdbElementPath in fileArrayForMDBFiles)
{
//Code (Above) to Each mdbElementPath which is the MDB File
}

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.