471,603 Members | 1,892 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,603 software developers and data experts.

Access table to sql server table.

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.

Feb 1 '06 #1
2 2409
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.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

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

// . . .

private void UpdateDataFromDifferentDataSourceForm_Load(object sender,
System.EventArgs e)
{
// Create the DataAdapter for the source records.
daSource = new SqlDataAdapter("SELECT * FROM Customers",
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
SqlCommandBuilder cbSource = new SqlCommandBuilder(daSource);
dsSource = new DataSet( );
// Get the schema and data for the source.
daSource.FillSchema(dsSource, SchemaType.Source, "Customers");
daSource.Fill(dsSource, "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",
ConfigurationSettings.AppSettings["Sql_Msde_ConnectString"]);
SqlCommandBuilder cbDest = new SqlCommandBuilder(daDest);
dsDest = new DataSet( );
// Get the schema and data for the destination.
daDest.FillSchema(dsDest, SchemaType.Source, "Customers");
daDest.Fill(dsDest, "Customers");
// Bind the default view of the customers table to the grid.
dataGridDest.DataSource = dsDest.Tables["Customers"].DefaultView;
}

private void updateDestButton_Click(object sender, System.EventArgs e)
{
try
{
// Create a DataSet of the added, modified, and deleted records.
DataSet dsDelta = dsSource.GetChanges(DataRowState.Added |
DataRowState.Modified | DataRowState.Deleted);
if (dsDelta != null)
// Update the destination with the delta DataSet.
daDest.Update(dsDelta, "Customers");

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

// Update the source.
daSource.Update(dsSource, "Customers");
}
catch(Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message, "Fill Destination",
MessageBoxButtons.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
Feb 1 '06 #2
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.

Feb 2 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Andrew Arace | last post: by
2 posts views Thread by egoldthwait | last post: by
5 posts views Thread by Kip | last post: by
1 post views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by MichaelMortimer | last post: by
reply views Thread by CCCYYYY | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.