Copy Data from Access to an Existing SQL Server Table

Question

0.00/5 (No votes)

See more:

this code copyies data from access to sql server table. but this code have some problems.

1- this code can not copy data from access to sql server table where have data.

my sqlserver table has some data and I want add data from access to under existing data in sql server table.

How do I add data to the existing table?

2-can not read data from access 2007 or 2010.

How do I read data from access 2007/2010

C#

OpenFileDialog openfiledialog1 = new OpenFileDialog();
         openfiledialog1.Title = "select access file";

            openfiledialog1.Filter = "Access 2003 (*.mdb)|*.mdb|Access 2007|*.accdb";
            if (openfiledialog1.ShowDialog() == DialogResult.OK)
            {

                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openfiledialog1.FileName;
                const string connectionStringDest = @"server=ahmad-pc\anfd;database = phonebook;Integrated Security = true";
                using (var sourceConnection = new OleDbConnection(connectionString))
                {
                    sourceConnection.Open();

                    var commandSourceData = new OleDbCommand("SELECT * from numberperson", sourceConnection);
                    var reader = commandSourceData.ExecuteReader();

                    using (var destinationConnection = new SqlConnection(connectionStringDest))
                    {
                        destinationConnection.Open();

                        using (var bulkCopy = new SqlBulkCopy(destinationConnection))
                        {


                            bulkCopy.ColumnMappings.Add("name", "nameperson"); //THIS A MAPPING REPLACE IT WITH YOUR NEED
                            bulkCopy.ColumnMappings.Add("family", "family1");
                            bulkCopy.DestinationTableName = "profile2";

                            try
                            {
                                bulkCopy.WriteToServer(reader);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex.Message);
                            }
                            finally
                            {
                                reader.Close();
                            }
                        }
                    }
                    MessageBox.Show("success");
                }

            }

Posted 10-Apr-13 6:45am

zxo004

Updated 10-Apr-13 6:47am

NotPolitcallyCorrect

v2

Add a Solution

Comments

[no name] 10-Apr-13 12:47pm

www.connectionstrings.com

Richard C Bishop 10-Apr-13 12:54pm

I already answered this question for you but you must have deleted that question. If "numberperson" is the name of a table in the database, you have to use the connection string for the database. You are currently using the connection string for reading a file as the one for querying the database. You need to switch those around.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-04-13T10:39:00

Steps to do:
1) create OleDbConnection and open it
2) create OleDbCommand, DataTable and fetch MS Access database data into datatable, providing column names mapping in SQL query

SQL

SELECT col1, col2, name as nameperson, family as family1, col5, col6,... colN
FROM ...

3) create SqlConnection and open it
4) copy data using bulkcopy

WARNING:
The structure and datatypes for both tables must be the same!

Example function:

VB

Function CopyData(ByVal sSQL As String, ByVal sDbName As String,  ByVal sDestTableName As String) As Integer
    Dim retVal As Integer = 0, sAccConn As String = String.Empty
    Dim oAccConn As OleDb.OleDbConnection = Nothing, oAccComm As OleDb.OleDbCommand = Nothing, oAccRdr As OleDb.OleDbDataReader = Nothing
    Dim oSqlConn As SqlClient.SqlConnection = Nothing, oSqlBC As SqlClient.SqlBulkCopy = Nothing, oSqlComm As SqlClient.SqlCommand = Nothing, oSqlRdr As SqlClient.SqlDataReader = Nothing
    Dim oDt As Data.DataTable = Nothing

    Try
        'get data from MS Access database
        sAccConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDbName & ";Jet OLEDB:Database Password=myPass;"
        oAccConn = New OleDb.OleDbConnection(sAccConn)
        oAccConn.Open()
        oAccComm = New OleDb.OleDbCommand(sSQL, oAccConn)
        oAccRdr = oAccComm.ExecuteReader()

        oSqlConn = New SqlClient.SqlConnection(sSQLConnString)
        oSqlConn.Open()
        'copy data
        oSqlBC = New SqlClient.SqlBulkCopy(oSqlConn)
        With oSqlBC
            .DestinationTableName = sDestTableName
            .WriteToServer(oAccRdr)
        End With
        oSqlComm = New SqlClient.SqlCommand("SELECT * FROM " & sDestTableName, oSqlConn)
        oSqlRdr = oSqlComm.ExecuteReader()
        oDt = New Data.DataTable(sDestTableName)
        oDt.Load(oSqlRdr)
        retVal = oDt.Rows.Count
        oDt.Dispose()

    Catch ex As OleDb.OleDbException
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "OleDb Error")
    Catch ex As SqlClient.SqlException
        MsgBox(ex.Message & vbCr & "Tabela: " & sDestTableName, MsgBoxStyle.Exclamation, "SQL Error")
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")
    Finally
        oDt.Dispose()
        oDt = Nothing
        oAccRdr = Nothing
        oAccComm.Dispose()
        oAccComm = Nothing
        If oAccConn.State = ConnectionState.Open Then oAccConn.Close()
        oAccConn.Dispose()
        oAccConn = Nothing
        oSqlComm.Dispose()
        oSqlComm = Nothing
        oSqlRdr = Nothing
        oSqlBC = Nothing
        If oSqlConn.State = ConnectionState.Open Then oSqlConn.Close()
        oSqlConn = Nothing
    End Try

    Return retVal
End Function