import data from access database 2010 to sql server 2008

[elie.karkafy]

how i can import data from access database to my database in sql server , with a check for ducplicate each time i move data
i need the code in vb.net
any help ?

[elie.karkafy]

**For export from access database to dataview : **

 ByRef oView As DataView, _
  ByVal SelectSQL As String, ByVal WhereSQL As String)
        '
        ' Export Access table to data view
       Dim strSQL As String
        Dim oRS As DataSet
        Dim oConnOleDb As OleDbConnection
        Dim oCmdOleDb As OleDbCommand
        Dim oDAOleDb As OleDbDataAdapter
        strSQL = ""
        oCmdOleDb = Nothing
        oDAOleDb = Nothing
        oConnOleDb = New OleDbConnection("provider = Microsoft.ACE.OLEDB.12.0;Data Source = Import.accdb")
        oCmdOleDb = oConnOleDb.CreateCommand()
        'If SelectSQL.Length = 0 Then
        '    strSQL = "SELECT * FROM " & "import"
        'Else
        '    strSQL = "SELECT " & SelectSQL & " FROM " & "import"
        'End If
        'If WhereSQL.Length > 0 Then
        '    strSQL = strSQL & " WHERE " & WhereSQL
        'End If
        ' Execute
        strSQL = "select * from import "
        oCmdOleDb.CommandText = strSQL
        oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
        oRS = New DataSet
        oDAOleDb.Fill(oRS, "import")
        oView = New DataView(oRS.Tables(0))
        oConnOleDb.Close()
        oConnOleDb = Nothing
        oDAOleDb = Nothing
        oRS = Nothing
        oCmdOleDb = Nothing
        MsgBox("done")

for import to sql database

 Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
  ByRef oImportView As DataView, _
  ByVal SelectSQL As String, ByVal WhereSQL As String)
        '
        ' Import table from data view
        Dim strSQL As String
        Dim oConn As SqlConnection
        Dim oCmd As SqlCommand
        Dim oDA As SqlDataAdapter
        Dim oRS As DataSet
        Dim oView As DataView

        strSQL = ""
        oConn = New SqlConnection("Data Source=CKSQLSRV3\CKSQLSRV3;Database=importSQL;Integrated Security=true;")
        oConn.Open()
        oCmd = Nothing
        oDA = Nothing
        oCmd = oConn.CreateCommand()
        'If SelectSQL.Length = 0 Then
        '    strSQL = "SELECT * FROM " & "import"
        'Else
        '    strSQL = "SELECT " & SelectSQL & " FROM " & "import"
        'End If
        'If WhereSQL.Length > 0 Then
        '    strSQL = strSQL & " WHERE " & WhereSQL
        'End If
        ' Execute
        strSQL = "select * from import "
        oCmd.CommandText = strSQL
        oDA = New SqlDataAdapter(oCmd)
        oRS = New DataSet
        oDA.Fill(oRS, "import")
        oView = New DataView(oRS.Tables(0))
        ' Set rowstates so that rows are inserted
        oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
        ' The trick is to use CommandBuilder to create INSERT statement "automatically"
        Dim oCmdBuilder As SqlCommandBuilder
        oCmdBuilder = New SqlCommandBuilder(oDA)
        ' Finally call update to commit changes to database table  
        oDA.Update(oView.Table)
        oConn.Close()
        oConn = Nothing
        oDA = Nothing
        oRS = Nothing
        oCmd = Nothing
        MsgBox("done")

**2 buttons for export and import **

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ExportTableData("provider = Microsoft.ACE.OLEDB.12.0;Data Source = Import.accdb", "import", oView, "", "")
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        ImportTableData("Data Source=CKSQLSRV3\CKSQLSRV3;Database=importSQL;Integrated Security=true;", "import", oView, "", "")
    End Sub

Still have one problem its the duplicate of the data, how i can check when selecting that the data selected was already selected or already exsits in the sql database

any help thx

this code work 100 % so hope will help you