Transfer data from Access to SQL Server by DataSet problems...

[Guest]

Hello
I need to transfer data from Access to MS SQL Server, so I craete DataSet, Fill it from Access and try to Update() it to SQL Server, but tables still empty. Why? Here is my code

<code
sqlServerConn.Open();
SqlDataAdapter modelsTableAdapter = new SqlDataAdapter("SELECT * FROM AS_PARKERS_MODELS", sqlServerConn)
DataSet myDataSet = new DataSet(); // fill DataSet from SQL Server (really no data is here
modelsTableAdapter.Fill(myDataSet, "AS_PARKERS_MODELS")

// MS Access adapte
OleDbDataAdapter modelsAccessAdapter = new OleDbDataAdapter("SELECT * FROM models", accessConn)
// Fill the same DataSet from Access
modelsAccessAdapter.Fill(myDataSet, "AS_PARKERS_MODELS");
// try to save changes to SQL Serve
modelsTableAdapter.Update(myDataSet, "AS_PARKERS_MODELS")
sqlServerConn.Close()
</code>

 

[Cowboy \(Gregory A. Beamer\)]

Unless this is a learning exercise, I would use DTS from SQL Server and move
the data that way, as it is easier. If you need to do the same transform
more than once, you can save it: in SQL Server, as a file or as a VB6 source
file.

As far as the method you are going at, you need to get the data from the
Access DataSet to the SQL DataSet. Simply naming them the same name is not
enough.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

Hello,
I need to transfer data from Access to MS SQL Server, so I craete DataSet,

Fill it from Access and try to Update() it to SQL Server, but tables still
empty. Why? Here is my code:

<code>
sqlServerConn.Open();
SqlDataAdapter modelsTableAdapter = new SqlDataAdapter("SELECT * FROM

AS_PARKERS_MODELS", sqlServerConn);

 

[Jan]

Anton,

Insert
modelsTableAdapter.AcceptChangesDuringFill = false
before
modelsTableAdapter.Update(myDataSet, "AS_PARKERS_MODELS"

Otherwise Update will not update anything.

Regards,
Jan

Hello,
I need to transfer data from Access to MS SQL Server, so I craete DataSet,

Fill it from Access and try to Update() it to SQL Server, but tables still
empty. Why? Here is my code:

<code>
sqlServerConn.Open();
SqlDataAdapter modelsTableAdapter = new SqlDataAdapter("SELECT * FROM

AS_PARKERS_MODELS", sqlServerConn);