@ 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
