locked
C# Sql server export dataTable to file access mdb RRS feed

  • Question

  • Hi friends,

    I search a technic to save SqlServer data table into Access file .mdb.

    I want do this :

    Select * form table (in sql serve) and save the table and data in Access file .mdb

    Anny one have solution.

    Thinks.

    Wednesday, June 18, 2014 10:31 AM

Answers

  • Hi samFr12,

    Try the following code:

    //Using JET.OLEDB :
    System.Data.OleDb.OleDbConnection AccessConn = new 
    System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test Files\\db1.mdb");
     
    //Using ACE.OLEDB :
    //System.Data.OleDb.OleDbConnection AccessConn = new
    //System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test Files\\db1.mdb")
    
    AccessConn.Open();
     
    //New table, using SELECT INTO
    System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn);
     
    //Existing table, using INSERT INTO 
    //Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' //[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)
    
    AccessCommand.ExecuteNonQuery();
    AccessConn.Close();
    Thanks,

    • Marked as answer by samFr12 Thursday, June 19, 2014 11:48 AM
    Thursday, June 19, 2014 3:41 AM

All replies

  • SQL Server Management Studio has "Export Data" function that can do this.
    Wednesday, June 18, 2014 10:42 AM
    Answerer
  • Think you for your answer,

    I know, but I would like do it with C # code, it is for reasons of archiving, it must be stored in a mdb file.

    Do you have an idea how to do it ?
    Thanks.
    Wednesday, June 18, 2014 10:57 AM
  • If you put a template mdb file in your project, you can just duplicate one out when you need, then use System.Data.Odbc.OdbcConnection to connect to it and dump data inside as you do in normal SQL query.

    The required connection string can be found here.

    Thursday, June 19, 2014 1:35 AM
    Answerer
  • Hi samFr12,

    Try the following code:

    //Using JET.OLEDB :
    System.Data.OleDb.OleDbConnection AccessConn = new 
    System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Test Files\\db1.mdb");
     
    //Using ACE.OLEDB :
    //System.Data.OleDb.OleDbConnection AccessConn = new
    //System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test Files\\db1.mdb")
    
    AccessConn.Open();
     
    //New table, using SELECT INTO
    System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn);
     
    //Existing table, using INSERT INTO 
    //Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ORDERS] SELECT * FROM [Orders] IN '' //[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];", AccessConn)
    
    AccessCommand.ExecuteNonQuery();
    AccessConn.Close();
    Thanks,

    • Marked as answer by samFr12 Thursday, June 19, 2014 11:48 AM
    Thursday, June 19, 2014 3:41 AM
  • Hello,

       Check this solution,

    //The connection strings needed: One for SQL and one for Access
    String accessConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\...\\test.accdb;";
    String sqlConnectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=Your_Catalog;Integrated Security=True";   
    
    //Make adapters for each table we want to export
    SqlDataAdapter adapter1 = new SqlDataAdapter("select * from Table1", sqlConnectionString);
    SqlDataAdapter adapter2 = new SqlDataAdapter("select * from Table2", sqlConnectionString);
    
    //Fills the data set with data from the SQL database
    DataSet dataSet = new DataSet();
    adapter1.Fill(dataSet, "Table1");
    adapter2.Fill(dataSet, "Table2");
    
    //Create an empty Access file that we will fill with data from the data set
    ADOX.Catalog catalog = new ADOX.Catalog();
    catalog.Create(accessConnectionString);
    
    //Create an Access connection and a command that we'll use
    OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);
    OleDbCommand command = new OleDbCommand();
    command.Connection = accessConnection;
    command.CommandType = CommandType.Text;
    accessConnection.Open();
    
    //This loop creates the structure of the database
    foreach (DataTable table in dataSet.Tables)
    {
        String columnsCommandText = "(";
        foreach (DataColumn column in table.Columns)
        {
            String columnName = column.ColumnName;
            String dataTypeName = column.DataType.Name;
            String sqlDataTypeName = getSqlDataTypeName(dataTypeName);
            columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";
        }
        columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
        columnsCommandText += ")";
    
        command.CommandText = "CREATE TABLE " + table.TableName + columnsCommandText;
    
        command.ExecuteNonQuery();
    }
    
    //This loop fills the database with all information
    foreach (DataTable table in dataSet.Tables)
    {
        foreach (DataRow row in table.Rows)
        {
            String commandText = "INSERT INTO " + table.TableName + " VALUES (";
            foreach (var item in row.ItemArray)
            {
                commandText += "'"+item.ToString() + "',";
            }
            commandText = commandText.Remove(commandText.Length - 1);
            commandText += ")";
    
            command.CommandText = commandText;
            command.ExecuteNonQuery();
        }
    }
    
    accessConnection.Close();

    -----------------------------------------
     if the reply help you mark it as your answer.
     Free No OLE C# Word PDF, ExcelPowerPoint Component(Create, Modify, Convert & Print) 

    Thursday, June 19, 2014 3:46 AM
  • Thank you friend, good job, it works perfectly^^

    SamFr12

    Thursday, June 19, 2014 11:48 AM
  • In the example given by Noodles LV, where is the function getSqlDataTypeName(dataTypeName) defined?

    Monday, June 23, 2014 3:17 PM
  • Here is a resource which will help you solve your problem.

    http://www.c-sharpcorner.com/forums/thread/165838/inserting


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, June 26, 2014 9:58 PM
  • Hi @SamFr12,

    I couldn't run my own code as it keeps flagging this method as unidentified 'getSqlDataTypeName'.
    How did you go around it please.

    Friday, September 1, 2017 6:14 AM