Writing an SQL OleDbCommand to import data from SqlServer To An Access Database

Question

I have the following code :

ADOX.Catalog cat = new ADOX.Catalog();
string pathToNewAccessDatabase = "Data Source=D:\\Data\\NewMDB.mdb";

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + pathToNewAccessDatabase + ";Jet OLEDB:Engine Type=5");

System.Data.OleDb.OleDbConnection AccessConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " + pathToNewAccessDatabase);

AccessConn.Open();
System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand("SELECT * INTO [ReportFile] FROM [Data Source=server_path; Initial Catalog=database_name; User Id=user_name; Password=pass_word;Trusted_Connection=False].[dbo.DataSourceTable]", AccessConn);

AccessCommand.ExecuteNonQuery();
AccessConn.Close();

I want to select from SQL SERVER into the ACCESS database.

Also, if the password contains the [ character, how do I escape that ?

I is quite possible to refer to either the SQL Server or the Access database in-line and update one to the other with a single sql statement : stackoverflow.com/questions/3473380/… — Fionnuala
– Fionnuala, Commented Jan 18, 2012 at 20:04
@Remou Yes, you are correct, but there are changes I have to make in SqlServer in ordr for that to work. I am a little scared I might mess up something. (like performance) — Dragos Durlut
– Dragos Durlut, Commented Jan 23, 2012 at 8:48

SLOBY · Accepted Answer · 2012-01-18 14:15:05Z

I suggest establishing an SQLConnection first to the SQL server and query your desired data into a DataTable.

using (SqlConnection conn = new SqlConnection("yourConnectionString"))
{
    using (SqlCommand comm = new SqlCommand("Select columns from targetTable", conn))
    {                    
        SqlDataReader reader = comm.ExecuteReader();
        DataTable tbl = new DataTable();
        tbl.Load(reader);
    }
}

After you have the data in your datatatable, create the query for your insert command from it by looping through the data.

string insertCommandString = string.Empty;

for (int row = 0; row < tbl.Rows.Count; row++)
{
    insertCommandString = "Insert into yourTableName(yourColumnNames) values(";
    for (int column = 0; column < tbl.Columns.Count; column++)
    {
        if(tbl.Columns[column].DataType == typeof(String))
        {
            insertCommandString += "'" + tbl.Rows[row][column].ToString() + "'";
        }
        else
        {
            insertCommandString += tbl.Rows[row][column].ToString();
        }

        if (column < tbl.Columns.Count - 1)
        {
            insertCommandString += ",";
        }
   }

   insertCommandString += ")";

   System.Data.OleDb.OleDbCommand AccessCommand = new System.Data.OleDb.OleDbCommand(insertCommandString, AccessConn);
   AccessCommand.ExecuteNonQuery();
 }

You should not escape any character from password.

Collectives™ on Stack Overflow

Writing an SQL OleDbCommand to import data from SqlServer To An Access Database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related