0

@ by : O

This is topic (http://www.codeproject.com/Articles/21351/Import-Data-to-SQL-Server-from-Excel-or-Access-usi) is reactivated again which was posted by : Oxiegen for further information:

The code is corrected but the data is not getting exported from Access database to SQL. The code is as follows :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fileName As String = ""

        Dim ofd As New OpenFileDialog
        If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
            fileName = ofd.FileName
            PerformImportToSql(fileName)
        End If
    End Sub
    Private Sub PerformImportToSql(ByVal Filename As String)
        Dim table As DataTable = New DataTable

        Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Filename & ";Jet OLEDB:Database Password=pass2009;")
        Dim sqlConnection As New SqlClient.SqlConnection("Data Source=tashi\dbtestsrv; Initial Catalog=testdb; User Id=sa; Password=s@1234;")
        Try

            'Import the Access data
            accConnection.Open()

            Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM tblEmployees", accConnection)
            accDataAdapter.Fill(table)
            accConnection.Close()

            'Export to MS SQL
            sqlConnection.Open()
            Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM Employees", sqlConnection)
            Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
            sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
            sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
            sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
            sqlDataAdapter.Update(table)
            sqlConnection.Close()
        Catch ex As Exception
            If accConnection.State = ConnectionState.Open Then
                accConnection.Close()
            End If
            If sqlConnection.State = ConnectionState.Open Then
                sqlConnection.Close()
            End If
            MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
            & ex.ToString)
        End Try
    End Sub
3
Contributors
2
Replies
18
Views
3 Years
Discussion Span
Last Post by Deep Modi
0

Watch this Article:
HERE
or HERE

Is this Article Helpfull to you?
If yes, then Mark as solve else continue with comments

Edited by Deep Modi

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.