Access table to sql server table.

[myPosts]

Hi all,

I have one ms access database containing some tables and empty database
in sql server.

I want to copy only tables of access dataase to sql server's database.

Is there any way to do it without copying row by row.

Can someone shed some light on it.

Any help will be truely appreciated.

thanks in advance.

[Michael Nemtsev]

Re: Access table to sql server table.

Hello myPosts,

m> I have one ms access database containing some tables and empty
m> database in sql server.
m> I want to copy only tables of access dataase to sql server's
m> database.
m> Is there any way to do it without copying row by row.
m> Can someone shed some light on it.
m> Any help will be truely appreciated.

See this codeSnippet
// Namespaces, variables, and constants
using System;
using System.Configur ation;
using System.Windows. Forms;
using System.Data;
using System.Data.Sql Client;

private DataSet dsSource, dsDest;
private SqlDataAdapter daSource, daDest;

// . . .

private void UpdateDataFromD ifferentDataSou rceForm_Load(ob ject sender,
System.EventArg s e)
{
// Create the DataAdapter for the source records.
daSource = new SqlDataAdapter( "SELECT * FROM Customers",
ConfigurationSe ttings.AppSetti ngs["Sql_ConnectStr ing"]);
SqlCommandBuild er cbSource = new SqlCommandBuild er(daSource);
dsSource = new DataSet( );
// Get the schema and data for the source.
daSource.FillSc hema(dsSource, SchemaType.Sour ce, "Customers" );
daSource.Fill(d sSource, "Customers" );
// Bind the default view of the customers table to the grid.
dataGridSource. DataSource = dsSource.Tables["Customers"].DefaultView;

// Create the DataAdapter for the destination records.
daDest = new SqlDataAdapter( "SELECT * FROM Customers",
ConfigurationSe ttings.AppSetti ngs["Sql_Msde_Conne ctString"]);
SqlCommandBuild er cbDest = new SqlCommandBuild er(daDest);
dsDest = new DataSet( );
// Get the schema and data for the destination.
daDest.FillSche ma(dsDest, SchemaType.Sour ce, "Customers" );
daDest.Fill(dsD est, "Customers" );
// Bind the default view of the customers table to the grid.
dataGridDest.Da taSource = dsDest.Tables["Customers"].DefaultView;
}

private void updateDestButto n_Click(object sender, System.EventArg s e)
{
try
{
// Create a DataSet of the added, modified, and deleted records.
DataSet dsDelta = dsSource.GetCha nges(DataRowSta te.Added |
DataRowState.Mo dified | DataRowState.De leted);
if (dsDelta != null)
// Update the destination with the delta DataSet.
daDest.Update(d sDelta, "Customers" );

// Reload the destination DataSet.
dsDest.Clear( );
daDest.Fill(dsD est, "Customers" );

// Update the source.
daSource.Update (dsSource, "Customers" );
}
catch(Exception ex)
{
MessageBox.Show ("ERROR: " + ex.Message, "Fill Destination",
MessageBoxButto ns.OK, MessageBoxIcon. Error);
}
}


---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/members/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

[myPosts]

Re: Access table to sql server table.

Hi,
thanks for your reply.

But one question, does it require customer table to be present in
destination database.

And do i need to have a datagrid for just copying table from source to
destination.

Thanks and Regards,
Archana.