none
Merge multiple Access database into one big database with c# code

    Question

  • I have multiple Access 2007 databases (accdb files) that are identical and I want to MERGE them into one using windows form using c# code but i don't know how to do it because i'm a beginner in c#?

    databases are identical but i want to keep all data into one because all datatables have only one or two rows because i use access form for questionnaires from different students in my university and they sent me their answers in database table so i must open all files one by one and i want to greate a windows form application because i am going to take some results from the answers and use the results for future presentations .

    Monday, March 18, 2013 8:51 PM

Answers

  • Hello,

    Although a sound idea to merge a better method would be to have one MS-Access database that links to other databases which is done inside of MS-Access. Using links lessens the likelyhood of data corruption and bloating of the database while the opposite opens the door to constant maintence and possibly lose of data. You might consider link tables, each table can have a different name than the one you do the linking with and queries can be done against them as if they were part of one database.


    Enjoy life

    Tuesday, March 19, 2013 9:59 AM
    Moderator
  • I'm not sure what you're trying to do, pouroutis, but I'll echo what Kevininstructor said.  Use C# for C# stuff and use Access for Access stuff.  There's no way you need C# to 'merge' Access databases, whatever that is.

    Simply use Access to connect to any number of databases, import the data, create your required/necessary queries, and pull the results into a DataGridView in C#.

    NOTE: first button_click evend pulls data from MS Access and displays it in a DataGridView; the second button_click event takes the data from the DataGridView, and writes it to an Access Table.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using Excel = Microsoft.Office.Interop.Excel;

    namespace WindowsFormsApplication6
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"", "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to Excel File\\Import_List.xls");
                using (OleDbConnection dbConnection = new OleDbConnection(strConn))
                {
                    using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dbConnection)) //rename sheet if required!
                        dbAdapter.Fill(table);
                    dataGridView1.DataSource = table;
                    int rows = table.Rows.Count;
                }
            }

            private void button2_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0;

                for (i = 0; i <= dataGridView1.RowCount - 1; i++)
                {
                    for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                    {
                        DataGridViewCell cell = dataGridView1[j, i];
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                    }
                }

                xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Excel file created , you can find the file C:\\Users\\Excel\\Documents\\csharp.net-informations.xls");

            }

            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }

        }
    }


    Ryan Shuell

    Sunday, March 24, 2013 2:23 PM

All replies

  • Hello,

    Although a sound idea to merge a better method would be to have one MS-Access database that links to other databases which is done inside of MS-Access. Using links lessens the likelyhood of data corruption and bloating of the database while the opposite opens the door to constant maintence and possibly lose of data. You might consider link tables, each table can have a different name than the one you do the linking with and queries can be done against them as if they were part of one database.


    Enjoy life

    Tuesday, March 19, 2013 9:59 AM
    Moderator
  • I'm not sure what you're trying to do, pouroutis, but I'll echo what Kevininstructor said.  Use C# for C# stuff and use Access for Access stuff.  There's no way you need C# to 'merge' Access databases, whatever that is.

    Simply use Access to connect to any number of databases, import the data, create your required/necessary queries, and pull the results into a DataGridView in C#.

    NOTE: first button_click evend pulls data from MS Access and displays it in a DataGridView; the second button_click event takes the data from the DataGridView, and writes it to an Access Table.

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using Excel = Microsoft.Office.Interop.Excel;

    namespace WindowsFormsApplication6
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                DataTable table = new DataTable();
                string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"", "C:\\Users\\Excel\\Desktop\\Coding\\DOT.NET\\Samples C#\\Export DataGridView to Excel File\\Import_List.xls");
                using (OleDbConnection dbConnection = new OleDbConnection(strConn))
                {
                    using (OleDbDataAdapter dbAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", dbConnection)) //rename sheet if required!
                        dbAdapter.Fill(table);
                    dataGridView1.DataSource = table;
                    int rows = table.Rows.Count;
                }
            }

            private void button2_Click(object sender, EventArgs e)
            {
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i = 0;
                int j = 0;

                for (i = 0; i <= dataGridView1.RowCount - 1; i++)
                {
                    for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
                    {
                        DataGridViewCell cell = dataGridView1[j, i];
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                    }
                }

                xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Excel file created , you can find the file C:\\Users\\Excel\\Documents\\csharp.net-informations.xls");

            }

            private void releaseObject(object obj)
            {
                try
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }

        }
    }


    Ryan Shuell

    Sunday, March 24, 2013 2:23 PM