none
convert sql database to access .mdb file. RRS feed

  • Question

  • hi i  am trying to import sql database table to Access database .mdb file .

    i have dataset now i am trying to dataset table show in access file .mdb but its is to slow please help me to improve the speed or any other solution.

      try
                {
                    SqlConnection SQLConnection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ConnectionString);
                    String accessConnectionString = string.Format("Provider = Microsoft.Jet.OLEDB.4.0; " + "Data Source={0};" + "Jet OLEDB:Engine Type=5", fileName);

                    SqlCommand cmd = new SqlCommand("[dbo].[sp_test]", SQLConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tableName", SqlDbType.VarChar).Value = param;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet dataSet = new DataSet();
                    da.Fill(dataSet);
                    SQLConnection.Close();
                    SQLConnection.Dispose();



                    //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();
                    int i = 0;
                    //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 = SqlDbType.Text.ToString();
                            columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";
                        }

                        columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
                        columnsCommandText += ")";

                        string cellValue = "";
                        if (table.Rows.Count > 0)
                        {
                            cellValue = table.Rows[0][0].ToString();
                           
                        }
                        else
                        {
                            i = i + 1;
                            cellValue = "E" + i;
                        }
                        command.CommandText = "CREATE TABLE " + cellValue + columnsCommandText;
                        command.ExecuteNonQuery();
                    }

                    int r = 0;
                    foreach (DataTable table in dataSet.Tables)
                    {
                        string cellValue1 = "";
                        foreach (DataRow row in table.Rows)
                        {
                          
                            if (table.Rows.Count > 0)
                            {
                                cellValue1 = table.Rows[0][0].ToString();
                                // table.Columns.Remove("table");
                            }
                            else
                            {
                                r = r + 1;
                                cellValue1 = "E" + r;
                            }
                            String commandText = "INSERT INTO " + cellValue1 + " VALUES (";
                            foreach (var item in row.ItemArray)
                            {
                                string Result2 = string.Join(" ", item.ToString().Split(' ').Select(x => x.Trim('\'')));
                                commandText += "'" + Result2 + "',";
                            }
                            commandText = commandText.Remove(commandText.Length - 1);
                            commandText += ")";
                            command.CommandText = commandText;
                            command.ExecuteNonQuery();



                        }
                    }

                    accessConnection.Close();
                }

                catch (Exception ex)
                {

                    throw ex;
                }

    Wednesday, June 12, 2019 7:13 PM

All replies

  • Hi ovais71,

    Here is a similar thread maybe you can refer to.

    Copy Data from SQL Server Table to MS Access Table.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 13, 2019 6:39 AM
    Moderator