@ 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

Recommended Answers

All 2 Replies

If simply you want to import data in mssql server then you can use mssql server data import feature.

Watch this Article:
HERE
or HERE

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.