CODEWITHSUNDEEP
c#sql-serverms-accessconverters
Aditya Bokade
Aditya Bokade

Reputation: 1786

Copy table from SQL Server 2008 to MS Access 2007

Firstly, For Those who would like to ask WHY on earth am I DOWNSIZING from SQL SERVER to ACCESS, let me tell you the scenario. There are some PC's with very low configuration, (256 MB RAM, 2GHzProcessor) I cannot install SQL Server. Hence I want major operations to carry out on SQL server and some data retrieving and auditing work to be done on Slower machine.

So here we go: I want to copy table from SQL Server to MS Access 2007. I tried:

1)Connect to sql server, fill a datatable object by reading table from sql server.

2) Create a connection to MS Access, and use Dataadapter.Update method to update table to MS Access database.

However 2nd step is not working although its not throwing any error. Here is my code:

SqlConnection cnn = new SqlConnection(@"initial catalog=DBTempleERM;user id=aditya;password=Aditya_ravi$;Data Source=adityalappy\sqlexpress");
SqlCommand cmd = new SqlCommand("SELECT * FROM donationdetails", cnn);
cnn.Open();
System.Data.SqlClient.SqlDataAdapter sDA = new SqlDataAdapter(cmd);
DataTable donationdetails = new DataTable();
sDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sDA.Fill(donationdetails);

MessageBox.Show(donationdetails.Rows.Count.ToString());

OleDbConnection oleConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Database Password=Aditya_ravi$;Data Source=C:\dbt.accdb");
oleConn.Open();
OleDbCommand oleComm = new OleDbCommand();
OleDbDataAdapter oDA = new OleDbDataAdapter(oleComm);
OleDbCommandBuilder oCb = new OleDbCommandBuilder(oDA);
oDA.Update(donationdetails);

No error is thrown at the end of the execution, but I cannot see any records copied from SQL Server to MS Access.

I learnt that SQL Bulk copy cannot be used to copy from SQL Server to Access.

I also want to add the primary key from SQL Server table to MS Access table.

Upvotes: 1

Views: 2584

Answers (4)

amonroejj
amonroejj

Reputation: 633

Ancient question, but I'm betting the RowState of all your rows in donationdetails were Unchanged, so the DataAdapter treats them as "I don't need to do anything with this row."

Upvotes: 1

Rohini
Rohini

Reputation: 1

You can use dataset object instead of datatable.

DataSet ds=new DataSet();
sDA.Fill(ds,tablename);

oDA.Update(ds);

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89731

At this point, oDA is not connected to any table on the Access side:

oDA.Update(donationdetails);

So even though you have all the data in a DataTable, you haven't got a target to copy it into.

I don't think this is the best approach, but that's the core of why your code isn't working as it is.

Upvotes: 1

Sathya Narayanan
Sathya Narayanan

Reputation: 847

Why dont you use SSIS to do this for you.

You can create a SSIS package to copy a sql table to MS access.

If you want to initiate by .NET then create a SSIS package and call it from .NET

For details

Upvotes: 2

Related Questions